How to convert monthly data into daily data

General
#1

I have CPI inflation data of India and US. I have to regress this data with a dataset of frequency daily. How to convert the monthly CPI inflation data to daily CPI inflation data.

Thanks in advance.

TIME inflation_US inflation_IN
2011-01 1.631847 9.302325
2011-02 2.107585 8.823529
2011-03 2.681603 8.823529
2011-04 3.163631 9.411765
2011-05 3.568646 8.72093
2011-06 3.558828 8.620689
2011-07 3.628716 8.426967
2011-08 3.771208 8.988764
2011-09 3.868357 10.05587
2011-10 3.5252 9.392265
2011-11 3.394378 9.340659
2011-12 2.962419 6.486486
2012-01 2.925217 5.319149
2012-02 2.871099 7.567567
2012-03 2.651398 8.648648
2012-04 2.30274 10.21505
2012-05 1.704254 10.16043
2012-06 1.663994 10.05291
2012-07 1.408451 9.84456
2012-08 1.692379 10.30928
2012-09 1.991282 9.137055
2012-10 2.162344 9.59596
2012-11 1.764134 9.547739
2012-12 1.741022 11.16751
2013-01 1.594865 11.61616
2013-02 1.977924 12.0603
2013-03 1.473896 11.44279
2013-04 1.063085 10.2439
2013-05 1.361965 10.67961
2013-06 1.754417 11.05769
2013-07 1.960682 10.84906
2013-08 1.518368 10.74766
2013-09 1.184925 10.69767
2013-10 0.9636127 11.05991
2013-11 1.237072 11.46789
2013-12 1.501736 9.132421
2014-01 1.578947 7.239819
2014-02 1.126349 6.726458
2014-03 1.512203 6.696429
2014-04 1.952858 7.079646
2014-05 2.127111 7.017544
2014-06 2.072341 6.493506
2014-07 1.992329 7.234043
2014-08 1.699611 6.751055
2014-09 1.657919 6.302521
2014-10 1.66434 4.979253
2014-11 1.322355 4.115226
2014-12 0.7564933 5.85774
2015-01 -0.08934832 7.172996
2015-02 -0.0251298 6.302521
2015-03 -0.07363739 6.276151
2015-04 -0.1995174 5.785124
2015-05 -0.03993275 5.737705
2015-06 0.1237712 6.097561
2015-07 0.1695698 4.365079
2015-08 0.1950793 4.347826
2015-09 -0.03612975 5.13834
2015-10 0.1705744 6.324111
2015-11 0.5017976 6.719368
2015-12 0.7295198 6.324111
2016-01 1.373087 5.905512
2016-02 1.0178 5.533597
2016-03 0.8525362 5.511811
2016-04 1.12511 5.859375
2016-05 1.019323 6.589147
2016-06 0.9973265 6.130268
2016-07 0.8271388 6.463878
2016-08 1.062875 5.30303
2016-09 1.463784 4.135338
2016-10 1.635988 3.345725
2016-11 1.692537 2.592592
2016-12 2.074622 2.230483
2017-01 2.500042 1.858736
2017-02 2.737958 2.621723
2017-03 2.380612 2.61194
2017-04 2.19969 2.214022
2017-05 1.874878 1.090909
2017-06 1.633488 1.083032
2017-07 1.727978 1.785714
2017-08 1.938974 2.517986
2017-09 2.232964 2.888087
2017-10 2.041129 3.23741
2017-11 2.202583 3.971119
2017-12 2.109082 4
2018-01 2.070508 5.109489
2018-02 2.211795 4.744525
2018-03 2.359711 4.363636
2018-04 2.462744 3.971119
2018-05 2.801012 3.956835
2018-06 2.871548 3.928571
2018-07 2.949515 5.614035
2018-08 2.69918 5.614035
2018-09 2.276972 5.614035
2018-10 2.52247 5.226481
2018-11 2.176601 4.861111
2018-12 1.910159 5.244755
2019-01 1.551235 6.597222
2019-02 1.520135 6.968641
2019-03 1.862523 7.665505
2019-04 1.99644 8.333333
2019-05 1.790228 8.650519
2019-06 1.648485 8.591065
2019-07 1.811465 5.980066
2019-08 1.74978 6.312293
2019-09 1.711305 6.976744
2019-10 1.764043 7.615894
2019-11 2.051278 8.609271
2019-12 2.28513 9.634551
2020-01 2.486572 7.491857
#2

by far the simplest approach would be to replicate the monthly value for every day in its month.

1 Like
#3

do we have any package\code for it in R.
Thanks in advance.

#4

This is one way

library(tidyverse)
library(lubridate)
df <- tribble(~TIME,~inflation_US,~inflation_IN,
"2011-01",	1.631847,	9.302325,
"2011-02",	2.107585,	8.823529,
"2011-03",	2.681603,	8.823529,
"2011-04",	3.163631,	9.411765,
"2011-05",	3.568646,	8.72093,
"2011-06",	3.558828,	8.620689)

base_df <- df %>% mutate(dt=lubridate::ymd(paste0(TIME,"-01"))) 
all_dates <- base_df %>%select(dt) %>% 
  complete(dt=  seq.Date(min(dt), max(dt), by="day"),
           ) %>% mutate(TIME=paste(year(dt),str_pad(month(dt), 2, pad = "0"),sep = "-"))

left_join(all_dates,df,by="TIME")
1 Like
#5

Thank you very much.

#6

can i get the same result with other package/code?
I am facing problem with tidyverse. I am unable it install or upload properly. It is showing the error, your help is very much appreciated.
Thanks in advance.

