I would like to merge the non-na rows of a right table to a left table

Admin 1
Viewed 82

I have a list of dataframes that I am seeking to merge into a singular dataframe. Each of the dataframes contains the same number of rows and columns, as well as the same column names. However, some of the dataframes contain many values with a few non-NA values. I would like to go through the list of df's and merge the non values of the right table into the left table, whether the left table has values at that index or not.

My data appears like the following

A B  C       A B  C
1 t 5.0      1 t <NA>
2 n 2.0      2 n <NA>
3 z 3.0      3 z 2.0
4 m <NA>     4 m 4.0
5 j 1.0      5 j <NA>

I attempted to merge the list of dataframes using pd.concat and filtering conditions contained inside of the expression:

table_of_dfs = [df1, df2, df3, df4, df5]
merged_table = pd.concat(table_of_dfs[i].merge(table_of_dfs[i+1][~table_of_dfs[i+1].isnull().T.any().T], on=['A','B','C'], how='left') for i in range(len(table_of_dfs) - 1))

My desired result:

A B  C 
1 t 5.0  
2 n 2.0     
3 z 2.0     
4 m 4.0  
5 j 1.0

My actual result:

    A B  C  
    1 t 5.0  
    2 n 2.0    
    3 z 3.0    
    4 m <NA> 
    5 j 1.0 
    6 t <NA>
    7 n <NA>
    8 z 2.0
    9 m 4.0
   10 j <NA>
pandas dataframe merge python
2 Answers

Use combine_first and reduce from functools module:

from functools import reduce
dfs = [df1, df2, df3]

>>> reduce(lambda dfA, dfB: dfB.combine_first(dfA), dfs)
   A  B    C
0  1  t  5.0
1  2  n  2.0
2  3  z  2.0
3  4  m  4.0
4  5  j  6.0
5  6  p  9.0

Data:

>>> df1
   A  B    C
0  1  t  5.0
1  2  n  2.0
2  3  z  3.0
3  4  m  NaN
4  5  j  1.0

>>> df2
   A  B    C
0  1  t  NaN
1  2  n  NaN
2  3  z  2.0
3  4  m  4.0
4  5  j  NaN

>>> df3
   A  B    C
0  1  t  5.0
1  2  n  NaN
2  3  z  2.0
3  4  m  4.0
4  5  j  6.0
5  6  p  9.0  # added a new row
user_152399510

Let's call you dataframes df1 and df2. You can use where to replace the NaN values:

df2['C'] = df1['C'].where(df2['C'].isna(), df2['C'])
user_163434640
Related