We will leverage the pandas library in Python for this chapter. The code and notebooks for this chapter are available on GitHub at https://github.com/PacktPublishing/Exploratory-Data-Analysis-with-Python-Cookbook.

When we group data, we are aggregating the data by category. This can be very useful especially when we need to get a high-level view of a detailed dataset. Typically, to group a dataset, we need to identify the column/category to group by, the column to aggregate by, and the specific aggregation to be done. The column/category to group by is usually a categorical column while the column to aggregate by is usually a numeric column. The aggregation to be done can be a count, sum, minimum, maximum, and so on. We can also perform aggregation such as count directly on the categorical column we group by

In pandas, the groupby method helps us group data.

Getting ready

We will work with one dataset in this chapter – the Marketing Campaign data from Kaggle.

Create a folder for this chapter and create a new Python script or Jupyter notebook file in that folder. Create a data subfolder and place the marketing_campaign.csv file in that subfolder. Alternatively, you can retrieve all the files from the GitHub repository.

Note

Kaggle provides the Marketing Campaign data for public use at https://www.kaggle.com/datasets/imakash3011/customer-personality-analysis. In this chapter, we use both the full dataset and samples of the dataset for the different recipes. The data is also available in the repository. The data in Kaggle appears in a single-column format, but the data in the repository was transformed into a multiple-column format for easy usage in pandas.

How to do it…

We will learn how to group data using the pandas library:

1. Import the pandas library:

   import pandas as pd

2. Load the .csv file into a dataframe using read_csv. Then, subset the dataframe to include only relevant columns:

   marketing_data = pd.read_csv("data/marketing_campaign.csv")

   marketing_data = marketing_data[['ID','Year_Birth', 'Education','Marital_Status','Income','Kidhome', 'Teenhome', 'Dt_Customer',              'Recency','NumStorePurchases', 'NumWebVisitsMonth']]

3. Inspect the data. Check the first few rows and use transpose (T) to show more information. Also, check the data types as well as the number of columns and rows:

   marketing_data.head(2).T

               0    1

   ID    5524    2174

   Year_Birth    1957    1954

   Education    Graduation    Graduation

   …        …        …

   NumWebVisitsMonth    7    5

   marketing_data.dtypes

   ID    int64

   Year_Birth    int64

   Education    object

   …           …

   NumWebVisitsMonth    int64

   marketing_data.shape

   (2240, 11)

4. Use the groupby method in pandas to get the average number of store purchases of customers based on the number of kids at home:

   marketing_data.groupby('Kidhome')['NumStorePurchases'].mean()

   Kidhome

   0    7.2173240525908735

   1    3.863181312569522

   2    3.4375

That’s all. Now, we have grouped our dataset.

How it works...

All of the recipes in this chapter use the pandas library for data transformation and manipulation. We refer to pandas as pd in step 1. In step 2, we use read_csv to load the .csv file into a pandas dataframe and call it marketing_data. We also subset the dataframe to include only 11 relevant columns. In step 3, we inspect the dataset using the head method to see the first two rows in the dataset; we also use transform (T) along with head to transform the rows into columns, due to the size of the data (i.e., it has many columns). We use the dtypes attribute of the dataframe to show the data types of all columns. Numeric data has int and float data types while character data has the object data type. We inspect the number of rows and columns using shape, which returns a tuple that displays the number of rows as the first element and the number of columns as the second element.

In step 4, we apply the groupby method to get the average number of store purchases of customers based on the number of kids at home. Using the groupby method, we group by Kidhome, then we aggregate by NumStorePurchases, and finally, we use the mean method as the specific aggregation to be performed on NumStorePurchases.

There’s more...

Using the groupby method in pandas, we can group by multiple columns. Typically, these columns only need to be presented in a Python list to achieve this. Also, beyond the mean, several other aggregation methods can be applied, such as max, min, and median. In addition, the agg method can be used for aggregation; typically, we will need to provide specific numpy functions to be used. Custom functions for aggregation can be applied through the apply or transform method in pandas.

See also

Here is an insightful article by Dataquest on the groupby method in pandas: https://www.dataquest.io/blog/grouping-data-a-step-by-step-tutorial-to-groupby-in-pandas/.

