I create new table from group by function as follow

with code:

df[['BIRTH_RATE', 'Credit','ID']].groupby(by=['BIRTH_RATE','Credit']).count()

And I would like to split Credit row into columns

(Credit Total = Good   Bad)
(Bad rate = (Bad/Total)*100)

I have try to create Credit Total with the following code

df.groupby(["BIRTH_RATE"]).agg(Credit=('Credit', 'count'))

CodePudding user response：

Use pivot to reformat your dataframe and assign to create the two new columns:

out = df.pivot_table(values='ID', index='BIRTH_RATE', columns='Credit', aggfunc='sum') \
        .assign(**{'Credit Total': lambda x: x['Bad'] x['Good'],
                   'Bad Rate': lambda x: round(x['Bad']/(x['Bad'] x['Good'])*100, 1)}) \
        .reset_index().rename_axis(columns=None)
print(out)

# Output:
         BIRTH_RATE   Bad    Good  Credit Total  Bad Rate
0  (339.999, 566.0]  9829  101495        111324       8.8
1    (566.0, 788.0]   336    2345          2681      12.5
2   (788.0, 1011.0]  1910   24616         26526       7.2

CodePudding user response：

Here's a pretty straight forward way to do it. Get your good/bad columns witha simple pivot. Then it's very easy to calculate the rest.

df = df.pivot(index='BIRTH_RATE',columns='Credit',values='ID').reset_index().rename_axis(None,axis=1)
df['Credit Total'] = df['Bad'] df['Good']
df['Bad Rate'] = (df['Bad']/df['Credit Total'])*100
print(df)

Output

         BIRTH_RATE   Bad    Good  Credit Total   Bad Rate
0  (339.999, 566.0]  9829  101495        111324   8.829183
1    (566.0, 788.0]   336    2345          2681  12.532637
2   (788.0, 1011.0]  1910   24616         26526   7.200483