CODEWITHSUNDEEP
pythonpandas
swifty
swifty

Reputation: 1282

Pandas - Merge multiple columns and sum

I have a main df like so:

index   A  B  C
5       1  5  8
6       2  4  1
7       8  3  4
8       3  9  5

and an auxiliary df2 that I want to add to the main df like so:

index   A  B  
5       4  2 
6       4  3 
7       7  1 
8       6  2

Columns A & B are the same name, however the main df contains many columns that the secondary df2 does not. I want to sum the columns that are common and leave the others as is.

Output:

index   A  B  C
5       5  7  8
6       6  7  1
7       15 4  4
8       9  11 5

Have tried variations of df.join, pd.merge and groupby but having no luck at the moment.

Last Attempt:

df.groupby('index').sum().add(df2.groupby('index').sum())

But this does not keep common columns.

pd.merge I am getting suffix _x and _y

Upvotes: 1

Views: 4220

Answers (2)

jezrael
jezrael

Reputation: 863761

Use add only with same columns by intersection:

c = df.columns.intersection(df2.columns)
df[c] = df[c].add(df2[c], fill_value=0)
print (df)
        A   B  C
index           
5       5   7  8
6       6   7  1
7      15   4  4
8       9  11  5

If use only add, integers columns which not matched are converted to floats:

df = df.add(df2, fill_value=0)
print (df)
        A   B    C
index             
5       5   7  8.0
6       6   7  1.0
7      15   4  4.0
8       9  11  5.0

EDIT:

If possible strings common columns:

print (df)
       A  B  C  D
index            
5      1  5  8  a
6      2  4  1  e
7      8  3  4  r
8      3  9  5  w
print (df2)
       A  B  C  D
index            
5      1  5  8  a
6      2  4  1  e
7      8  3  4  r
8      3  9  5  w

Solution is similar, only filter first only numeric columns by select_dtypes:

c = df.select_dtypes(np.number).columns.intersection(df2.select_dtypes(np.number).columns)
df[c] = df[c].add(df2[c], fill_value=0)
print (df)
        A   B  C  D
index              
5       5   7  8  a
6       6   7  1  e
7      15   4  4  r
8       9  11  5  w

Upvotes: 3

Sociopath
Sociopath

Reputation: 13426

Not the cleanest way but it might work.

df_new = pd.DataFrame()
df_new['A'] = df['A'] + df2['A']
df_new['B'] = df['B'] + df2['B']
df_new['C'] = df['C']


print(df_new)

    A   B  C
0   5   7  8
1   6   7  1
2  15   4  4
3   9  11  5

Upvotes: -1

Related Questions