Transforming multiple columns structure using Dplyr in R

Question

I have a dataset, df,

State         Year         0       1         2       3         4         5 

Georgia      2001         10,000   200      300      400      500        800  
Georgia      2002         20,000   500      500      1,000    2,000      2,500
Georgia      2003         2,000    5,000    1,000    400      300        8,000
Washington   2001         1,000    10,000   6,000    8,000    9,900      10,000
Washington   2006         5,000    300      200      900      1,000      8,000

I would like my desired output to look like this:

State          Year         Age         Population      

Georgia        2001         0           10,000
Georgia        2002         0           20,000
Georgia        2003         0           2,000
Georgia        2001         1           200
Georgia        2002         1           500
Georgia        2003         1           5000
Georgia        2001         2           300
Georgia        2002         2           500
Georgia        2003         2           1000
Georgia        2001         3           400
Georgia        2002         3           1000
Georgia        2003         3           400
Georgia        2001         4           500
Georgia        2002         4           2000
Georgia        2003         4           300
Georgia        2001         5           800
Georgia        2002         5           2500
Georgia        2003         5           8000
Washington     2001         0           1000
Washington     2006         0           5000
Washington     2001         1           10000
Washington     2006         1           300
Washington     2001         2           6000
Washington     2006         2           200
Washington     2001         3           8000
Washington     2006         3           900
Washington     2001         4           9900
Washington     2006         4           1000
Washington     2001         5           10000
Washington     2006         5           8200

Here is my dput

structure(list(state = structure(c(1L, 1L, 1L, 2L, 2L), .Label = c("georgia", 
"washington"), class = "factor"), year = c(2001L, 2002L, 2003L, 
2001L, 2006L), X0 = structure(c(1L, 3L, 4L, 2L, 5L), .Label = c("10,000", 
"1000", "20,000", "2000", "5000"), class = "factor"), X1 = structure(c(2L, 
4L, 5L, 1L, 3L), .Label = c("10,000", "200", "300", "500", "5000"
), class = "factor"), X2 = c(300L, 500L, 1000L, 6000L, 200L), 
 X3 = c(400L, 1000L, 400L, 8000L, 900L), X4 = c(500L, 2000L, 
300L, 99000L, 1000L), X5 = structure(c(3L, 2L, 4L, 1L, 4L
), .Label = c("10,000", "2500", "800", "8000"), class = "factor")), class = "data.frame", row.names 
 = 
   c(NA, 
-5L))

This is what I have tried:

I know that I must groupby the state and the year as well as perform some type of pivot by possibly utilizing the gather() function

library(tidyr)
library(dplyr)

df1 <- gather(df, 0, 1, 2, 3, 4, 5 factor_key=TRUE)

df %>% groupby(State, Year) %>%
mutate('Age', 'Population')

`tidyr::gather(df, key, value, -c(state, year))`. Also note that `gather` is retired and replaced by `pivot_longer`. — Ronak Shah, May 10 '20 at 01:16

score 1 · Accepted Answer · answered May 10 '20 at 18:52

We can first convert the column type to numeric by extracting the numeric part and then do the reshape

library(dplyr)
library(tidyr)
df %>% 
  mutate_at(vars(matches('\\d+$')), ~readr::parse_number(as.character(.))) %>% 
  pivot_longer(cols = -c(state, year), names_to = "Age", values_to = "Population")

Transforming multiple columns structure using Dplyr in R

1 Answers1