Reshaping A Data Table in R: Adding Sub Column

Question

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:

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?

Well, a dataframe cannot have shared column names but it should be possible using other packages if you want this only for display purposes. — Ronak Shah
– Ronak Shah, Commented Aug 2, 2021 at 13:48
It is only for display, do you have any package recommendations? — a_js12
– a_js12, Commented Aug 2, 2021 at 13:55
Look at knitr::kable and kableExtra package. Specifically add_header_above function. — Ronak Shah
– Ronak Shah, Commented Aug 2, 2021 at 14:09
If you don't want a common header and just want to reshape you can use tidyr::pivot_wider(df, names_from = Quarter, values_from = starts_with('Variable')) — Ronak Shah
– Ronak Shah, Commented Aug 2, 2021 at 14:15

ThomasIsCoding · Accepted Answer · 2021-08-02 14:12:38Z

1

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

answered Aug 2, 2021 at 14:12

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Chaltiel · Accepted Answer · 2021-08-10 15:28:46Z

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)}

Collectives™ on Stack Overflow

Reshaping A Data Table in R: Adding Sub Column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related