Pandas - Aggregating and Grouping Data - 7 Days of Pandas
Published: 2022-12-25 10:08:00

Welcome to the fifth article in the "7 Days of Pandas" series where we cover the `pandas` library in Python which is used for data manipulation.

In the [first article](https://dev.to/piyushraj/pandas-read-and-write-data-from-csv-files-7-days-of-pandas-1o4f) of the series, we looked at how to read and write CSV files with Pandas. In this tutorial, we will look at some of the most common operations that we perform on a dataframe in Pandas.  

In the [second article](https://dev.to/piyushraj/pandas-basic-data-manipulation-7-days-of-pandas-4c47), we looked at how to perform basic data manipulation.   

In the [third article](https://dev.to/piyushraj/pandas-basic-exploratory-data-analysis-7-days-of-pandas-3816), we looked at how to perform EDA (exploratory data analysis) with Pandas.  

In the [fourth article](https://dev.to/piyushraj/pandas-handling-missing-values-7-days-of-pandas-a16), we looked at how to handle missing values in a dataframe.

In this tutorial, we will look aggregate and group data in Pandas.

Aggregating and grouping data is a common task when working with datasets, and pandas provides a range of functions and methods to help you do this efficiently. 

In this tutorial, we will cover the following topics:

1. Applying aggregate functions to pandas dataframe.
2. Grouping data in pandas dataframe (and applying aggregate functions to the grouped data).

Before we begin, let's first import pandas and create a sample dataframe that we will be using throughout this tutorial.


```python
import pandas as pd

# employee data
data = {
    "Name": ["Tim", "Shaym", "Noor", "Esha", "Sam", "James", "Lily"],
    "Gender": ["M", "M", "F", "F", "M", "M", "F"],
    "Age": [26, 28, 27, 32, 24, 31, 33],
    "Department": ["Marketing", "Product", "Product", "HR", "Product", "HR", "Marketing"],
    "Salary": [60000, 70000, 82000, 55000, 58000, 55000, 65000]
}

# create pandas dataframe
df = pd.DataFrame(data)

# display the dataframe
df
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Name</th>
      <th>Gender</th>
      <th>Age</th>
      <th>Department</th>
      <th>Salary</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>Tim</td>
      <td>M</td>
      <td>26</td>
      <td>Marketing</td>
      <td>60000</td>
    </tr>
    <tr>
      <th>1</th>
      <td>Shaym</td>
      <td>M</td>
      <td>28</td>
      <td>Product</td>
      <td>70000</td>
    </tr>
    <tr>
      <th>2</th>
      <td>Noor</td>
      <td>F</td>
      <td>27</td>
      <td>Product</td>
      <td>82000</td>
    </tr>
    <tr>
      <th>3</th>
      <td>Esha</td>
      <td>F</td>
      <td>32</td>
      <td>HR</td>
      <td>55000</td>
    </tr>
    <tr>
      <th>4</th>
      <td>Sam</td>
      <td>M</td>
      <td>24</td>
      <td>Product</td>
      <td>58000</td>
    </tr>
    <tr>
      <th>5</th>
      <td>James</td>
      <td>M</td>
      <td>31</td>
      <td>HR</td>
      <td>55000</td>
    </tr>
    <tr>
      <th>6</th>
      <td>Lily</td>
      <td>F</td>
      <td>33</td>
      <td>Marketing</td>
      <td>65000</td>
    </tr>
  </tbody>
</table>
</div>



## Applying aggregate functions

Pandas comes with a number of aggregate functions that you can apply to the entire dataframe or one or more columns in the dataframe.

For example, you can apply the `sum()` funciton to get the sum of values in each column.


```python
df.sum()
```




    Name                         TimShaymNoorEshaSamJamesLily
    Gender                                            MMFFMMF
    Age                                                   201
    Department    MarketingProductProductHRProductHRMarketing
    Salary                                             445000
    dtype: object



Note that for `object` type `sum()` resulted in a concatenated string.

You can select which columns to apply the aggregate functions to. 

For example, let's the mean value of the "Age" and the "Salary" columns.


```python
df[["Age", "Salary"]].mean()
```




    Age          28.714286
    Salary    63571.428571
    dtype: float64



## Grouping data

To group a pandas DataFrame by one or more columns, you can use the pandas dataframe `groupby()` method. This method takes one or more column names as arguments and returns a groupby object that can be used to apply various operations to the grouped data.

Let's group the above data on the "Gender" column.


```python
df.groupby("Gender")
```




    <pandas.core.groupby.generic.DataFrameGroupBy object at 0x10684b970>



We get a groupby object. You can now use this object to [apply aggregations to the grouped data](https://datascienceparichay.com/article/pandas-groupby-mean/). For example, let's get the average "Age" value for each group.


```python
df.groupby("Gender")["Age"].mean()
```




    Gender
    F    30.666667
    M    27.250000
    Name: Age, dtype: float64



We get the mean value of the "Age" column for each group (here, "Gender") in the data.

You can group the data of more than one columns as well. For example, let's group the data on "Gender" and "Department" and get the average "Age" in each group.


```python
df.groupby(["Gender", "Department"])["Age"].mean()
```




    Gender  Department
    F       HR            32.0
            Marketing     33.0
            Product       27.0
    M       HR            31.0
            Marketing     26.0
            Product       26.0
    Name: Age, dtype: float64



You can also apply multiple aggregate functions to the grouped data using the `.agg()` function.


```python
df.groupby(["Gender", "Department"]).agg(['mean', 'count'])
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr>
      <th></th>
      <th></th>
      <th colspan="2" halign="left">Age</th>
      <th colspan="2" halign="left">Salary</th>
    </tr>
    <tr>
      <th></th>
      <th></th>
      <th>mean</th>
      <th>count</th>
      <th>mean</th>
      <th>count</th>
    </tr>
    <tr>
      <th>Gender</th>
      <th>Department</th>
      <th></th>
      <th></th>
      <th></th>
      <th></th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th rowspan="3" valign="top">F</th>
      <th>HR</th>
      <td>32.0</td>
      <td>1</td>
      <td>55000.0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>Marketing</th>
      <td>33.0</td>
      <td>1</td>
      <td>65000.0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>Product</th>
      <td>27.0</td>
      <td>1</td>
      <td>82000.0</td>
      <td>1</td>
    </tr>
    <tr>
      <th rowspan="3" valign="top">M</th>
      <th>HR</th>
      <td>31.0</td>
      <td>1</td>
      <td>55000.0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>Marketing</th>
      <td>26.0</td>
      <td>1</td>
      <td>60000.0</td>
      <td>1</td>
    </tr>
    <tr>
      <th>Product</th>
      <td>26.0</td>
      <td>2</td>
      <td>64000.0</td>
      <td>2</td>
    </tr>
  </tbody>
</table>
</div>