Speed Up Pandas DataFrame Groupby Apply

Question

I have the following code that I found on another post here (and modified slightly). It works great and the output is just as I expect, however I am wondering if anyone has suggestions on speed improvements. I am comparing two dataframes with about 93,000 rows and 110 columns. It takes about 20 minutes for the groupby to complete. I have tried to think of ways to speed if up but haven't come across anything. I am trying to think of anything now before my data sizes increase in the future. I am also open to other ways of doing this!

###Function that is called to check values in dataframe groupby     
def report_diff(x):
        return 'SAME' if x[0] == x[1] else '{} | {}'.format(*x)
        #return '' if x[0] == x[1] else '{} | {}'.format(*x)

print("Concatening CSV and XML data together...")
###Concat the dataframes together
df_all = pd.concat(
    [df_csv, df_xml], 
    axis='columns', 
    keys=['df_csv', 'df_xml'],
    join='outer',
)
print("Done")

print("Swapping column levels...")
###Display keys at the top of each column
df_final = df_all.swaplevel(axis='columns')[df_xml.columns[0:]]
print("Done")

df_final = df_final.fillna('None')

print("Grouping data and checking for matches...")
###Apply report_diff function to each row
df_excel = df_final.groupby(level=0, axis=1).apply(lambda frame: frame.apply(report_diff, axis=1))

user_9274732 · Answer

You can use np.where and check where df_csv[df_xml.columns] is equal to df_xml, if True then the value is 'SAME' else you can join the values of both dataframes like you do.

SETUP

df_csv = pd.DataFrame({'a':range(4),'b':[0,0,1,1],'c':list('abcd')})
df_xml = pd.DataFrame({'b':[0,2,3,1],'c':list('bbce')})

METHOD

df_excel = pd.DataFrame( np.where( df_csv[df_xml.columns] == df_xml, #find where
                                   'SAME', #True
                                   df_csv[df_xml.columns].astype(str) + ' | ' + df_xml.astype(str)), #False
                        columns=df_xml.columns
                        index=df_xml.index)

print (df_excel)
       b      c
0   SAME  a | b
1  0 | 2   SAME
2  1 | 3   SAME
3   SAME  d | e

Which is the same result that I got with your method.

Speed Up Pandas DataFrame Groupby Apply

1 Answers