Python Pandas: How to Merge Multiple Columns into a Dictionary Column in DataFrame – Step-by-Step Guide

Table of Contents#

1. Prerequisites
2. Understanding the Goal
3. Step 1: Set Up Your Environment
4. Step 2: Create a Sample DataFrame
5. Step 3: Merge Columns into a Dictionary Column
   • 5.1 Basic Method: Using apply() with to_dict()
   • 5.2 Merge Specific Columns
   • 5.3 Merge All Columns Except One
   • 5.4 Merge with Custom Dictionary Keys
   • 5.5 Handle Missing Values (NaNs)
6. Use Cases for Dictionary Columns
7. Troubleshooting Common Issues
8. Conclusion
9. References

1. Prerequisites#

Before starting, ensure you have:

• Python 3.6+ installed.
• Pandas library installed. If not, run:

  pip install pandas  

2. Understanding the Goal#

We aim to take a DataFrame with multiple columns (e.g., A, B, C) and create a new column (e.g., merged_dict) where each row entry is a dictionary mapping column names to their respective values. For example:

3. Step 1: Set Up Your Environment#

First, import Pandas to work with DataFrames:

import pandas as pd  

4. Step 2: Create a Sample DataFrame#

Let’s create a sample DataFrame to demonstrate merging. We’ll use data about users with columns: ID, Name, Age, City, and Occupation.

# Sample data  
data = {  
    'ID': [1, 2, 3, 4],  
    'Name': ['Alice', 'Bob', 'Charlie', 'Diana'],  
    'Age': [30, 25, 35, None],  # Note: Diana has a missing Age (NaN)  
    'City': ['New York', 'London', 'Paris', 'Sydney'],  
    'Occupation': ['Engineer', 'Designer', 'Teacher', 'Doctor']  
}  
 
# Create DataFrame  
df = pd.DataFrame(data)  
print(df)  

Output:

   ID     Name   Age      City Occupation  
0   1    Alice  30.0  New York   Engineer  
1   2      Bob  25.0    London   Designer  
2   3  Charlie  35.0     Paris    Teacher  
3   4    Diana   NaN    Sydney     Doctor  

5. Step 3: Merge Columns into a Dictionary Column#

5.1 Basic Method: Using apply() with to_dict()#

The simplest way to merge columns into a dictionary is to use df.apply() with a lambda function. The apply() method applies a function to each row (or column), and to_dict() converts the selected columns of a row into a dictionary.

Example: Merge Name, Age, and City into user_details#

# Merge 'Name', 'Age', 'City' into a dictionary column  
df['user_details'] = df[['Name', 'Age', 'City']].apply(lambda row: row.to_dict(), axis=1)  
 
print(df[['ID', 'user_details']])  # Show ID and the new dictionary column  

Output:

   ID                                   user_details  
0   1  {'Name': 'Alice', 'Age': 30.0, 'City': 'New York'}  
1   2    {'Name': 'Bob', 'Age': 25.0, 'City': 'London'}  
2   3  {'Name': 'Charlie', 'Age': 35.0, 'City': 'Paris'}  
3   4     {'Name': 'Diana', 'Age': nan, 'City': 'Sydney'}  

How It Works:

• df[['Name', 'Age', 'City']] selects the columns to merge.
• apply(..., axis=1) applies the lambda function to each row (axis=1 = row-wise).
• row.to_dict() converts the row (a Pandas Series) into a dictionary with column names as keys and row values as values.

5.2 Merge Specific Columns#

To merge only specific columns (e.g., exclude Age), modify the column list in the selection:

# Merge only 'Name' and 'Occupation'  
df['name_occupation'] = df[['Name', 'Occupation']].apply(lambda row: row.to_dict(), axis=1)  
 
print(df[['ID', 'name_occupation']])  

Output:

   ID                          name_occupation  
0   1    {'Name': 'Alice', 'Occupation': 'Engineer'}  
1   2      {'Name': 'Bob', 'Occupation': 'Designer'}  
2   3  {'Name': 'Charlie', 'Occupation': 'Teacher'}  
3   4    {'Name': 'Diana', 'Occupation': 'Doctor'}  

5.3 Merge All Columns Except One#

To merge all columns except a key column (e.g., ID), use df.drop() to exclude the key before merging:

