0

I am trying to mutate a 4 column data.table that has two keys and two additional columns. One of these columns is a factor with over 20 levels, and the other contains a value that corresponds to each level. I would like each of the factor levels to be its own column that would be populated with the corresponding values.

From this:
      Customer_ID      Order_ID   Category  Value
 1:   1                1          A         0.0432
 2:   1                1          B         3589.8027
 3:   1                1          C         0.0000
 4:   1                2          A         291.4043
 5:   1                2          B         3153.2292
 6:   1                2          C         755.7297
 7:   1                3          A         0.0764
 8:   1                3          B         0.0524
 9:   1                3          C         9144.1055
10:   1                4          A         4272.11
11:   1                4          B         256.1633
12:   1                4          C         2172.3953
13:   2                1          A         0.0605
14:   2                1          B         0.0638
15:   2                1          C         0.0000
16:   2                2          A         0.0000
17:   2                2          B         269.5240
18:   2                2          C         3015.1008
19:   2                3          A         755.7297
19:   2                3          B         7545.897
13:   2                3          C         0.0605
...

To this:

      Customer_ID      Order_ID   A          B           C   ....
 1:   1                1          0.0432    3589.8027    0.0000        
 2:   1                2          291.4043  3153.2292    755.7297
 3:   1                3          0.0764    0.0524       9144.1055
 4:   1                4          4272.11   256.1633     2172.3953
 5:   2                1          0.0605    0.0638       0.0000
 6:   2                2          0.0000    269.5240     3015.1008
 7:   2                3          755.7297  7545.897     0.0605

There are over 20 of these categories in my data so I would like to learn a more efficient way of restructuring this without manually creating a new data.table for each and then merging them back together. Perhaps with tidyr or mutate?

Thanks!

Lancerman
  • 1
  • 1
  • 2
    With the `tidyr` package, this is just `mydata %>% tidyr::pivot_wider(names_from=Category, values_from=Value)`. With `data.table` it would be something more like `dcast(mydata, Customer_ID+Order_ID~Category)` – MrFlick Nov 17 '22 at 19:45

0 Answers0