Using Comma separator on CSV file when reading into Python - not working for all rows

Question

I have a CSV file in the following format (these are the first 3 rows):

A,B,P,S,P,B,BA,DE,PREM,DISC,DISC_P,DISC_T,DISC_F
2021/05/31,2012,"10","S","","Dis","DI","EX,,0.00,,"Pt",0,
2021/05/31,2109,"10","S","","Dis","DI","EX",0.00,,"tt",0,

I want to read it into a dataframe on Python and this is my code:

df= pd.read_csv (r'C:\file.csv',sep=",")

df.head()

When I run this code, it only splits the first row into separate columns accordingly. However, the rest of the rows are just populated into 1 column and are not being separated and grouped under the headings accordingly.

What might the issue be?

Most likely the CSV file is ill-formated.

Kota Mori
– Kota Mori

2021-08-19 11:06:29 +00:00
Commented Aug 19, 2021 at 11:06 — Kota Mori
– Kota Mori, Commented Aug 19, 2021 at 11:06

Mahdi F. · Accepted Answer · 2021-08-19 11:16:51Z

1

maybe in your CSV file you don't close " or have more or less ,.

now in your CSV file I find in line one you don't close "EX.

I edit your csv file like below:

A,B,P,S,P,B,BA,DE,PREM,DISC,DISC_P,DISC_T,DISC_F
2021/05/31,2012,"10","S","","Dis","DI","EX",0.00,,"Pt",0,
2021/05/31,2109,"10","S","","Dis","DI","EX",0.00,,"tt",0,

run this code:

import csv
with open('file.csv', 'r') as file:
    reader = csv.reader(file)
    for row in reader:
        print(row)
        
df= pd.read_csv(r'file.csv',sep=",")

df.head()

got below output:

answered Aug 19, 2021 at 11:16

Mahdi F.

24.1k5 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Comma separator on CSV file when reading into Python - not working for all rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related