Pivot a longer dataframe to wide with multiple columns in R

Question

I have a dataframe with 1000+ rows in a long format. I need to pivot into wider format using the ID as the pivoter. Pivoting it using pivot_wider only returns values in the first column, but not all of the columns.

Here is my code:

#Test data
df <- data.frame(
  ID = c(TRCC1, TRCC1, DNRC1, ILAC1),
  AJ_Vol = c(14.454166, 14.976905, 11.344002, 5.673755)
)


# Use spread to pivot the dataframe
wide_df <- pivot_wider(df, names_from = ID, values_from = AJ_Vol, values_fn =  list(AJ_Vol = list))

This what the data looks like in long. head(wide_df,100)

	ID	AJ_Vol
21	TRCC1	14.454166
22	TRCC1	14.976905
46	DNRC1	11.344002
86	ILAC1	5.673755

Here is what I want it to look like:

TRCC1	DNRC1	ILAC1
14.454166	11.344002	5.673755
14.976905

Answer 1

We need to create a column that distinguishes between different observations of the same ID:

df |>
  mutate(row = row_number(), .by = ID) |>
  pivot_wider(names_from = ID, values_from = AJ_Vol)

Result:

# A tibble: 2 × 4
    row TRCC1 DNRC1 ILAC1
  <int> <dbl> <dbl> <dbl>
1     1  14.5  11.3  5.67
2     2  15.0  NA   NA 

Assuming data:

df <- data.frame(
  ID = c("TRCC1", "TRCC1", "DNRC1", "ILAC1"),
  AJ_Vol = c(14.454166, 14.976905, 11.344002, 5.673755)
)

Answer 2

tmp <- split(df$AJ_Vol, df$ID)
lapply(tmp, \(x) x[1:max(lengths(tmp))]) |> 
  data.frame()

#    DNRC1    ILAC1    TRCC1
# 1 11.344 5.673755 14.45417
# 2     NA       NA 14.97691

Reproducible data

df <- data.frame(
  ID = c("TRCC1", "TRCC1", "DNRC1", "ILAC1"),
  AJ_Vol = c(14.454166, 14.976905, 11.344002, 5.673755)
)