Format Data Frame with Date Index and Column to Consolidate Row Values into Each Month Category

Question

I have a data.frame that has a date index which is replicated in the first column and has a number of different columns and corresponding rows containing data. Because the rows containing data (which are indexed to the date) can vary depending on if corresponding data had been collected sometimes there can be a row (date) with many blanks and only values for several columns. I'd like to collapse these rows into an month and year.

I.e. A row can show data for yesterday but not today for all columns because it may not have been collected yet. Just looks aesthetically messy and would rather just say "June-2020" and collapse them to remove the NA's.

Here is the data.frame's dput:

structure(list(Date = structure(c(18292, 18320, 18321, 18351, 
18352, 18382, 18413, 18427, 18428), tzone = "UTC", tclass = "Date", class = "Date"), 
    `M-o-M Change in Median Rent - AMH GA` = c(0, NA, 0, NA, 
    0, 0, 0, NA, 0), `Median Advertised Rent - AMH GA` = c(1695, 
    NA, 1695, NA, 1695, 1695, 1695, NA, 1695), `Median of Daily Rental Listings Available - AMH GA` = c(438, 
    NA, 430.5, NA, 450, 458, 385, NA, 331), `M-o-M Change in Median Rent - AMH Charlotte` = c(0, 
    NA, 0, NA, 0, 0.0272727272727273, 0, NA, 0.0324483775811208
    ), `Median Advertised Rent - AMH Charlotte` = c(1650, NA, 
    1650, NA, 1650, 1695, 1695, NA, 1750), `Median of Daily Rental Listings Available - AMH Charlotte` = c(244, 
    NA, 257, NA, 256, 270, 227, NA, 220), `M-o-M Change in Median Rent - AMH Dallas` = c(0, 
    NA, 0, NA, 0, 0, 0.0306406685236769, NA, 0), `Median Advertised Rent - AMH Dallas` = c(1795, 
    NA, 1795, NA, 1795, 1795, 1850, NA, 1850), `Median of Daily Rental Listings Available - AMH Dallas` = c(148, 
    NA, 150, NA, 166, 152.5, 131, NA, 135), `M-o-M Change in Median Rent - AMH Houston` = c(0, 
    NA, 0.0272727272727273, NA, 0, 0, 0, NA, 0), `Median Advertised Rent - AMH Houston` = c(1650, 
    NA, 1695, NA, 1695, 1695, 1695, NA, 1695), `Median of Daily Rental Listings Available - AMH Houston` = c(222, 
    NA, 223, NA, 228, 237.5, 203, NA, 189), `M-o-M Change in Median Rent - AMH Jacksonville` = c(0, 
    0.00681818181818183, NA, NA, -0.00677200902934538, 0, 0.0272727272727273, 
    NA, 0), `Median Advertised Rent - AMH Jacksonville` = c(1650, 
    1661.25, NA, NA, 1650, 1650, 1695, NA, 1695), `Median of Daily Rental Listings Available - AMH Jacksonville` = c(164, 
    179.5, NA, NA, 188, 195.5, 185, NA, 174), `M-o-M Change in Median Rent - AMH NC` = c(0, 
    NA, 0.0344827586206897, NA, 0, 0, 0.0272727272727273, NA, 
    0), `Median Advertised Rent - AMH NC` = c(1595, NA, 1650, 
    NA, 1650, 1650, 1695, NA, 1695), `Median of Daily Rental Listings Available - AMH NC` = c(365, 
    NA, 387, NA, 405, 447, 344, NA, 323), `M-o-M Change in Median Rent - AMH NV` = c(0, 
    NA, 0, 0, NA, -0.0265486725663717, 0, NA, 0.0272727272727273
    ), `Median Advertised Rent - AMH NV` = c(1695, NA, 1695, 
    1695, NA, 1650, 1650, NA, 1695), `Median of Daily Rental Listings Available - AMH NV` = c(62, 
    NA, 59, 70, NA, 71, 63, NA, 58), `M-o-M Change in Median Rent - AMH Orlando` = c(0, 
    0.0112676056338028, NA, NA, 0, 0, 0, NA, 0.0306406685236769
    ), `Median Advertised Rent - AMH Orlando` = c(1775, 1795, 
    NA, NA, 1795, 1795, 1795, NA, 1850), `Median of Daily Rental Listings Available - AMH Orlando` = c(82, 
    91.5, NA, NA, 106, 119, 117, NA, 105), `M-o-M Change in Median Rent - AMH Phoenix` = c(0, 
    NA, 0.0216172938350681, NA, 0.0258620689655173, -0.0252100840336135, 
    0.0344827586206897, 0, NA), `Median Advertised Rent - AMH Phoenix` = c(1561.25, 
    NA, 1595, NA, 1636.25, 1595, 1650, 1650, NA), `Median of Daily Rental Listings Available - AMH Phoenix` = c(130, 
    NA, 127, NA, 129, 131, 97, 85, NA), `M-o-M Change in Median Rent - AMH Raleigh` = c(0, 
    NA, 0.0290322580645161, NA, 0, 0, 0, NA, 0.0344827586206897
    ), `Median Advertised Rent - AMH Raleigh` = c(1550, NA, 1595, 
    NA, 1595, 1595, 1595, NA, 1650), `Median of Daily Rental Listings Available - AMH Raleigh` = c(91, 
    NA, 104, NA, 114, 142, 90, NA, 81), `M-o-M Change in Median Rent - AMH SoFla` = c(0, 
    -0.00869565217391299, NA, NA, 0.0233918128654971, -0.0314285714285715, 
    0.0162241887905605, NA, 0.0159651669085632), `Median Advertised Rent - AMH SoFla` = c(1725, 
    1710, NA, NA, 1750, 1695, 1722.5, NA, 1750), `Median of Daily Rental Listings Available - AMH SoFla` = c(11, 
    14, NA, NA, 11, 10, 7, NA, 7), `M-o-M Change in Median Rent - AMH Winston-Salem/Greensboro` = c(0, 
    NA, 0.0290322580645161, NA, 0, 0, 0, NA, 0), `Median Advertised Rent - AMH Winston-Salem/Greensboro` = c(1550, 
    NA, 1595, NA, 1595, 1595, 1595, NA, 1595), `Median of Daily Rental Listings Available - AMH Winston-Salem/Greensboro` = c(66, 
    NA, 68, NA, 72, 73, 71, NA, 63)), class = "data.frame", row.names = c("2020-01-31", 
"2020-02-28", "2020-02-29", "2020-03-30", "2020-03-31", "2020-04-30", 
"2020-05-31", "2020-06-14", "2020-06-15"))