Sometimes, we may be analyzing multiple datasets that have a similar structure or samples of the same dataset. While analyzing our datasets, we may need to append them together into a new single dataset. When we append datasets, we stitch the datasets along the rows. For example, if we have 2 datasets containing 1,000 rows and 20 columns each, the appended data will contain 2,000 rows and 20 columns. The rows typically increase while the columns remain the same. The datasets are allowed to have a different number of rows but typically should have the same number of columns to avoid errors after appending.

In pandas, the concat method helps us append data.

Getting ready

We will continue working with the Marketing Campaign data from Kaggle. We will work with two samples of that dataset.

Place the marketing_campaign_append1.csv and marketing_campaign_append2.csv files in the data subfolder created in the first recipe. Alternatively, you could retrieve all the files from the GitHub repository.

How to do it…

We will explore how to append data using the pandas library:

1. Import the pandas library:

   import pandas as pd

2. Load the .csv files into a dataframe using read_csv. Then, subset the dataframes to include only relevant columns:

   marketing_sample1 = pd.read_csv("data/marketing_campaign_append1.csv")

   marketing_sample2 = pd.read_csv("data/marketing_campaign_append2.csv")

   marketing_sample1 = marketing_sample1[['ID', 'Year_Birth','Education','Marital_Status','Income', 'Kidhome','Teenhome','Dt_Customer', 'Recency','NumStorePurchases', 'NumWebVisitsMonth']]

   marketing_sample2 = marketing_sample2[['ID', 'Year_Birth','Education','Marital_Status','Income', 'Kidhome','Teenhome','Dt_Customer', 'Recency','NumStorePurchases', 'NumWebVisitsMonth']]

3. Take a look at the two datasets. Check the first few rows and use transpose (T) to show more information:

   marketing_sample1.head(2).T

       0    1

   ID    5524    2174

   Year_Birth    1957    1954

   …        …        …

   NumWebVisitsMonth    7    5

   marketing_sample2.head(2).T

       0    1

   ID    9135    466

   Year_Birth    1950    1944

   …        …        …

   NumWebVisitsMonth    8    2

4. Check the data types as well as the number of columns and rows:

   marketing_sample1.dtypes

   ID    int64

   Year_Birth    int64

   …          …

   NumWebVisitsMonth    int64

   marketing_sample2.dtypes

   ID    int64

   Year_Birth    int64

   …          …

   NumWebVisitsMonth    int64

   marketing_sample1.shape

   (500, 11)

   marketing_sample2.shape

   (500, 11)

5. Append the datasets. Use the concat method from the pandas library to append the data:

   appended_data = pd.concat([marketing_sample1, marketing_sample2])

6. Inspect the shape of the result and the first few rows:

   appended_data.head(2).T

       0    1

   ID    5524    2174

   Year_Birth    1957    1954

   Education    Graduation    Graduation

   Marital_Status    Single    Single

   Income    58138.0    46344.0

   Kidhome    0    1

   Teenhome    0    1

   Dt_Customer    04/09/2012    08/03/2014

   Recency    58    38

   NumStorePurchases    4    2

   NumWebVisitsMonth    7    5

   appended_data.shape

   (1000, 11)

Well done! We have appended our datasets.

How it works...

We import the pandas library and refer to it as pd in step 1. In step 2, we use read_csv to load the two .csv files to be appended into pandas dataframes. We call the dataframes marketing_sample1 and marketing_sample2 respectively. We also subset the dataframes to include only 11 relevant columns. In step 3, we inspect the dataset using the head method to see the first two rows in the dataset; we also use transform (T) along with head to transform the rows into columns due to the size of the data (i.e., it has many columns). In step 4, we use the dtypes attribute of the dataframe to show the data types of all columns. Numeric data has int and float data types while character data has the object data type. We inspect the number of rows and columns using shape, which returns a tuple that displays the number of rows and columns respectively.

In step 5, we apply the concat method to append the two datasets. The method takes in the list of dataframes as an argument. The list is the only argument required because the default setting of the concat method is to append data. In step 6, we inspect the first few rows of the output and its shape.

There’s more...

Using the concat method in pandas, we can append multiple datasets beyond just two. All that is required is to include these datasets in the list, and then they will be appended. It is important to note that the datasets must have the same columns.

