Pandas exploding out columns with groupby is very slow

user_11238780 0
Viewed 787

I have a df with a column parsed xml that contains a list of dictionaries for each row with the parsed attributes. It looks like this:

   id  type  length  parsed    
0  1   A     144     [{'key1':'value1'},{'key1':'value2', 'key2':'value3'},...]
1  1   B     20      [{'key1':'value4'},{'key2':'value5'},...]
2  4   A     54      [{'key3':'value6'},...]

I am using the following to expand out those dictionaries into additional columns.

s = df['parsed xml'].explode()
t = (df.join(pd.DataFrame(s.tolist(), index = s.index).groupby(level=0).agg(lambda x: x.dropna().tolist()), /
    lsuffix = '_x', rsuffix = '_y')).applymap(lambda x: x[0] if (type(x)==list and len(x)==1) else x)

This gives me a data frame like this, which is how I want it:

   id  type  length  key1             key2     key3
0  1   A     144     [value1,value2]  value3
1  1   B     20      value4           value5
2  4   A     54                                value6

The issue is that the code to get to this data frame take about 45 seconds for 5,000 records, but seemingly goes on forever with it is used on 12,000 records. It seems like the groupby is causing this, but can this be made more efficient?

pandas-groupby pandas python
1 Answers

you can slightly change your method to optimize a bit, using stack and unstack and perform the applymap on only the column you create. like this:

s = df['parsed'].explode()
df_join = (pd.DataFrame(s.tolist(), index = s.index)
             .stack()
             .groupby(level=[0,1])
             .agg(list)
             .apply(lambda x: x[0] if len(x)==1 else x)
             .unstack(fill_value='')
          )
t = df.join(df_join)
print (t) # I did not used exactly your input
  id                                             parsed              key1  \
0  a  [{'key1': 'value1'}, {'key1': 'value2', 'key2'...  [value1, value2]   
1  b           [{'key1': 'value4'}, {'key2': 'value5'}]            value4   
2  c                               [{'key3': 'value6'}]                     

     key2    key3  
0  value3          
1  value5          
2          value6

Now in terms of performance, I see an improvement of 2 on a dataframe of 3 rows like here, but the improvement increase with the size (it is 6 time faster with 3K rows). This increase in speed will depend on the number of elements you have in each list in the column parse and the number of different keys as well I guess

user_92747320
Related