python - How to summarize on different groupby combinations?

Question

I am compiling a table of top-3 crops by county. Some counties have the same crop varieties in the same order. Other counties have the same crop varieties in a different order.

df1 = pd.DataFrame( { 
    "County" : ["Harney", "Baker", "Wheeler", "Hood River", "Wasco" , "Morrow","Union","Lake"] , 
    "Crop1" : ["grain", "melons", "melons", "apples", "pears", "raddish","pears","pears"],
    "Crop2" : ["melons","grain","grain","melons","carrots","pears","carrots","carrots"],
    "Crop3": ["apples","apples","apples","grain","raddish","carrots","raddish","raddish"],
    "Total_pop": [2000,1500,3000,1500,2000,2500,2700,2000]} )

I can do a groupby on Crop1, Crop2 and Crop3 and get the sum of total_pop:

df1_grouped=df1.groupby(['Crop1',"Crop2","Crop3"])['Total_pop'].sum().reset_index()

That gives me the total for specific combinations of the crops:

df1_grouped
apples  melons  grain   1500
grain   melons  apples  2000
melons  grain   apples  4500
pears   carrots raddish 6700
raddish pears   carrots 2500

What I would like, though, is to get the total population on different combinations of crops -- irrespective of whether the listed crop was crop1, crop2, or crop3. The desired result would be this:

apples  melons   grain    8000
pears   carrots  raddish  9200 

Thank you for any guidance.

Answer 1

Method 1:

Combine the crop columns

>>> df1['combined_temp'] = df1.apply(lambda x : list([x['Crop1'],
...                           x['Crop2'],
...                           x['Crop3']]),axis=1)
>>> df1.head()
       County   Crop1    Crop2    Crop3  Total_pop              combined_temp
0      Harney   grain   melons   apples       2000    [grain, melons, apples]
1       Baker  melons    grain   apples       1500    [melons, grain, apples]
2     Wheeler  melons    grain   apples       3000    [melons, grain, apples]
3  Hood River  apples   melons    grain       1500    [apples, melons, grain]
4       Wasco   pears  carrots  raddish       2000  [pears, carrots, raddish]

make it a sorted tuple

>>> df1['sorted'] = df1.apply(lambda x : tuple(sorted(x['combined_temp'])),axis=1)
>>> df1.head()
       County   Crop1    Crop2            ...             Total_pop              combined_temp                     sorted
0      Harney   grain   melons            ...                  2000    [grain, melons, apples]    (apples, grain, melons)
1       Baker  melons    grain            ...                  1500    [melons, grain, apples]    (apples, grain, melons)
2     Wheeler  melons    grain            ...                  3000    [melons, grain, apples]    (apples, grain, melons)
3  Hood River  apples   melons            ...                  1500    [apples, melons, grain]    (apples, grain, melons)
4       Wasco   pears  carrots            ...                  2000  [pears, carrots, raddish]  (carrots, pears, raddish)

then proceed to your normal group by operation

>>> df1_grouped = df1.groupby(['sorted'])['Total_pop'].sum().reset_index()
>>> df1_grouped
                      sorted  Total_pop
0    (apples, grain, melons)       8000
1  (carrots, pears, raddish)       9200

Method 2: A shorted version based on the answer by aws-apprentice

df = df1.copy()

grouping_cols = ['Crop1', 'Crop2', 'Crop3']

df[grouping_cols] = pd.DataFrame(df.loc[:, grouping_cols] \
                            .apply(set, axis=1) \
                            .apply(sorted)            
                            .values \
                            .tolist(), columns=grouping_cols)

>>> df.head()
       County    Crop1  Crop2    Crop3  Total_pop
0      Harney   apples  grain   melons       2000
1       Baker   apples  grain   melons       1500
2     Wheeler   apples  grain   melons       3000
3  Hood River   apples  grain   melons       1500
4       Wasco  carrots  pears  raddish       2000

now take group by group by

>>> df.groupby(grouping_cols).Total_pop.sum()
Crop1    Crop2  Crop3  
apples   grain  melons     8000
carrots  pears  raddish    9200
Name: Total_pop, dtype: int64

but i personally prefer this answer using numpy

Answer 2

Since your data seem to guarantee 3 unique crops per country ("I am compiling a table of top-3 crops by county."), it suffices to sort the values and assign back.

import numpy as np

cols = ['Crop1', 'Crop2', 'Crop3']
df1[cols] = np.sort(df1[cols].to_numpy(), axis=1)

       County    Crop1  Crop2    Crop3  Total_pop
