基于列值将CSV拆分为多个文件

slsn1g29  于 2023-01-28  发布在  其他
关注(0)|答案(3)|浏览(158)

我有一个结构不良的CSV文件,名为file.csv,我想使用Python将其拆分为多个CSV。

|A|B|C|
|Continent||1|
|Family|44950|file1|
|Species|44950|12|
|Habitat||4|
|Species|44950|22|
|Condition|Tue Jan 24 00:00:00 UTC 2023|4|
|Family|Fish|file2|
|Species|Bass|8|
|Species|Trout|2|
|Habitat|River|3|

新文件需要根据Family行之间的所有内容进行分隔,例如:
file1.csv

|A|B|C|
|Continent||1|
|Family|44950|file1|
|Species|44950|12|
|Habitat||4|
|Species|44950|22|
|Condition|Tue Jan 24 00:00:00 UTC 2023|4|

file2.csv

|A|B|C|
|Continent||1|
|Family|Fish|file2|
|Species|Bass|8|
|Species|Trout|2|
|Habitat|River|3|

Species出现之间的行数不一致时,实现这一点的最佳方法是什么?

xghobddn

xghobddn1#

如果你的档案真的是这样),则可以使用标准库模块itertools中的groupby

from itertools import groupby

def key(line): return line.startswith("|Family|")

family_line, file_no = None, 0
with open("file.csv", "r") as fin:
    for is_family_line, lines in groupby(fin, key=key):
        if is_family_line:
            family_line = list(lines).pop()
        elif family_line is None:
            header = "".join(lines)
        else:
            file_no += 1
            with open(f"file{file_no}.csv", "w") as fout:
                fout.write(header + family_line)
                for line in lines:
                    fout.write(line)

Pandas的解决方案是:

import pandas as pd

df = pd.read_csv("file.csv", header=None, delimiter="|").fillna("")
blocks = df.iloc[:, 1].eq("Family").cumsum()
header_df = df[blocks.eq(0)]
for no, sdf in df.groupby(blocks):
    if no > 0:
        sdf = pd.concat([header_df, sdf])
        sdf.to_csv(f"file{no}.csv", index=False, header=False, sep="|")
nuypyhwy

nuypyhwy2#

import pandas as pd
pd.read_csv('file.csv',delimiter='|')
groups = df.groupby('Family')
for name, group in groups:
    group.to_csv(name + '.csv', index=False)
brtdzjyr

brtdzjyr3#

下面是一个纯python的工作方法:

# Read file
with open('file.csv', 'r') as file:
    text = file.read()

# Split using |Family|
splitted_text = text.split("|Family|")

# Remove unwanted content before first |Family|
splitted_text = splitted_text[1:]

# Add |Family| back to each part
splitted_text = ['|Family|' + item for item in splitted_text]

# Write files
for i, content in enumerate(splitted_text ):
    with open('file{}.csv'.format(i), 'w') as file:
        file.write(content)

相关问题