How to find the total length of a column value that has multiple values in different rows for another column

user_10919766 0
Viewed 60

Is there a way to find IDs that have both Apple and Strawberry, and then find the total length? and IDs that has only Apple, and IDS that has only Strawberry?

df:

        ID           Fruit
0       ABC          Apple        <-ABC has Apple and Strawberry
1       ABC          Strawberry   <-ABC has Apple and Strawberry
2       EFG          Apple        <-EFG has Apple only
3       XYZ          Apple        <-XYZ has Apple and Strawberry
4       XYZ          Strawberry   <-XYZ has Apple and Strawberry 
5       CDF          Strawberry   <-CDF has Strawberry
6       AAA          Apple        <-AAA has Apple only

Desired output:

Length of IDs that has Apple and Strawberry: 2
Length of IDs that has Apple only: 2
Length of IDs that has Strawberry: 1

Thanks!

pandas summary dataframe python-3.x numpy
2 Answers

If always all values are only Apple or Strawberry in column Fruit you can compare sets per groups and then count ID by sum of Trues values:

v = ['Apple','Strawberry']
out = df.groupby('ID')['Fruit'].apply(lambda x: set(x) == set(v)).sum()
print (out)
2

EDIT: If there is many values:

s = df.groupby('ID')['Fruit'].agg(frozenset).value_counts()
print (s)
{Apple}                2
{Strawberry, Apple}    2
{Strawberry}           1
Name: Fruit, dtype: int64
user_29010020

You can use pivot_table and value_counts for DataFrames (Pandas 1.1.0.):

df.pivot_table(index='ID', columns='Fruit', aggfunc='size', fill_value=0)\
.value_counts()

Output:

Apple  Strawberry
1      1             2
       0             2
0      1             1

Alternatively you can use:

df.groupby(['ID', 'Fruit']).size().unstack('Fruit', fill_value=0)\
.value_counts()
user_89736200
Related