使用Pandas递归编辑CSV到子目录

Question

/Invoice List/
              Invoice1folder/
                             ..._Invoice.csv
              Invoice2folder/ 
                             ..._Invoice.csv
              Invoice5folder/
                             ..._Invoice.csv
              Invoice9folder/
                             _Invoice.csv

"_Invoice.csv"       "_Invoice_Reduced.csv"
 A B C D        =>              A C
 1 2 3 4        =>              1 3 

import pandas as pd
import os

columns_to_keep = ['A','C']
final_form= pd.DataFrame()

for file in os.listdir():
    if file.endswith('*_Invoice.csv'):
        df = pd.read_csv(file)
        df = df.loc[;columns_to_keep]
        df = df.to_csv(f'{file.name}_Invoice_Reduced.csv')
   if file.endswith('*_Invoice_Reduced.csv'):
        df = pd.read_csv(file)
        final_form= final_form.append(df, ignore_index=True)

有什么想法吗？

Answer 1

这就可以完成任务了。

而不是打开，移除列，保存并继续移动；我选择只打开缩减的列，保存这个缩减的数据表，然后追加到df。这将导致所有缩减的文件都堆叠在这个数据目录中。

使用path=“.”从当前目录

from pathlib import Path
import pandas as pd


df = pd.DataFrame()
columns_to_keep = ['A','C']
path = "."
pattern = "*_Invoice.csv"

for file in Path(path).rglob(pattern):
    output_file = "{}/{}{}".format(file.parent, file.stem, "_Reduced.csv")
    _df = pd.read_csv(file, usecols=columns_to_keep)
    _df.to_csv(output_file, sep=",", index=False, header=True)
    df = pd.concat([df, _df])