Скачать или смотреть How to Group Data by Two Columns and Calculate Quantiles in Python with Pandas

Discover how to group data by two columns and efficiently calculate quantiles like p50 and p90 in Python's Pandas library.
---
This video is based on the question https://stackoverflow.com/q/64492936/ asked by the user 'Chris90' ( https://stackoverflow.com/u/8797830/ ) and on the answer https://stackoverflow.com/a/64493019/ provided by the user 'moys' ( https://stackoverflow.com/u/11232091/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Group by 2 columns with calculation of quantile of 3rd numerical column

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Group Data by Two Columns and Calculate Quantiles in Python with Pandas

Data manipulation is a crucial skill when working with datasets, especially in Python. One common task is to group data by multiple columns and then perform calculations on another column. This guide will guide you through the process of grouping a DataFrame by two columns and calculating quantiles—specifically, the 50th percentile (p50) and the 90th percentile (p90)—using the Pandas library.

The Problem

Imagine you have a DataFrame with daily meal data, where each entry records the type of meal and its corresponding quantity measured in ounces (oz). You might want to gain insights by finding out key statistics, such as what the average and high quantities are for each meal type on different days.

Here’s an example of the data structure:

daymealtypeozMondaySnack0.34MondaySnack0.43MondayDinner0.31TuesdayBreakfast0.10MondayDinner0.11TuesdayDinner0.09SundaySnack0.33SundayDinner0.01SundayLunch0.03Your goal is to group this data by day and mealtype, then calculate the 50th and 90th percentiles of the oz column.

The Solution

Let’s explore two methods to achieve this using Pandas. Both methods will yield the same result, so you can choose the one that best suits your coding style.

Method 1: Using agg()

You can aggregate the DataFrame and calculate the desired quantiles using the agg() function:

[[See Video to Reveal this Text or Code Snippet]]

Method 2: Using merge()

Alternatively, you can split the calculations into separate DataFrames and then merge them:

[[See Video to Reveal this Text or Code Snippet]]

Output

After executing either of the methods, you will get a new DataFrame new_df containing the 50th and 90th percentiles for each combination of day and meal type:

daymealtypep50p90MondayDinner0.210.29MondaySnack0.390.42SundayDinner0.010.01SundayLunch0.030.03SundaySnack0.330.33TuesdayBreakfast0.100.10TuesdayDinner0.090.09Conclusion

Calculating quantiles grouped by multiple columns in Pandas is straightforward once you know how to structure your groupby and aggregate functions. Whether you choose to compute the values in one go or split them into separate steps, the key takeaway is that Pandas provides powerful tools to manipulate and analyze your data effectively.

Feel free to experiment with your datasets and modify the quantiles as per your requirements!

Now you can confidently analyze your datasets, leveraging the flexibility of Pandas for powerful insights!