install.packages("tidyverse")
WARNING: Rtools is required to build R packages but is not currently installed. Please download and install the appropriate version of Rtools before proceeding:

https://cran.rstudio.com/bin/windows/Rtools/
Installing package into ‘C:/Users/Zeeshan/OneDrive/Documents/R/win-library/3.6’
(as ‘lib’ is unspecified)
also installing the dependencies ‘rlang’, ‘tibble’, ‘broom’

There are binary versions available but the source versions are later:
binary source needs_compilation
rlang 0.4.6 0.4.7 TRUE
broom 0.5.6 0.7.0 FALSE

Binaries will be installed
trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.6/rlang_0.4.6.zip'
Content type 'application/zip' length 1129926 bytes (1.1 MB)
downloaded 1.1 MB

trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.6/tibble_3.0.2.zip'
Content type 'application/zip' length 414560 bytes (404 KB)
downloaded 404 KB

trying URL 'https://cran.rstudio.com/bin/windows/contrib/3.6/tidyverse_1.3.0.zip'
Content type 'application/zip' length 440119 bytes (429 KB)
downloaded 429 KB

package ‘rlang’ successfully unpacked and MD5 sums checked
Warning in install.packages :
cannot remove prior installation of package ‘rlang’
Warning in install.packages :
problem copying C:\Users\Zeeshan\OneDrive\Documents\R\win-library\3.6\00LOCK\rlang\libs\x64\rlang.dll to C:\Users\Zeeshan\OneDrive\Documents\R\win-library\3.6\rlang\libs\x64\rlang.dll: Permission denied
Warning in install.packages :
restored ‘rlang’
package ‘tibble’ successfully unpacked and MD5 sums checked
Warning in install.packages :
cannot remove prior installation of package ‘tibble’
Warning in install.packages :
problem copying C:\Users\Zeeshan\OneDrive\Documents\R\win-library\3.6\00LOCK\tibble\libs\x64\tibble.dll to C:\Users\Zeeshan\OneDrive\Documents\R\win-library\3.6\tibble\libs\x64\tibble.dll: Permission denied
Warning in install.packages :
restored ‘tibble’
package ‘tidyverse’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\Zeeshan\AppData\Local\Temp\RtmpM74KMy\downloaded_packages
installing the source package ‘broom’

trying URL 'https://cran.rstudio.com/src/contrib/broom_0.7.0.tar.gz'
Content type 'application/x-gzip' length 604195 bytes (590 KB)
downloaded 590 KB

  • installing source package 'broom' ...
    ** package 'broom' successfully unpacked and MD5 sums checked
    ** using staged installation
    ** R
    ** inst
    ** byte-compile and prepare package for lazy loading
    Error in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()), versionCheck = vI[[j]]) :
    namespace 'tibble' 2.1.3 is already loaded, but >= 3.0.0 is required
    Calls: ... namespaceImportFrom -> asNamespace -> loadNamespace
    Execution halted
    ERROR: lazy loading failed for package 'broom'
  • removing 'C:/Users/Zeeshan/OneDrive/Documents/R/win-library/3.6/broom'
    Warning in install.packages :
    installation of package ‘broom’ had non-zero exit status

The downloaded source packages are in
‘C:\Users\Zeeshan\AppData\Local\Temp\RtmpM74KMy\downloaded_packages’

#7

zeeshan0112, I may very well be wrong here as I don't know the details of your use case, but does the monthly inflation rate have to be converted to a daily rate in this instance?
Building off of nigrahamuk's answer:

library(tidyverse)
library(lubridate)

all_dates <- left_join(all_dates, df, by="TIME")

r_daily_perc <- function(yr_month, r_monthly_perc) {
  # Convert current month to current date
  curr_date <- ymd(paste0(yr_month, "-1"))
  # Calculate number of days in the month
  n_days <- days_in_month(curr_date)
  # Calculate daily rate (in percent)
  100 * ((1 + (r_monthly_perc / 100))^(1/n_days) - 1)
}

all_dates <- all_dates %>%
    mutate(daily_rate_US = map2_dbl(TIME, inflation_US, r_daily_perc),
    daily_rate_IN = map2_dbl(TIME, inflation_IN, r_daily_perc))

all_dates
#> # A tibble: 152 x 6
#>    dt         TIME    inflation_US inflation_IN daily_rate_US daily_rate_IN
#>    <date>     <chr>          <dbl>        <dbl>         <dbl>         <dbl>
#>  1 2011-01-01 2011-01         1.63         9.30        0.0522         0.287
#>  2 2011-01-02 2011-01         1.63         9.30        0.0522         0.287
#>  3 2011-01-03 2011-01         1.63         9.30        0.0522         0.287
#>  4 2011-01-04 2011-01         1.63         9.30        0.0522         0.287
#>  5 2011-01-05 2011-01         1.63         9.30        0.0522         0.287
#>  6 2011-01-06 2011-01         1.63         9.30        0.0522         0.287
#>  7 2011-01-07 2011-01         1.63         9.30        0.0522         0.287
#>  8 2011-01-08 2011-01         1.63         9.30        0.0522         0.287
#>  9 2011-01-09 2011-01         1.63         9.30        0.0522         0.287
#> 10 2011-01-10 2011-01         1.63         9.30        0.0522         0.287
#> # … with 142 more rows

Created on 2020-07-10 by the reprex package (v0.3.0)

1 Like
#8

how to get the complete # ... with 142 more rows displayed here.

#9

One option is to use print(all_dates, n = Inf) to print the entire table to the console, or set options(tibble.print_max = Inf) at the beginning of your code.

1 Like
#10

Take a look at the base R function approx(), which interpolates between data points.

#11

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