CODEWITHSUNDEEP
pythonpandasdataframejoin
N08
N08

Reputation: 1315

Concatenate strings based on inner join

I have two DataFrames containing the same columns; an id, a date and a str:

df1 = pd.DataFrame({'id':      ['1', '2', '3', '4', '10'], 
                    'date':    ['4', '5', '6', '7', '8'],
                    'str':     ['a', 'b', 'c', 'd', 'e']})

df2 = pd.DataFrame({'id':      ['1', '2', '3', '4', '12'], 
                    'date':    ['4', '5', '6', '7', '8'],
                    'str':     ['A', 'B', 'C', 'D', 'Q']})

I would like to join these two datasets on the id and date columns, and create a resulting column that is the concatenation of str:

df3 = pd.DataFrame({'id':      ['1',  '2',   '3',  '4', '10', '12'], 
                    'date':    ['4',  '5',   '6',  '7', '8',  '8'],
                    'str':     ['aA', 'bB', 'cC', 'dD', 'e', 'Q']})

I guess I can make an inner join and then concatenate the strings, but is there an easier way to achieve this?

Upvotes: 6

Views: 473

Answers (2)

BENY
BENY

Reputation: 323316

IIUC concat+groupby

pd.concat([df1,df2]).groupby(['date','id']).str.sum().reset_index()
Out[9]: 
  date  id str
0    4   1  aA
1    5   2  bB
2    6   3  cC
3    7   4  dD
4    8  10   e
5    8  12   Q

And if we consider the efficiency using sum() base on level

pd.concat([df1,df2]).set_index(['date','id']).sum(level=[0,1]).reset_index()
Out[12]: 
  date  id str
0    4   1  aA
1    5   2  bB
2    6   3  cC
3    7   4  dD
4    8  10   e
5    8  12   Q

Upvotes: 6

cs95
cs95

Reputation: 402814

Using radd:

i = df1.set_index(['date', 'id'])
j = df2.set_index(['date', 'id'])

j['str'].radd(i['str'], fill_value='').reset_index()

  date  id str
0    4   1  aA
1    5   2  bB
2    6   3  cC
3    7   4  dD
4    8  10   e
5    8  12   Q

This should be pretty fast.

Upvotes: 6

Related Questions