Pandas - Merge rows based on similar contents of two cells

user_1884405 0
Viewed 239

I have a pandas dataframe which resembles the following. I am trying to merge all the rows which contains identical pair of ID and CountryCode values.

records = [ (1, 'IN', 'yes' , '', '' , '', '') ,
             (1, 'MY', '' , 'yes', '' , '', '' ) ,
             (1, 'MY', '' , '', 'yes', '', '' ) ,
             (1, 'MY', '' , '' , '' , 'yes', '') ,
             (1, 'US', '' , '', '' , '', 'yes') ,
             (2, 'MY', 'yes' , '', '' , '', ''),
             (2, 'UK', '' , 'yes', '' , '', '')]

dfRecords = pd.DataFrame(records, columns = ['ID' , 'CountryCode', 'Address' , 'MobileNo', 'HomeNo', 'OfficeNo', 'TacNo'])

Output:

ID  CountryCode Address MobileNo    HomeNo  OfficeNo    TacNo
1   IN          yes             
1   MY                  yes         
1   MY                              yes     
1   MY                                      yes 
1   US                                                  yes
2   MY          yes             
2   UK                  yes

This is what I need

ID  CountryCode Address MobileNo    HomeNo  OfficeNo    TacNo
1   IN          yes             
1   MY                  yes         yes     yes
1   US                                                  yes
2   MY          yes             
2   UK                  yes

I have an idea that I have to use groupby() based on ID and CountryCode columns but I am unable to merge the rows together.

groupings = dfRecords.groupby(['ID','CountryCode'])
groupings.groups

Output:

{(1, 'IN'): Int64Index([0], dtype='int64'),
 (1, 'MY'): Int64Index([1, 2, 3], dtype='int64'),
 (1, 'US'): Int64Index([4], dtype='int64'),
 (2, 'MY'): Int64Index([5], dtype='int64'),
 (2, 'UK'): Int64Index([6], dtype='int64')}
pandas python
1 Answers

max

Because 'yes' is greater than ''

dfRecords.groupby(['ID', 'CountryCode'], as_index=False).max()

   ID CountryCode Address MobileNo HomeNo OfficeNo TacNo
0   1          IN     yes                               
1   1          MY              yes    yes      yes      
2   1          US                                    yes
3   2          MY     yes                               
4   2          UK              yes

first

Without relying on max

g = dfRecords.mask(dfRecords == '').groupby(['ID', 'CountryCode'], as_index=False)
g.first().fillna('')

   ID CountryCode Address MobileNo HomeNo OfficeNo TacNo
0   1          IN     yes                               
1   1          MY              yes    yes      yes      
2   1          US                                    yes
3   2          MY     yes                               
4   2          UK              yes
user_23366540
Related