In R, From the following example df:
df <- as.data.frame(ID= c("ACTA", "ACTZ", "APHT", "ACTA", "ACTZ", "APHT"),
date = c("2011-12-21", "2011-12-20", "2011-12-20", "2011-12-21", "2011-12-20", "2011-12-20", "2011-12-20"),
time = c("07:07:40", "07:08:20", "07:10:09", "07:10:43", "07:11:32", "07:12:32"),
weight_type = c("weight 1", "weight 1", "weight 1", "weight 2","weight 2", "weight 2"),
combined_weights = c(73.40, 77.70, 73.10, 71.80, 69.60, 68.60))
I would like to recreate a new df to:
In other words, I want each row in the new df to present the data (i.e. the two times and measured body measurements) for a specific ID during a specific date.
The end result should be:
df_new <- as.data.frame(ID= c("ACTA", "ACTZ", "APHT"),
date = c("2011-12-21", "2011-12-20", "2011-12-20"),
time_weight_1 = c("07:07:40", "07:08:20", "07:10:09"),
time_weight_2 = c("07:10:43", "07:11:32", "07:12:32"),
weight_1 = c(73.40, 77.70, 73.10),
weight_2 = c(71.80, 69.60, 68.60))
I tried the "transpose" and aggregate functions. But I don't think it is the right direction.
Thanks a lot!
directly use pivot_wider
:
df %>%
pivot_wider(id_cols = c(ID, date),
names_from = weight_type,
values_from =c(time, combined_weights),
names_repair = ~str_replace(str_remove(.x,"combined_weights_")," ", "_"))
# A tibble: 3 × 6
ID date time_weight_1 time_weight_2 weight_1 weight_2
<chr> <chr> <chr> <chr> <dbl> <dbl>
1 ACTA 2011-12-21 07:07:40 07:10:43 73.4 71.8
2 ACTZ 2011-12-20 07:08:20 07:11:32 77.7 69.6
3 APHT 2011-12-20 07:10:09 07:12:32 73.1 68.6