If I understand your goal correctly, you want to append the Country data to each City data frame for the purpose of writing the combined data to an Excel file, one file for each city. Because of the mismatch in the number of columns and because you seem to want to have column headers in the rows of the data frame, the combined data are not conveniently stored in a data frame. If what you really want is Excel files with the data from the City and Country data frame displayed, I would combine the data in a workbook object from the openxlsx package and just save that.
In the code below, I use my toy data sets with two rows in the Country data frame. I write that to the workbook, occupying its first three rows. I then write the City data starting in row 4.
DF <- data.frame(City = rep(c("A", "B", "C"),each = 2), area = LETTERS[1:6],
P = 1:6, S = 6:11, T = 11:16)
DF
City area P S T
1 A A 1 6 11
2 A B 2 7 12
3 B C 3 8 13
4 B D 4 9 14
5 C E 5 10 15
6 C F 6 11 16
Country <- data.frame(id = c("AR", "AD"), P = c(20,21), S = c(30,31), T = c(50,51))
Country
id P S T
1 AR 20 30 50
2 AD 21 31 51
DFlist <- split(DF, DF$City)
NAMES <- names(DFlist)
WriteFunc <- function(NM) {
wb <- createWorkbook()
addWorksheet(wb, sheetName = "Sheet1")
writeData(wb, Country, sheet = "Sheet1")
writeData(wb, DFlist[[NM]], sheet = "Sheet1", startRow = 4)
saveWorkbook(wb, file = paste0(NM, ".xlsx"))
}
Map(WriteFunc, NAMES)