Time series with implicit missing data

Hello,

I have hourly weather data from 42 stations for 2 months. The data set is an irregular series (See the HR/hour column). I want to add the missing rows to make it a regular series.

I am new to R, so I don't have much knowledge to complete the task.

Can anyone please help me in this regard?!
Screenshot%20(3)|690x316

Can you please share a small part of the data set in a copy-paste friendly format?

In case you don't know how to do it, there are many options, which include:

  1. If you have stored the data set in some R object, dput function is very handy.

  2. In case the data set is in a spreadsheet, check out the datapasta package. Take a look at this link.

Sorry for the late reply!

That process looks kinda lengthy. I have a time constraint to complete the project, therefore I can't afford to spend time on that.

Could you please state the solution from the attached image?

The process outlined by andresrcs is neither lengthy, nor complicated.

You are requesting people to help you. The bare minimum you are requested to do is to present your problem and the data in a convenient manner.

1 Like

Dear all,

I sincerely apologize for my rudeness!

I've written a few commands to complete the task.

df = read.csv(file = "clipboard", sep = ",", header = TRUE)

df[1:50,]

#> STATE STATION ABBR LAT LON ELEV YEAR MN DT HR MSLP DBT DPT
#> 1 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 0 1015.0 8.1 -999.0
#> 2 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 1 1015.4 7.8 -999.0
#> 3 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 2 1016.1 7.3 -999.0
#> 4 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 3 -999.0 -999.0 -999.0
#> 5 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 4 1016.9 15.4 -999.0
#> 6 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 5 -999.0 -999.0 -999.0
#> 7 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 6 1016.3 20.7 -999.0
#> 8 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 7 1015.2 22.2 -999.0
#> 9 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 8 1013.8 23.9 25.3
#> 10 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 9 1013.4 24.2 -999.0
#> 11 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 10 1012.9 24.1 -999.0
#> 12 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 11 1012.8 23.3 -999.0
#> 13 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 12 -999.0 -999.0 -999.0
#> 14 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 13 1013.9 14.2 -999.0
#> 15 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 14 1014.5 12.6 -999.0
#> 16 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 15 1015.3 11.5 -999.0
#> 17 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 17 1015.9 10.2 -999.0
#> 18 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 1 23 1016.4 8.2 -999.0
#> 19 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 0 1016.6 7.3 -999.0
#> 20 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 1 1017.4 7.1 -999.0
#> 21 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 2 1018.0 7.5 -999.0
#> 22 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 3 -999.0 -999.0 -999.0
#> 23 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 4 1018.6 14.9 -999.0
#> 24 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 5 1018.6 17.9 -999.0
#> 25 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 6 1018.1 20.4 -999.0
#> 26 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 7 1016.9 22.7 -999.0
#> 27 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 9 1015.0 24.1 17.6
#> 28 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 10 1014.3 24.2 -999.0
#> 29 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 11 1014.3 23.1 -999.0
#> 30 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 12 -999.0 -999.0 -999.0
#> 31 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 13 1015.8 14.0 -999.0
#> 32 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 14 1016.2 13.0 -999.0
#> 33 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 15 1016.7 12.5 -999.0
#> 34 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 18 1017.6 9.8 -999.0
#> 35 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 20 1017.2 9.3 -999.0
#> 36 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 21 1017.0 8.9 -999.0
#> 37 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 2 23 1016.8 8.0 -999.0
#> 38 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 0 1017.2 8.0 -999.0
#> 39 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 2 1018.1 7.4 -999.0
#> 40 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 3 -999.0 -999.0 -999.0
#> 41 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 4 1018.7 13.9 -999.0
#> 42 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 5 -999.0 -999.0 -999.0
#> 43 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 6 1018.0 20.5 -999.0
#> 44 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 8 1015.5 22.8 -999.0
#> 45 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 9 1014.7 23.2 -999.0
#> 46 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 10 1014.2 22.7 -999.0
#> 47 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 11 1014.1 21.9 -999.0
#> 48 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 12 -999.0 -999.0 -999.0
#> 49 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 13 1015.0 14.0 -999.0
#> 50 PUNJAB AMRITSAR AMT 31.6 75.8 226 2017 12 3 14 1015.6 12.6 -999.0
#> RH RRR DD FF X
#> 1 -999 0 -999 -999 NA
#> 2 -999 -999 -999 -999 NA
#> 3 -999 -999 -999 -999 NA
#> 4 -999 -999 -999 -999 NA
#> 5 -999 0 -999 -999 NA
#> 6 -999 0 -999 -999 NA
#> 7 -999 0 -999 -999 NA
#> 8 -999 0 -999 -999 NA
#> 9 109 -999 -999 -999 NA
#> 10 -999 -999 -999 -999 NA
#> 11 -999 -999 -999 -999 NA
#> 12 -999 -999 -999 -999 NA
#> 13 -999 -999 -999 -999 NA
#> 14 -999 0 -999 -999 NA
#> 15 -999 0 -999 -999 NA
#> 16 -999 0 -999 -999 NA
#> 17 -999 0 -999 -999 NA
#> 18 -999 -999 -999 -999 NA
#> 19 -999 0 -999 -999 NA
#> 20 -999 -999 -999 -999 NA
#> 21 -999 -999 -999 -999 NA
#> 22 -999 -999 -999 -999 NA
#> 23 -999 0 -999 -999 NA
#> 24 -999 0 -999 -999 NA
#> 25 -999 0 -999 -999 NA
#> 26 -999 0 -999 -999 NA
#> 27 67 -999 -999 -999 NA
#> 28 -999 -999 -999 -999 NA
#> 29 -999 -999 -999 -999 NA
#> 30 -999 -999 -999 -999 NA
#> 31 -999 0 -999 -999 NA
#> 32 -999 0 -999 -999 NA
#> 33 -999 0 -999 -999 NA
#> 34 -999 0 -999 -999 NA
#> 35 -999 0 -999 -999 NA
#> 36 -999 0 -999 -999 NA
#> 37 -999 0 -999 -999 NA
#> 38 -999 0 -999 -999 NA
#> 39 -999 0 -999 -999 NA
#> 40 -999 0 -999 -999 NA
#> 41 -999 0 -999 -999 NA
#> 42 -999 0 -999 -999 NA
#> 43 -999 0 -999 -999 NA
#> 44 -999 -999 -999 -999 NA
#> 45 -999 -999 -999 -999 NA
#> 46 -999 0 -999 -999 NA
#> 47 -999 0 -999 -999 NA
#> 48 -999 0 -999 -999 NA
#> 49 -999 0 -999 -999 NA
#> 50 -999 0 -999 -999 NA

