CODEWITHSUNDEEP
pythonpython-3.xpandaspandas-groupby
user1718097
user1718097

Reputation: 4292

Is it possible to split a Pandas dataframe using groupby and merge each group with separate dataframes

I have a Pandas dataframe that contains a grouping variable. I would like to merge each group with other dataframes based on the contents of one of the columns. So, for example, I have a dataframe, dfA, which can be defined as:

dfA = pd.DataFrame({'a':[1,2,3,4,5,6],
                    'b':[0,1,0,0,1,1],
                    'c':['a','b','c','d','e','f']})

   a  b  c
0  1  0  a
1  2  1  b
2  3  0  c
3  4  0  d
4  5  1  e
5  6  1  f

Two other dataframes, dfB and dfC, contain a common column ('a') and an extra column ('d') and can be defined as:

dfB = pd.DataFrame({'a':[1,2,3],
                    'd':[11,12,13]})

   a   d
0  1  11
1  2  12
2  3  13


dfC = pd.DataFrame({'a':[4,5,6],
                    'd':[21,22,23]})

   a   d
0  4  21
1  5  22
2  6  23

I would like to be able to split dfA based on column 'b' and merge one of the groups with dfB and the other group with dfC to produce an output that looks like:

   a  b  c   d
0  1  0  a  11
1  2  1  b  12
2  3  0  c  13
3  4  0  d  21
4  5  1  e  22
5  6  1  f  23

In this simplified version, I could concatenate dfB and dfC and merge with dfA without splitting into groups as shown below:

dfX = pd.concat([dfB,dfC])
dfA = dfA.merge(dfX,on='a',how='left')      
print(dfA)

   a  b  c   d
0  1  0  a  11
1  2  1  b  12
2  3  0  c  13
3  4  0  d  21
4  5  1  e  22
5  6  1  f  23

However, in the real-world situation, the smaller dataframes will be generated from multiple different complex sources; generating the dataframes and combining into a single dataframe beforehand may not be feasible because there may be overlapping data on the column that will be used for merging the dataframes (but this will be avoided if the dataframe can be split based on the grouping variable). Is it possible to use Pandas groupby() method to do this instead? I was thinking of something like the following (which doesn't work, perhaps because I'm not combining the groups into a new dataframe correctly):

grouped = dfA.groupby('b')
for name, group in grouped:
    if name == 0:
        group = group.merge(dfB,on='a',how='left')
    elif name == 1:
        group = group.merge(dfC,on='a',how='left')

Any thoughts would be appreciated.

Upvotes: 1

Views: 774

Answers (1)

BENY
BENY

Reputation: 323316

This will fix your code 

l=[]
grouped = dfA.groupby('b')
for name, group in grouped:
    if name == 0:
        group = group.merge(dfB,on='a',how='left')
    elif name == 1:
        group = group.merge(dfC,on='a',how='left')
    l.append(group)
pd.concat(l)
Out[215]: 
   a  b  c     d
0  1  0  a  11.0
1  3  0  c  13.0
2  4  0  d   NaN
0  2  1  b   NaN
1  5  1  e  22.0
2  6  1  f  23.0

Upvotes: 3

Related Questions