How can I concatenate rows into column names?

MartinVF 0
Viewed 24

so I have a dataframe imported from excel that looks something like this:

X1 A A A A B B B B B B
Region 2001 2001 2002 2002 2001 2001 2002 2002 2003 2003
City Home Work Home Work Home Work Home Work Home Work
City1 1 1 1 1 1 1 1 1 1 1
City2 2 2 2 2 2 2 2 2 2 2
City3 3 3 3 3 3 3 3 3 3 3

I want to turn it into something that looks like this:

X1RegionCity A2001Home A2001Work A2002Home A2002Work B2001Home B2001Work B2002Home B2002Work B2003Home B2003Work
City1 1 1 1 1 1 1 1 1 1 1
City2 2 2 2 2 2 2 2 2 2 2
City3 3 3 3 3 3 3 3 3 3 3

How can I combine three first rows into the column name? I eventually plan to later turn this into a long DF with the year, and Home/Work as a separate column. Is there a way to do this entirely in R, with or without dplyr? Thanks!

dplyr r
1 Answers

You can use paste0:

colnames(df) <- paste0(colnames(df), df[1, ], df[2, ])
df <- df[-c(1, 2), ]

  X1RegionCity A2001Home A2001Work A2002Home A2002Work B2001Home B2001Work B2002Home B2002Work B2003Home B2003Work
3        City1         1         1         1         1         1         1         1         1         1         1
4        City2         2         2         2         2         2         2         2         2         2         2
5        City3         3         3         3         3         3         3         3         3         3         3
Maël0
Related