df1 = read.csv(file = "clipboard", sep = ",", header = TRUE)

df1[1:50,]

#> DT HR
#> 1 1 0
#> 2 2 1
#> 3 3 2
#> 4 4 3
#> 5 5 4
#> 6 6 5
#> 7 7 6
#> 8 8 7
#> 9 9 8
#> 10 10 9
#> 11 11 10
#> 12 12 11
#> 13 13 12
#> 14 14 13
#> 15 15 14
#> 16 16 15
#> 17 17 16
#> 18 18 17
#> 19 19 18
#> 20 20 19
#> 21 21 20
#> 22 22 21
#> 23 23 22
#> 24 24 23
#> 25 25 0
#> 26 26 1
#> 27 27 2
#> 28 28 3
#> 29 29 4
#> 30 30 5
#> 31 31 6
#> 32 1 7
#> 33 2 8
#> 34 3 9
#> 35 4 10
#> 36 5 11
#> 37 6 12
#> 38 7 13
#> 39 8 14
#> 40 9 15
#> 41 10 16
#> 42 11 17
#> 43 12 18
#> 44 13 19
#> 45 14 20
#> 46 15 21
#> 47 16 22
#> 48 17 23
#> 49 18 0
#> 50 19 1

merge(x = df, y = df1, by ="HR")

#> Error: cannot allocate vector of size 447.8 Mb

My logic behind the code is:

I have a data frame (df) with irregular time series with discontinuous date (not visible in this sample) and hour values (See DT and HR column) which I want to make a regular one. For that. I have created a separate df1 which includes the continuous date and hour data (1-31 and 0-23). I'm using the merge command (by = "HR") of base R to add the missing rows. But, I'm getting an error. I don't know why it is creating a big sized vector.

Does anyone have any idea where I'm going wrong?

Thanks!

The error indicates that your RAM has been exhausted.

The first thing to try is to run from a clean session and only to load the objects you require. Memory can get clogged up if you have been running many things in the same session.

You could also close any other applications which are consuming large amounts of RAM.

If that doesn't work then you could use the far more memory efficient data.table package:
https://cran.r-project.org/web/packages/data.table/index.html

Otherwise you could look to install more RAM or run off a cloud server.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.