Search code examples
rdataframeloopsapply

Create new columns based on 2 columns

So I have this kind of table df

Id Type QTY unit
1 A 5 1
2 B 10 2
3 C 5 3
2 A 10 4
3 B 5 5
1 C 10 6

I want to create this data frame df2

Id A_QTY A_unit B_QTY B_unit C_QTY C_unit
1 5 1 0 0 10 6
2 10 4 10 2 0 0
3 0 0 5 5 5 3

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 :

Id Type QTY unit A_QTY A_unit B_QTY B_unit C_QTY C_unit
1 A 5 1 5 1 0 0 0 0
2 B 10 2 0 0 10 2 0 0
3 C 5 3 0 0 0 0 5 3
2 A 10 4 10 4 0 0 0 0
3 B 5 5 0 0 5 5 0 0
1 C 10 6 0 0 0 0 10 6

, 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!

Solution

  • 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