python - 来自 DictReader 的 For 循环未迭代所有字段名

Question

我正在尝试将CSV 看起来像文件的 csv 加载到字典中，将 excel 日期转换为 ISO 时间戳并根据标题检索值。我没有收到错误，但我的 for 循环仅解析第一个标头。在这篇文章的最后，你会找到一个你需要的数据样本。

import csv
from openpyxl.utils.datetime import from_excel, to_ISO8601

#Capturing the headers in a list
with open("file.csv", "r") as f:
    reader = csv.reader(f)
    headers = next(reader)

input_file = csv.DictReader(open("file.csv"))
for i in range(4,9):
    print(headers[i])
    for row in vehicle_list:
        x = to_ISO8601(dt=from_excel(value = float(row[str(headers[i])])))
        print(x)

结果是：

Engineon
2019-12-16T01:57:08Z
2019-12-16T02:08:42Z
2019-12-16T02:31:44Z
2019-12-16T02:45:41Z
2019-12-16T02:45:41Z
2019-12-16T03:02:57Z
2019-12-16T03:02:57Z
2019-12-16T03:31:37Z
2019-12-16T17:31:05Z
2019-12-16T17:54:54Z
2019-12-16T17:54:54Z
2019-12-16T18:23:04Z
2019-12-16T18:23:04Z
2019-12-16T19:03:48Z
2019-12-16T19:18:13Z
2019-12-16T20:02:56Z
2019-12-16T20:34:46Z
2019-12-16T20:51:35Z
2019-12-16T21:00:01Z
2019-12-16T21:15:06Z
2019-12-16T21:15:06Z
2019-12-16T21:55:33Z
2019-12-16T22:12:13Z
Engineoff
WorkStart
WorkEnd
ParkStart

所以基本上它只为“Engineon”标题运行，而不是为其余的

样本数据：

vehicle_list = [('85807835', [{'ID': '85807835', 'Vhcl': 'V001', 'Mode': 'Manual', 'CID': 'AAA5846129341',
'Engineon': '43815.08135','Engineoff': '43815.08938', 'WorkStart': '', 'WorkEnd': '', 'ParkStart': '43815.07334',
'ParkEnd': '43815.08211'}]), ('85809668', [{'ID': '85809668', 'Vhcl': 'V002', 'Mode': 'Auto',
'CID': 'AAA8022407504', 'Engineon': '43815.08938', 'Engineoff': '43815.10535', 'WorkStart': '43815.08938',
'WorkEnd': '43815.10535', 'ParkStart': '43815.08938', 'ParkEnd': '43815.09535'}]), ('85810976', [{'ID': '85810976',
'Vhcl': 'V003', 'Mode': 'Auto', 'CID': 'AAA0571518764', 'Engineon': '43815.10538', 'Engineoff': '43815.11505',
'WorkStart': '43815.10538', 'WorkEnd': '43815.11505', 'ParkStart': '43815.10959', 'ParkEnd': '43815.11505'}]),
 ('85813025', [{'ID': '85813025', 'Vhcl': 'V004', 'Mode': 'Manual', 'CID': 'AAA3189634914', 'Engineon': '43815.11506',
'Engineoff': '43815.12703', 'WorkStart': '43815.11506', 'WorkEnd': '43815.12703', 'ParkStart': '43815.11506',
'ParkEnd': '43815.12303'}]), ('85813028', [{'ID': '85813028', 'Vhcl': 'V005', 'Mode': 'Manual', 'CID': 'AAA1940741282',
'Engineon': '43815.11506', 'Engineoff': '43815.12703', 'WorkStart': '', 'WorkEnd': '', 'ParkStart': '43815.11506',
'ParkEnd': '43815.12372'}]), ('85815305', [{'ID': '85815305', 'Vhcl': 'V006', 'Mode': 'Manual', 'CID': 'AAA1894455904',
'Engineon': '43815.12705', 'Engineoff': '43815.14505', 'WorkStart': '43815.12705', 'WorkEnd': '43815.14692',
'ParkStart': '43815.13366', 'ParkEnd': '43815.14505'}]), ('85815467', [{'ID': '85815467', 'Vhcl': 'V001',
'Mode': 'Auto', 'CID': 'AAA9538532026', 'Engineon': '43815.12705', 'Engineoff': '43815.14692', 'WorkStart': '',
'WorkEnd': '', 'ParkStart': '43815.13361', 'ParkEnd': '43815.14692'}]), ('85821410', [{'ID': '85821410',
'Vhcl': 'V002', 'Mode': 'Auto', 'CID': 'AAA8391952906', 'Engineon': '43815.14696', 'Engineoff': '43815.20984',
'WorkStart': '43815.14696', 'WorkEnd': '43815.20984', 'ParkStart': '43815.14696', 'ParkEnd': '43815.15206'}])]

