Unable to separate csv file with thousands and commas

Question content

I need to read a csv file with commas along with strings and numbers, but the numbers contain commas, for example 1,260. Also, the csv file is comma delimited so I cannot read the file in the correct way. How can I separate them?

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

The file contains the following 3 lines

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

Correct answer

I think the core problem is that your data doesn't seem to have headers, so the display of the data frame is a bit wonky.

Taking your sample data, I seem to be able to load it fine just by specifying the thousands separator and not specifying a header.

import io
import pandas

data = """
angel melo,<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="cdaca3aaa8a1a0a8a1a2f8fb8daaa0aca4a1e3aea2a0">[email&#160;protected]</a>,"1,260",yes,0
michael alem,<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="55383c363d343930393a3a153238343c397b363a38">[email&#160;protected]</a>,60,yes,0
charles ekk,<a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="6a09020b18060f195f5c2a0d070b030644090507">[email&#160;protected]</a>,"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  <a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="74151a1311181911181b4142341319151d185a171b19">[email&#160;protected]</a>  1260  Yes  0
1  michael alem   <a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="1a777379727b767f7675755a7d777b737634797577">[email&#160;protected]</a>    60  Yes  0
2   charles ekk    <a href="https://www.php.cn/link/89fee0513b6668e555959f5dc23238e9" class="__cf_email__" data-cfemail="6e0d060f1c020b1d5b582e09030f0702400d0103">[email&#160;protected]</a>  2220  Yes  0

The above is the detailed content of Unable to separate csv file with thousands and commas. For more information, please follow other related articles on the PHP Chinese website!