2

I have a data that looks like this:

SN  TimeStamp      MOTOR
1   1/27/20 18:00   0
2   1/27/20 18:02   1
3   1/27/20 18:04   0
4   1/27/20 18:05   1
5   1/27/20 18:08   0

How can make it look like this?

SN  TimeStamp      MOTOR
1   1/27/20 18:00   0
2   1/27/20 18:01   NA
3   1/27/20 18:02   1
4   1/27/20 18:03   NA
5   1/27/20 18:04   0
6   1/27/20 18:05   1
7   1/27/20 18:06   NA
8   1/27/20 18:07   NA
9   1/27/20 18:08   0

Basically, my question is how can I insert missing timestamp and assign the corresponding MOTOR values as NA?

I would be thankful if anyone could help. I just started learning R and this is giving me headache since morning.

Thanks.

OTStats
  • 1,820
  • 1
  • 13
  • 22
user11570034
  • 43
  • 5

2 Answers2

1

You can create a dataframe with a timestamp being the sequence from the min and max values of your original dataframe and then, make a left join (here using dplyr and lubridate):

library(lubridate)
library(dplyr)
df_or$TimeStamp = mdy_hm(df_or$TimeStamp) # Convert TimeStamp into appropriate date format

DF <- data.frame(TimeStamp = seq(min(df_or$TimeStamp),max(df_or$TimeStamp), by = "min"))
DF %>% left_join(., df_or, by = "TimeStamp")

            TimeStamp MOTOR
1 2020-01-27 18:00:00     0
2 2020-01-27 18:01:00    NA
3 2020-01-27 18:02:00     1
4 2020-01-27 18:03:00    NA
5 2020-01-27 18:04:00     0
6 2020-01-27 18:05:00     1
7 2020-01-27 18:06:00    NA
8 2020-01-27 18:07:00    NA
9 2020-01-27 18:08:00     0

Data

df_or <- data.frame(TimeStamp = c("1/27/20 18:00","1/27/20 18:02","1/27/20 18:04","1/27/20 18:05", "1/27/20 18:08"),
                    MOTOR = c(0,1,0,1,0))
dc37
  • 15,840
  • 4
  • 15
  • 32
  • You are awesome. It works now... Thank you so much.. You saved me tonnes of work.. – user11570034 Feb 19 '20 at 22:32
  • You're welcome ;) Take a look also at @Ben's answer, it is really interesting solution too. – dc37 Feb 19 '20 at 22:35
  • Hello bro.. I have another question. Is it possible to change 'NA' in MOTOR to the earlier answer. I mean for NA in row 2, the answer would be the same as earlier i.e. 0. For NAs at row 7 and 8, the answer would be that of row 6 which is 1. Is it possible to program like that? – user11570034 Feb 19 '20 at 22:47
  • Thanks bro. I am looking at it. But I already post the detailed question in this link. https://stackoverflow.com/questions/60310552/changing-na-values-in-a-column-to-presiding-values-until-other-binary-number-app if you have time, please look at it.. – user11570034 Feb 19 '20 at 22:58
1

Another possible approach is to use library(padr):

library(padr)
library(dplyr)

df %>%
  pad(interval = 'min') %>%
  mutate(SN = row_number())

Output

  SN           TimeStamp Motor
1  1 2020-01-27 18:00:00     0
2  2 2020-01-27 18:01:00    NA
3  3 2020-01-27 18:02:00     1
4  4 2020-01-27 18:03:00    NA
5  5 2020-01-27 18:04:00     0
6  6 2020-01-27 18:05:00     1
7  7 2020-01-27 18:06:00    NA
8  8 2020-01-27 18:07:00    NA
9  9 2020-01-27 18:08:00     0

Data

df <- data.frame(
  SN = 1:5,
  TimeStamp = as.POSIXct(c("1/27/20 18:00", "1/27/20 18:02", "1/27/20 18:04", "1/27/20 18:05", "1/27/20 18:08"), format = "%m/%d/%y %H:%M"),
  Motor = c(0,1,0,1,0)
)
Ben
  • 28,684
  • 5
  • 23
  • 45