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.
CodePudding user response:
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
CodePudding user response:
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