Pandas library is a Python library which is used to perform data manipulation and analysis. It offers various 2D data structures and methods to work with tables. Some times, the entire data can be in the format of string, which needed to be broken down in-order to organize the information in the pandas data structures. In this article, let us understand how to break a comma separated string in a pandas column along with different possible approaches.

Break Up A Comma Separated String In Pandas Column

Using str.split()

let us understand the requirements for this approach:

Requirements:

• Pandas library: In this approach, we import the Pandas library and utilize the `DataFrame()` method in order to create a 2D data structure or table.
• str.split() method: This method is used to split the string of comma-separated values into individual strings based on a delimiter. The str.split() method accepts the delimiter as a parameter along with `expand=true`.

import pandas as pd
# Example DataFrame
data = {'Category': ['Fruits', 'Vegetables', 'Dairy'],
        'Contains': ['Apple,Orange,Banana', 'Carrot,Potato,Tomato,Cucumber', 'Milk,Cheese,Yogurt']}
df = pd.DataFrame(data)

# Split the 'Items_string' column by commas and create a new column 'Items_list'
df['Contains_list'] = df['Contains'].str.split(',')

# Display the DataFrame
print(df)

Output:

     Category                       Contains  \
0      Fruits            Apple,Orange,Banana   
1  Vegetables  Carrot,Potato,Tomato,Cucumber   
2       Dairy             Milk,Cheese,Yogurt   
                        Contains_list  
0             [Apple, Orange, Banana]  
1  [Carrot, Potato, Tomato, Cucumber]  
2              [Milk, Cheese, Yogurt]

In the above example, we have imported the pandas library.

• The script begins by importing the Pandas library as ‘pd’, enabling the utilization of Pandas functionalities.
• A DataFrame named ‘df’ is instantiated using a dictionary ‘data’, containing ‘Category’ and ‘Contains’ columns.
• The comma-separated strings in the ‘Contains’ column are split into lists using the str.split(',') method
• A new column ‘Contains_list’ is appended to the DataFrame, storing the resultant lists from the string splitting process.
• The DataFrame ‘df’ is printed, showcasing the original columns alongside the newly created ‘Contains_list’, aiding in data visualization and interpretation.

Using str.split() with the expand

We will again create a dataframe and use “expand=True” parameter.

import pandas as pd

# Example DataFrame
data = {'Category': ['Fruits', 'Vegetables', 'Dairy'],
        'Contains': ['Apple,Orange,Banana', 'Carrot,Potato,Tomato,Cucumber', 'Milk,Cheese,Yogurt']}
df = pd.DataFrame(data)

# Split the 'Contains' column by commas and expand it into separate columns
df[['Item1', 'Item2', 'Item3', 'Item4']] = df['Contains'].str.split(',', expand=True)

# Display the modified DataFrame
print(df)

Output:

     Category                       Contains   Item1   Item2   Item3     Item4
0      Fruits            Apple,Orange,Banana   Apple  Orange  Banana      None
1  Vegetables  Carrot,Potato,Tomato,Cucumber  Carrot  Potato  Tomato  Cucumber
2       Dairy             Milk,Cheese,Yogurt    Milk  Cheese  Yogurt      None

• The str.split(',', expand=True) method splits each element of the ‘Contains’ column by commas and expands the result into separate columns.
• Since the maximum number of items after splitting is 4 (in the second row), we create 4 new columns (‘Item1’, ‘Item2’, ‘Item3’, ‘Item4’) to accommodate the split values.
• The resulting DataFrame shows each item from the original comma-separated string in its respective column. Any missing values are filled with None.

How To Break Up A Comma Separated String In Pandas Column – FAQs

How to Split a String Separated by Comma in Python?

To split a string separated by commas, you can use the split method. Here’s an example:

# Sample string
string = "apple,banana,cherry"

# Split string by comma
split_list = string.split(',')

print(split_list)

How to Split String in Pandas Column?

To split a string in a Pandas column into multiple columns, you can use the str.split method combined with the expand=True parameter. Here’s an example:

import pandas as pd

# Sample DataFrame
data = {'fruits': ['apple,banana,cherry', 'grape,orange,lemon']}
df = pd.DataFrame(data)

# Split column into multiple columns
df[['fruit1', 'fruit2', 'fruit3']] = df['fruits'].str.split(',', expand=True)

print(df)

How to Remove Commas from a String in Pandas?

To remove commas from a string in a Pandas DataFrame, you can use the str.replace method. Here’s an example:

import pandas as pd

# Sample DataFrame
data = {'numbers': ['1,234', '5,678', '9,012']}
df = pd.DataFrame(data)

# Remove commas from the string
df['numbers_no_commas'] = df['numbers'].str.replace(',', '')

print(df)

How to Trim a String Column in Pandas?

To trim leading and trailing whitespace from a string column in a Pandas DataFrame, you can use the str.strip method. Here’s an example:

import pandas as pd

# Sample DataFrame
data = {'names': ['  Alice  ', '  Bob', 'Charlie  ']}
df = pd.DataFrame(data)

# Trim whitespace from the string
df['names_trimmed'] = df['names'].str.strip()

print(df)

How to Extract Part of String in Pandas?

To extract a part of a string in a Pandas DataFrame, you can use the str.slice method or regular expressions with the str.extract method. Here’s an example using both methods:

Using str.slice:

import pandas as pd

# Sample DataFrame
data = {'text': ['abcdef', 'ghijkl', 'mnopqr']}
df = pd.DataFrame(data)

# Extract part of the string (first 3 characters)
df['extracted'] = df['text'].str.slice(0, 3)

print(df)

Using Regular Expressions with str.extract:

import pandas as pd

# Sample DataFrame
data = {'text': ['abc123', 'def456', 'ghi789']}
df = pd.DataFrame(data)

# Extract part of the string using regex (digits)
df['digits'] = df['text'].str.extract(r'(\d+)')

print(df)