Split up summary data and resummarise

user_4896331 0
Viewed 62

I have a summary df that looks like this:

Apples             100
Bananas            34
Kumquats           54
Greengages         101
Apples;Kumquats    5
Bananas;Greengages 7

And I want to simplify it by splitting up the counts for combined fruit into individual items:

Apples             105
Bananas            41
Kumquats           59
Greengages         108

i.e. I've dropped rows like Apples;Kumquats but increased both Apples and Kumquats by 5.

Is there a good way to do this in Pandas?

pandas python
4 Answers

You can split values by ;, reshape by stack and aggregate sum:

print (df)
                    a    b
0              Apples  100
1             Bananas   34
2            Kumquats   54
3          Greengages  101
4     Apples;Kumquats    5
5  Bananas;Greengages    7

df1 = (df.set_index('b')['a']
         .str.split(';', expand=True)
         .stack()
         .reset_index(name='c')
         .groupby('c', as_index=False)['b'].sum())
print (df1)
            c    b
0      Apples  105
1     Bananas   41
2  Greengages  108
3    Kumquats   59

Or solution with defaultdict:

from collections import defaultdict

d = defaultdict(int)
for a, b in zip(df['a'], df['b']):
    for x in a.split(';'):
        d[x] += b

df = pd.DataFrame({'a':list(d.keys()), 'b':list(d.values())})
print (df)
            a    b
0      Apples  105
1     Bananas   41
2    Kumquats   59
3  Greengages  108
user_29010020

You can use str.get_dummies setting ; as a separator and multiply with the corresponding quantity:

df.food.str.get_dummies(sep=';').mul(df.quantity.values[:,None]).sum()

Apples        105
Bananas        41
Greengages    108
Kumquats       59
dtype: int64

Input data:

print(df)
           food          quantity
0              Apples       100
1             Bananas        34
2            Kumquats        54
3          Greengages       101
4     Apples;Kumquats         5
5  Bananas;Greengages         7
user_96986840

You can use str.get_dummies, transpose, and then multiply by the count, which will broadcast across the dataframe:

(df['fruit'].str.get_dummies(';').T * df['count']).sum(axis=1)

Output:

Apples        105
Bananas        41
Greengages    108
Kumquats       59
dtype: int64
user_110154270

Maybe:

>>> pd.DataFrame([[x, s[s.index.str.contains(x)].tolist()] for i in s.reset_index()['index'].str.split(';').values.tolist() for x in i]).apply(lambda x: [x[0], sum(x[1])], axis=1).drop_duplicates()
            0    1
0      Apples  105
1     Bananas   41
2    Kumquats   59
3  Greengages  108
>>>
user_87083640
Related