So I have this kind of table df

I want to create this data frame df2

This means that I want to create a new column for every "Type's" "QTY" and "unit" for each "Id". I was thinking to use a loop to first create a new column for each Type, to get something like this :

, and then group_by() to agregate them resulting in df2. But I get stuck when it comes to creating the new columns. I have tried the for loop but my level on R is still not that great yet. I can't manage to create new columns from those existing columns...

I'll appreciate any suggestions you have for me!

CodePudding user response：

You can use pivot_wider from the tidyr package:

library(dplyr)
library(tidyr)
df %>% 
  pivot_wider(names_from = "Type",            # Columns to get the names from
              values_from = c("QTY", "unit"), # Columns to get the values from
              names_glue = "{Type}_{.value}", # Column naming
              values_fill = 0,                # Fill NAs with 0 
              names_vary = "slowest")         # To get the right column ordering

output

# A tibble: 3 × 7
     Id A_QTY A_unit B_QTY B_unit C_QTY C_unit
  <int> <int>  <int> <int>  <int> <int>  <int>
1     1     5      1     0      0    10      6
2     2    10      4    10      2     0      0
3     3     0      0     5      5     5      3

CodePudding user response：

library(tidyverse)

df %>% 
  pivot_longer(-c(Id, Type)) %>% 
  mutate(name = str_c(Type, name, sep = "_")) %>% 
  select(-Type) %>%  
  pivot_wider(names_from = "name", values_from = "value", values_fill = 0)

# A tibble: 3 × 7
     Id A_QTY A_unit B_QTY B_unit C_QTY C_unit
  <dbl> <dbl>  <dbl> <dbl>  <dbl> <dbl>  <dbl>
1     1     5      1     0      0    10      6
2     2    10      4    10      2     0      0
3     3     0      0     5      5     5      3