Merge Pandas 2 DataFrame, with different number of rows and columns

Question

I have my original DataFrame (df1).
I create a new DataFrame (df2) with only some rows from the first one (df1).
I add some columns to this new DataFrame (df2).
Now I want to update first DataFrame (df1) with my new content (df2).

So...I need to merge 2 DataFrame and the second DataFrame has more columns and less rows.

import pandas as pd

print(pd.__version__)
# 0.24.1

index1 = [1, 2, 3, 4]
columns1 = ['a', 'b', 'c']
data1 = [
    ['a1', 'b1', 'c1'],
    ['a2', 'b2', 'c2'],
    ['a3', 'b3', 'c3'],
    ['a4', 'b4', 'c4']]


index2 = [1, 4]
columns2 = ['b', 'c', 'd', 'e']
data2 = [
    ['b1', 'c1', '<D1', 'e1'],
    ['b4', '<C4', 'd4', 'e4']]

df1 = pd.DataFrame(index=index1, columns=columns1, data=data1)
df2 = pd.DataFrame(index=index2, columns=columns2, data=data2)

print(df1)
#     a   b   c
# 1  a1  b1  c1
# 2  a2  b2  c2
# 3  a3  b3  c3
# 4  a4  b4  c4


print(df2)
#     b     c     d   e
# 1  b1    c1   <D1  e1
# 4  b4   <C4    d4  e4

# What I want:
#     a    b    c    d    e
# 1  a1   b1   c1  <D1   e1
# 2  a2   b2   c2  NaN  NaN
# 3  a3   b3   c3  NaN  NaN
# 4  a4   b4  <C4   d4   e4

I tried, but I'm lost with all the .merge, .update, .concat, .join, .combine_first etc. methods and all parameters. How can I simply merge these 2 DataFrame?

Hannah · Accepted Answer · 2019-06-07 09:42:14Z

3

I couldn't do it in one line but this should work

df1.update(df2)
df1 = df1.merge(df2, how='left')

And then for some reason "merge" resets the index, so if you still want 1 to 4:

df1.index = index1

Out[]: 
    a   b    c    d    e
1  a1  b1   c1  <D1   e1
2  a2  b2   c2  NaN  NaN
3  a3  b3   c3  NaN  NaN
4  a4  b4  <C4   d4   e4

answered Jun 7, 2019 at 9:42

Hannah

6628 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cyrille Over a year ago

It works for me, thank you. Kind of strange merge behaviour but it's ok!

Hannah Over a year ago

The merge thing is really strange! I tried to figure a way around it but this is probably the simplest. I found this if you're interested: stackoverflow.com/questions/11976503/…

Collectives™ on Stack Overflow

Merge Pandas 2 DataFrame, with different number of rows and columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related