Groupby column based on condition in dataframe

user_14225893 0
Viewed 352

I want to combine the following dataframe df according to the column Region and the column Inhabitants. Moreover, I want to sum up the value in Area.

import pandas as pd
df = pd.DataFrame({'Region': ['Region1', 'Region1', 'Region1', 'Region2', 'Region2', 'Region2', 'Region3', 'Region3', 'Region4', 'Region4'], 'Inhabitants': [True, False, True, True, False, True, True, True, False, False], 'Area': [60, 20, 20, 60, 30, 10, 50, 50, 50, 50]})
print(df)

  Region  Inhabitants  Area
0  Region1         True    60
1  Region1        False    20
2  Region1         True    20
3  Region2         True    60
4  Region2        False    30
5  Region2         True    10
6  Region3         True    50
7  Region3         True    50
8  Region4        False    50
9  Region4        False    50

This worked with this code:

df = df.groupby(['Region', 'Inhabitants'], as_index=False)['Area'].sum()
print(df)

 Region  Inhabitants  Area
0  Region1        False    20
1  Region1         True    80
2  Region2        False    30
3  Region2         True    70
4  Region3         True   100
5  Region4        False   100

In the end I want to decide whether the sum of the true areas per region is >= 80. This should be stated in the added column Region_ok as True, if the true area per region is >= 80 or False, if not. This again should be grouped by region. The result should look like this:

    Region        Region_ok
0  Region1        True
1  Region2        False
2  Region3        True
3  Region4        False

I tried several things with groupby, but can't figure out a suitable solution.

pandas dataframe group-by python
1 Answers

The simpliest solution ir replace Area to 0 if False in Inhabitants by Series.where, then aggregate sum, compare for greater or equal alnd last convert Series to DataFrame:

df2 = (df['Area'].where(df['Inhabitants'], 0)
                 .groupby(df['Region'])
                 .sum()
                 .ge(80)
                 .reset_index(name='Region_ok'))
print(df2)
    Region  Region_ok
0  Region1       True
1  Region2      False
2  Region3       True
3  Region4      False

You can working with aggregate DataFrame - filter by greater by 80 by Series.gt and chain with & with boolean column and last test if at least one value match by GroupBy.any:

df1 = df.groupby(['Region', 'Inhabitants'], as_index=False)['Area'].sum()
m = df1['Area'].ge(80) & df1['Inhabitants']

df2 = m.groupby(df1['Region']).any().reset_index(name='Region_ok')
print(df2)
    Region  Region_ok
0  Region1       True
1  Region2      False
2  Region3       True
3  Region4      False

Similar idea but working with origonal data is use GroupBy.transform with sum and filter in origonal Dataframe:

s = df.groupby(['Region', 'Inhabitants'])['Area'].transform('sum')
m = s.ge(80) & df['Inhabitants']

df2 = m.groupby(df['Region']).any().reset_index(name='Region_ok')
print(df2)
    Region  Region_ok
0  Region1       True
1  Region2      False
2  Region3       True
3  Region4      False

Next solution first filter only Trues rows, and aggregate sum only by Region:

s = df[df['Inhabitants']].groupby('Region')['Area'].sum()
df2 = (s.ge(80)
        .reindex(df['Region'].unique(), fill_value=False)
        .reset_index(name='Region_ok'))

print(df2)
    Region  Region_ok
0  Region1       True
1  Region2      False
2  Region3       True
3  Region4      False
user_29010020
Related