I am trying to pivot a data frame (tbl_df) from wide to long and get the following error
Error: Can't combine character and double
My column names are doubles and it worked fine in the example below:
dat3 <- data.frame(
sex = c("F","F","F", "F","M","M","M", "M","TOT","TOT","TOT","TOT","F","F","F", "F","M","M","M", "M","TOT","TOT","TOT","TOT"),
age = c(rep("Y70-74",12),rep("Y75-79",12)),
geo = c("UK","GER","FRA", "POL","UK","GER","FRA", "POL","UK","GER","FRA","POL","UK","GER","FRA", "POL","UK","GER","FRA", "POL","UK","GER","FRA","POL"),
"2021W3" = c(5,3,7,5,8,2,6,5,13,5,13,10, 1,2,3,4,1,1,1,1,2,3,4,5),
"2021W2" = c(6,8,4,5,9,1,0,4,15,9,12,9, 1,2,3,4,1,1,1,1,2,3,4,5),
"2021W1" = c(8,7,9,2,1,2,3,6,9, 9,12,8 , 1,2,3,4,1,1,1,1,2,3,4,5),
"2020W52"= c(1,2,8,2,5,1,2,4,6, 3,10,6, 1,2,3,4,1,1,1,1,2,3,4,5),
"2020W51"= c(4,4,3,6,4,5,1,0,9, 5,4, 6 , 1,2,3,4,1,1,1,1,2,3,4,5),
"2020W50"= c(3,6,3,5,2,1,2,5,5, 7,5, 10, 1,2,3,4,1,1,1,1,2,3,4,5))
dat3
sex age geo X2021W3 X2021W2 X2021W1 X2020W52 X2020W51 X2020W50
1 F Y70-74 UK 5 6 8 1 4 3
2 F Y70-74 GER 3 8 7 2 4 6
3 F Y70-74 FRA 7 4 9 8 3 3
4 F Y70-74 POL 5 5 2 2 6 5
5 M Y70-74 UK 8 9 1 5 4 2
6 M Y70-74 GER 2 1 2 1 5 1
7 M Y70-74 FRA 6 0 3 2 1 2
8 M Y70-74 POL 5 4 6 4 0 5
9 TOT Y70-74 UK 13 15 9 6 9 5
10 TOT Y70-74 GER 5 9 9 3 5 7
11 TOT Y70-74 FRA 13 12 12 10 4 5
12 TOT Y70-74 POL 10 9 8 6 6 10
13 F Y75-79 UK 1 1 1 1 1 1
14 F Y75-79 GER 2 2 2 2 2 2
15 F Y75-79 FRA 3 3 3 3 3 3
16 F Y75-79 POL 4 4 4 4 4 4
17 M Y75-79 UK 1 1 1 1 1 1
18 M Y75-79 GER 1 1 1 1 1 1
19 M Y75-79 FRA 1 1 1 1 1 1
20 M Y75-79 POL 1 1 1 1 1 1
21 TOT Y75-79 UK 2 2 2 2 2 2
22 TOT Y75-79 GER 3 3 3 3 3 3
23 TOT Y75-79 FRA 4 4 4 4 4 4
24 TOT Y75-79 POL 5 5 5 5 5 5
I got great help on another post solving my example:
names_list <- colnames(dat3[4:length(dat3)])
library(dplyr)
library(tidyverse)
datNew <- dat3 %>%
pivot_longer(cols = names_list, names_to = "Time") %>%
pivot_wider(names_from = c("sex","age"), )
datNew
# A tibble: 24 x 8
geo Time `F_Y70-74` `M_Y70-74` `TOT_Y70-74` `F_Y75-79` `M_Y75-79`
<chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 UK X2021W3 5 8 13 1 1
2 UK X2021W2 6 9 15 1 1
3 UK X2021W1 8 1 9 1 1
4 UK X2020W52 1 5 6 1 1
5 UK X2020W51 4 4 9 1 1
6 UK X2020W50 3 2 5 1 1
7 GER X2021W3 3 2 5 2 1
8 GER X2021W2 8 1 9 2 1
9 GER X2021W1 7 2 9 2 1
10 GER X2020W52 2 1 3 2 1
# ... with 14 more rows, and 1 more variable: TOT_Y75-79 <dbl>
BUT now when I try to use it on my real data it doesn't work. As you can see in the example the columns are double as well so I really don't know why it doesn't work on my big data set