ID,Vhcl,模式,CID,Engineon,Engineoff,WorkStart,WorkEnd,ParkStart,ParkEnd
85807835,V001,手册,AAA5846129341,43815.08135,43815.08938,,,43815.07334,43815.08211
85809668,V002,汽车,AAA8022407504,43815.08938,43815.10535,43815.08938,43815.10535,43815.08938,43815.09535
85810976,V003,汽车,AAA0571518764,43815.10538,43815.11505,43815.10538,43815.11505,43815.10959,43815.11505
85813025,V004,说明书,AAA3189634914,43815.11506,43815.12703,43815.11506,43815.12703,43815.11506,43815.12303
85813028,V005,手册,AAA1940741282,43815.11506,43815.12703,,,43815.11506,43815.12372
85815305,V006,说明书,AAA1894455904,43815.12705,43815.14505,43815.12705,43815.14692,43815.13366,43815.14505
85815467,V001,汽车,AAA9538532026,43815.12705,43815.14692,,,43815.13361,43815.14692
85821410,V002,汽车,AAA8391952906,43815.14696,43815.20984,43815.14696,43815.20984,43815.14696,43815.15206
85873358,V003,说明书,AAA4922964611,43815.72992,43815.74645,43815.72992,43815.74645,43815.73586,43815.74645
85875020,V004,手册,AAA6039158858,43815.74646,43815.76461,43815.74646,43815.76601,43815.75975,43815.76461
85875137,V005,手册,AAA7495366053,43815.74646,43815.76601,,,43815.75972,43815.76601
85877825,V006,汽车,AAA7638509608,43815.76602,43815.79272,43815.76602,43815.79429,43815.76602,43815.77079
85877942,V001,汽车,AAA1265572219,43815.76602,43815.79429,,,43815.76602,43815.76985
85879040,V002,手册,AAA2968711840,43815.79431,43815.80431,43815.79431,43815.80431,43815.79431,43815.80127
85882028,V003,手册,AAA7692514875,43815.80432,43815.83537,43815.80432,43815.83537,43815.80432,43815.82109
85884230,V004,说明书,AAA4674654439,43815.83538,43815.85745,43815.83538,43815.85745,43815.83538,43815.84685
85885460,V005,汽车,AAA8107186366,43815.85748,43815.86895,43815.85748,43815.86895,43815.86016,43815.86895
85885994,V006,汽车,AAA5796012701,43815.86916,43815.875,43815.86916,43815.875,43815.87164,43815.875
85886981,V001,手册,AAA8719492664,43815.87502,43815.88547,43815.87502,43815.88547,43815.8795,43815.88547
85890116,V002,手册,AAA2865936367,43815.88549,43815.91355,43815.88549,43815.91355,43815.89722,43815.9073
85890119,V003,手册,AAA1887612592,43815.88549,43815.91355,,,43815.88549,43815.8972
85891310,V004,汽车,AAA1144467605,43815.91358,43815.92514,43815.91358,43815.92514,43815.91856,43815.92514
85892144,V005,汽车,AAA3719694551,43815.92516,43815.93397,43815.92516,43815.93523,43815.92922,43815.93397

标签： pythondatetimefor-loopopenpyxl

Answer 1

您的程序逻辑在这里是错误的：

for i in range(4,9):              # runs 5 times (works)
    print(headers[i])             # prints current header name (works)
    for row in vehicle_list:      # runs the generator vehicle_list to its end (works once!)
        ...

CSV 阅读器是一个生成器，而生成器是只进的。它将遍历文件一次，并且一旦完成就不会重新开始。对于第二列，将不会留下任何行，这解释了您在输出中看到的内容。

你必须反转你的循环：外循环for row in vehicle_list，内循环for i in range(4, 9)。这样 CSV 阅读器只需要循环一次，但您仍然可以遍历每一列。

这有点好（它像过滤器一样工作，在文件中替换 Excel 日期值）：

def read_vehicle_data(csv_filename):
    dt_cols = ["Engineon","Engineoff","WorkStart","WorkEnd","ParkStart","ParkEnd"]

    with open(csv_filename, encoding="utf8", newline="") as f:
        reader = csv.DictReader(f, delimiter=",")
        for row in reader:
            for col in dt_cols:
                row[col] = to_ISO8601(from_excel(float(row[col])))
            yield row

你可以这样称呼它：

for record in read_vehicle_data("file.csv"):
    print(record)

range(4, 9)也是一个生成器，所以它也只会前进。但是每行迭代都会创建一个新的 - 并且创建一个新range(4, 9)的比再次从头开始打开 ​​CSV 文件要便宜得多，如果您保留为外部循环，这将是必要的range(4, 9)。