CODEWITHSUNDEEP
rdataframedplyrreshape
J.Doe
J.Doe

Reputation: 165

Reshape table using column values as column names?

I am trying to reshape a table. The below table was created from using this code on a df:

df2 <- df %>% 
    group_by(Organization, Year) %>% 
    tally()
Organization Year N
X 1999 3
X 2010 3
Y 2009 4
Y 2010 5
Y 2011 5
Z 2008 5
Z 2011 5

What I'm trying to create:

Organization 1999 2008 2009 2010 2011
X 3 0 0 3 0
Y 0 0 4 5 5
Z 0 5 0 0 5

Where it will only take the existing years and not create new years in-between (like the gap between 1999 and 2008) and it will fill in 0's as needed.

Upvotes: 1

Views: 454

Answers (2)

B. Christian Kamgang
B. Christian Kamgang

Reputation: 6489

You can also use the function dcast in the data.table as follows:

library(data.table)
dcast(as.data.table(df), Organization ~ Year, value.var="N", fill=0)

#    Organization  1999  2008  2009  2010  2011
# 1:            X     3     0     0     3     0
# 2:            Y     0     0     4     5     5
# 3:            Z     0     5     0     0     5

Upvotes: 1

akrun
akrun

Reputation: 887118

We need pivot_wider after arrangeing the rows by 'Year'. With pivot_wider, it uses the same order of occurrence of data

library(dplyr)
library(tidyr)
df %>%
     arrange(Year) %>%
     pivot_wider(names_from = Year, values_from = N, values_fill = 0)

-output

# A tibble: 3 x 6
  Organization `1999` `2008` `2009` `2010` `2011`
  <chr>         <int>  <int>  <int>  <int>  <int>
1 X                 3      0      0      3      0
2 Z                 0      5      0      0      5
3 Y                 0      0      4      5      5

data

df <- structure(list(Organization = c("X", "X", "Y", "Y", "Y", "Z", 
"Z"), Year = c(1999L, 2010L, 2009L, 2010L, 2011L, 2008L, 2011L
), N = c(3L, 3L, 4L, 5L, 5L, 5L, 5L)), class = "data.frame", row.names = c(NA, 
-7L))

Upvotes: 2

Related Questions