How to add a data frame to a list of data frames

[Tamim]

I have a data frame includes summary stat for each city in the country (20 cities) and another data frame includes summary stat for the whole country. I export the cities data separately as a DF in excel files (20 data frames) How can I add the country DF to each of the 20 data frames?

# data frame includes a summary of the country:
countrylevel

# data frame includes all cities:
citylevel

# separate data for each city in list of lists:
list_data <- split(citylevel , citylevel$city)

# export df for each city as an excel file:
Map(openxlsx::write.xlsx, list_data, paste0(names(list_data), '.xlsx'))

Before exporting to excel, How can I add the countrylevel df to each city's df?

[FJCC]

If the City and Country data frames have the same columns, you can do it like this.

DF <- data.frame(City = c("A", "B", "C"), P = 1:3, S = 6:8, T = 11:13)
DF
  City P S  T
1    A 1 6 11
2    B 2 7 12
3    C 3 8 13
Country <- data.frame(City = "all", P = 20, S = 30, T = 50)
DFlist <- split(DF, DF$City)
DFlist2 <- lapply(DFlist, function(D, D2) rbind(D, D2), D2 = Country)
DFlist2
$A
  City  P  S  T
1    A  1  6 11
2  all 20 30 50

$B
  City  P  S  T
2    B  2  7 12
1  all 20 30 50

$C
  City  P  S  T
3    C  3  8 13
1  all 20 30 50

[Tamim]

Many thanks FJCC, does not work for 2 reasons:
1- country has less columns.
2- each city has several rows for multiple areas like this:

#countrylevel
id    v1   v2   v3
AC    2.2  3.6  9.2
AR    4.2  5.2  7.1
AD    4.2  5.2  7.1
#citylevel
city area   v1  v2   v3
A       er  2.2 3.6  9.2
A       ea  4.2 5.2  7.1
A       ew  4.2 5.2  7.1
B       er  6.2 7.1  9.2
B       ew  7.5 3.8  7.3
C       er  1.5 2.2  3.1
C       ea  1.8 2.3  4.7
C       ew  2.5 9.8  3.8
C       ex  1.3 2.5  4.6

[FJCC]

Given the two data frames in your last post, what do you want the combined data frame to be?

[Tamim]

I would like the combined data frame to be like this:

id    v1    v2   v3
AC    2.2   3.6  9.2
AR    4.2   5.2  7.1
AD    4.2   5.2  7.1
city area   v1   v2   v3
A       er  2.2  3.6  9.2
A       ea  4.2  5.2  7.1
A       ew  4.2  5.2  7.1
id    v1   v2   v3
AC    2.2  3.6  9.2
AR    4.2  5.2  7.1
AD    4.2  5.2  7.1
city area   v1  v2   v3
B       er  6.2 7.1  9.2
B       ew  7.5 3.8  7.3
id    v1   v2   v3
AC    2.2  3.6  9.2
AR    4.2  5.2  7.1
AD    4.2  5.2  7.1
city area   v1  v2   v3
C       er  1.5 2.2  3.1
C       ea  1.8 2.3  4.7
C       ew  2.5 9.8  3.8
C       ex  1.3 2.5  4.6

[FJCC]

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)

[Tamim]

Many thanks FJCC. It worked and did what I wanted. This is much appreciated.

[Tamim]

Quick question FJCC. This code produce an excel file for each city (perfect). In case I'd like to produce Two excel files for each city (each one contains diff info for that city), Is it possible to produce a Folder for each city and each folder includes the two excel files produced for that city?

Thanks

[FJCC]

There is a dir.create() function that you could add to the code. Something like

WriteFunc <- function(NM) {
   wb <- createWorkbook()
   addWorksheet(wb, sheetName = "Sheet1")
   writeData(wb, Country, sheet = "Sheet1")
   writeData(wb, DFlist[[NM]], sheet = "Sheet1", startRow = 4)
   dir.create(Nm)
   saveWorkbook(wb, file = paste0(NM,"/",NM,"_first", ".xlsx"))
 }

Of course, that only makes one Excel file, so you would have to make another Workbook, write data to it and run a second saveWorkBook() command.

[Tamim]

Thank you FJCC very much. Worked!