How do I paste data from the first day of the month for the whole month?

Maurits Published at Dev

Maurits

I have a data frame containing weights for stocks in a portfolio, it is around 1000 stocks and 4000 days of data. I want to apply the weights of the first day of each month to all days of that month. However, I still want to retain the structure of daily data.

My data is similar to this:

data <- as.data.frame(matrix(1:4000, nrow = 200, ncol = 20))
rownames(data) <- seq(as.Date("2018/01/01"), as.Date("2018/07/19"), 1)

So I want to have the values of the first of January copied to all days of January, values of the first day of February copied to all days in Februari etc. I have no clue how to handle this.

Any tips?

nurandi

You might want to use grouped dplyr::mutate_at:

library(dplyr)

data %>%
  rownames_to_column("Date") %>%
  mutate(Month = format(as.Date(Date), "%Y-%m")) %>%
  group_by(Month) %>%
  mutate_at(vars(starts_with("V")), .funs = list(weight = ~first(.))) %>%
  column_to_rownames("Date") %>%
  select(Month, starts_with("V"))

Output

#              Month V1  V2  V3  V4  V5   V6   V7   V8   V9  V10  V11  V12  V13  V14  V15  V16  V17  V18  V19  V20 V1_weight
# 2018-01-01 2018-01  1 201 401 601 801 1001 1201 1401 1601 1801 2001 2201 2401 2601 2801 3001 3201 3401 3601 3801         1
# 2018-01-02 2018-01  2 202 402 602 802 1002 1202 1402 1602 1802 2002 2202 2402 2602 2802 3002 3202 3402 3602 3802         1
# 2018-01-03 2018-01  3 203 403 603 803 1003 1203 1403 1603 1803 2003 2203 2403 2603 2803 3003 3203 3403 3603 3803         1
# 2018-01-04 2018-01  4 204 404 604 804 1004 1204 1404 1604 1804 2004 2204 2404 2604 2804 3004 3204 3404 3604 3804         1
# ...
# 2018-01-21 2018-01 21 221 421 621 821 1021 1221 1421 1621 1821 2021 2221 2421 2621 2821 3021 3221 3421 3621 3821         1
# 2018-01-22 2018-01 22 222 422 622 822 1022 1222 1422 1622 1822 2022 2222 2422 2622 2822 3022 3222 3422 3622 3822         1
# 2018-01-23 2018-01 23 223 423 623 823 1023 1223 1423 1623 1823 2023 2223 2423 2623 2823 3023 3223 3423 3623 3823         1
# 2018-01-24 2018-01 24 224 424 624 824 1024 1224 1424 1624 1824 2024 2224 2424 2624 2824 3024 3224 3424 3624 3824         1
#            V2_weight V3_weight V4_weight V5_weight V6_weight V7_weight V8_weight V9_weight V10_weight V11_weight V12_weight
# 2018-01-01       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# 2018-01-02       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# 2018-01-03       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# 2018-01-04       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# ...
# 2018-01-21       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# 2018-01-22       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# 2018-01-23       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
# 2018-01-24       201       401       601       801      1001      1201      1401      1601       1801       2001       2201
#            V13_weight V14_weight V15_weight V16_weight V17_weight V18_weight V19_weight V20_weight
# 2018-01-01       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-02       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-03       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-04       2401       2601       2801       3001       3201       3401       3601       3801
# ...
# 2018-01-20       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-21       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-22       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-23       2401       2601       2801       3001       3201       3401       3601       3801
# 2018-01-24       2401       2601       2801       3001       3201       3401       3601       3801

Collected from the Internet

Please contact [email protected] to delete if infringement.