R pivot example

Question

I am having trouble with this relativity simple pivoting problem with tidyr. This is best illustrated by and example. I have this unprocessed data:

data_unprocessed <- tribble(
  ~statistic, ~value,
  "median_geo_wo_nw", 2.66, 
  "median_travel_wo_nw", 4.11,
  "mean_geo_wo_nw", 12.4,
  "mean_travel_wo_nw", 34.2)

This I need to convert to wide format like so:

data_processed <- tribble(
  ~statistic, ~geo_distance, ~travel_distance,
  "median", 2.66, 4.11,
  "mean", 12.4, 34.2)

Sorry if this seems basic but I can't get it to work.

Thanks,

Answer 1

Separate the statistic column into two variables: one for the new column name, and the other to identify the new rows, then pivot_wider

data_unprocessed %>%

    mutate(
        # create a variable to name the new variables/columns
        name = if_else(grepl("geo", statistic), "geo_distance", "travel_distance"),

        # create a separate variable to name the new rows
        statistic = if_else(grepl("mean", statistic), "mean", "median")
    ) %>%

    pivot_wider(names_from = "name", values_from = "value")

Result

# A tibble: 2 x 3
  statistic geo_distance travel_distance
  <chr>            <dbl>           <dbl>
1 median            2.66            4.11
2 mean             12.4            34.2 

Answer 2

We can separate the 'statistic' column by the first delimiter _ and then use pivot_wider

library(dplyr)
library(tidyr)
library(stringr)
data_unprocessed %>% 
    separate(statistic, into = c('statistic', 'colnm'), sep="_", 
       extra = 'merge') %>% 
    mutate(colnm = str_replace(colnm, '_wo_nw', '_distance')) %>%
    pivot_wider(names_from = colnm, values_from = value)

-output

# A tibble: 2 x 3
#  statistic geo_distance travel_distance
#  <chr>            <dbl>           <dbl>
#1 median            2.66            4.11
#2 mean             12.4            34.2