How to read CSV in pandas when one column is pipe delimted?

user_13127469 0
Viewed 94

Hi I have a CSV structured like this

ColA          ColB
entry1    A|B|C|D|E|F

I want to read the file in pandas using the read_csv method I need the output to be

ColA    ColB1 colB2 colB3 ...
entry1    A     B     C   ...

What is the cleanest way to achieve this?

pandas dataframe csv python-3.x python
1 Answers

Let's say test.csv is:

ColA;ColB
entry1;A|B|C|D|E|F

Here is a solution:

import pandas as pd
df = pd.read_csv('test.csv', sep=';')
df[['ColB1','ColB2', 'ColB3', 'ColB4', 'ColB5', 'ColB6']] = df['ColB'].str.split("|",expand=True,)
df = df.drop(columns=['ColB'])
print(df)

Output:

     ColA ColB1 ColB2 ColB3 ColB4 ColB5 ColB6
0  entry1     A     B     C     D     E     F

Source: https://cmdlinetips.com/2018/11/how-to-split-a-text-column-in-pandas/

user_14220960
Related