Reading and Passing Excel Filename with Pandas

user_9610684 0
Viewed 10070

I want to read excel file with Pandas, delete the header row and the first column and write the resultant data in an excel file with the same name. I want to do it for all the excel files in a folder. I have written the code for data reading and writing but having trouble with saving the data in a file with the same name. The code I have written is like this-

import numpy as np
import pandas as pd
import os
for filename in os.listdir ('./'):
    if filename.endswith ('.xlsx'):
        df = pd.read_excel ('new.xlsx', skiprows=1)
        df.drop (df.columns [0], axis=1, inplace=True)
        df.to_csv ('new.csv', index=False)

How can I automate my code for all the excel files in the same folder?

pandas excel python
2 Answers

Use variable filename in function read_excel and then create new file names by format and for remove first column is possible use DataFrame.iloc - select all columns without first:

for filename in os.listdir ('./'):
    if filename.endswith ('.xlsx'):
        df = pd.read_excel (filename, skiprows=1)
        df.iloc[:, 1:].to_csv('new_{}.csv'.format(filename), index=False)

Another solution with glob, there is possible specify extensions:

import glob

for filename in glob.glob('./*.xlsx'):
    df = pd.read_excel (filename, skiprows=1)
    df.iloc[:, 1:].to_csv('new_{}.csv'.format(filename), index=False)   
    #python 3.6+    
    #df.iloc[:, 1:].to_csv (f'new_{filename}.csv', index=False)
user_29010020

Try Below for reading multiple files as follows:

import pandas as pd
import glob

# Read multiple files into one dataframe along with pandas `concat`
# if you have path defined like `/home/data/` then you can use `/home/data/*.xlsx` otherwise you directly mention the path.

df = pd.concat([pd.read_excel(files, sep=',', index=False, skiprows=1) for files in glob.glob("/home/data/*.xlsx")])

Alternative: Read multiple files into one dataframe

all_Files = glob.glob('/home/data/*.xlsx')

df = pd.concat((pd.read_excel(files, sep=',', index=False, skiprows=1) for files in all_Files))
user_57048630
Related