Wide the tibble into row and creating new columns based on row using R

Question

The below image is a representation of the data set. I tried using reshape and pivot_wider to widen the data but could not get the result in a manner which I expected. I tried Merging multiple rows into single row from the stack overflow but found that the solution was wrong.

The below image is the expected result that I want from the dataset.

Code for the random dataset generation :

df1 <- data.frame(Components = c(rep("ABC",5),rep("BCD",5)), 
              Size = c(sample(1:100,5),sample(45:100,5)),
              Age = c(sample(1:100,5),sample(45:100,5)))

Use `set.seed()` to make the input data reproducible. Don't show input data as images but console printouts instead. — s_baldur, Oct 16 '20 at 13:59
Dear sindri_baldur, I had already mentioned that it's a random data set. It is totally fine to have any values in my solutions. All I want is to widen the table/tibble as expected — Sai Anand, Oct 16 '20 at 15:41
The purpose is just making live easier for those helping. These are general StackOverflow standards: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — s_baldur, Oct 16 '20 at 15:49

score 0 · Accepted Answer · answered Oct 16 '20 at 13:52

Try this tidyverse solution which will produce an output close to what you want. You can group by Components then create a sequential id that will identify the future columns. After that reshape to long (pivot_longer()) combine the variable name with the id and then reshape to wide (pivot_wider()). Here the code where I have used the data you shared:

library(tidyverse)
#Code
newdf <- df1 %>% group_by(Components) %>% mutate(id=row_number()) %>%
  pivot_longer(-c(Components,id)) %>%
  mutate(name=paste0(name,'.',id)) %>% select(-id) %>%
  pivot_wider(names_from = name,values_from=value)

Output:

# A tibble: 2 x 11
# Groups:   Components [2]
  Components Size.1 Age.1 Size.2 Age.2 Size.3 Age.3 Size.4 Age.4 Size.5 Age.5
  <fct>       <int> <int>  <int> <int>  <int> <int>  <int> <int>  <int> <int>
1 ABC            23    94     52    89     15    25     76    38     33    99
2 BCD            59    62     55    81     81    61     80    83     97    68

score 0 · Answer 2 · answered Oct 16 '20 at 18:41

We could use unite to unite the columns and then use pivot_wider

library(dplyr)
library(tidyr)
library(data.table)
df1 %>%
   mutate(rn = rowid(Components)) %>%
   pivot_longer(cols = Size:Age) %>% 
   unite(name, name, rn, sep=".") %>%
   pivot_wider(names_from = name, values_from = value)

-output

# A tibble: 2 x 11
#  Components Size.1 Age.1 Size.2 Age.2 Size.3 Age.3 Size.4 Age.4 Size.5 Age.5
#  <chr>       <int> <int>  <int> <int>  <int> <int>  <int> <int>  <int> <int>
#1 ABC            11    16     79    57     70     2     80     6     91    24
#2 BCD            67    81     63    77     48    73     52   100     49    76

Wide the tibble into row and creating new columns based on row using R

2 Answers2