Reshaping dataframe (columns and rows)

user_14759021 0
Viewed 93

I have the following random (shortened) dataset with dates and quintile:

Date Quintile
05/03/2021 5
05/03/2021 3
05/03/2021 1
04/03/2021 2
04/03/2021 4
03/03/2021 4
03/03/2021 1
03/03/2021 2

I would like to reshape the dataframe as follows:

Date 1 2 3 4 5
05/03/2021 1 0 1 0 1
04/03/2021 0 1 0 1 0
03/03/2021 1 1 0 0 1

The new data frame will be aggregated by date, with the individual quintiles the new columns. I've explored the dplyr functions but I can't quite get it right :(

I set the Quintile values 'as.character' but I'm not sure where I am going wrong.

dplyr tidyverse reshape dataframe r
3 Answers

You could use pivot_wider with some modifications

Edit: Add unique identifier row for each Date and then use pivot_wider

library(tidyverse)

# your data
df <- tribble(
  ~Date,    ~Quintile, 
  "05/03/2021", 5,
  "05/03/2021", 3, 
  "05/03/2021", 1, 
  "04/03/2021", 2, 
  "04/03/2021", 4, 
  "03/03/2021", 4, 
  "03/03/2021", 1, 
  "03/03/2021", 2)

df1 <- df %>% 
  arrange(Quintile) %>% 
  group_by(Date, Quintile) %>% 
  mutate(row = row_number()) %>% # unique identifier
  mutate(count = n()) %>% 
  pivot_wider(names_from = Quintile, values_from = count) %>% 
  replace(is.na(.), 0) %>% 
  select(-row) # remove unique identifier

enter image description here

user_133216470

Here's the dataset which I am actually working with, and the dataset in which the error actually occurs (as mentioned in a comment on the TarJae's answer).

enter image description here

Edit:

Here's the dataframe when I run TarJae's code (excluding the unique Identifiers) on the dataframe above. No warning errors are produced, there just seems to be issues with the values.

enter image description here

With the Unique Identifiers, the result is:

enter image description here

user_147590210

Here is a simple base R option using table

> table(df)
            Quintile
Date         1 2 3 4 5
  03/03/2021 1 1 0 1 0
  04/03/2021 0 1 0 1 0
  05/03/2021 1 0 1 0 1

or reshape

reshape(
  data.frame(table(df)),
  direction = "wide",
  idvar = "Date",
  timevar = "Quintile")

gives

        Date Freq.1 Freq.2 Freq.3 Freq.4 Freq.5
1 03/03/2021      1      1      0      1      0
2 04/03/2021      0      1      0      1      0
3 05/03/2021      1      0      1      0      1

or aggregate

aggregate(
  Quintile ~ Date, 
  df, 
  function(x) table(factor(x, levels = sort(unique(df$Quintile)))))

gives

        Date Quintile.1 Quintile.2 Quintile.3 Quintile.4 Quintile.5
1 03/03/2021          1          1          0          1          0
2 04/03/2021          0          1          0          1          0
3 05/03/2021          1          0          1          0          1
user_121587570
Related