Multi tool use

Clash Royale CLAN TAG

Create new columns from aggregated categories

I have a dataframe looks like:

SK_ID_CURR CREDIT_ACTIVE
0 215354 Closed
1 215354 Active
2 215354 Active
3 215354 Active
4 215354 Active
5 215354 Active
6 215354 Active
7 162297 Closed
8 162297 Closed
9 162297 Active


I would like to aggregate the number of active and closed credits for each id, and then make a new column for Active_credits, Closed_credits with the number of corresponding active and closed credits for each id.

Active_credits

Closed_credits

2 Answers
2

You can use pandas.crosstab, which avoids your suggested intermediary step:

pandas.crosstab

res = pd.crosstab(df['SK_ID_CURR'], df['CREDIT_ACTIVE'])

print(res)

CREDIT_ACTIVE Active Closed
SK_ID_CURR
162297 1 2
215354 6 1


You can achieve this using pd.DataFrame.groupby

pd.DataFrame.groupby

df1.groupby(['SK_ID_CURR','CREDIT_ACTIVE']).size()


Output:

SK_ID_CURR CREDIT_ACTIVE
162297 Active 1
Closed 2
215354 Active 6
Closed 1


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.