How to group Pandas rows by a function of multiple columns

user_6567949 0
Viewed 87

I have a dataframe with records characterizing roof surfaces of buildings, so each building has multiple planes, with an area and a description of its form. e.g.

df=pd.DataFrame([[1000, 12, 'slope'],
                [1000, 10, 'flat'],
                [1001, 10, 'slope'],
                [1001, 15, 'flat'],
                [1001, 7, 'slope']],
               index = [1,2,3,4,5],
               columns=['building_id', 'area', 'form'],
               )
df
building_id     area    form
1   1000    12  slope
2   1000    10  flat
3   1001    10  slope
4   1001    15  flat
5   1001    7   slope

I want to combine the rows so i have one for each building, with the total roof area and the predominant roof form - ie the form that has the greatest area for that building, not the form that appears most frequently:

df_out
building_id     area    form
    1   1000    22  slope
    2   1001    32  slope

I need something like this:

group_functions={'area' : ['sum'],
                 'form' : lambda x: find_predominant(x)}
df_out = df.groupby('building_id').agg(group_functions)

But find_predominant needs to be a function of area as well as form: It returns the string 'flat' or 'slope' depending on which has the biggest area for that building_id.

What is the function find_predominant ? Or what script will have the same effect?

pandas-groupby pandas python
2 Answers

My suggestion would be to calculate the sum and call the find_predomonant function separately, since that will require a call to apply.

g = df.groupby('building_id')
area = g['area'].sum()
form = g.apply(find_predominant) 

df_out = pd.concat([area, form], axis=1)

Now, for this to work, please recognise that find_predominant should accept a DataFrame and access the "area" and "form" columns appropriately. 

def find_predominant(df):
    ar = df['area']
    fm = df['form']
    ... # Do something with ar and fm

    return result

That may or may not require refactoring on your part.

Edit: Okay, so you don't know what that function is. In that case, let's get rid of it.

Try this.

area = df.groupby('building_id')['area'].sum()
form = (df.groupby(['building_id', 'form'])['area']
          .sum()
          .groupby(level=0)
          .idxmax()
          .str[1])
form.name = 'form'

df_out = pd.concat([area, form], axis=1).reset_index()
print(df_out)
   building_id  area   form
0         1000    22  slope
1         1001    32  slope

This will select the form corresponding to the one holding the maximum area (by sum) per building_id.

If the form by maximum sum is not required, and you simply want the form by maximum area, then the solution simplifies.

g = df.groupby('building_id')['area']
area = g.sum()
form = (df.set_index('building_id')
          .iloc[g.idxmax(), df.columns.get_loc('form') - 1])

df_out = pd.concat([area, form], axis=1).reset_index()
print(df_out)
   building_id  area   form
0         1000    22   flat
1         1001    32  slope
user_49090870

You can use sort_values and assign the value after agg

(df.groupby(['building_id','form'])['area']
   .sum()
   .sort_values()
   .reset_index(level=1)
   .groupby(level=0)
   .agg({'form':'last','area':'sum'}))

              form  area
building_id             
1000         slope    22
1001         slope    32
user_79645270
Related