How to automate left join of multiple data frames with single data frame one by one in R

Question

i have 206 data frames named after country(FRANCE,GERMANY,HONGKONG...) with same 37 variables in these 206 files and another data frame name pf with 9 variables.

i want to do left join of these 206 data frames with PF data frame one by one selecting all variables and store in other data frame. left join is based on two condition( hcom_id=tnow_id and city=region). hcom_id and city are variables in 206 different data frames and tnow_id and region are in PF

i am using sqldf package to join: a<-sqldf("select * from FRANCE left join PF on (FRANCE.hcom_id=pf.tnow_id and FRANCE.city=pf.region)")

I am struggling to automate it using loop rather than doing 206 times separately.

Any idea please suggest

Answer 1

If your data resides in clean form inside a database already:

write an sql statement that includes a series of UNION statements for your 206 country tables. You can use R or any other programming language to create this string.

save this series of 206 UNIONs as a view (maybe as an indexed view / materialized view), or as a new table, or as a stored-procedure that creates a temp-table on your database server

perform one SQL join on the DB server

fetch the data batch-wise

Answer 2

It is tough for me to tell you a precise way I would do this without looking at the data frames.

However, the way I might approach this is would involve the following steps:

• Load all 206 data frame;
• Rename all variables such that they begin with the data frames name (sub + an apply or loop might suffice here);
• Append all data frames columns wise (ugly, I know);
• Use tidyr to reshape the data and separate the date in such way that I would be abble to joing it with the PF data frame;

This last step is a bit vague, but that is how I would approach it.

Answer 3

Here is one possible solution to the problem (if the dataframes fit in memory):

1. Put the dataframes into a list, naming the list entries the dataframe names
2. Wrap the sqldf command into a function with one input, names(list)
3. Use lapply to apply the function to each dataframe in the list

This is an example function that would work. It takes an input dataframe and left joins columns from a dataframe PF.

sqldf_join_wrapper = function(df_title) {

    temp_df = sqldf(
    paste0("SELECT distinct a.* 
           FROM ", df_title, " a
           LEFT JOIN PF b
           ON (a.hcom_id = b.tnow_id and a.city = b.region)")
    )

    return(temp_df)
}

You could use this function in an lapply statement to left join the columns from PF into all 206 of your country dataframes at once (assuming they are in a list). However, you need to pass in the titles of the input dataframes to the sql string, and it seems like lapply makes it a bit difficult based on my trial and error. Here is a solution:

output_list = lapply(names(initial_list_of_dfs), sqldf_join_wrapper)

Output list should then contain the initial dataframes.