Here are examples of two approaches I've tried:

test <- AMH_final_Monthly3 %>% mutate(month= month(Date), year=year(Date))
test2 <- AMH_final_Monthly3 %>%
   collapse_by("monthly") %>%
   dplyr::group_by(Date, add = TRUE)

test3 <- as.yearmon(AMH_final_Monthly3)

Help is appreciated!

score 0 · Accepted Answer · answered Jun 15 '20 at 16:29

Something like below? My settings are Dutch hence the weird month names. But I use the yearmon function from tsibble, get rid of the Date column group by the yearmonth variable and summarise all variables using sum. Now you could also use first, last, or any other function you want.

AMH_final_Monthly3 %>% 
  mutate(yearmonth = tsibble::yearmonth(Date)) %>% 
  select(-Date) %>% 
  group_by(yearmonth) %>% 
  summarise_all(.funs = "sum", na.rm = TRUE)

# A tibble: 6 x 37
  yearmonth `M-o-M Change i~ `Median Adverti~ `Median of Dail~ `M-o-M Change i~ `Median Adverti~ `Median of Dail~ `M-o-M Change i~
      <mth>            <dbl>            <dbl>            <dbl>            <dbl>            <dbl>            <dbl>            <dbl>
1  2020 jan                0             1695             438            0                  1650              244           0     
2  2020 feb                0             1695             430.           0                  1650              257           0     
3  2020 mrt                0             1695             450            0                  1650              256           0     
4  2020 apr                0             1695             458            0.0273             1695              270           0     
5  2020 mei                0             1695             385            0                  1695              227           0.0306
6  2020 jun                0             1695             331            0.0324             1750              220           0     
# ... with 29 more variables:

Format Data Frame with Date Index and Column to Consolidate Row Values into Each Month Category

1 Answers1