Merge two DataFrames with some equal columns

Question

I have two csv files:

1.csv

id,noteId,text
id2,idNote19,This is my old text 2
id5,idNote13,This is my old text 5
id1,idNote12,This is my old text 1
id3,idNote10,This is my old text 3
id4,idNote11,This is my old text 4

2.csv

id,noteId,text,other
id3,idNote10,new text 3,On1
id2,idNote19,My new text 2,Pre8

Loading them like:

>>> df1 = pd.read_csv('1.csv', encoding='utf-8').set_index('id')
>>> df2 = pd.read_csv('2.csv', encoding='utf-8').set_index('id')
>>>
>>> print df1
       noteId                   text
id
id2  idNote19  This is my old text 2
id5  idNote13  This is my old text 5
id1  idNote12  This is my old text 1
id3  idNote10  This is my old text 3
id4  idNote11  This is my old text 4
>>> print df2
        noteId            text other
id
id3   idNote10      new text 3   On1
id2   idNote19   My new text 2  Pre8
id5        NaN   My new text 2   Hl0
id22  idNote22  My new text 22    M1

I need merge both DataFrames in something like this (ovewriting values on df1 that are empty on df2, adding extra columns and rows that not exists on df1):

        noteId                   text other
id
id2   idNote19          My new text 2  Pre8
id5        NaN          My new text 2   Hl0
id1   idNote12  This is my old text 1   NaN
id3   idNote10             new text 3   On1
id4   idNote11  This is my old text 4   NaN
id22  idNote22         My new text 22    M1

My real DataFrames has other columns that should be merged too, not just text

I tried using merge getting something like:

>>> df1 = pd.read_csv('1.csv', encoding='utf-8')
>>> df2 = pd.read_csv('2.csv', encoding='utf-8')
>>>
>>> print df1
    id    noteId                   text
0  id2  idNote19  This is my old text 2
1  id5  idNote13  This is my old text 5
2  id1  idNote12  This is my old text 1
3  id3  idNote10  This is my old text 3
4  id4  idNote11  This is my old text 4
>>> print df2
    id    noteId           text
0  id3  idNote10     new text 3
1  id2  idNote19  My new text 2
>>>
>>> print merge(df1, df2, how='left', on=['id'])
    id  noteId_x                 text_x  noteId_y         text_y
0  id2  idNote19  This is my old text 2  idNote19  My new text 2
1  id5  idNote13  This is my old text 5       NaN            NaN
2  id1  idNote12  This is my old text 1       NaN            NaN
3  id3  idNote10  This is my old text 3  idNote10     new text 3
4  id4  idNote11  This is my old text 4       NaN            NaN
>>>

But it's not what I need. I don't know if I'm on the right path and should merge the suffixed columns or if there is a better way to do this.

Thanks!

Update: Added ovewriting values on df1 that are empty on df2, adding extra columns on df2 that should be present on df1 after "merge" and rows that should be appended on df1

--

SOLUTION

Based on a @U2EF1 (thanks!) comment, I found the solution:

df1.fillna(value='None', inplace=True)
df2.fillna(value='None', inplace=True)

concat([df1, df2]).groupby('id').last().fillna(value='None')

In my case, it's very important to define a default "empty" value, that's why the fillna.

Answer 1

Usually you can solve this with the proper index:

df1.set_index(['id', 'noteId'], inplace=True)
df1.update(df2)

(And if you don't want that index after, just df1.reset_index(inplace=True))

Answer 2

Edited to update add rows,columns and update data, Efficiently merging on the Indexes

Code to update your df1 with df2 data...

    df1 = """id,noteId,text
id2,idNote19,This is my old text 2
id5,idNote13,This is my old text 5
id1,idNote12,This is my old text 1
id3,idNote10,This is my old text 3
id4,idNote11,This is my old text 4"""

df2 ="""id,noteId,text,other
id3,idNote10,My new text 3,On1
id2,idNote19,My new text 2,Pre8
id5,NaN,My new text 2,Hl0
id22,idNote22,My new text 22,M1"""


df1 = pd.read_csv(StringIO.StringIO(df1),sep=",",index_col='id')#this is how you should
df2 = pd.read_csv(StringIO.StringIO(df2),sep=",",index_col='id')#set your index in read_csv not after

**SOLUTION**

df = pd.merge(df2,df1,how='outer',on=df1.columns.tolist(),left_index=True,right_index=True)
#joined on indexes for speed

OUTPUT

>>print df

        noteId                   text other
id                                         
id1   idNote12  This is my old text 1   NaN
id2   idNote19          My new text 2  Pre8
id22  idNote22         My new text 22    M1
id3   idNote10             new text 3   On1
id4   idNote11  This is my old text 4   NaN
id5        NaN          My new text 2   Hl0

Reason it works...

pd.merge has a couple of multipurpose params. The on key actually is actually only used to join the two dataframes when the left_index and right_index keys are set to False - the default value. Otherwise it will just join the identically named columns that are found from the on value. In this case the two columns 'text' and 'noteId'. (I made it a more general by using df1.columns.tolist() as the param - this means any identically named columns in df2 will overwrite the data from df1 instead of marking it text_y)

Using the more general on key (df1.values.tolist()) you can actually loop through a bunch of csvs updating the data from the dataframe as you go

**3X faster than accepted solution**

In [25]: %timeit       pd.merge(df2,df1,how='outer',on=df1.columns.tolist(),left_index=True,right_index=True)
1000 loops, best of 3: 1.11 ms per loop

accepted solution

In [30]: %timeit pd.concat([df1, df2]).groupby('noteId').last().fillna(value='None')
100 loops, best of 3: 3.29 ms per loop