CODEWITHSUNDEEP
rtidyr
gdeniz
gdeniz

Reputation: 177

Pivot numeric value column wider based on logical columns

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.

Upvotes: 1

Views: 394

Answers (2)

AndrewGB
AndrewGB

Reputation: 16856

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

Upvotes: 1

Dubukay
Dubukay

Reputation: 2071

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

Upvotes: 1

Related Questions