python - 忽略 pandas csv 中的引号

Question

我有一个像这样的 CSV:

A  B  C  D                     E   F  G
-- -- -- --------------------- --- -- --
G1 M1 C1 "2015-01-01 00:00:00" SR1 E1 N1
G1 M1 C1 "2015-01-01 00:00:00" SR1 E1 N2
G1 M1 C1 "2015-01-01 00:00:00" SR1 E1 N3
G2 M2 C1 "2015-01-01 00:00:00" SR1 E1 N1
G2 M2 C1 "1/1/2015 00:00:00" SR1 E1 N2
G2 M2 C1 "1/1/2015 00:00:00" SR1 E1 N3

我需要将其读入 pandas df 并忽略 D 列中的引号，以便我可以将其解析为日期时间列。我尝试执行以下操作:

df = pd.read_csv(
        infile,
        sep=r"\s*(?![0-9][0-9]:)",
        skiprows=[1],
        header=0,
        quoting=csv.QUOTE_NONE
    )

但是生成的 df 中仍然有引号:

>>> df
    A   B   C                      D    E   F   G
0  G1  M1  C1  "2015-01-01 00:00:00"  SR1  E1  N1
1  G1  M1  C1  "2015-01-01 00:00:00"  SR1  E1  N2
2  G1  M1  C1  "2015-01-01 00:00:00"  SR1  E1  N3
3  G2  M2  C1  "2015-01-01 00:00:00"  SR1  E1  N1
4  G2  M2  C1    "1/1/2015 00:00:00"  SR1  E1  N2
5  G2  M2  C1    "1/1/2015 00:00:00"  SR1  E1  N3

如果我尝试直接将 D 列解析为日期时间列，pandas 会中断:

>>> pd.to_datetime(df.D)
...
ValueError: Unknown string format

如何将 D 列格式化为 pandas 可以将其解析为日期列？

Pandas 版本:0.19.2

Answer 1

演示:

In [44]: df = pd.read_csv(r'D:\download\1.csv', delim_whitespace=True, skiprows=[1], 
                          parse_dates=['D'])

In [45]: df
Out[45]:
    A   B   C          D    E   F   G
0  G1  M1  C1 2015-01-01  SR1  E1  N1
1  G1  M1  C1 2015-01-01  SR1  E1  N2
2  G1  M1  C1 2015-01-01  SR1  E1  N3
3  G2  M2  C1 2015-01-01  SR1  E1  N1
4  G2  M2  C1 2015-01-01  SR1  E1  N2
5  G2  M2  C1 2015-01-01  SR1  E1  N3

In [46]: df.dtypes
Out[46]:
A            object
B            object
C            object
D    datetime64[ns]
E            object
F            object
G            object
dtype: object

其中D:\download\1.csv:

A  B  C  D                     E   F  G
-- -- -- --------------------- --- -- --
G1 M1 C1 "2015-01-01 00:00:00" SR1 E1 N1
G1 M1 C1 "2015-01-01 00:00:00" SR1 E1 N2
G1 M1 C1 "2015-01-01 00:00:00" SR1 E1 N3
G2 M2 C1 "2015-01-01 00:00:00" SR1 E1 N1
G2 M2 C1 "1/1/2015 00:00:00" SR1 E1 N2
G2 M2 C1 "1/1/2015 00:00:00" SR1 E1 N3