Python: pandas merge multiple dataframes

Question

I have diferent dataframes and need to merge them together based on the date column. If I only had two dataframes, I could use df1.merge(df2, on='date'), to do it with three dataframes, I use df1.merge(df2.merge(df3, on='date'), on='date'), however it becomes really complex and unreadable to do it with multiple dataframes.

All dataframes have one column in common -date, but they don't have the same number of rows nor columns and I only need those rows in which each date is common to every dataframe.

So, I'm trying to write a recursion function that returns a dataframe with all data but it didn't work. How should I merge multiple dataframes then?

I tried diferent ways and got errors like out of range, keyerror 0/1/2/3 and can not merge DataFrame with instance of type .

This is the script I wrote:

dfs = [df1, df2, df3] # list of dataframes

def mergefiles(dfs, countfiles, i=0):
    if i == (countfiles - 2): # it gets to the second to last and merges it with the last
        return

    dfm = dfs[i].merge(mergefiles(dfs[i+1], countfiles, i=i+1), on='date')
    return dfm

print(mergefiles(dfs, len(dfs)))

An example: df_1:

May 19, 2017;1,200.00;0.1%
May 18, 2017;1,100.00;0.1%
May 17, 2017;1,000.00;0.1%
May 15, 2017;1,901.00;0.1%

df_2:

May 20, 2017;2,200.00;1000000;0.2%
May 18, 2017;2,100.00;1590000;0.2%
May 16, 2017;2,000.00;1230000;0.2%
May 15, 2017;2,902.00;1000000;0.2%

df_3:

May 21, 2017;3,200.00;2000000;0.3%
May 17, 2017;3,100.00;2590000;0.3%
May 16, 2017;3,000.00;2230000;0.3%
May 15, 2017;3,903.00;2000000;0.3%

Expected merge result:

May 15, 2017;  1,901.00;0.1%;  2,902.00;1000000;0.2%;   3,903.00;2000000;0.3%

user_8704112 · Answer

functools.reduce and pd.concat are good solutions but in term of execution time pd.concat is the best.

from functools import reduce
import pandas as pd

dfs = [df1, df2, df3, ...]
nan_value = 0

# solution 1 (fast)
result_1 = pd.concat(dfs, join='outer', axis=1).fillna(nan_value)

# solution 2
result_2 = reduce(lambda df_left,df_right: pd.merge(df_left, df_right, 
                                              left_index=True, right_index=True, 
                                              how='outer'), 
                  dfs).fillna(nan_value)

user_11396055 · Answer

Another way to combine: functools.reduce

From documentation:

For example, reduce(lambda x, y: x+y, [1, 2, 3, 4, 5]) calculates ((((1+2)+3)+4)+5). The left argument, x, is the accumulated value and the right argument, y, is the update value from the iterable.

So:

from functools import reduce
dfs = [df1, df2, df3, df4, df5, df6]
df_final = reduce(lambda left,right: pd.merge(left,right,on='some_common_column_name'), dfs)

user_12246706 · Answer

Look at this pandas three-way joining multiple dataframes on columns

filenames = ['fn1', 'fn2', 'fn3', 'fn4',....]
dfs = [pd.read_csv(filename, index_col=index_col) for filename in filenames)]
dfs[0].join(dfs[1:])

user_7606453 · Answer

@everestial007 's solution worked for me. This is how I improved it for my use case, which is to have the columns of each different df with a different suffix so I can more easily differentiate between the dfs in the final merged dataframe.

from functools import reduce
import pandas as pd
dfs = [df1, df2, df3, df4]
suffixes = [f"_{i}" for i in range(len(dfs))]
# add suffixes to each df
dfs = [dfs[i].add_suffix(suffixes[i]) for i in range(len(dfs))]
# remove suffix from the merging column
dfs = [dfs[i].rename(columns={f"date{suffixes[i]}":"date"}) for i in range(len(dfs))]
# merge
dfs = reduce(lambda left,right: pd.merge(left,right,how='outer', on='date'), dfs)

user_10029392 · Answer

I had a similar use case and solved w/ below. Basically captured the the first df in the list, and then looped through the reminder and merged them where the result of the merge would replace the previous.

Edit: I was dealing w/ pretty small dataframes - unsure how this approach would scale to larger datasets. #caveatemptor

import pandas as pd
df_list = [df1,df2,df3, ...dfn]
# grab first dataframe
all_merged = df_list[0]
# loop through all but first data frame
for to_merge in df_list[1:]:
    # result of merge replaces first or previously
    # merged data frame w/ all previous fields
    all_merged = pd.merge(
        left=all_merged
        ,right=to_merge
        ,how='inner'
        ,on=['some_fld_across_all']
        )

# can easily have this logic live in a function
def merge_mult_dfs(df_list):
    all_merged = df_list[0]
    for to_merge in df_list[1:]:
        all_merged = pd.merge(
            left=all_merged
            ,right=to_merge
            ,how='inner'
            ,on=['some_fld_across_all']
            )
    return all_merged

user_7311260 · Answer

For me the index is ignored without explicit instruction. Example:

    > x = pandas.DataFrame({'a': [1,2,2], 'b':[4,5,5]})
    > x
        a   b
    0   1   4
    1   2   5
    2   2   5

    > x.drop_duplicates()
        a   b
    0   1   4
    1   2   5

( duplicated lines removed despite different index)

Python: pandas merge multiple dataframes

12 Answers