Python: How do I merge rows that shares the same name of "Ocode" or "Ccode in dataframe

user_12853501 0
Viewed 51

I'm thinking of using pandas to merge several repetitive rows of "Ocode" and Ccode". Ideally I want only one "Ocode" or "Ccode" per row. When there are repetitive dates under c## (I.E. c21), only the latest date is kept. Separate dates under different column with the same "Ocode"/"Ccode" should also be merged.

(For reference purpose: O and C code correspondingly represents Organization Code and Corporation code.)

This is the heading of the dataframe.

Num      Ocode      Ccode         c21  c57         c58  c59  c70         c71         c74         c75
0    BK0001000        NaN         NaN  NaN         NaN  NaN  NaN         NaN         NaN  2021-02-09
1    CU0030000        NaN         NaN  NaN         NaN  NaN  NaN  2021-12-31         NaN         NaN
2    CU0030000        NaN         NaN  NaN         NaN  NaN  NaN  2021-12-31         NaN         NaN
3    CU0048000        NaN         NaN  NaN  2018-06-19  NaN  NaN         NaN         NaN         NaN
4    CU0056000        NaN         NaN  NaN         NaN  NaN  NaN         NaN  2020-06-04         NaN
...        ...        ...         ...  ...         ...  ...  ...         ...         ...         ...         

2384       NaN  CU0280002  2017-12-31  NaN         NaN  NaN  NaN         NaN         NaN         NaN
2385       NaN  CU0280002  2016-12-31  NaN         NaN  NaN  NaN         NaN         NaN         NaN
2386       NaN  CU0280002         NaN  NaN         NaN  NaN  NaN         NaN  2017-12-31         NaN
2387       NaN  CU0659001         NaN  NaN         NaN  NaN  NaN         NaN  2022-05-31         NaN

Which should become ---->

Num      Ocode      Ccode         c21  c57         c58  c59  c70         c71         c74         c75
0    BK0001000        NaN         NaN  NaN         NaN  NaN  NaN         NaN         NaN  2021-02-09
1    CU0030000        NaN         NaN  NaN         NaN  NaN  NaN  2021-12-31         NaN         NaN
3    CU0048000        NaN         NaN  NaN  2018-06-19  NaN  NaN         NaN         NaN         NaN
4    CU0056000        NaN         NaN  NaN         NaN  NaN  NaN         NaN  2020-06-04         NaN
...        ...        ...         ...  ...         ...  ...  ...         ...         ...         ...         
2384       NaN  CU0280002  2017-12-31  NaN         NaN  NaN  NaN         NaN  2017-12-31         NaN
2387       NaN  CU0659001         NaN  NaN         NaN  NaN  NaN         NaN  2022-05-31         NaN

Attempt:

import os
import xlsxwriter
import pandas as pd
import numpy as np

# Pandas and XR

df = pd.read_excel('Result - Company.xlsx')

#ic = 'c21'#IndustryCode
ic = 'c57'
#ic = 'c58'
#ic = 'c59'
#ic = 'c70'
#ic = 'c71'
#ic = 'c74'
#ic = 'c75'

df[ic] = pd.to_datetime(df[ic]) # , errors='coerce'
df = df.sort_values(ic).drop_duplicates('Ccode', keep='last')

and perform the merge one by one. However, this method tends to delete information under other column when dealing with one of the c##(I.E. c21) column

df.to_excel(ic + '.xlsx', index=False)

pandas dataframe excel python
1 Answers

One idea is grouping by both columns witt replace NaN for avoid remove this groups in oldier pandas versions with GroupBy.last for last non missing values per groups:

df = (df.assign(Ocode = df['Ocode'].fillna('nan'),Ccode = df['Ccode'].fillna('nan'))
        .groupby(['Ocode','Ccode'])
        .last()
        .reset_index()
        .replace({'Ocode': {'nan':np.nan}, 'Ccode':{'nan':np.nan}}))

For last versions of pandas:

df = (df.groupby(['Ocode','Ccode'])
        .last()
        .reset_index())

print (df)
       Ocode      Ccode         c21  c57         c58  c59  c70         c71  \
0  BK0001000        NaN         NaN  NaN         NaN  NaN  NaN         NaN   
1  CU0030000        NaN         NaN  NaN         NaN  NaN  NaN  2021-12-31   
2  CU0048000        NaN         NaN  NaN  2018-06-19  NaN  NaN         NaN   
3  CU0056000        NaN         NaN  NaN         NaN  NaN  NaN         NaN   
4        NaN  CU0280002  2016-12-31  NaN         NaN  NaN  NaN         NaN   
5        NaN  CU0659001         NaN  NaN         NaN  NaN  NaN         NaN   

          c74         c75  
0         NaN  2021-02-09  
1         NaN         NaN  
2         NaN         NaN  
3  2020-06-04         NaN  
4  2017-12-31         NaN  
5  2022-05-31         NaN

EDIT:

Solution above test combination of both columns, if need prioritize Ocode it means set NaN to Ccode if exist both use before solution above:

df.loc[df['Ocode'].notna() & df['Ocode'].notna(), 'Ccode'] = np.nan

EDIT1: One idea processing both codes separately:

df1 = (df.assign(Ocode = df['Ocode'].fillna('nan'))
         .drop('Ccode', axis=1)
         .groupby('Ocode')
         .last()
         .reset_index())

df2 = (df.assign(Ccode = df['Ccode'].fillna('nan'))
         .drop('Ocode', axis=1)
         .groupby('Ccode')
         .last()
         .reset_index())

both = (pd.concat([df1, df2], sort=False)
          .replace({'Ocode': {'nan':np.nan}, 'Ccode':{'nan':np.nan}}))
user_29010020
Related