Python Pandas - Concatenation

Pandas - Concatenating Objects

Pandas provides the powerful pd.concat() function for combining Series or DataFrame objects along an axis. It offers flexibility in how objects are joined and how their indices are handled.

Important Note: The older .append() method on Series and DataFrame is deprecated and removed in Pandas 2.0. You should exclusively use pd.concat() for combining objects.

The pd.concat() Function

pd.concat(objs, axis=0, join='outer', ignore_index=False, keys=None,
          levels=None, names=None, verify_integrity=False,
          sort=False, copy=None) # copy is deprecated

Key Parameters:

• objs: A sequence (usually a list) or mapping of Series or DataFrame objects to concatenate.
• axis ({0 or ‘index’, 1 or ‘columns’}, default 0): The axis to concatenate along. 0 means stacking rows vertically, 1 means stacking columns horizontally.
• join ({‘inner’, ‘outer’}, default ‘outer’): How to handle indexes on the other axis (the one not being concatenated). ‘outer’ performs a union (keeps all labels), ‘inner’ performs an intersection (keeps only shared labels).
• ignore_index (boolean, default False): If True, does not preserve the index values along the concatenation axis. The resulting axis will be labeled with a new RangeIndex (0, …, n-1).
• keys: Sequence (e.g., list or tuple). Constructs a hierarchical index (MultiIndex) on the concatenation axis, using the provided keys as the outermost level.
• verify_integrity (boolean, default False): If True, checks if the new concatenated axis contains duplicates. Raises an exception if duplicates are found. Useful when indices should be unique.
• sort (boolean, default False): Sorts the other axis (columns if axis=0, index if axis=1) if it is not already aligned when performing an ‘outer’ join.

Concatenating DataFrames (Stacking Rows)

The most common use case is stacking DataFrames vertically (along axis=0).

import pandas as pd

df1 = pd.DataFrame({
        'Name': ['Alex', 'Amy', 'Allen', 'Alice'],
        'SubjectID': ['sub1', 'sub2', 'sub4', 'sub6'],
        'Score': [98, 90, 87, 69]
    },
    index=[101, 102, 103, 104]) # Meaningful index

df2 = pd.DataFrame({
        'Name': ['Billy', 'Brian', 'Bran', 'Bryce'],
        'SubjectID': ['sub2', 'sub4', 'sub3', 'sub6'],
        'Score': [89, 80, 79, 97]
    },
    index=[201, 202, 203, 204]) # Different meaningful index

print("DataFrame df1:")
print(df1)
print("\nDataFrame df2:")
print(df2)

# Concatenate along axis=0 (default)
concatenated_df = pd.concat([df1, df2])
print("\nConcatenated DataFrame (axis=0, default):")
print(concatenated_df)

Output:

DataFrame df1:
      Name SubjectID  Score
101   Alex      sub1     98
102    Amy      sub2     90
103  Allen      sub4     87
104  Alice      sub6     69

DataFrame df2:
      Name SubjectID  Score
201  Billy      sub2     89
202  Brian      sub4     80
203   Bran      sub3     79
204  Bryce      sub6     97

Concatenated DataFrame (axis=0, default):
      Name SubjectID  Score
101   Alex      sub1     98
102    Amy      sub2     90
103  Allen      sub4     87
104  Alice      sub6     69
201  Billy      sub2     89
202  Brian      sub4     80
203   Bran      sub3     79
204  Bryce      sub6     97

Notice that the original indices are preserved.

Using keys for Hierarchical Indexing

If you want to track the origin of the concatenated parts, use the keys argument to create a MultiIndex.

# Using df1 and df2 from the previous example

concatenated_keys = pd.concat([df1, df2], keys=['Set1', 'Set2'])
print("\nConcatenated DataFrame with keys:")
print(concatenated_keys)

# Accessing data using the MultiIndex
print("\nAccessing Set1:")
print(concatenated_keys.loc['Set1'])

print("\nAccessing specific row in Set2:")
print(concatenated_keys.loc[('Set2', 201)])

Output:

Concatenated DataFrame with keys:
           Name SubjectID  Score
Set1 101   Alex      sub1     98
     102    Amy      sub2     90
     103  Allen      sub4     87
     104  Alice      sub6     69
Set2 201  Billy      sub2     89
     202  Brian      sub4     80
     203   Bran      sub3     79
     204  Bryce      sub6     97

Accessing Set1:
      Name SubjectID  Score
101   Alex      sub1     98
102    Amy      sub2     90
103  Allen      sub4     87
104  Alice      sub6     69

Accessing specific row in Set2:
Name         Billy
SubjectID     sub2
Score           89
Name: (Set2, 201), dtype: object

Ignoring the Original Index

If the original indices are not meaningful or you want a simple 0-based index, use ignore_index=True.

# Using df1 and df2 from the previous example

concatenated_ignore = pd.concat([df1, df2], ignore_index=True)
print("\nConcatenated DataFrame with ignore_index=True:")
print(concatenated_ignore)

# Note: Using ignore_index=True overrides the keys argument if both are provided.

Output:

Concatenated DataFrame with ignore_index=True:
    Name SubjectID  Score
0   Alex      sub1     98
1    Amy      sub2     90
2  Allen      sub4     87
3  Alice      sub6     69
4  Billy      sub2     89
5  Brian      sub4     80
6   Bran      sub3     79
7  Bryce      sub6     97

Concatenating Horizontally (Adding Columns)

To place DataFrames side-by-side based on their index, use axis=1.

import pandas as pd

