根据 pandas 中的csv文件名重命名列 [英] Renaming columns based on csv filename in pandas

问题描述

鉴于我正在读取N个csv文件，并将它们合并到单个Pandas DataFrame中，例如:

Given that I'm reading N csv files and merging them into a single Pandas DataFrame like:

dfs = [pd.read_csv(f) for f in list_of_files]
df = pd.concat(dfs, axis=1)

如何重命名每个文件中的列，使其包含基于文件名的后缀?

How can I rename the columns from each file, so that they include a suffix based on the file name?

例如，如果文件f1和f2具有以下内容:

For example, if files f1 and f2 have the following contents:

f1:

A
1
2
3

f2:

B
4
5
6

然后按列的 concat 会产生:

A  B
1  4
2  5
3  6

...但是我想要:

A_f1  B_f2
   1     4
   2     5
   3     6

推荐答案

在使用 pd.concat 之前，您可以在df文件中添加后缀:

You can add suffixes to your df's before you use pd.concat:

lst_dfs = []

for file in list_of_files:
    df = pd.read_csv(file)
    df = df.add_suffix(f'_{file}')
    lst_dfs.append(df)

df_all = pd.concat(lst_dfs, axis=1)

修改

包含两个csv文件的小型测试

A small test with two csv files

list_of_files = ['table1.csv', 'table2.csv']

lst_dfs = []

for file in list_of_files:
    df = pd.read_csv(file, sep='|')
    df = df.add_suffix(f'_{file}')
    lst_dfs.append(df)

df_all = pd.concat(lst_dfs, axis=1)

#Optional to remove the filename extension
df_all.columns = df_all.columns.str.replace('.csv', '')

print(df_all)
  key_table1  value_table1 key_table2  value_table2
0          A     -0.323896          B      0.050969
1          B      0.073764          D     -0.228590
2          C     -0.798652          E     -2.160319
3          D      0.970627          F     -0.213936

这篇关于根据 pandas 中的csv文件名重命名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！