I have a df called dfmelt that I want to pivot to wide format. It has five columns: study_id and redcap_event_name are ID columns, with variable and value being key-value pairs and num being a numeric (1,2,...n) grouped by study_id and redcap_event_name that I want suffixed to the variable column header in the wide dataframe. Below is a snippet:
| study_id | redcap_event_name | variable | value | num |
|---|---|---|---|---|
| CHLA_0014 | csf_shunt_infectio_arm_1 | micro_sample_dt | 7/22/21 | 1 |
| CHLA_0014 | csf_shunt_infectio_arm_1 | micro_sample_dt | 7/22/21 | 2 |
| CHLA_0014 | csf_shunt_infectio_arm_1 | micro_sample_dt | 7/22/21 | 3 |
| CHLA_0014 | csf_shunt_infectio_arm_1 | micro_sample_time | 18:19 | 1 |
| CHLA_0014 | csf_shunt_infectio_arm_1 | micro_sample_time | 18:38 | 2 |
| CHLA_0014 | csf_shunt_infectio_arm_1 | micro_sample_time | 18:38 | 3 |
| CHLA_0067 | initial_shunt_plac_arm_1 | micro_culture_source | 1 | 1 |
| CHLA_0067 | initial_shunt_plac_arm_1 | micro_culture_source | 1 | 2 |
| CHLA_0067 | initial_shunt_plac_arm_1 | micro_culture_source | 1 | 3 |
I attempted pivot_wider with these arguments to get {variable}{num} as the column headers with no separator.
pivot_wider(dfmelt, id_cols = c(1:2), names_from = c(variable), values_from = c(value), names_glue = "{variable}{num}")
I received this error message: Warning message: Values are not uniquely identified; output will contain list-cols.
This is the target format of above:
| study_id | redcap_event_name | micro_sample_dt1 | micro_sample_time1 | micro_culture_source1 | micro_sample_dt2 | micro_sample_time2 | micro_culture_source2 | micro_sample_dt3 | micro_sample_time3 | micro_culture_source3 | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| CHLA_0014 | csf_shunt_infectio_arm_1 | 7/22/21 | 18:19 | 1 | 0 | 7/22/21 | 18:38 | |||||||
| CHLA_0067 | initial_shunt_plac_arm_1 | 1 | 1 | 1 |