Sometimes, we may need to stitch multiple datasets or samples of the same dataset by columns and not rows. This is where we concatenate our data. While appending stitches rows of data together, concatenating stitches columns together to provide a single dataset. For example, if we have 2 datasets containing 1,000 rows and 20 columns each, the concatenated data will contain 1,000 rows and 40 columns. The columns typically increase while the rows remain the same. The datasets are allowed to have a different number of columns but typically should have the same number of rows to avoid errors after concatenating.

In pandas, the concat method helps us concatenate data.

Getting ready

We will continue working with the Marketing Campaign data from Kaggle. We will work with two samples of that dataset.

Place the marketing_campaign_concat1.csv and marketing_campaign_concat2.csv files in the data subfolder created in the first recipe. Alternatively, you can retrieve all the files from the GitHub repository.

How to do it…

We will explore how to concatenate data using the pandas library:

1. Import the pandas library:

   import pandas as pd

2. Load the .csv files into a dataframe using read_csv:

   marketing_sample1 = pd.read_csv("data/marketing_campaign_concat1.csv")

   marketing_sample2 = pd.read_csv("data/marketing_campaign_concat2.csv")

3. Take a look at the two datasets. Check the first few rows and use transpose (T) to show more information:

   marketing_sample1.head(2).T

       0    1

   ID    5524    2174

   Year_Birth    1957    1954

   Education    Graduation    Graduation

   Marital_Status    Single    Single

   Income    58138.0    46344.0

   marketing_sample2.head(2).T

       0    1

   NumDealsPurchases    3    2

   NumWebPurchases    8    1

   NumCatalogPurchases    10    1

   NumStorePurchases    4    2

   NumWebVisitsMonth    7    5

4. Check the data types as well as the number of columns and rows:

   marketing_sample1.dtypes

   ID    int64

   Year_Birth    int64

   Education    object

   Marital_Status    object

   Income    float64

   marketing_sample2.dtypes

   NumDealsPurchases    int64

   NumWebPurchases         int64

   NumCatalogPurchases    int64

   NumStorePurchases    int64

   NumWebVisitsMonth     int64

   marketing_sample1.shape

   (2240, 5)

   marketing_sample2.shape

   (2240, 5)

5. Concatenate the datasets. Use the concat method from the pandas library to concatenate the data:

   concatenated_data = pd.concat([marketing_sample1, marketing_sample2], axis = 1)

6. Inspect the shape of the result and the first few rows:

   concatenated_data.head(2).T

       0    1

   ID    5524    2174

   Year_Birth    1957    1954

   Education    Graduation    Graduation

   Marital_Status    Single    Single

   Income    58138.0    46344.0

   NumDealsPurchases    3    2

   NumWebPurchases    8    1

   NumCatalogPurchases    10    1

   NumStorePurchases    4    2

   NumWebVisitsMonth    7    5

   concatenated_data.shape

   (2240, 10)

Awesome! We have concatenated our datasets.

How it works...

We import the pandas library and refer to it as pd in step 1. In step 2, we use read_csv to load the two .csv files to be concatenated into pandas dataframes. We call the dataframes marketing_sample1 and marketing_sample2 respectively. In step 3, we inspect the dataset using head(2) to see the first two rows in the dataset; we also use transform (T) along with head to transform the rows into columns due to the size of the data (i.e., it has many columns). In step 4, we use the dtypes attribute of the dataframe to show the data types of all columns. Numeric data has int and float data types while character data has the object data type. We inspect the number of rows and columns using shape, which returns a tuple that displays the number of rows and columns respectively.

In step 5, we apply the concat method to concatenate the two datasets. Just like when appending, the method takes in the list of dataframes as an argument. However, it takes an additional argument for the axis parameter. The value 1 indicates that the axis refers to columns. The default value is typically 0, which refers to rows and is relevant for appending datasets. In step 6, we check the first few rows of the output as well as the shape.

There’s more...

Using the concat method in pandas, we can concatenate multiple datasets beyond just two. Just like appending, all that is required is to include these datasets in the list and the axis value, which is typically 1 for concatenation. It is important to note that the datasets must have the same number of rows.

See also

You can read this insightful article by Dataquest on concatenation: https://www.dataquest.io/blog/pandas-concatenation-tutorial/.