Merge Row based on Condition

user_14784453 0
Viewed 127

i have df like this

   Date    Description  Debit     Credit     Balance originalIdx
0   01-03-19    AAAA        NaN        NaN        49Cr       0
1   01-03-19    ASSS        NaN      6,000.00     55Cr       1
2   NaN         XYZ         ABC        saa        NaN        1
3   01-03-19    ABZ        289.00      NaN        55Cr       3

I want this

      Date     Description       Debit        Credit     Balance  originalIdx
0   01-03-19    AAAA              NaN          NaN        49Cr        0
1   01-03-19    ASSSXYZABCsaa     NaN        6,000.00     55Cr        1
3   01-03-19    ABZ              289.00         NaN       55Cr        3

I want to merge The Row if The originalIdx Is the same , so merge the Row in Description Column

this was my real time data enter image description here

pandas dataframe python
2 Answers

Assuming that Date will have NaN if the row needs to be merged, here's the code.

first create a dummy column merged. It will merge all the values of Description, Debit, and Credit. It will only merge if the value is alpha (excludes numeric values)

Then replace Description by using groupby transform (lambda) function.

Then dropna if rows have Date as NaN. Also drop the temp column merged.

df['merged'] = df[['Description','Debit','Credit']].apply(lambda x: ''.join([str(a) for a in x if pd.notnull(a) and not isinstance(a, float)]) ,axis=1)

df['Description'] = df.groupby("originalIdx")['merged'].transform(lambda x: "".join(x))
df.dropna(subset=['Date'],inplace=True)
df.drop(columns='merged',inplace=True)
print (df)

This will give you:

       Date    Description  Debit  Credit Balance  originalIdx
0  01-03-19           AAAA    NaN     NaN    49Cr            0
1  01-03-19  ASSSXYZABCsaa    NaN  6000.0    55Cr            1
3  01-03-19            ABZ  289.0     NaN    55Cr            3

Here's the full code with data and output.

Replace your df['merged'] with the below code:

df['merged'] = df[['Description','Debit','Credit']].apply(lambda x: ''.join([str(a) for a in x if pd.notnull(a) and not isinstance(a, float)]) ,axis=1)

Full Code is:

import pandas as pd
import numpy as np
pd.set_option('display.max_columns', 200)
pd.set_option('display.max_colwidth', 250)
c = ['Date','Description','Debit','Credit','Balance','originalIdx']
d = [['01-03-19','FORTAP-MUMBAI/',np.NaN, np.NaN, '49656.25Cr',0],
    ['01-03-19','FORTAP-MUMBAI/******',np.NaN,6000.00,'55656.25Cr',1],
    [np.NaN,'UP/*ABC*/*DEF*','UPI/*PQR*/*XYZ*','paytm/NA',np.NaN,1],
['01-03-19','MBK/*ABCDEF*/*ZZZ*',289.00,np.NaN,'55357.25Cr',3]]
df = pd.DataFrame(d,columns=c)
print (df)
df['merged'] = df[['Description','Debit','Credit']].apply(lambda x: ''.join([str(a) for a in x if pd.notnull(a) and not isinstance(a, float)]) ,axis=1)
df['Description'] = df.groupby("originalIdx")['merged'].transform(lambda x: "".join(x))
df.dropna(subset=['Date'],inplace=True)
df.drop(columns='merged',inplace=True)
print (df)

Before and after output attached:

Before:

       Date           Description            Debit    Credit     Balance  originalIdx  
0  01-03-19        FORTAP-MUMBAI/              NaN       NaN  49656.25Cr             0  
1  01-03-19  FORTAP-MUMBAI/******              NaN    6000.0  55656.25Cr             1  
2       NaN        UP/*ABC*/*DEF*  UPI/*PQR*/*XYZ*  paytm/NA         NaN             1  
3  01-03-19    MBK/*ABCDEF*/*ZZZ*            289.0       NaN  55357.25Cr             3

After:

       Date                                                Description  Debit  Credit     Balance  originalIdx  
0  01-03-19                                             FORTAP-MUMBAI/    NaN     NaN   49656.25Cr           0  
1  01-03-19  FORTAP-MUMBAI/******UP/*ABC*/*DEF*UPI/*PQR*/*XYZ*paytm/NA    NaN  6000.0   55656.25Cr           1  
3  01-03-19                                         MBK/*ABCDEF*/*ZZZ*  289.0     NaN   55357.25Cr           3
user_138739800

I understand that you are using pandas!

df.fillna("").groupby("originalIdx").agg(lambda x: "".join(x))

You can do it easily with this line.

We group the column, and with agg we execute the lambda function that we concatenate the output

EDIT:

To control the data None we use .fillna (""). we replace the None by empty string

user_142960380
Related