# Merge all columns except 'ID' into 'all_details'  
df['all_details'] = df.drop('ID', axis=1).apply(lambda row: row.to_dict(), axis=1)  
 
print(df[['ID', 'all_details']])  

Output:

   ID                                                                 all_details  
0   1  {'Name': 'Alice', 'Age': 30.0, 'City': 'New York', 'Occupation': 'Engineer'}  
1   2    {'Name': 'Bob', 'Age': 25.0, 'City': 'London', 'Occupation': 'Designer'}  
2   3  {'Name': 'Charlie', 'Age': 35.0, 'City': 'Paris', 'Occupation': 'Teacher'}  
3   4     {'Name': 'Diana', 'Age': nan, 'City': 'Sydney', 'Occupation': 'Doctor'}  

5.4 Merge with Custom Dictionary Keys#

By default, row.to_dict() uses column names as dictionary keys. To use custom keys (e.g., rename Name to full_name), explicitly map keys in the lambda function:

# Merge with custom keys: 'Name' → 'full_name', 'Age' → 'years_old'  
df['custom_details'] = df.apply(  
    lambda row: {  
        'full_name': row['Name'],  
        'years_old': row['Age'],  
        'location': row['City']  
    },  
    axis=1  
)  
 
print(df[['ID', 'custom_details']])  

Output:

   ID                                         custom_details  
0   1  {'full_name': 'Alice', 'years_old': 30.0, 'locati...  
1   2  {'full_name': 'Bob', 'years_old': 25.0, 'location...  
2   3  {'full_name': 'Charlie', 'years_old': 35.0, 'loca...  
3   4  {'full_name': 'Diana', 'years_old': nan, 'locatio...  

5.5 Handle Missing Values (NaNs)#

If your DataFrame has missing values (e.g., NaN in Age for Diana), row.to_dict() will include the NaN in the dictionary. To exclude NaN values from the dictionary, use dropna():

# Merge 'Name', 'Age', 'City' and exclude NaNs  
df['user_details_clean'] = df[['Name', 'Age', 'City']].apply(  
    lambda row: row.dropna().to_dict(),  # dropna() removes NaN entries  
    axis=1  
)  
 
print(df[['ID', 'user_details_clean']])  

Output:

   ID                              user_details_clean  
0   1  {'Name': 'Alice', 'Age': 30.0, 'City': 'New York'}  
1   2    {'Name': 'Bob', 'Age': 25.0, 'City': 'London'}  
2   3  {'Name': 'Charlie', 'Age': 35.0, 'City': 'Paris'}  
3   4             {'Name': 'Diana', 'City': 'Sydney'}  # NaN Age is excluded  

6. Use Cases for Dictionary Columns#

• Nested Data: Group related attributes (e.g., user demographics) into a single field for clarity.
• JSON Serialization: Dictionary columns are easily converted to JSON using df.to_json(), which is useful for APIs or NoSQL databases (e.g., MongoDB).
• Simplifying DataFrames: Reduce the number of columns when analyzing high-dimensional data.
• Feature Engineering: Combine multiple features into a single "metadata" column for machine learning pipelines.

7. Troubleshooting Common Issues#

KeyError: Column Not Found#

If you get a KeyError, ensure the column names in df[['col1', 'col2']] match the DataFrame’s actual columns.

Non-Serializable Values#

If your dictionary contains non-serializable objects (e.g., datetime objects), convert them to strings first:

# Example: Convert datetime to string  
df['custom_details'] = df.apply(  
    lambda row: {'date_joined': str(row['JoinDate'])},  # Convert datetime to str  
    axis=1  
)  

Slow Performance for Large DataFrames#

For very large DataFrames, apply() can be slow. Use vectorized methods like df.to_dict('records') to create a list of dictionaries, then assign it as a column:

# Faster for large DataFrames: Convert entire DataFrame to list of dicts, then assign  
df['all_details_fast'] = df.drop('ID', axis=1).to_dict('records')  

8. Conclusion#

Merging multiple columns into a dictionary column in Pandas is a powerful technique for organizing nested or related data. By using apply() with to_dict(), you can flexibly merge specific columns, customize keys, and handle missing values. This method simplifies DataFrames, eases JSON serialization, and supports advanced data workflows.

9. References#

• Pandas apply() Documentation
• Pandas to_dict() Documentation
• Pandas dropna() Documentation