Pandas读取CSV文件解析失败，求大数据友好型解决方案

数据片段 #

gateway_din;charger_vin;bucket;bucket_dt;post;operative_status;alert_codes;alert_codes_cabinet
STST-SM-12964;GF2191430006CD;1682503200000;"2023-04-26 10:00:00";2;Operative;;
STST-SM-13783;GF2193050000CM;1682199000000;"2023-04-22 21:30:00";1;Operative;;
STST-SM-2978;GF2200050000W5;1681243200000;"2023-04-11 20:00:00";2;Operative;;
STST-SM-3227;GF2200190001EC;1680750900000;"2023-04-06 03:15:00";1;Operative;;
STST-SM-3184;GF22002500014D;1682155800000;"2023-04-22 09:30:00";0;Operative;;

使用Pandas读取CSV的尝试 #

import pandas as pd
import pyparsing as pp
import csv

Test = pd.read_csv("C:/.../Test.csv", delimiter=';', header=0)

终端输出 #

Test
                                         gateway_din  ...  alert_codes_cabinet
0  STST-SM-12964;GF2191430006CD;1682503200000;"20...  ...                  NaN
1  STST-SM-13783;GF2193050000CM;1682199000000;"20...  ...                  NaN
2  STST-SM-2978;GF2200050000W5;1681243200000;"202...  ...                  NaN
3  STST-SM-3227;GF2200190001EC;1680750900000;"202...  ...                  NaN
4  STST-SM-3184;GF22002500014D;1682155800000;"202...  ...                  NaN

[5 rows x 8 columns]

Test.iat[0,0]
'STST-SM-12964;GF2191430006CD;1682503200000;"2023-04-26 10:00:00";2;Operative;;'

Test.iat[0,1]
nan

使用PyParse解析第一列字符串的尝试 #

ParseTest = pp.DelimitedList(';').parse_string(Test.iat[0,0])

报错信息 #

Traceback (most recent call last):
  File "<pyshell#7>", line 1, in <module>
    ParseTest = pp.DelimitedList(';').parse_string(Test.iat[0,0])
  File "C:\Users\dschell\AppData\Roaming\Python\Python311\site-packages\pyparsing\core.py", line 1190, in parse_string
    raise exc.with_traceback(None)
pyparsing.exceptions.ParseException: , found 'STST'  (at char 0), (line:1, col:1)

使用CSV模块读取CSV的尝试 #

with open('C:/Users/dschell/Desktop/Reliability and Usability/Survey Data/Test.csv', newline='') as csvfile:
    spamreader = csv.reader(csvfile, delimiter=';', quotechar='"')
    for row in spamreader:
        print(', '.join(row))

终端输出 #

gateway_din, charger_vin, bucket, bucket_dt, post, operative_status, alert_codes, alert_codes_cabinet
STST-SM-12964;GF2191430006CD;1682503200000;"2023-04-26 10:00:00";2;Operative;;
STST-SM-13783;GF2193050000CM;1682199000000;"2023-04-22 21:30:00";1;Operative;;
STST-SM-2978;GF2200050000W5;1681243200000;"2023-04-11 20:00:00";2;Operative;;
STST-SM-3227;GF2200190001EC;1680750900000;"2023-04-06 03:15:00";1;Operative;;
STST-SM-3184;GF22002500014D;1682155800000;"2023-04-22 09:30:00";0;Operative;;

内容的提问来源于stack exchange，提问作者chemeee