Explode a pandas column containing a dictionary into new rows

Introduction

When working with data in pandas, it’s not uncommon to encounter columns that contain dictionaries of varying lengths. This can make it difficult to perform operations on these values, as you might expect. In this article, we’ll explore how to explode such a column into separate rows, creating two new columns for each entry.

Problem Description

The problem arises when you want to extract specific information from a dictionary in a pandas DataFrame. For example, suppose you have a DataFrame with one column containing dictionaries of different lengths:

   Chrom   POS   N_Allels   dict

0      1   345   2010       {"A":0.1,"T":0.22,"G":0.01}
1      1   357   1989       {"T":0.9}
2      1   365   1850       {"A":0.3,"G":0.2}

In this case, you might want to explode the dict column into two separate columns: one for each key in the dictionary, and another for the corresponding value.

Solution Overview

One way to achieve this is by using a combination of pandas’ built-in functions: assign, explode, str.get, and map. We’ll go through each step and explain how it works before providing an example code snippet.

Step 1: Replace Dictionaries with Items Iterators

First, we replace the dictionaries with their corresponding items iterators using the dict.items() method. This gives us a list of tuples, where each tuple contains a key-value pair from the original dictionary.

df = df.assign(dict=df.dict.map(lambda d: d.items()))

Step 2: Explode the Items Column

Next, we explode the new items column into separate rows using the explode function. This creates a new row for each item in the list.

df_exploded = (
    df.explode("dict")
    .assign(
        base=lambda df: df.dict.str.get(0),
        freq=lambda df: df.dict.str.get(1)
    )
    .drop(columns="dict")
    .reset_index(drop=True)
)

Step 3: Extract Base and Frequency Values

We then extract the values for the base and freq columns using the str.get method. Since dictionaries have two entries, we use indexing to select the first value (0) for base and the second value (1) for freq.

Putting it all Together

Now that we’ve explained each step, let’s combine them into a single code snippet:

import pandas as pd

# Create a sample DataFrame
data = {
    "Chrom": [1, 1, 1],
    "POS": [345, 357, 365],
    "N_Allels": [2010, 1989, 1850],
    "dict": [
        {"A": 0.1, "T": 0.22, "G": 0.01},
        {"T": 0.9},
        {"A": 0.3, "G": 0.2}
    ]
}
df = pd.DataFrame(data)

# Explode the dict column into new rows
df_exploded = (
    df.assign(dict=df.dict.map(lambda d: d.items()))
    .explode("dict")
    .assign(
        base=lambda df: df.dict.str.get(0),
        freq=lambda df: df.dict.str.get(1)
    )
    .drop(columns="dict")
    .reset_index(drop=True)
)

# Print the resulting DataFrame
print(df_exploded)

Example Output

The output of this code snippet should look something like this:

   Chrom  POS  N_Allels base  freq
0      1  345      2010    A  0.10
1      1  345      2010    T  0.22
2      1  345      2010    G  0.01
3      1  357      1989    T  0.90
4      1  365      1850    A  0.30
5      1  365      1850    G  0.20

This shows us how the original dictionary column has been successfully exploded into separate rows, with two new columns for base and freq.

Last modified on 2024-08-30