Groupby and aggregate using lambda functions

user_7455917 0
Viewed 378

I am trying to groupby-aggregate a dataframe using lambda functions that are being created programatically. This so I can simulate a one-hot encoder of the categories present in a column.

Dataframe:

df = pd.DataFrame(np.array([[10, 'A'], [10, 'B'], [20, 'A'],[30,'B']]),
                   columns=['ID', 'category'])

ID category
10 A
10 B
20 A
30 B

Expected result:

ID A B
10 1 1
20 1 0
30 0 1

What I am trying:

one_hot_columns = ['A','B']
lambdas = [lambda x: 1 if x.eq(column).any() else 0 for column in one_hot_columns]
df_g = df.groupby('ID').category.agg(lambdas)

Result:

ID A B
10 1 1
20 0 0
30 1 1

But the above is not quite the expected result. Not sure what I am doing wrong. I know I could do this with get_dummies, but using lambdas is more convenient for automation. Also, I can ensure the order of the output columns.

pandas dataframe python
2 Answers

Use crosstab:

pd.crosstab(df.ID, df['category']).reset_index()

Output:

category  ID  A  B
0         10  1  1
1         20  1  0
2         30  0  1
user_42384080

You can use pd.get_dummies with Groupby.sum:

In [4331]: res = pd.get_dummies(df, columns=['category']).groupby('ID', as_index=False).sum()

In [4332]: res
Out[4332]: 
   ID  category_A  category_B
0  10           1           1
1  20           1           0
2  30           0           1

OR, use pd.concat with pd.get_dummies:

In [4329]: res = pd.concat([df, pd.get_dummies(df.category)], axis=1).groupby('ID', as_index=False).sum()

In [4330]: res
Out[4330]: 
   ID  A  B
0  10  1  1
1  20  1  0
2  30  0  1
user_58208140
Related