Search code examples
rgroup-byaggregatetranspose

in r, transposing a column and grouping

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!

Solution

  • 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