Python pandas separate dataframe by commas, except commas inside of quotations

scrubcoder 0
Viewed 37

I have a very large csv file (2+ million rows) which is separated by commas. Except there are a few entries such as Company Name and a value field where fields can contain commas inside quotations.

E.g:

product1,"23-12-2021 21:37:22.567",50,18.32,"Company, A"

product1,"23-12-2021 21:37:24.237","1,000",17.34,"Company, C"

Note that when the value field after the datetime is >= 1000 it adds the comma and quotations. As a result of the above when I try:

pd.read_csv(r'C:\example_file.csv', delimiter=",", quoting=csv.QUOTE_NONE, quotechar='"', encoding='utf-8')

It throws a pandas.errors.ParserError: Error tokenizing data. C error: Expected 24 fields in line x, saw 25

I've managed a workaround to get it into a dataframe using this:

import pandas as pd
import csv

file = pd.read_csv(r'C:\example_file.csv', delimiter="\t", quoting=csv.QUOTE_NONE, encoding='utf-8')
df = pd.Dataframe(file)

column_list = df_columns
column_string = str(column_list[0])
# column_list[0] returns index with 24 column names
column_string_split = column_string.split(",")

df.rename(columns{df.columns[0]: 'fill'}, inplace=True)

new_df = pd.Dataframe(data_df['fill'].str.split(',').tolist(), columns=column_string_split)

I understand what I've tried so far isnt optimal and ideally I would separate the data when I read it in but I'm at a loss on how to progress. I've been looking into regex expressions to exclude commas inside quotations (such as Python, split a string at commas, except within quotes, ignoring whitespace) but nothing as worked. Any ideas on how to str.split commas except within quotes?

string dataframe pandas python
1 Answers

Considering the .csv you gave :

product1,"23-12-2021 21:37:22.567",50,18.32,"Company, A"
product1,"23-12-2021 21:37:24.237","1,000",17.34,"Company, C"

First, as explained in the comments by @Chris and @Quang Hoang, a standard pandas.read_csv will ignore the comma inside the quotes.

Second, you can pass the thousands parameter to pandas.read_csv to get rid of the comma for all the numbers > 999.

Try this :

df = pd.read_csv(r'C:\example_file.csv', encoding='utf-8', thousands=",")
abokey0
Related