# How can I make my dataset continuous over time? [R]

I have a dataset for x, y dates and times.

My initial dataset:

``````x    y   date    time
1    2    1-1-01  15:00
2    5    1-1-01  17:00
3    1    1-1-01  18:00
5    7    1-1-01  21:00
2    6    1-1-01  22:00
6    3    1-1-01  23:00
9    2    2-1-01  01:00
6    1    2-1-01  04:00
.....
```

```

I want it like:

``````x    y   date    time
1    2    1-1-01  15:00
n/a n/a   1-1-01  16:00
2    5    1-1-01  17:00
3    1    1-1-01  18:00
n/a n/a   1-1-01  19:00
n/a n/a   1-1-01  20:00
5    7    1-1-01  21:00
2    6    1-1-01  22:00
6    3    1-1-01  23:00
n/a n/a   2-1-01  00:00
9    2    2-1-01  01:00
n/a n/a   2-1-01  02:00
n/a n/a   2-1-01  03:00
6    1    2-1-01  04:00
.....
```

```

How can I fill in n / a values?

I tried to use xspline function to interpolate "x" and "y"

``````plot(df[,2:1])
xspline(df[,2:1], shape=-0.3, lwd=1)
```

```

Using this graph I can find values ββfor n / a or is there another way to find values ββfor n / a?

+3

source to share

We can create another dataset with the time sequence grouped by date and join the original dataset. This can be done using `devel`

version `data.table`

. Devel version installation instructions:`here`

``````library(data.table)
DT <- setDT(df1)[, {tmp <- as.numeric(substr(time,1,2))
list(time=sprintf('%02d:00', min(tmp):max(tmp)))}, date]
df1[DT, on=c('date', 'time')]
# x  y   date  time
# 1:  1  2 1-1-01 15:00
# 2: NA NA 1-1-01 16:00
# 3:  2  5 1-1-01 17:00
# 4:  3  1 1-1-01 18:00
# 5: NA NA 1-1-01 19:00
# 6: NA NA 1-1-01 20:00
# 7:  5  7 1-1-01 21:00
# 8:  2  6 1-1-01 22:00
# 9:  6  3 1-1-01 23:00
#10:  9  2 2-1-01 01:00
#11: NA NA 2-1-01 02:00
#12: NA NA 2-1-01 03:00
#13:  6  1 2-1-01 04:00
```

```

Or if we want to create a "time" `00`

before `23`

hours, then delete lines that are NA before the first non-NA value in 'x' and 'y', and similar for lines that are NA after the last non-NA

`````` DT <- setDT(df1)[, list(time=sprintf('%02d:00', 0:23)) , date]
res <- df1[DT, on=c('date', 'time')
][,{tmp <- which(!(is.na(x) & is.na(y)))
.SD[tmp[1L]:tmp[length(tmp)]]}]
res
# x  y   date  time
#1:  1  2 1-1-01 15:00
#2: NA NA 1-1-01 16:00
#3:  2  5 1-1-01 17:00
#4:  3  1 1-1-01 18:00
#5: NA NA 1-1-01 19:00
#6: NA NA 1-1-01 20:00
#7:  5  7 1-1-01 21:00
#8:  2  6 1-1-01 22:00
#9:  6  3 1-1-01 23:00
#10:NA NA 2-1-01 00:00
#11: 9  2 2-1-01 01:00
#12:NA NA 2-1-01 02:00
#13:NA NA 2-1-01 03:00
#14: 6  1 2-1-01 04:00
```

```

I haven't read the last part. If you need to populate the NA values ββas mentioned in @bdecaf's post (and the same one I commented and removed earlier), you can use `na.approx`

from`library(zoo)`

``````library(zoo)
res[, c('x', 'y') :=lapply(.SD, na.approx), .SDcols= x:y]
#           x        y   date  time
# 1: 1.000000 2.000000 1-1-01 15:00
# 2: 1.500000 3.500000 1-1-01 16:00
# 3: 2.000000 5.000000 1-1-01 17:00
# 4: 3.000000 1.000000 1-1-01 18:00
# 5: 3.666667 3.000000 1-1-01 19:00
# 6: 4.333333 5.000000 1-1-01 20:00
# 7: 5.000000 7.000000 1-1-01 21:00
# 8: 2.000000 6.000000 1-1-01 22:00
# 9: 6.000000 3.000000 1-1-01 23:00
#10: 7.500000 2.500000 2-1-01 00:00
#11: 9.000000 2.000000 2-1-01 01:00
#12: 8.000000 1.666667 2-1-01 02:00
#13: 7.000000 1.333333 2-1-01 03:00
#14: 6.000000 1.000000 2-1-01 04:00
```

```

### data

``````df1 <- structure(list(x = c(1L, 2L, 3L, 5L, 2L, 6L, 9L, 6L), y = c(2L,
5L, 1L, 7L, 6L, 3L, 2L, 1L), date = c("1-1-01", "1-1-01", "1-1-01",
"1-1-01", "1-1-01", "1-1-01", "2-1-01", "2-1-01"), time = c("15:00",
"17:00", "18:00", "21:00", "22:00", "23:00", "01:00", "04:00"
)), .Names = c("x", "y", "date", "time"), class = "data.frame",
row.names = c(NA, -8L))
```

```
+2

source

## about getting the required table

you can do it in base r:

Data

``````in.data <- read.table(text='x    y    date    time
1    2    1-1-01  15:00
2    5    1-1-01  17:00
3    1    1-1-01  18:00
5    7    1-1-01  21:00
2    6    1-1-01  22:00
6    3    1-1-01  23:00
9    2    2-1-01  1:00
6    1    2-1-01  4:00

times <- paste0(0:23,':00')
dates <- paste0(1:2,'-1-01')
```

```

create desired table

``````all.dt <- expand.grid(date=dates,time=times)

big.data <- merge(all.dt, in.data, all.x=TRUE)
```

```

tools provided by zoo

They have many functions to solve this problem: `na.approx`

, `na.spline`

and `na.locf`

. For example.

``````library(zoo)
big.data <- within(big.data,{
x <- na.approx(x,na.rm=FALSE)
y <- na.approx(y,na.rm=FALSE)
})
```

```

big.data then contains:

``````     date  time        x        y
1  1-1-01  0:00       NA       NA
2  1-1-01  1:00       NA       NA
...
15 1-1-01 14:00       NA       NA
16 1-1-01 15:00 1.000000 2.000000
17 1-1-01 16:00 1.500000 3.500000
18 1-1-01 17:00 2.000000 5.000000
19 1-1-01 18:00 3.000000 1.000000
20 1-1-01 19:00 3.666667 3.000000
21 1-1-01 20:00 4.333333 5.000000
22 1-1-01 21:00 5.000000 7.000000
23 1-1-01 22:00 2.000000 6.000000
24 1-1-01 23:00 6.000000 3.000000
25 2-1-01  0:00 7.500000 2.500000
26 2-1-01  1:00 9.000000 2.000000
27 2-1-01  2:00 8.000000 1.666667
28 2-1-01  3:00 7.000000 1.333333
29 2-1-01  4:00 6.000000 1.000000
30 2-1-01  5:00       NA       NA
31 2-1-01  6:00       NA       NA
...
```

```
+2

source

All Articles