df_a = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]}, index=['r1', 'r2', 'r3'])
df_b = pd.DataFrame({'C': [7, 8, 9], 'D': [10, 11, 12]}, index=['r1', 'r2', 'r3'])
df_c = pd.DataFrame({'E': [13, 14]}, index=['r1', 'r2']) # Mismatched index

print("DataFrame A:")
print(df_a)
print("\nDataFrame B:")
print(df_b)
print("\nDataFrame C (mismatched index):")
print(df_c)

# Concatenate A and B horizontally (aligned index)
concat_axis1 = pd.concat([df_a, df_b], axis=1)
print("\nConcatenated A and B (axis=1):")
print(concat_axis1)

# Concatenate A and C horizontally (mismatched index -> NaN)
concat_axis1_nan = pd.concat([df_a, df_c], axis=1) # join='outer' is default
print("\nConcatenated A and C (axis=1, outer join):")
print(concat_axis1_nan)

# Concatenate A and C using inner join
concat_axis1_inner = pd.concat([df_a, df_c], axis=1, join='inner')
print("\nConcatenated A and C (axis=1, inner join):")
print(concat_axis1_inner)

Output:

DataFrame A:
    A  B
r1  1  4
r2  2  5
r3  3  6

DataFrame B:
    C   D
r1  7  10
r2  8  11
r3  9  12

DataFrame C (mismatched index):
     E
r1  13
r2  14

Concatenated A and B (axis=1):
    A  B  C   D
r1  1  4  7  10
r2  2  5  8  11
r3  3  6  9  12

Concatenated A and C (axis=1, outer join):
    A  B     E
r1  1  4  13.0
r2  2  5  14.0
r3  3  6   NaN

Concatenated A and C (axis=1, inner join):
    A  B   E
r1  1  4  13
r2  2  5  14

When axis=1, the join parameter controls whether to keep rows based on the union (outer) or intersection (inner) of the input DataFrames’ indices.

Brief Note on Time Series (Related Concepts)

While concatenation deals with combining existing objects, Pandas also excels at creating sequences of dates and times, which are often used as indices in DataFrames before concatenation or merging.

Get Current Time

import pandas as pd

current_time = pd.Timestamp.now()
print(f"Current Timestamp: {current_time}")

# Get only the date part
print(f"Current Date: {current_time.date()}")

Output (will vary):

Current Timestamp: 2023-10-27 10:30:00.123456
Current Date: 2023-10-27

Create a Specific Timestamp

import pandas as pd

# From a string
ts_from_string = pd.Timestamp('2024-01-15 09:00:00')
print(f"Timestamp from string: {ts_from_string}")

# From epoch seconds (specify unit)
ts_from_epoch = pd.Timestamp(1705309200, unit='s') # 15 Jan 2024 09:00:00 GMT
print(f"Timestamp from epoch: {ts_from_epoch}")

# With timezone
ts_with_tz = pd.Timestamp('2024-01-15 09:00:00', tz='America/New_York')
print(f"Timestamp with timezone: {ts_with_tz}")

Output:

Timestamp from string: 2024-01-15 09:00:00
Timestamp from epoch: 2024-01-15 09:00:00
Timestamp with timezone: 2024-01-15 09:00:00-05:00

Create a Range of Timestamps (date_range)

pd.date_range is highly versatile for creating sequences of dates or times.

import pandas as pd

# Daily frequency for 5 periods
daily_index = pd.date_range(start='2024-03-01', periods=5, freq='D')
print("Daily Date Range:")
print(daily_index)

# Hourly frequency between two times
hourly_index = pd.date_range(start='2024-03-01 10:00', end='2024-03-01 14:00', freq='H')
print("\nHourly Date Range:")
print(hourly_index)

# Business days
business_days = pd.date_range(start='2024-03-01', periods=7, freq='B')
print("\nBusiness Days:")
print(business_days)

Output:

Daily Date Range:
DatetimeIndex(['2024-03-01', '2024-03-02', '2024-03-03', '2024-03-04',
               '2024-03-05'],
              dtype='datetime64[ns]', freq='D')

Hourly Date Range:
DatetimeIndex(['2024-03-01 10:00:00', '2024-03-01 11:00:00',
               '2024-03-01 12:00:00', '2024-03-01 13:00:00',
               '2024-03-01 14:00:00'],
              dtype='datetime64[ns]', freq='H')

Business Days:
DatetimeIndex(['2024-03-01', '2024-03-04', '2024-03-05', '2024-03-06',
               '2024-03-07', '2024-03-08', '2024-03-11'],
              dtype='datetime64[ns]', freq='B')

Convert Objects to Timestamps (to_datetime)

pd.to_datetime intelligently converts various representations (strings, epochs, lists) into datetime objects. It returns a DatetimeIndex if passed a list-like object, or a Series of datetimes if passed a Series.

import pandas as pd

# From a Series containing various formats and None
date_series = pd.Series(['Jul 31, 2024', '2024-01-10 15:30', None, '2024/05/20'])
converted_series = pd.to_datetime(date_series)
print("Converted Series:")
print(converted_series)

# From a list
date_list = ['2024/11/23', '2025.12.31', None]
converted_index = pd.to_datetime(date_list)
print("\nConverted List to DatetimeIndex:")
print(converted_index)

Output:

Converted Series:
0   2024-07-31 00:00:00
1   2024-01-10 15:30:00
2                   NaT
3   2024-05-20 00:00:00
dtype: datetime64[ns]

Converted List to DatetimeIndex:
DatetimeIndex(['2024-11-23', '2025-12-31', 'NaT'], dtype='datetime64[ns]', freq=None)

NaT (Not a Time) represents missing timestamp values, analogous to NaN for numerical data.