Loop to merge multiple csv files-CodePudding

I am new to python. Please advise me following question.

By using for or while loop, I would like to merge around 200 csv files into a single csv file. Some of csv files with head(5) are like below(Each csv files are 1122 rows * 2 columns).

0sec.csv
    ID  Damage
0   0   2697.5
1   2   1431.7
2   3   2719.3
3   4   345.8
4   5   349.1

1sec.csv
    ID  Damage
0   0   507.1
1   2   0.0
2   3   9644.3
3   4   388.7
4   5   0.0

My goal is to make a single csv file like below.

total_damage.csv
    ID      0sec        1sec    ~~~    199sec        200sec
0   0       2697.5      507.1          xxx.x         xxx.x
1   2       1431.7      0.0            xxx.x         xxx.x
2   3       2719.3      9644.3         xxx.x         xxx.x
3   4       345.8       388.7          xxx.x         xxx.x
4   5       349.1       0.0            xxx.x         xxx.x

What I've tried

files = sorted(glob.glob('./python/*'))

df = pd.DataFrame()
for i, f in enumerate(files):
    file = pd.read_csv(f)
    file.rename(columns = {'Damage': '{}sec'.format(i)})
    df = pd.merge(df, file)

df.to_csv('./python/total_Damage.csv', index=False)

Error message

No common columns to perform merge on. Merge options: left_on=None, right_on=None, left_index=False, right_index=False

I would appreciate it if you could teach me current use of pd.merge with loop.

CodePudding user response：

You were almost there. First of all, you are not actually renaming because you missed file= in front of the rename.

Then, to add a column to a dataframe, you simply do df[col]=file[col].

Therefore:

df = pd.DataFrame()
for i, f in enumerate(files):
    file = pd.read_csv(f)
    file = file.rename(columns = {'Damage': '{}sec'.format(i)})
    df['{}sec'.format(i)] = file['{}sec'.format(i)]

Don't forget to add the id column once before iterating.