Merging DFs from two different lists in python

Question

There are two lists where elements are DFs and having datetimeindex:

lst_1 = [ df1, df2, df3, df4]   #columns are same here 'price' 

lst_2 = [df1, df2, df3, df4]    #columns are same here 'quantity'

I am doing it with one by one using the pandas merge function. I tried to do something where i add the two list and make function like this:

def df_merge(df1 ,df1):
    p_q_df1 = pd.merge(df1,df1,  on='Dates') 
    return p_q_df1        

#this merged df has now price and quantity representing df1 from list! and list_2

still i have to apply to every pair again. Is there a better way, maybe in loop to automate this?

What kind of merge are you trying to apply? Maybe `pd.concat` can help you? — Celius Stingher, Nov 27 '19 at 19:29

score 2 · Accepted Answer · answered Nov 27 '19 at 19:45

2

Consider elementwise looping with zip which can be handled in a list comprehension.

# DATES AS INDEX
final_lst = [pd.concat(i, j, axis=1) for i, j in zip(lst_1, lst_2)]

# DATES AS COLUMN
final_lst = [pd.merge(i, j, on='Dates') for i, j in zip(lst_1, lst_2)]

answered Nov 27 '19 at 19:45

Parfait

104,375
17
94
125

Thanks it worked perfectly. Came out the desired output – Ajax Nov 27 '19 at 19:48
1

Great! Glad to help. – Parfait Nov 27 '19 at 20:00

Umar.H · Answer 2 · 2019-11-27T19:33:49.367

1

IIUC,

you could concat your df's then merge

dfs_1 = pd.concat(lst_1)
dfs_2 = pd.concat(lst_2)
pd.merge(dfs_1,dfs_2,on='Dates',how='outer') 
# change how to specify the behavior of the merge.

I'm assuming your dataframes are the same shape so they can be concatenated.

if you want to merge multiple dataframes in your list you can use the reduce function from the standard python lib using an outer merge to get every possible row.

from functools import reduce
lst_1 = [ df1, df2, df3, df4] 

df_merged = reduce(lambda  left,right: pd.merge(left,right,on=['Dates'],
                                            how='outer'), lst_1)

edited Nov 27 '19 at 19:33

answered Nov 27 '19 at 19:29

Umar.H

22,559
7
39
74

This is a much better method than my answer – alex067 Nov 27 '19 at 19:30
@Datanovice thanks for your answer. But what to do if i want output like this: ````df_online ```` which is the merge of df1 from lst1 and df from lst2. i want like this 4 DFs – Ajax Nov 27 '19 at 19:39
Thanks @ArpitGothwal I think you meant to green tick Mr Parfait's answer. – Umar.H Nov 27 '19 at 19:57
@ArpitGothwal then an upvote is totally fine but you should green tick the solution that solved your problem ! best of luck my friend – Umar.H Nov 27 '19 at 20:00

score 0 · Answer 3 · answered Nov 27 '19 at 19:29

0

lst_1 = [ df1, df2, df3, df4]   #columns are same here 'price' 

lst_2 = [df1, df2, df3, df4]    #columns are same here 'quantity'

def merge(lst_1, lst_2):
   df = pd.DataFrame()
   for _df in lst_1:
      df = df.merge(_df, on='Dates')

   for _df in lst_2:
      df = df.merge(_df, on='Dates')

answered Nov 27 '19 at 19:29

alex067

3,159
1
11
17

2

You should never call `merge`, `append`, `concat` or other large data frame operations in a loop. It can lead to [quadractic copying](https://stackoverflow.com/a/36489724/1422451) – Parfait Nov 27 '19 at 19:40

Merging DFs from two different lists in python

3 Answers3