Looking for a way in R to create a data-frame from another data-frame where the values of Col A act as indices and each unique values in Col B become new columns in the new data-frame with values of Col C being the values in the new column.
Data-frame 1:
| Col A | Col B | Col C |
|---|---|---|
| A | 2011 | 1 |
| B | 2012 | 2 |
| C | 2013 | 3 |
| D | 2011 | 4 |
| E | 2012 | 5 |
| D | 2013 | 6 |
| A | 2013 | 7 |
Result:
| Col A | 2011 | 2012 | 2013 |
|---|---|---|---|
| A | 1 | 0 | 7 |
| B | 0 | 2 | 0 |
| C | 0 | 0 | 3 |
| D | 4 | 0 | 6 |
| E | 0 | 0 | 5 |
Here is a base R option using reshape
reshape(
df,
direction = "wide",
idvar = "ColA",
timevar = "ColB"
)
which gives
ColA ColC.2011 ColC.2012 ColC.2013
1 A 1 NA 7
2 B NA 2 NA
3 C NA NA 3
4 D 4 NA 6
5 E NA 5 NA
A data.table option with dcast
> dcast(setDT(df),ColA~ColB, value.var = "ColC")
ColA 2011 2012 2013
1: A 1 NA 7
2: B NA 2 NA
3: C NA NA 3
4: D 4 NA 6
5: E NA 5 NA
Data
> dput(df)
structure(list(ColA = c("A", "B", "C", "D", "E", "D", "A"), ColB = c(2011L,
2012L, 2013L, 2011L, 2012L, 2013L, 2013L), ColC = 1:7), class = "data.frame", row.names = c(NA,
-7L))
Here is a tidyverse solution.
library(tidyverse)
df1 %>%
pivot_wider(
id_cols = Col.A,
names_from = Col.B,
values_from = Col.C,
values_fill = 0L
)
## A tibble: 5 x 4
# Col.A `2011` `2012` `2013`
# <chr> <int> <int> <int>
#1 A 1 0 7
#2 B 0 2 0
#3 C 0 0 3
#4 D 4 0 6
#5 E 0 5 0
Data
df1 <-
structure(list(Col.A = c("A", "B", "C", "D", "E", "D", "A"),
Col.B = c(2011L, 2012L, 2013L, 2011L, 2012L, 2013L, 2013L),
Col.C = 1:7), row.names = c(NA, -7L), class = "data.frame")
We can use xtabs from base R
xtabs(Col.C ~ Col.A + Col.B, df1)
df1 <- structure(list(Col.A = c("A", "B", "C", "D", "E", "D", "A"),
Col.B = c(2011L, 2012L, 2013L, 2011L, 2012L, 2013L, 2013L
), Col.C = 1:7), row.names = c(NA, -7L), class = "data.frame")
