How to split a text column into two separate columns in pandas

In Python, the pandas library includes built-in functionalities that allow the performance of different tasks with only a few lines of code. One of these functionalities is to split a text column into two separate columns.

Representation

Input

  • ID, Name, City, Country
  • 21, Ali, Islamabad, Pakistan
  • 23, Ahmad, Karachi, Pakistan

Output

ID

Name

City

Country

21

Ali

Islamabad

Pakistan

23

Ahmad

Karachi

Pakistan

Method

We first initialize a pandas dataframe. Then we split the dataframe according to a delimeter, which in this case is comma (,). We consider the first row of the split output as header and the rest as data.

Code

#importing pandas library
import pandas as pd
#initializing pandas input dataframe
df = pd.DataFrame(["ID, Name, City, Country",
"21, Ali, Islamabad, Pakistan",
"22, Usman, Multan, Pakistan",
"23, Ahmad,  Karachi, Pakistan",
"24, Arslan, Lahore, Pakistan"], 
columns=['row'])
#splitting rows based on commas
result = df.row.str.split(',', expand=True)
#making first row as header of the data
header = result.iloc[0]
result = result[1:]
result.columns = header
print(result)

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)