0      Harney   apples  grain   melons       2000
1       Baker   apples  grain   melons       1500
2     Wheeler   apples  grain   melons       3000
3  Hood River   apples  grain   melons       1500
4       Wasco  carrots  pears  raddish       2000
5      Morrow  carrots  pears  raddish       2500
6       Union  carrots  pears  raddish       2700
7        Lake  carrots  pears  raddish       2000

Then to summarize:

df1.groupby(cols).sum()

#                       Total_pop
#Crop1   Crop2 Crop3             
#apples  grain melons        8000
#carrots pears raddish       9200

---

The benefit is that you avoid Series.apply or .apply(axis=1). For larger DataFrames, the performance difference is noticeable:

df1 = pd.concat([df1]*10000, ignore_index=True)

cols = ['Crop1', 'Crop2', 'Crop3']
%timeit df1[cols] = np.sort(df1[cols].to_numpy(), axis=1)
#36.1 ms ± 399 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

to_sum = ['Crop1', 'Crop2', 'Crop3']
%timeit df1[to_sum] = pd.DataFrame(df1.loc[:, to_sum].apply(set, axis=1).apply(list).values.tolist(), columns=to_sum)
#1.41 s ± 51.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Answer 3

Here is one way to do it.

First let's get the unique values across the columns and then reassign these values back to the DataFrame. We will perform this on a copy of the original data since you might need to preserve the original data.

df = df1.copy()

to_sum = ['Crop1', 'Crop2', 'Crop3']

df[to_sum] = pd.DataFrame(df.loc[:, to_sum] \
                            .apply(set, axis=1) \
                            .apply(sorted) \
                            .values \
                            .tolist(), columns=to_sum)

print(df)

       County  Crop1    Crop2    Crop3  Total_pop
0      Harney  grain   apples   melons       2000
1       Baker  grain   apples   melons       1500
2     Wheeler  grain   apples   melons       3000
3  Hood River  grain   apples   melons       1500
4       Wasco  pears  carrots  raddish       2000
5      Morrow  pears  carrots  raddish       2500
6       Union  pears  carrots  raddish       2700
7        Lake  pears  carrots  raddish       2000

Now we can perform our groupby to get the desired results.

df.groupby(to_sum).Total_pop.sum()

Crop1    Crop2  Crop3  
apples   grain  melons     8000
carrots  pears  raddish    9200
Name: Total_pop, dtype: int64

Answer 4

np.bincount

i, u = pd.factorize([*map(frozenset, zip(df1.Crop1, df1.Crop2, df1.Crop3))])
s = np.bincount(i, df1.Total_pop)

pd.Series(s, u)

(melons, grain, apples)      8000.0
(carrots, raddish, pears)    9200.0
dtype: float64

Or, if you want separate columns

pd.Series(dict(zip(map(tuple, u), s)))

melons   grain    apples    8000.0
carrots  raddish  pears     9200.0
dtype: float64

And fully pretty

pd.Series(dict(zip(map(tuple, u), s))) \
  .rename_axis(['Crop1', 'Crop2', 'Crop3']).reset_index(name='Total_pop')

     Crop1    Crop2   Crop3  Total_pop
0   melons    grain  apples     8000.0
1  carrots  raddish   pears     9200.0

Answer 5

import pandas as pd

df = pd.DataFrame( {
    "County" : ["Harney", "Baker", "Wheeler", "Hood River", "Wasco" , "Morrow","Union","Lake"] ,
    "Crop1" : ["grain", "melons", "melons", "apples", "pears", "raddish","pears","pears"],
    "Crop2" : ["melons","grain","grain","melons","carrots","pears","carrots","carrots"],
    "Crop3": ["apples","apples","apples","grain","raddish","carrots","raddish","raddish"],
    "Total_pop": [2000,1500,3000,1500,2000,2500,2700,2000]} )
print(df)
df["Merged"] = df[["Crop1", "Crop2", "Crop3"]].apply(lambda x: ','.join(x.dropna().astype(str).values), axis=1).str.split(",")
df["Merged"] = df["Merged"].sort_values().apply(lambda x: sorted(x)).apply(lambda x: ",".join(x))
df[["x", "y", "z"]] = df["Merged"].str.split(",", expand=True)
df1=df.groupby(['x',"y","z"])['Total_pop'].sum().reset_index()
print(df1)

Output:

      County    Crop1    Crop2    Crop3  Total_pop
      Harney    grain   melons   apples       2000
       Baker   melons    grain   apples       1500
     Wheeler   melons    grain   apples       3000
  Hood River   apples   melons    grain       1500
       Wasco    pears  carrots  raddish       2000
      Morrow  raddish    pears  carrots       2500
       Union    pears  carrots  raddish       2700
        Lake    pears  carrots  raddish       2000

           x      y        z  Total_pop
      apples  grain   melons       8000
     carrots  pears  raddish       9200