CODEWITHSUNDEEP
pythonpandas
user_12
user_12

Reputation: 2129

How to read csv file in pandas as two column from multiple delimiter values

I've a csv file like this:

123, a, b, c, d
1433, b, c, d, e
2323, c, d, e, f
4543, d, e, f

I want to read this into dataframe but I want the first delimiter value as one column and rest as another column

id         values
123        a, b, c, d
1433       b, c, d, e
2323       c, d, e, f
4543       d, e, f, NaN

I tried to use pandas read_csv but i couldn't find a option such as maxsplit there. If anyone is familiar with how to do it do help me out.

Upvotes: 1

Views: 304

Answers (1)

sammywemmy
sammywemmy

Reputation: 28644

I put in a wrong delimiter in the read_csv function, which forces Pandas to read the data into one column, from there I split the column into the format I want. Note however, this does not trump Datanovice's solution, as the NaN is not introduced:

data = '''123, a, b, c, d
          1433, b, c, d, e
          2323, c, d, e, f
          4543, d, e, f'''

df = pd.read_csv(StringIO(data),sep=';', header= None, names=['string'])
df.string.str.split(pat=',', n=1,expand=True)

    0         1
0   123     a, b, c, d
1   1433    b, c, d, e
2   2323    c, d, e, f
3   4543    d, e, f

Upvotes: 2

Related Questions