CODEWITHSUNDEEP
pythonpandas
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Calculating quantile when a condition is met

I am new to Pandas.

My DataFrame looks like this:

DataFrame

I am having problems with adding 1st, 2nd, 3rd quartiles to my DataFrame.

I am trying to get quartiles for column CTR if they are on the same group determined by column Cat.

In total, I have about 40 groups.

What I've tried:

df_final['1st quartile'] = round(
    df_final.groupby('Cat')['CTR'].quantile(0.25), 2)
df_final['2nd quartile'] = round(
    df_final.groupby('Cat')['CTR'].quantile(0.5), 2)
df_final['3rd quartile'] = round(
    df_final.groupby('Cat')['CTR'].quantile(0.75), 2)

But values get added in a way I cannot explain, like starting in second row and rather than being added the way as last column CTR Average Difference vs category.

My desired output would look the same as the last column, CTR Average Difference vs category, one line per category.

Any suggestions what might be wrong? Thank you.

Upvotes: 0

Views: 698

Answers (1)

jezrael
jezrael

Reputation: 862681

If want new column filled by aggregated values like mean, sum or quantile use GroupBy.transform:

#similar ofr 2. and 3rd quantile
df_final['1st quartile'] = (df_final.groupby('Cat')['CTR']
                                    .transform(lambda x: x.quantile(0.25))
                                    .round(2))

Or you can use DataFrameGroupBy.quantile and then DataFrame.join by Cat column:

df = df_final.groupby('Cat')['CTR'].quantile([0.2, 0.5, 0.75]).round(2)
df.columns = ['1st quartile','2nd quartile','3rd quartile']
df_final = df_final.join(df, on='Cat')

Upvotes: 1

Related Questions