0

I was trying to reshape my data from long to wide format using pivot_wider() and spread() function. Is it possible to spread to wide dataframe group by multiple variables, i.e ccode and year in my dataframe?

current dataframe:

ccode industry year TO
2 agri 2000 8.3
2 agri 2001 9.2
2 mineral 2000 9.1
3 agri 2000 6.1
3 agri 2001 8.1
3 mineral 2000 9
4 agri 2000 9
4 agri 2001 5.3
4 mineral 2000 8.1
5 agri 2000 8.4
5 agri 2001 4.3
5 mineral 2000 4.2

ideal result:

ccode year agri mineral
2 2000 8.3 9.1
2 2001 9.2 NA
3 2000 6.1 9
3 2001 8.1 NA
4 2000 9 8.1
4 2001 5.3 NA
5 2000 8.4 9.1
5 2001 4.3 4.2

Thanks.

Ques
  • 41
  • 3
  • 1
    You're not actually spreading the data by multiple variables—the only one you're using to create column names is industry. Should just be a simple use-case for `spread` or `pivot_wider` – camille May 10 '21 at 01:35

1 Answers1

0

We can use pivot_wider

library(tidyr)
pivot_wider(df1, names_from = industry, values_from = TO)

-output

# A tibble: 8 x 4
#  ccode  year  agri mineral
#  <int> <int> <dbl>   <dbl>
#1     2  2000   8.3     9.1
#2     2  2001   9.2    NA  
#3     3  2000   6.1     9  
#4     3  2001   8.1    NA  
#5     4  2000   9       8.1
#6     4  2001   5.3    NA  
#7     5  2000   8.4     4.2
#8     5  2001   4.3    NA

data

df1 <- structure(list(ccode = c(2L, 2L, 2L, 3L, 3L, 3L, 4L, 4L, 4L, 
5L, 5L, 5L), industry = c("agri", "agri", "mineral", "agri", 
"agri", "mineral", "agri", "agri", "mineral", "agri", "agri", 
"mineral"), year = c(2000L, 2001L, 2000L, 2000L, 2001L, 2000L, 
2000L, 2001L, 2000L, 2000L, 2001L, 2000L), TO = c(8.3, 9.2, 9.1, 
6.1, 8.1, 9, 9, 5.3, 8.1, 8.4, 4.3, 4.2)), class = "data.frame",
row.names = c(NA, 
-12L))
akrun
  • 874,273
  • 37
  • 540
  • 662