in r, transposing a column and grouping

Question

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:

1. have 4 new columns named "time_weight_1", "time_weight_2", "weight_1" and "weight_2".
2. each of the rows in these 4 columns to be filled with the data currently in columns "time", "weight_type" and "combined_weights" while grouped according to the same ID and date.
3. the date and ID columns should remain

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!

Answer 1

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

Answer 2

Possible option:

library(tidyverse)

df <- 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"),
  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)
)

df |> 
  mutate(weight_type = str_replace(weight_type, " ", "_")) |> 
  pivot_wider(names_from = "weight_type", values_from = c("time", "combined_weights")) |> 
  rename_with(\(x) str_remove(x, "combined_weights_"), starts_with("comb"))
#> # 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

^{Created on 2024-03-17 with reprex v2.1.0}