CODEWITHSUNDEEP
pythonpandaspandas-groupby
yankeefan11
yankeefan11

Reputation: 485

Python new columns from multi groupby

I have a simplified dataframe, lets say:

df = pd.DataFrame({'Letter': ['A', 'B', 'A', 'B','A','B','A','B'], 'T/F': [True, True, False, False,True,False,True,False], 'Number':[5, 6, 7, 8, 9,10,11,12]})

I have some other df2 which already has columns for (isAB or isTF). What I want is to do make my new columns something like df2.loc[:,'A_True] which has the mean of Number for A and True. If I do the following:

df.groupby(['Letter','T/F'])['Number'].mean()[1::2]

This outputs 

Letter  T/F 
A       True    8.333333
B       True    6.000000
Name: Number, dtype: float64

I want my df2 to have a column which is just that 8.33333, 6. Is a merge required? I am trying to save time instead of doing

 for name,group in df.groupby('Letter'):
     df2.loc[df['Letter']==A,'A_True'] = group.loc[group['T/F'==True],'Number'].mean()

Upvotes: 0

Views: 34

Answers (2)

ansev
ansev

Reputation: 30920

We can use DataFrame.unstack:

new_df=df.groupby(['Letter','T/F'],sort=False).Number.mean().unstack()
print(new_df)

T/F        True   False
Letter                 
A       8.333333    7.0
B       6.000000   10.0

new_df[True]

Letter
A    8.333333
B    6.000000
Name: True, dtype: float64

Edit

new_df=df.groupby(['Letter','T/F'],sort=False).Number.mean().unstack().reset_index()
print(new_df)

T/F Letter      True  False
0        A  8.333333    7.0
1        B  6.000000   10.0

Upvotes: 1

BENY
BENY

Reputation: 323226

You can filter before 

df.loc[df['T/F']].groupby('Letter')['Number'].mean()
Out[93]: 
Letter
A    8.333333
B    6.000000
Name: Number, dtype: float64

Upvotes: 1

Related Questions