[FIXED] How to split data from groupby into columns

Issue

I create new table from group by function as follow

BIRTH_RATE	Credit	ID
(339.999, 566.0]	Bad	9829
(339.999, 566.0]	Good	101495
(566.0, 788.0]	Bad	336
(566.0, 788.0]	Good	2345
(788.0, 1011.0]	Bad	1910
(788.0, 1011.0]	Good	24616

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)

BIRTH_RATE	Credit Total	Bad	Good	Bad rate
(339.999, 566.0]	111324	9829	101495	8.8
(566.0, 788.0]	2681	336	2345	12.5
(788.0, 1011.0]	26526	1910	24616	7.2

I have try to create Credit Total with the following code

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

Solution

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

Answered By - Corralien