How to pivot dataframe string value with Python

Question

i have some trouble with Pivot (Seem like Pivot in excel). i tried it so many times but i have no idea. Please anyone help me.

Below is a sample data.

Date report     Result
01/11/2020      Achieved
01/11/2020      Achieved
02/11/2020      Failed
02/11/2020      Failed
02/11/2020      Achieved
03/11/2020      Achieved

Then result that i need to make it as below

Date report      Result
01/11/2020       100.00%
02/11/2020       33.33%
03/11/2020       100.00%

Remark : The percentage result comes from Countif('Date report' and 'Achieved')/Countif('Date report')

Ch3steR · Accepted Answer · 2020-11-09 13:59:54Z

3

Compare values by Achieved with Series.eq, aggregate mean, multiple 100 and convert to percentages:

df1 = (df['Result'].eq('Achieved')
                   .groupby(df['Date report'])
                   .mean()
                   .mul(100)
                   .round(2)
                   .astype(str)
                   .add('%')
                   .reset_index())
print (df1)
  Date report  Result
0  01/11/2020  100.0%
1  02/11/2020  33.33%
2  03/11/2020  100.0%

Another idea, thank you @Chester:

df1 = (df['Result'].eq('Achieved')
                   .groupby(df['Date report'])
                   .mean()
                   .map('{:.2%}'.format)
                   .reset_index())
print (df1)

'{:.num%}' where num is precision after the decimal point, read about mini string language

edited Nov 9, 2020 at 13:59

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

answered Nov 9, 2020 at 13:45

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ch3steR Over a year ago

Similar idea, df['Result'].eq('Achieved').groupby(df['Date report']).mean().map('{:.2%}'.format) but leveraging on mini string language

Ch3steR Over a year ago

Added few dets, feel free to change if not ok. ;)

Collectives™ on Stack Overflow

How to pivot dataframe string value with Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related