How to pivot a dataframe on a two level header in R code

When you read your data in, use the option to specify starting the reading at the row where "Country" is found. For example, you would use startRow for read.xlsx -- not sure what you're using to read your Excel in. ... represents any other arguments to read.xlsx.

x <- read.xlsx("your_file.xlsx", startRow = row_that_country_is_found, ...)

After you've done this, you may need to clean up your column names. The years might be read in as "X2015" or something similar. We'll assume that "x" now looks like this (random data):

x
#   Country      2015      2014      2013      2012       2011      2010
# 1       A 0.6883601 0.9199372 0.8996433 0.9644212 0.97940387 0.7564401
# 2       B 0.1572208 0.6507811 0.9444197 0.9420349 0.06572698 0.1445383
# 3       C 0.7599602 0.8948640 0.6020316 0.7315661 0.90211468 0.5831917
#          2015       2014      2013      2012      2011       2010
# 1 0.26770837 0.45293675 0.2363191 0.9718356 0.3290432 0.57801166
# 2 0.39756729 0.06007054 0.7108505 0.6843454 0.1690740 0.93432731
# 3 0.05011677 0.30123347 0.2633371 0.5079645 0.9527117 0.04442355

From here, you should rename your columns.

names(x) <- c("Country", paste(rep(c("ATM", "POS"), each = 6),
              names(x)[-1], sep = "_"))

Your names should now look like this:

names(x)
#  [1] "Country"  "ATM_2015" "ATM_2014" "ATM_2013" "ATM_2012" "ATM_2011"
#  [7] "ATM_2010" "POS_2015" "POS_2014" "POS_2013" "POS_2012" "POS_2011"
# [13] "POS_2010"

Then, you can pivot_longer, separate the names into multiple variables, and then pivot_wider to get your desired output.

x %>% 
  pivot_longer(-Country) %>% 
  separate(name, into = c("Type", "Year")) %>% 
  pivot_wider(names_from = Type, values_from = value)
# # A tibble: 18 x 4
#    Country Year     ATM    POS
#    <chr>   <chr>  <dbl>  <dbl>
#  1 A       2015  0.688  0.268 
#  2 A       2014  0.920  0.453 
#  3 A       2013  0.900  0.236 
#  4 A       2012  0.964  0.972 
#  5 A       2011  0.979  0.329 
#  6 A       2010  0.756  0.578 
#  7 B       2015  0.157  0.398 
#  8 B       2014  0.651  0.0601
# ... 
# 17 C       2011  0.902  0.953 
# 18 C       2010  0.583  0.0444
          

We can also use melt from data.table which can also split by specifying any patterns in the column name. The regex ^ matches the beginning of the column name followed by substring 'ATM' or 'POS'. So, all the columns with 'ATM' will go into a single column 'ATM' while the 'POS' as next

library(data.table)
melt(setDT(df1), measure = patterns('^ATM', "^POS"), 
          value.name = c("ATM", "POS"), variable.name = "Year")

Or using pivot_longer with names_sep and specify the names_to so that it would split the columns at the delimiter _. The order of names_to indicates where the 'year' and the value in that column (.value) would go into. Here, we want the suffix of column name to be 'year' while the prefix (before the _) should get the values from that column

library(tidyr)
library(dplyr)
df1 %>%
    pivot_longer(cols = -Country, names_to = c(".value", "Year"), names_sep="_")
# A tibble: 18 x 4
#   Country Year     ATM    POS
#   <chr>   <chr>  <dbl>  <dbl>
# 1 A       2015  0.688  0.268 
# 2 A       2014  0.920  0.453 
# 3 A       2013  0.900  0.236 
# 4 A       2012  0.964  0.972 
# 5 A       2011  0.979  0.329 
# 6 A       2010  0.756  0.578 
# 7 B       2015  0.157  0.398 
# 8 B       2014  0.651  0.0601
# 9 B       2013  0.944  0.711 
#10 B       2012  0.942  0.684 
#11 B       2011  0.0657 0.169 
#12 B       2010  0.145  0.934 
#13 C       2015  0.760  0.0501
#14 C       2014  0.895  0.301 
#15 C       2013  0.602  0.263 
#16 C       2012  0.732  0.508 
#17 C       2011  0.902  0.953 
#18 C       2010  0.583  0.0444

NOTE: Here it is based on the _. So, it can handle any number of columns and it is not true about what is described in another post

data

df1 <- structure(list(Country = c("A", "B", "C"), ATM_2015 = c(0.6883601, 
0.1572208, 0.7599602), ATM_2014 = c(0.9199372, 0.6507811, 0.894864
), ATM_2013 = c(0.8996433, 0.9444197, 0.6020316), ATM_2012 = c(0.9644212, 
0.9420349, 0.7315661), ATM_2011 = c(0.97940387, 0.06572698, 0.90211468
), ATM_2010 = c(0.7564401, 0.1445383, 0.5831917), POS_2015 = c(0.26770837, 
0.39756729, 0.05011677), POS_2014 = c(0.45293675, 0.06007054, 
0.30123347), POS_2013 = c(0.2363191, 0.7108505, 0.2633371), POS_2012 = c(0.9718356, 
0.6843454, 0.5079645), POS_2011 = c(0.3290432, 0.169074, 0.9527117
), POS_2010 = c(0.57801166, 0.93432731, 0.04442355)), class = "data.frame",
row.names = c(NA, 
-3L))

You can use this solution for any number of devices and years, compared to previous that handle only ATM and POS. This was done using your first data screen (Just load your excel file):

library(xlsx)
library(zoo)
library(reshape2)
library(dplyr)
library(tidyr)

#Data
dat <- structure(list(NA. = structure(1:2, .Label = c("Country", "France"
), class = "factor"), ATM = c(2015, 12), NA..1 = c(2014, 3), 
    NA..2 = c(2013, 4), NA..3 = c(2012, 6), NA..4 = c(2011, 7
    ), NA..5 = c(2010, 8), POS = c(2015, 9), NA..6 = c(2014, 
    9), NA..7 = c(2013, 12), NA..8 = c(2012, 11), NA..9 = c(2011, 
    56), NA..10 = c(2010, 78)), class = "data.frame", row.names = c(NA, 
-2L))

#Process names
names(dat)[1]<-'Country'
vecnames <- names(dat)[-1]
vecnames[which(grepl('NA',vecnames))]<-NA
vecnames <- na.locf(vecnames)
#Include year
vecnames2 <- dat[1,-1]
#Join
vecnames3 <- paste0(vecnames,'_',vecnames2)
#Assign names
names(dat)[-1]<-vecnames3
#Remove first row
dat <- dat[-1,]
#Melt data
melt.data <- melt(dat,id.vars = 'Country')
melt.data$variable<-as.character(melt.data$variable)
#Create columns
melt.data %>%
  separate(variable, c("Device", "Year"), "_") -> melt.data
#Output
DataG <- reshape(melt.data, idvar=c('Country','Year'), timevar='Device', direction="wide")

Output:

  Country Year value.ATM value.POS
1  France 2015        12         9
2  France 2014         3         9
3  France 2013         4        12
4  France 2012         6        11
5  France 2011         7        56
6  France 2010         8        78