Converting drug level data to individual level data

[lamisi]

I am working with a NHANES derived dataset on prescription drug and dietary use. Its drug level data needs to be converted to personal level data (i.e. a record for each person) before merging it with NHANES demographic data files by the unique identifier for each individual (variable called seqn).
seqn rxduse rxddrgid rxdcount
73557 1 d00262 2
73557 1 d04113 2
73557 1 d00262 4
73558 1 d04538 4
73558 1 d00746 4
73558 1 d03182 4

Tried this code

staxpiv <- stax22 %>%
group_by(SEQN) %>%
mutate(id = 1:n()) %>%
ungroup() %>%
pivot_wider(values_from = RXDUSE2,
names_from = RXDDRGID,
names_prefix = 'id_')

It didn't work, this was the output:

SEQN id id_ id_d03740 id_d04532 id_d00325
93704 1 0 NA NA
93705 1 NA 1 NA
93705 2 NA NA 1

Would greatly appreciate any assistance with this.

[MarekGierlinski]

It is not clear to me what you want to achieve. If the patient is identified by seqn and you want rdxuse per patient, the pivot needs to be unfolded by the remaining two variables, rxddrgrid and rxdcount.

Here is your data:

stax22 <- structure(list(seqn = c(73557, 73557, 73557, 73558, 73558, 73558
), rxduse = c(1, 1, 1, 1, 1, 1), rxddrgid = c("d00262", "d04113", 
"d00262", "d04538", "d00746", "d03182"), rxdcount = c(2, 2, 4, 
4, 4, 4)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-6L))

and the pivot:

stax22 |>
  pivot_wider(id_cols = seqn, values_from = rxduse,
              names_from = c(rxddrgid, rxdcount))

The result is rxduse per patient (row) and column (grid + count):

# A tibble: 2 × 7
   seqn d00262_2 d04113_2 d00262_4 d04538_4 d00746_4 d03182_4
  <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>
1 73557        1        1        1       NA       NA       NA
2 73558       NA       NA       NA        1        1        1

But I'm not sure if this is what you need. If not, type the expected result using the data above and we will try to find a solution.

[lamisi]

THANK YOU! It worked.