CODEWITHSUNDEEP
rdataframedplyrtidyversereshape
a_js12
a_js12

Reputation: 329

Reshaping A Data Table in R: Adding Sub Column

I have a dataset which looks like this:

df<-data.frame("Category"=c("A","B","C","D","A","B","C","D"),"Quarter"=c("2021 Q1","2021 Q1","2021 Q1","2021 Q1","2021 Q2","2021 Q2","2021 Q2","2021 Q2"),Variable1=c(1005,2007,1684,1545,4648,5016,4646,5555),Variable2=c(48,500,100,350,357,565,498,600))

What I'd like to do, using the data, would look like this:

enter image description here

I can't seem to find any similar questions asked here. I did see there are alternative approaches where I could use the pivot_longer function but this wouldn't give me the same table that I'm looking for.

Does anyone have any suggestions? Is this possible to do with R?

Upvotes: 0

Views: 2119

Answers (2)

Dan Chaltiel
Dan Chaltiel

Reputation: 8484

Here is a solution using tidyr::pivot_longer() and tidyr::pivot_wider() for the pivoting (to add on Thomas' use of reshape), and then flextable::flextable() for the display.

Note that automating the merging of the headers might not be easy depending on your real dataset.

library(tidyverse)
library(flextable)

df<-data.frame("Category"=c("A","B","C","D","A","B","C","D"),"Quarter"=c("2021 Q1","2021 Q1","2021 Q1","2021 Q1","2021 Q2","2021 Q2","2021 Q2","2021 Q2"),Variable1=c(1005,2007,1684,1545,4648,5016,4646,5555),Variable2=c(48,500,100,350,357,565,498,600))

#pivoting
df2 = df %>%
    pivot_longer(starts_with("Variable")) %>% 
    transmute(Category, name=paste(Quarter, "-", name), value) %>% 
    pivot_wider()


#Creating the table
header_df = tibble(col_keys = names(df2),
                   Quarter = c("Category", "2021 Q1", "2021 Q1", "2021 Q2", "2021 Q2"),
                   Variable = c("Category", "Variable 1", "Variable 2", "Variable 1", "Variable 2"))
df2 %>% 
    flextable() %>% 
    set_header_df(header_df) %>%
    merge_v(part="header") %>% 
    merge_h(part="header") %>% 
    theme_box() %>% 
    align(align = "center", part = "header") %>% 
    autofit()

Created on 2021-08-10 by the reprex package (v2.0.0)

table

Upvotes: 0

ThomasIsCoding
ThomasIsCoding

Reputation: 101064

Seems you want to reshape your data.frame. Below might be one option

reshape(
  df,
  direction = "wide",
  idvar = "Category",
  timevar = "Quarter"
)

which gives

  Category Variable1.2021 Q1 Variable2.2021 Q1 Variable1.2021 Q2
1        A              1005                48              4648
2        B              2007               500              5016
3        C              1684               100              4646
4        D              1545               350              5555
  Variable2.2021 Q2
1               357
2               565
3               498
4               600

Upvotes: 1

Related Questions