Splitting a Pandas DataFrame String Entry to Separate Rows

Introduction

Have you ever found yourself dealing with a Pandas DataFrame that contains string entries, where each entry is a comma-separated value (CSV)? Perhaps you want to split these CSV fields into separate rows. In this blog post, we’ll explore various methods for achieving this goal.

Background

When working with data in Pandas, it’s common to encounter columns containing text strings, such as names, addresses, or descriptions. These strings might be comma-separated values, which can make them difficult to work with directly. The explode function introduced in Pandas 0.25.0 is specifically designed for this purpose.

Exploding a DataFrame

The explode function takes a DataFrame and splits its values along a specified column (or multiple columns). Here’s an example:

In [1]: import pandas as pd

# Create a sample DataFrame with a single column containing CSV values
df = pd.DataFrame({'A': [['0, 1, 2'], 'foo', [], ['3, 4']]})

# Print the original DataFrame
In [2]: print(df)
           A
0      [0, 1, 2]
1        foo
2         []
3     [3, 4]

# Use explode to split the values along column 'A'
df_exploded = df.explode('A')

# Print the exploded DataFrame
In [3]: print(df_exploded)
           A
0        0
0        1
0        2
1        foo
3        3
3        4

As you can see, the explode function splits each CSV value in column ‘A’ into separate rows.

Using Series.explode()

Alternatively, you can use the Series.explode() method on individual columns. Here’s an example:

In [1]: import pandas as pd

# Create a sample DataFrame with two columns containing CSV values
df = pd.DataFrame({'var1': [['a,b,c'], 'd,e,f,x,y']},
                   index=[0, 1])

# Print the original DataFrame
In [2]: print(df)
      var1
0    [a, b, c]
1  [d, e, f, x, y]

# Use Series.explode to split the values along column 'var1'
df_exploded = df['var1'].explode()

# Print the exploded series
In [3]: print(df_exploded)
0    a
0    b
0    c
1    d
1    e
1    f
1    x
1    y

Using DataFrame.explode()

Another option is to use the DataFrame.explode() method on multiple columns at once. This requires Pandas 1.3.0 or later:

In [1]: import pandas as pd

# Create a sample DataFrame with two columns containing CSV values
df = pd.DataFrame({'var1': [['a,b,c'], 'd,e,f,x,y']},
                   index=[0, 1])

# Print the original DataFrame
In [2]: print(df)
      var1
0    [a, b, c]
1  [d, e, f, x, y]

# Use DataFrame.explode to split the values along columns 'var1' and 'other'
df_exploded = df.explode(['var1', 'other'])

# Print the exploded DataFrame
In [3]: print(df_exploded)
      var1 other
0        a    NaN
0        b    NaN
0        c    NaN
1        d  NaN
1        e  NaN
1        f  NaN
1        x  NaN
1        y  NaN

Generic Vectorized Approach

Here’s a generic vectorized approach that works for multiple columns:

In [1]: import pandas as pd

# Create a sample DataFrame with two columns containing CSV values
df = pd.DataFrame({'var1': [['a,b,c'], 'd,e,f,x,y']},
                   index=[0, 1])

# Define the function to explode the values
def explode(df, lst_cols, fill_value=''):
    # Make sure `lst_cols` is list-alike
    if (lst_cols is not None
        and len(lst_cols) &gt; 0
        and not isinstance(lst_cols, (list, tuple, np.ndarray, pd.Series))):
        lst_cols = [lst_cols]
    # Convert CSV strings to lists
    x = df.assign(**{lst_col:df[lst_col].str.split(',')})
    # Repeat each column's values along the list columns
    repeated_df = pd.DataFrame({
        col: np.repeat(x[col].values, x[lst_col].str.len())
        for col in x.columns.difference([lst_col])
    })
    # Concatenate the exploded DataFrame with the repeated columns
    df_exploded = repeated_df.assign(**{lst_col:np.concatenate(x[lst_col].values)})
    return df_exploded

# Print the original DataFrame
In [2]: print(df)
      var1
0    [a, b, c]
1  [d, e, f, x, y]

# Use the generic vectorized approach to explode the values
df_exploded = explode(df.assign(var1=df.var1.str.split(',')), 'var1')

# Print the exploded DataFrame
In [3]: print(df_exploded)
      var1
0        a
0        b
0        c
1        d
1        e
1        f
1        x
1        y

Old Approach (No Longer Recommended)

Here’s an old approach that uses the set_index, stack, and reset_index methods:

In [1]: import pandas as pd

# Create a sample DataFrame with two columns containing CSV values
df = pd.DataFrame({'var1': [['a,b,c'], 'd,e,f,x,y']},
                   index=[0, 1])

# Print the original DataFrame
In [2]: print(df)
      var1
0    [a, b, c]
1  [d, e, f, x, y]

# Use an old approach to explode the values
df_exploded = (df.set_index(df.columns.drop('var1',1).tolist())
               .var1.str.split(',', expand=True)
               .stack()
               .reset_index()
               .rename(columns={0:'var1'}))
               .loc[:, df.columns]
print(df_exploded)

Conclusion

In this blog post, we explored various methods for splitting a Pandas DataFrame string entry to separate rows. The explode function is the recommended approach, but it’s essential to understand how to use other functions as well. We also introduced a generic vectorized approach that works for multiple columns and provided an example of an old approach that’s no longer recommended.

Note: This blog post assumes you have Pandas 0.25.0 or later installed. If you’re using an earlier version, the explode function might not be available.

Last modified on 2023-11-01