Looping through multiple Excel file to modify and rewrite original using pandas

user_13941222 0
Viewed 44

I'm new to this so please bear with me.... I have an .xls file with 49 rows and 5 columns viz. a,b,c,d,e. I want to calculate the squareroot of (b^2+c^2+d^2) and put into the new column as f. in the same .xls file. Now imagine i have 49 of these files. I'm trying to write a code using pandas that automatically parse each and every file in the folder and add a column in the original file with the above formula.

My code is :

import glob
import pandas as pd
import numpy as np
#size = len(glob.glob('test/*.xls'))
file = glob.glob('test/*.xls')
for f in file:
    print(f)
    name = 12
    df = pd.read_excel(f, header = None)
    df.columns = ['a','b', 'c', 'd', 'e','f']
    df['Result'] = ((df['b']**2)+(df['c']**2)+(df['d']**2))**(1/2)
    df.to_excel(r'test/Nodal pressure at 8 us_at_Y_'+str(name)+'.5.xls', index = False)
    name = name + 1

I don't know if it is possible or not but anyhelp would be useful. Also, i'm new to the coding so i may not some basic.

anaconda pandas excel python
1 Answers

try the following where you create a new column Filename when reading in the data and groupby the filename when writing to excel and drop that column just before writing.:

import glob
import pandas as pd
file = glob.glob('test/*.xls')
df = pd.concat([pd.read_excel(f, header=None).assign(Filename=os.path.basename(f)) for f in file])
df.columns = ['a','b', 'c', 'd', 'e','f','Filename']
df['Result'] = ((df['b']**2)+(df['c']**2)+(df['d']**2))**(1/2)
name = 12
for x in df.groupby(['Filename']):
    x.drop('Filename', axis=1).to_excel(r'test/Nodal pressure at 8 us_at_Y_'+str(name)+'.5.xls', index=False)
    name += 1
user_63667700
Related