Transpose rows to columns and then group by unique ID values

Question

I'm looking for a way to dynamically transpose row variables and their values into columns and then group them by their unique original row ID's in R. Any help with this would be greatly appreciated.

Current table:

ID	Variable	Value
111	process	assessment
111	account number	7583901264
111	surname	James
222	policy number	P761
222	process	review
222	account number	4720129840
333	claim value	$1000.00
333	claim date	2021-11-12 09:00:00

Desired table:

ID	process	account number	surname	policy number	claim value	claim date
111	assessment	7583901264	James
222	review	4720129840		P761
333					$1000.00	2021-11-12 09:00:00

Keep in mind that not all unique ID's in my original table have the same number of rows and the same type of variables

Please post a small reproducible sample of data - `dput()` is the nicest way to do this because it is copy/pasteable. — Yuriy Saraykin, Jan 12 '22 at 12:13

Yuriy Saraykin · Answer 1 · 2022-01-12T13:59:26.913

library(tidyverse)
df %>%
  pivot_wider(id_cols = ID,
              names_from = Variable,
              values_from = Value)


# A tibble: 3 x 7
     ID process    `account number` surname `policy number` `claim value` `claim date`  
  <int> <chr>      <chr>            <chr>   <chr>           <chr>         <chr>         
1   111 assessment 7583901264       James   NA              NA            NA            
2   222 review     4720129840       NA      P761            NA            NA            
3   333 NA         NA               NA      NA              $1000.00      12.11.2021 9:~

or

library(data.table)
dcast(data = setDT(df), formula = ID ~ Variable, value.var = "Value")

    ID account number      claim date claim value policy number    process surname
1: 111     7583901264            <NA>        <NA>          <NA> assessment   James
2: 222     4720129840            <NA>        <NA>          P761     review    <NA>
3: 333           <NA> 12.11.2021 9:00    $1000.00          <NA>       <NA>    <NA>

then do the data type conversion

Thanks for the quick response. For the dcast solution, I end up with either 0 or 1 as values in my cells? — Paul Pretorius, Jan 12 '22 at 13:22

Transpose rows to columns and then group by unique ID values

1 Answers1