Table of Contents
Splitting a column of lists into multiple columns involves using a delimiter to separate the lists into individual values and then mapping each of those values to a new column. This can be done using a variety of methods, such as SQL, Excel formulas, or programming languages. A well-defined and organized data source is essential for this process. Once the columns are separated, advanced data analysis can be performed on the individual columns.
You can use the following basic syntax to split a column of lists into multiple columns in a pandas DataFrame:
#split column of lists into two new columns
split = pd.DataFrame(df['my_column'].to_list(), columns = ['new1', 'new2'])
#join split columns back to original DataFrame
df = pd.concat([df, split], axis=1) The following example shows how to use this syntax in practice.
Example: Split Column of Lists into Multiple Columns in Pandas
Suppose we have the following pandas DataFrame in which the column called points contains lists of values:
import pandas as pd #create DataFrame df = pd.DataFrame({'team': ['Mavs', 'Heat', 'Kings', 'Suns'], 'points': [[99, 105], [94, 113], [99, 97], [87, 95]]}) #view DataFrame print(df) team points 0 Mavs [99, 105] 1 Heat [94, 113] 2 Kings [99, 97] 3 Suns [87, 95]
We can use the following syntax to create a new DataFrame in which the points column is split into two new columns called game1 and game2:
#split column of lists into two new columns
split = pd.DataFrame(df['my_column'].to_list(), columns = ['new1', 'new2'])
#view DataFrame
print(split)
game1 game2
0 99 105
1 94 113
2 99 97
3 87 95
If we’d like, we can then join this split DataFrame back with the original DataFrame by using the concat() function:
#join split columns back to original DataFrame
df = pd.concat([df, split], axis=1)
#view updated DataFrame
print(df)
team points game1 game2
0 Mavs [99, 105] 99 105
1 Heat [94, 113] 94 113
2 Kings [99, 97] 99 97
3 Suns [87, 95] 87 95
Lastly, we can drop the original points column from the DataFrame if we’d like:
#drop original points column
df = df.drop('points', axis=1)
#view updated DataFrame
print(df)
team game1 game2
0 Mavs 99 105
1 Heat 94 113
2 Kings 99 97
3 Suns 87 95
The end result is a DataFrame in which the original points column of lists is now split into two new columns called game1 and game2.
Note: If your column of lists has an uneven number of values in each list, pandas will simply fill in missing values with NaN values when splitting the lists into columns.