Pivot numeric value column wider based on logical columns

Question

I have a table that has a signal value and additional columns defining the type of signal. Each location has all types of signal. See example:

d <- data.frame("location"=c("L1","L1","L1","L2","L2","L2"),"signal"=c(1.1,1.04,1.75,1.24,2.2,22),"type1"=c(1,0,0,1,0,0),"type2"=c(0,1,0,0,1,0),"type3"=c(0,0,1,0,0,1))
d
 location signal type1 type2 type3
1       L1   1.10     1     0     0
2       L1   1.04     0     1     0
3       L1   1.75     0     0     1
4       L2   1.24     1     0     0
5       L2   2.20     0     1     0
6       L2  22.00     0     0     1

I would like to pivot this table wide so each type of signal has its own column depicting the signal such as:

location type1 type2 type3
L1        1.10  1.04  1.75
L2        1.24  2.20  22.00

Thank you.

score 1 · Accepted Answer · answered Mar 25 '22 at 00:08

The trick here will be converting the data set to a tidy (long) format, because right now it's a blend of wide and long. Here's one way of doing that, with the pivot_wider at the very end.

library(tidyverse)
d %>%
  pivot_longer(starts_with("type")) %>%
  filter(value>0) %>%
  select(-value) %>%
  pivot_wider(names_from = name, values_from = signal)

# A tibble: 2 x 4
  location type1 type2 type3
  <chr>    <dbl> <dbl> <dbl>
1 L1        1.1   1.04  1.75
2 L2        1.24  2.2  22

If you set `id_cols = location` in your `pivot_wider`, you can skip the `select` line — camille, Mar 25 '22 at 00:26

score 1 · Answer 2 · answered Mar 25 '22 at 05:16

Here is another tidyverse option, where we can create a new column with the type of signal, then put into the wide format. Essentially, for the type columns, we return the column name for the column that has the max value (i.e., 1).

library(tidyverse)

d %>%
  mutate(name = pmap(across(starts_with("type")), ~ names(c(...)[which.max(c(...))]))) %>%
  pivot_wider(id_cols = location, names_from = name, values_from = signal)

Output

  location type1 type2 type3
  <chr>    <dbl> <dbl> <dbl>
1 L1        1.1   1.04  1.75
2 L2        1.24  2.2  22

Pivot numeric value column wider based on logical columns

2 Answers2