Data Error Using function and groupby to union strings in pandas dataframe

Question

I have a dataframe of the following structure:

mydf:

    Entry   Address         ShortOrdDesc
0   988     Fake Address 1  SC_M_W_3_1
1   989     Fake Address 2  SC_M_W_3_3
2   992     Fake Address 3  nan_2
3   992                     SC_M_G_1_1
4   992                     SC_M_O_1_1

There is work to be done on this df to combine rows with the same Entry. For these only the first row has Address. I need to concatenate the ShortOrdDesc column and Address. I found a very useful link on this:

Pandas groupby: How to get a union of strings

Working from this I have developed the following function:

def f(x):
     return pd.Series(dict(A = x['Entry'].sum(), 
                        B = x['Address'].sum(), 
                        C = "%s" % '; '.join(x['ShortOrdDesc'])))

Which is applied using

myobj = ordersToprint.groupby('Entry').apply(f)

This returns the error:

TypeError: must be str, not int

Looking at my data I don't see what the issue is, as running .sum() on the integers of 'Entry' should work I believe.

What is the error in my code or my approach?

jezrael · Accepted Answer · 2017-10-29T08:32:56.490

I think some column is numeric and need string.

So use astype and if need remove NaNs add dropna:

def f(x):
 return pd.Series(dict(A = x['Entry'].sum(), 
                    B = ''.join(x['Address'].dropna().astype(str)), 
                    C = '; '.join(x['ShortOrdDesc'].astype(str))))

myobj = ordersToprint.groupby('Entry').apply(f)
print (myobj)
          A               B                              C
Entry                                                     
988     988  Fake Address 1                     SC_M_W_3_1
989     989  Fake Address 2                     SC_M_W_3_3
992    2976  Fake Address 3  nan_2; SC_M_G_1_1; SC_M_O_1_1

Another solution with agg, but then is necessary rename columns:

f = {'Entry':'sum', 
      'Address' : lambda x: ''.join(x.dropna().astype(str)), 
      'ShortOrdDesc' : lambda x: '; '.join(x.astype(str))}
cols = {'Entry':'A','Address':'B','ShortOrdDesc':'C'}
myobj = ordersToprint.groupby('Entry').agg(f).rename(columns=cols)[['A','B','C']]
print (myobj)
          A               B                              C
Entry                                                     
988     988  Fake Address 1                     SC_M_W_3_1
989     989  Fake Address 2                     SC_M_W_3_3
992    2976  Fake Address 3  nan_2; SC_M_G_1_1; SC_M_O_1_1

Data Error Using function and groupby to union strings in pandas dataframe

1 Answers1