0

I need to convert my long excel table into a wide table as shown in the image. enter image description here

I already has some access doing this in R and Tableau Prep, however I want all the empty/blank cells to be at the end of each category. Not at the top. Excel VBA may be my best option. However I am not familiar with VBA.

My code in R to get a wide table: data_wide <- spread(dataset, Sector, "Description(Unique)")

However with this, I don't get the blank cells at the end.

HEre is my dataset for R:

structure(list(Number = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 
12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26), 
Theme = c("Summer", "Summer", "Summer", "Summer", "Summer", 
"Summer", "Summer", "Summer", "Summer", "Summer", "Summer", 
"Summer", "Summer", "Summer", "Winter", "Winter", "Winter", 
"Winter", "Winter", "Winter", "Fall", "Fall", "Fall", "Fall", 
"Fall", "Fall"), Description = c("A", "B", "C", "D", "E", 
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", 
"R", "S", "T", "U", "V", "W", "X", "Y", "Z"), Sector = c("APPLE", 
"APPLE", "APPLE", "ORANGE", "ORANGE", "ORANGE", "ORANGE", 
"ORANGE", "BANANA", "BANANA", "CHERRY", "CHERRY", "CHERRY", 
"APPLE", "APPLE", "ORANGE", "BANANA", "CHERRY", "CHERRY", 
"APPLE", "APPLE", "ORANGE", "ORANGE", "ORANGE", "CHERRY", 
"CHERRY")), row.names = c(NA, -26L), class = c("tbl_df", 
"tbl", "data.frame"))
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Rohamsk
  • 53
  • 10
  • Unfortunately as you can see mine has some Blank cells in the wide format. and I need to have the blank cells at the end of each category. Simply reshaping in R will not do that. – Rohamsk Jul 19 '19 at 01:22
  • 1
    Please provide data as plain text to make this question reproducible. You can copy/paste the output of `dput(dataset)`. – neilfws Jul 19 '19 at 01:26
  • 1
    @neilfws I updated my question with the data as plain text (From R). Mycode : > DATA_STACK <- spread(sTACK_R, Sector, "Description") > View(DATA_STACK) > dput(sTACK_R) – Rohamsk Jul 19 '19 at 01:54

1 Answers1

2

For spread to work, you need to do some pre-processing. Create a row number variable for each Sector, remove Number column and then spread.

library(dplyr)

df %>%
  group_by(Sector) %>%
  mutate(row = row_number()) %>%
  select(-Number) %>%
  tidyr::spread(Sector, Description) %>%
  select(-row)

# A tibble: 13 x 5
#   Theme  APPLE BANANA CHERRY ORANGE
#   <chr>  <chr> <chr>  <chr>  <chr> 
# 1 Fall   NA    NA     Y      NA    
# 2 Fall   U     NA     Z      V     
# 3 Fall   NA    NA     NA     W     
# 4 Fall   NA    NA     NA     X     
# 5 Summer A     I      K      D     
# 6 Summer B     J      L      E     
# 7 Summer C     NA     M      F     
# 8 Summer N     NA     NA     G     
# 9 Summer NA    NA     NA     H     
#10 Winter NA    Q      NA     NA    
#11 Winter NA    NA     R      NA    
#12 Winter O     NA     S      NA    
#13 Winter T     NA     NA     P

Or a little shorter is to change Number column itself as row index

df %>%
  group_by(Sector) %>%
  mutate(Number = row_number()) %>%
  tidyr::spread(Sector, Description)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Hi, look like this option is working. Do you know how I can change the Tibble to a dataset so I can export to Excel? Thanks! – Rohamsk Jul 19 '19 at 02:48
  • @Rohamsk yes, just assign it to a variable `df1 <-df %>% group_by(Sector) %>% mutate(Number = row_number()) %>% tidyr::spread(Sector, Description) %>% select(-Number)` and then use `write.csv`, `write.csv(df1, "/path/of/file/temp.csv", row.names = FALSE)` – Ronak Shah Jul 19 '19 at 02:57