CODEWITHSUNDEEP
pythonpandascsvseparator
rose
rose

Reputation: 1

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

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?

Upvotes: 0

Views: 1875

Answers (1)

I'mahdi
I'mahdi

Reputation: 24049

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:

enter image description here

Upvotes: 1

Related Questions