can not separate csv file with thousands and comma

Question

I need to read csv file with comma and also string and numbers but number contains comma in it like 1,260. Also csv file is seperated by comma so i can not read file in right way. How could i seperate them?

import pandas as pd
df_customer_list=pd.read_csv("Customer_list 09.01.2024.csv",sep=',')

the file contains below 3 rows

angel melo,[email protected],"1,260",Yes,0
michael alem,[email protected],60,Yes,0
charles ekk,[email protected],"2,220",Yes,0

Answer 1

I think the core issue is that your data does not appear to have a header so that makes display of the dataframe a little wonky.

Taking your example data, I seem to be able to load it ok by just specifying the thousands separator and no headers.

import io
import pandas

data = """
angel melo,[email protected],"1,260",Yes,0
michael alem,[email protected],60,Yes,0
charles ekk,[email protected],"2,220",Yes,0
"""

df = pandas.read_csv(io.StringIO(data), thousands=",", header=None)
print(df)

Should produce:

              0                      1     2    3  4
0    angel melo  [email protected]  1260  Yes  0
1  michael alem   [email protected]    60  Yes  0
2   charles ekk    [email protected]  2220  Yes  0

Answer 2

import pandas as pd

# Specify the quote character to ensure proper parsing of fields with commas
df_customer_list = pd.read_csv("Customer_list 09.01.2024.csv", sep=',', quotechar='"')

# Optionally, convert the columns with numbers to integers or floats, if needed
# This step is required if you want to perform numerical operations on these fields
df_customer_list['number_column'] = df_customer_list['number_column'].str.replace(',', '').astype(int)

# Display the DataFrame
print(df_customer_list)

If your numbers are floating-point values, use astype(float) instead.

Answer 3

import pandas as pd
your_data = pd.read_csv("yourfile.csv", sep=",", thousands=',', decimal='.')
print(your_data)