合并csv文件时将文件名添加为标头

Question

我想将所有csv合并到一个文件夹中。这可以达到预期的效果。

import os
import glob
import pandas as pd

extension = 'csv'
all_filenames = [i for i in glob.glob('*.{}'.format(extension))]

#combine all files in the list
combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames], axis = 1)
#export to csv
combined_csv.to_csv( "combined.matrix", index=False)

但是，我想添加不带扩展名的文件名作为头文件。

File1.csv

A,B
1,2
3,4

File2.csv

A,B
5,6

combined.matrix

File1,File1,File2,File2
A,B,A,B
1,2,5,6
3,4,,

Answer 1

试试下面的代码：

import pandas as pd

all_filenames = ['File1.csv','File2.csv']
headers = []
for i in all_filenames:
    headers.append(i.replace('.csv', ''))

combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames], keys=headers, axis = 1)

已创建不包含扩展名的文件名的标题列表。将列表传递给pd.concat函数中的键参数。

Answer 2

import os
import pandas as pd

parent_dir = 'YOUR_PARENT_DIRECTORY_PATH'
ext = 'csv'

combined_csv = pd.DataFrame()

for root, dir, files in os.walk(parent_dir):
    for f in files:
        path = os.path.join(root, f)
        filename, extension = os.path.splitext(f)
        if extension == f'.{ext}':
            new_df = pd.read_csv(path)
            cols = new_df.columns
            new_cols = []
            for c in cols:
                new_cols.append(f'{filename}{c}')
            new_df.columns = new_cols
            combined_csv = pd.concat([combined_csv, new_df], axis=1)

combined_csv.to_csv( "combined.matrix", index=False)

Answer 3

基本的想法是，您可以将文件名包含在DataFrame本身的某个位置(在本例中，我将其包含在列名中，您也可以将它们包含在一行中)，因为您无论如何都要将其导出到csv中进行进一步处理

all_filenames = [i for i in glob.glob('*.{}'.format(extension))]
# This takes the value ["file1.csv", "file2.csv"]

#combine all files in the list
combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames], axis = 1)
# This looks like
# A B A B
# 1 2 5 6
# 3 4 nan nan

由于列名是固定的(A和B)，并且您对文件名更感兴趣，因此可以使用以下命令更改列

combined_csv.columns = sorted(all_filenames * len(combined_csv.columns) / len(all_filenames))
# This evaluates to sorted(["file1.csv", "file2.csv"] * 4 / 2) which is equal to ["file1.csv", "file1.csv", "file2.csv", "file2.csv"]

现在，您的数据帧将如下所示-它指示哪一列来自哪个文件

# file1.csv file1.csv file2.csv file2.csv
# 1 2 5 6
# 3 4 nan nan

您可以将其导出到combined.matrix.csv