Pivot_longer on a file that updates daily

Asked Mar 08 '21 at 22:09

Active Mar 08 '21 at 22:44

Viewed 62 times

Trying to pivot a data frame of Covid-19 deaths imported each day from the web (Johns Hopkins data). The current file is 414 columns wide, growing by one column per day. Pivot_longer works when I specify the width by column index but triggers an error when I try last_col().

For example, this works:

CountyDeathsC <- CountyDeathsB %>% 
  pivot_longer(cols = c(4:414), names_to="Date", values_to="Cumulative Deaths") %>% 
  group_by(FIPS, Population, Combined_Key) %>% 
  mutate(Date = mdy(Date)) %>% 
  mutate(DeathsToday = `Cumulative Deaths` - dplyr::lag(`Cumulative Deaths`, 
                                                      n = 1, default = 0),
       Deaths7DayAvg = round(zoo::rollapplyr(DeathsToday, 7, mean, na.rm=TRUE, fill=NA))) %>% 
  mutate(CumDeathsPer100k = `Cumulative Deaths` / (Population / 100000))

This code (excerpt) does not:

pivot_longer(cols = c(4:last_col()), names_to="Date", values_to="Cumulative Deaths")

I get an error saying the term "last_col()" is not recognized. So it looks like I have to go in each day and manually insert the index for the last column. Or is there a better answer?

edited Mar 08 '21 at 22:44

deschen

10,012
3
27
50

asked Mar 08 '21 at 22:09

Ron Campbell

1

Could you add the data using dput or code for reading it in some way? https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example – jsv Mar 08 '21 at 22:25
2

If you want to go from column 4 to the last column, probably just use ...`c(4:ncol(.)`... – deschen Mar 08 '21 at 22:43
Or (4: length(colnames (CountyDeathsC)) – CALUM Polwart Mar 08 '21 at 22:52
Or (cols = !any_of(1:3)) – CALUM Polwart Mar 08 '21 at 22:54
1

@CALUMPolwart `cols != 1:3` will try to evaluate and throw an error. The syntax would be `cols = -(1:3)` or `cols = !(1:3)`. – LMc Mar 08 '21 at 23:00
The solution suggested by deschen works. Thanks very much! – Ron Campbell Mar 10 '21 at 00:35

Pivot_longer on a file that updates daily

0 Answers0