CODEWITHSUNDEEP
pythonpandas
Satyabrat Sahoo
Satyabrat Sahoo

Reputation: 33

Pandas Function to Split multi-line text column into multiple columns

I have a column (stud_info) in the below format

stud_info = """Name: Mark
Address: 
PHX, AZ
Hobbies: 
1. Football
2. Programming
3. Squash"""

source data

The column (stud_info) from raw data is stud_info which contains data as multiline text. I need to split it into 3 columns (Name, Address, and Hobbies). For a simple split, we can do it via lambda functions but this is a multiline split and the column names are also a part of the data. (i.e. the text Name, Address, and Hobbies should not be a part of the columns). The final columns should look like

final data

Please suggest a way to do it using pandas.

Upvotes: 3

Views: 330

Answers (2)

Shahab Rahnama
Shahab Rahnama

Reputation: 1012

My solution:

import pandas as pd 
import re

txt = """Name: Mark
Address: 
PHX, AZ
Hobbies: 
1. Football
2. Programming
3. Squash"""

#[\w\W]* select all chars after Hobbies
pattern = re.compile('Name:\s(.+)\nAddress:\s\n(.+)\nHobbies:\s\n([\w\W]*)')

re_match = pattern.match(txt)
df = pd.DataFrame([list(re_match.groups())], columns=['Name', 'Address', 'Hobbies'])
df

Output:

enter image description here

Upvotes: 2

BeRT2me
BeRT2me

Reputation: 13242

Given:

df = pd.DataFrame({'stud_info': {0: 'Name: Mark\nAddress: \nPHX, AZ\nHobbies: \n1. Football\n2. Programming\n3. Squash'}})

We can define a Regex Expression for your particular formatting, and use the pd.Series.str.extract method to break the groups into different columns. For an explanation of the pattern see Regexr.

import re

pattern = 'Name:\s(.+)\nAddress:\s\n(.+)\nHobbies:\s\n(.+)'
# We need flags=re.DOTALL to allow the final group to encompass multiple lines.
df[['Name', 'Address', 'Hobbies']] = df.stud_info.str.extract(pattern, flags=re.DOTALL)
print(df[['Name', 'Address', 'Hobbies']])

Output:

   Name  Address                                 Hobbies
0  Mark  PHX, AZ  1. Football\n2. Programming\n3. Squash

Upvotes: 4

Related Questions