Search code examples
rdataframedatatablepivotreshape

How to reshape a dataframe into wide format with specified column pairs

I have the following melted sample dataframe:

df_melted <- data.frame(ID = c(21, 21, 21, 21, 49, 49, 49, 49), instance = c(1, 1, 1, 1, 1, 1, 2, 2), variable = c("causeofdeath", "deathdate", "dob", "gender", "causeofdeath", "deathdate", "deathdate", "causeofdeath"), value_temp = c("BLANK", "MM/DD/YYYY", "BLANK", "F", "BLANK", "BLANK", "MM/DD/YYYY", "BLANK"))

df_melted

And the following mapping file that I want to eventually use to produce new values by using a left_join():

df_map <- data.frame(var_name_1 = c("causeofdeath", "causeofdeath", "dob"), val_1 = c("BLANK", "BLANK", "MM/DD/YYYY"), var_name_2 = c("deathdate", "deathdate", "gender"), val_2 = c("MM/DD/YYYY", "BLANK", "F"), new_var_name = c("ida", "idf", "ids"), new_val = c("T60", "T55", "T67"))

df_map

How can I reshape df_melted so that it is in the same particular arrangement as shown in df_map without making any assumptions about the order of the dataframe?, ie:

df_wanted

I have tried using pivot_wider() to no avail

Solution

  • You can try pivot_wider after adding some auxiliary columns, e.g., p and grp

    df_melted %>%
    mutate(p = rep(1:2, length.out = n()), grp = ceiling(row_number() / 2)) %>%
    pivot_wider(
        names_from = p,
        values_from = c(variable, value_temp),
        id_cols = c(ID, instance, grp),
        names_vary = "slowest"
    ) %>%
    select(-grp)

    and you will obtain

    # A tibble: 4 × 6
     ID instance variable_1   value_temp_1 variable_2   value_temp_2
  <dbl>    <dbl> <chr>        <chr>        <chr>        <chr>
1    21        1 causeofdeath BLANK        deathdate    MM/DD/YYYY
2    21        1 dob          BLANK        gender       F
3    49        1 causeofdeath BLANK        deathdate    BLANK
4    49        2 deathdate    MM/DD/YYYY   causeofdeath BLANK