Csv -插入缺少的行

Question

我有一个很大的文件，其中有一些缺失的行。数据需要扎根于国家/地区。

输入数据如下：

csv_str = """Type,Country,State,County,City,
1,USA,,,
2,USA,OH,,
3,USA,OH,Franklin,
4,USA,OH,Franklin,Columbus
4,USA,OH,Franklin,Springfield
4,USA,WI,Dane,Madison
"""

它需要是：

csv_str = """Type,Country,State,County,City,
1,USA,,,
2,USA,OH,,
3,USA,OH,Franklin,
4,USA,OH,Franklin,Columbus
4,USA,OH,Franklin,Springfield
4,USA,WI,,
4,USA,WI,Dane,
4,USA,WI,Dane,Madison
"""

根据我的逻辑，关键字是Type字段，如果我找不到城市(类型4)的县(类型3)，那么插入一行字段，直到县。

县的情况也一样。如果我找不到某个县(类型3)的州(类型2)，则插入一行，其中的字段最高可达State。

由于我不了解python中的工具，我尝试了更多的暴力方法。这有点问题，因为我需要在同一个文件上进行大量迭代。

我也尝试过google-refine，但不能让它工作。手动操作很容易出错。

感谢您的帮助。

import csv
import io

csv_str = """Type,Country,State,County,City,
1,USA,,,
2,USA,OH,,
3,USA,OH,Franklin,
4,USA,OH,Franklin,Columbus
4,USA,OH,Franklin,Springfield
4,USA,WI,Dane,Madison
"""
found_county =[]
missing_county =[]

def check_missing_county(row):
    found = False
    for elm in found_county:
        if elm.Type == row.Type:
            found = True
    if not found:
        missing_county.append(row)
        print(row)

reader = csv.reader(io.StringIO(csv_str))
for row in reader:
    check_missing_county(row)

Answer 1

根据我对这个问题的理解，我总结出了以下几点：

import csv
import io

csv_str = u"""Type,Country,State,County,City,
1,USA,,,
2,USA,OH,,
3,USA,OH,Franklin,
4,USA,OH,Franklin,Columbus
4,USA,OH,Franklin,Springfield
4,USA,WI,Dane,Madison
"""

counties = []
states = []


def handle_missing_data(row):
    try:
        rtype = int(row[0])
    except ValueError:
        return []

    rtype = row[0]
    country = row[1]
    state = row[2]
    county = row[3]

    rows = []
    # if a state is present and it hasn't a row of it's own
    if state and state not in states:
        rows.append([rtype, country, state, '', ''])
        states.append(state)

    # if a county is present and it hasn't a row of it's own
    if county and county not in counties:
        rows.append([rtype, country, state, county, ''])
        counties.append(county)

    # if the row hasn't already been added add it now
    if row not in rows:
        rows.append(row)

    return rows

csvf = io.StringIO(csv_str)
reader = csv.reader(csvf)
for row in reader:
    new_rows = handle_missing_data(row)
    for new_row in new_rows:
        print new_row