How to read dirty csv file in Python?

user_12064858 0
Viewed 84

I have a very dirty data that I need to read in Python. The csv file seperated with comma, but there are also some commas in the first column of my data that puts me in a tight position. Something like this:

import pandas as pd
 
# initialize data of lists.
data = {'reportname':['column1,column2, column3, column4', 
                      'some...texts, are here with commas, www.facebook.com, 2000, 150', 
                      'some-texts_again_here, www.twitter.com, 1000, 50']}
 
# Create DataFrame
df = pd.DataFrame(data)

However, it does not match the data in correct columns since there is comma inside some of the rows of the first column. Here is my desired data:

desired_data = {'column1':['some...texts, are here with commas', 'some-texts_again_here'],
        'column2':['www.facebook.com', 'www.twitter.com'],
        'column3':[2000, 1000],
        'column4':[150, 50]}
 
# Create DataFrame
df = pd.DataFrame(desired_data)

Is there a way to read the csv file accurately?

pandas python
1 Answers

You can preprocess your file before handing it off to pandas.

with open('yourfile.csv', 'r') as f:
    data = [line.strip().rsplit(',', number_of_columns - 1) for line in f]

# if your csv has column names on the first row
new_df = pd.DataFrame(columns=data[0], data=data[1:])

This solution only works if the only extra commas are in the first column of data.

user_13224010
Related