How can I turn the unique values of a column into row values in a table in R?

Question

I have a dataframe (df1) and would like to rearrange the information for further processing. However, I am not sure how to convert the unique Site values into headers in the table. How could I accomplish this?

Here is the example data

df1 <- data.frame(matrix(ncol = 4, nrow = 27))
x <- c("Date","Distance","Site","MeanVal")
colnames(df1) <- x
df1$Date <- c("2020-06-01","2020-06-01","2020-06-01","2020-06-01","2020-06-01","2020-06-01","2020-06-01","2020-06-01","2020-06-01",
              "2020-07-01","2020-07-01","2020-07-01","2020-07-01","2020-07-01","2020-07-01","2020-07-01","2020-07-01","2020-07-01",
              "2020-08-01","2020-08-01","2020-08-01","2020-08-01","2020-08-01","2020-08-01","2020-08-01","2020-08-01","2020-08-01")
df1$Date <- as.Date(df1$Date, format = "%Y-%m-%d")
df1$Distance <- as.numeric(rep(c("0.5","1.5","2.5"),9))
df1$Site <- c("1A","1A","1A","2B","2B","2B","3C","3C","3C",
              "1A","1A","1A","2B","2B","2B","3C","3C","3C",
              "1A","1A","1A","2B","2B","2B","3C","3C","3C")
set.seed(123)
df1$MeanVal <- round(rnorm(27,100,50), digits = 0)

The output should look something like this

Date    Distance  1A  2B  3C
2020-06-01  0.5  108 121 144
2020-06-01  1.5  43 85 141
2020-06-01  2.5  163 145 134
2020-07-01  0.5  128 81 37
2020-07-01  1.5  97 65 208
2020-07-01  2.5  85 90 160
2020-08-01  0.5  44 139 99
2020-08-01  1.5  80 96 98
2020-08-01  2.5  77 113 168

`reshape(df1, idvar = c('Date', 'Distance'), timevar = 'Site', dir = 'wide')` should work — Onyambu, May 28 '21 at 03:19

score 1 · Accepted Answer · answered May 28 '21 at 03:09

We can use pivot_wider

library(dplyr)
library(tidyr)
df1 %>%
    pivot_wider(names_from = Site, values_from = MeanVal)

-output

# A tibble: 9 x 5
  Date       Distance  `1A`  `2B`  `3C`
  <date>        <dbl> <dbl> <dbl> <dbl>
1 2020-06-01      0.5    72   104   123
2 2020-06-01      1.5    88   106    37
3 2020-06-01      2.5   178   186    66
4 2020-07-01      0.5    78   120   189
5 2020-07-01      1.5   161   106   125
6 2020-07-01      2.5   118    72     2
7 2020-08-01      0.5   135    89    69
8 2020-08-01      1.5    76    49    16
9 2020-08-01      2.5    47    64   142

Or use dcast

library(data.table)
dcast(setDT(df1), ... ~ Site, value.var = 'MeanVal')

How can I turn the unique values of a column into row values in a table in R?

1 Answers1