Concatenating data

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/.