CODEWITHSUNDEEP
rtidyversetidyr
Ecg
Ecg

Reputation: 942

Pivot_longer to maintain two columns and make the rest long

How can you make a long table keeping two character columns as they are (A and B)and transforming into long format the rest.

df <- data.frame(A=c("A", "B", "C"), B= c("AA", "BB", "CC"),`1`=c("1.9", "6.8", "4.7"), `2`=c("1.9", "6.8", "4.7"), `34`=c("3.9", "0.3", "2.7"), `39`=c("2.9", "2.3", "2.9"), `158`=c("2.9", "3", "45"),`190`=c("22.1", "7.4", "56"), check.names=FALSE)

library(dplyr)
library(tidyr)
df2<- df %>% pivot_longer(cols = c(A,B), names_to = 'Number', values_to = 'Value') %>% type.convert(as.is = T) %>% 
  mutate(Variable = case_when(Number %in% c(1,2) ~ 'WW', Number %in% c(34,39) ~ 'MM', TRUE ~ 'EE')) %>% 
   select('Letter' = A, B, Number, Variable, Value)

I get the following error:

Error: Can't subset columns that don't exist. x Column One doesn't exist.

I believe the error is on pivot_longer(cols = HERE?

Desired output:

One  Two      Number  Variable  Value
A     AA        1        WW      1.9
A     AA        2        WW      1.9
A     AA        34       MM      3.9
A     AA        39       MM      2.9
A     AA        158      EE      2.9
A     AA        190      EE      22.1
B     BB        1        WW      6.8
B     BB        2        WW      6.8
B     BB        34       MM      0.3
B     BB        39       MM      2.3
B     BB        158      EE      3
B     BB        190      EE      7.4 
...

Upvotes: 0

Views: 2496

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389047

If you want data in long format A, B to remain as it is remove them from cols :

library(dplyr)
library(tidyr)

df %>% 
  pivot_longer(cols = -c(A,B), names_to = 'Number', values_to = 'Value') %>% 
  type.convert(as.is = T) %>% 
  mutate(Variable = case_when(Number %in% c(1,2) ~ 'WW', 
                              Number %in% c(34,39) ~ 'MM', TRUE ~ 'EE')) %>%
  select(One = A, two = B, Number, Variable, Value)

# A tibble: 18 x 5
#   One   two   Number Variable Value
#   <chr> <chr>  <int> <chr>    <dbl>
# 1 A     AA         1 WW         1.9
# 2 A     AA         2 WW         1.9
# 3 A     AA        34 MM         3.9
# 4 A     AA        39 MM         2.9
# 5 A     AA       158 EE         2.9
# 6 A     AA       190 EE        22.1
# 7 B     BB         1 WW         6.8
# 8 B     BB         2 WW         6.8
# 9 B     BB        34 MM         0.3
#10 B     BB        39 MM         2.3
#11 B     BB       158 EE         3  
#12 B     BB       190 EE         7.4
#13 C     CC         1 WW         4.7
#14 C     CC         2 WW         4.7
#15 C     CC        34 MM         2.7
#16 C     CC        39 MM         2.9
#17 C     CC       158 EE        45  
#18 C     CC       190 EE        56

Upvotes: 2

Related Questions