读取多个 csv 文件并将文件名添加为 pandas 中的新列

2022-01-11 00:00:00 python operating-system pandas csv glob

问题描述

我在一个文件夹中有多个 csv 文件，我想在一个数据框中将它们全部打开并插入一个具有相关文件名的新列.到目前为止，我已经编写了以下代码:

I have several csv files in a single folder and I want to open them all in one dataframe and insert a new column with the associated filename. So far I've coded the following:

import pandas as pd
import glob, os
df = pd.concat(map(pd.read_csv, glob.glob(os.path.join('path/*.csv'))))
df['filename']= os.path.basename(csv)
df

这给了我想要的数据框，但在新列文件名"中，它只列出了文件夹中每一行的最后一个文件名.我正在寻找每一行都填充它的关联 csv 文件.不仅仅是文件夹中的最后一个文件.

This gives me the dataframe I want but in the new column 'filename' it's only listing the last filename in the folder for every row. I'm looking for each row to be populated with it's associated csv file. Not just the last file in the folder.

非常感谢对这个新手的任何帮助.

Any assistance for this newbie is much appreciated.

解决方案

我觉得你需要assign 用于在 loop 中添加新列，参数 ignore_index=True 也被添加到 concat 用于删除重复项索引:

I think you need assign for add new column in loop, also parameter ignore_index=True was added to concat for remove duplicates in index:

测试文件是 a.csv, b.csv, c.csv.

Files for test are a.csv, b.csv, c.csv.

import pandas as pd
import glob, os


files = glob.glob('samples_for_so/*.csv')
print (files)
#['samples_for_so\a.csv', 'samples_for_so\b.csv', 'samples_for_so\c.csv']


df = pd.concat([pd.read_csv(fp).assign(New=os.path.basename(fp)) for fp in files])
print (df)
   a  b  c  d    New
0  0  1  2  5  a.csv
1  1  5  8  3  a.csv
0  0  9  6  5  b.csv
1  1  6  4  2  b.csv
0  0  7  1  7  c.csv
1  1  3  2  6  c.csv


files = glob.glob('samples_for_so/*.csv')
df = pd.concat([pd.read_csv(fp).assign(New=os.path.basename(fp).split('.')[0]) 
       for fp in files])
print (df)
   a  b  c  d New
0  0  1  2  5   a
1  1  5  8  3   a
2  0  9  6  5   b
3  1  6  4  2   b
4  0  7  1  7   c
5  1  3  2  6   c

相关文章