Reshape R dataframe from long format to a wide format

user_17542761 0
Viewed 17

I have a table I want to reshape/pivot. The Agency No will have duplicates as this is looking at years worth of data but they are grouped by Agency No, Fiscal Year, and Type currently. The table is provided below as well as a desired output.

Agency No Fiscal Year Type Total Gross Weight
W1000FP 2018 Dry 1000
W1004CSFP 2018 Dry 2000
W1000FP 2018 Produce 500
W1004CSFP 2018 Produce 1000
W1004DR 2018 Produce 1000
W1004DR 2018 Dry 1000
W1005DR 2019 Dry 2000
W1000FP 2019 Dry 1000
W1005DR 2019 Produce 1000
W1000FP 2019 Produce 1000

Desired Output:

Agency No Fiscal Year Produce Weight Dry Weight
W1000FP 2018 500 1000
W1004CSFP 2018 1000 2000
W1004DR 2018 1000 1000
W1005DR 2019 1000 2000
W1000FP 2019 1000 1000

Here is the script that I ran but did not provide the desired output:

reshape(df, idvar = "Agency No", timevar = "Type", direction = "wide"
tidyr reshape r
1 Answers

We could use pivot_wider

library(tidyr)
pivot_wider(df1, names_from = Type, values_from = `Total Gross Weight`)

-output

# A tibble: 5 × 4
  `Agency No` `Fiscal Year`   Dry Produce
  <chr>               <int> <int>   <int>
1 W1000FP              2018  1000     500
2 W1004CSFP            2018  2000    1000
3 W1004DR              2018  1000    1000
4 W1005DR              2019  2000    1000
5 W1000FP              2019  1000    1000

With reshape, specify the 'Fiscal Year' also a idvar

reshape(df1, idvar = c("Agency No", "Fiscal Year"), 
     timevar = "Type", direction = "wide")
  Agency No Fiscal Year Total Gross Weight.Dry Total Gross Weight.Produce
1   W1000FP        2018                   1000                        500
2 W1004CSFP        2018                   2000                       1000
5   W1004DR        2018                   1000                       1000
7   W1005DR        2019                   2000                       1000
8   W1000FP        2019                   1000                       1000

data

df1 <- structure(list(`Agency No` = c("W1000FP", "W1004CSFP", "W1000FP", 
"W1004CSFP", "W1004DR", "W1004DR", "W1005DR", "W1000FP", "W1005DR", 
"W1000FP"), `Fiscal Year` = c(2018L, 2018L, 2018L, 2018L, 2018L, 
2018L, 2019L, 2019L, 2019L, 2019L), Type = c("Dry", "Dry", "Produce", 
"Produce", "Produce", "Dry", "Dry", "Dry", "Produce", "Produce"
), `Total Gross Weight` = c(1000L, 2000L, 500L, 1000L, 1000L, 
1000L, 2000L, 1000L, 1000L, 1000L)), class = "data.frame", row.names = c(NA, 
-10L))
user_37322710
Related