Possible to loop through excel files with differently named sheets, and import into a list?

user_8872868 0
Viewed 1413

I do not have a reproducible example for this but asking it based on interest.

With a loop function in R, we are able to obtain all .csv from a directory with the below code: 

file.list <- list.files(pattern='*.csv') #obtained name of all the files in directory
df.list <- lapply(file.list, read.csv) #list

Would it be possible for us to loop through a directory with .xlsx files instead with different number of sheets?

For instance: A.xlsx contains 3 sheets, Jan01, Sheet2 and Sheet3; B.xlsx contains 3 sheets, Jan02, Sheet2 and Sheet3 ... and so on. The first sheet name changes.

Is it possible to loop through a directory and just obtain the dataframes for the first sheet in all excel files?

Python or R codes are welcome!

Thank you!

r python
5 Answers

In R

Here is an R solution using the package openxlsx

# get all xlsx files in given directory
filesList <- list.files("d:/Test/", pattern = '.*\\.xlsx', full.names = TRUE)

# pre-allocate list of first sheet names
firstSheetList <- rep(list(NA),length(filesList))

# loop through files and get the data of first sheets
for (k in seq_along(filesList)) 
  firstSheetList[[k]] <- openxlsx::read.xlsx(filesList[k], sheet = 1)
user_85065480

another (fast) R-solution using the readxl-package

l <- lapply( file.list, readxl::read_excel, sheet = 1 )
user_63562780

Sure, its possible using pandas and python. 

import pandas as pd

excel_file = pd.ExcelFile('A.xlsx')
dataframes = {sheet: excel_file.parse(sheet) for sheet in excel_file.sheet_names}

dataframes becomes a dictionary, with the keys being the names of the sheets, and the values becoming the dataframe containing the sheet data. You can iterate through them like so:

for k,v in dataframes.items():
    print('Sheetname: %s' % k)
    print(v.head())
user_24912990

By using Openpyxl

get_sheet_names().

This function returns the names of the sheets in a workbook and you can count the names to tell about total number of sheets in current workbook. The code will be:

>>> wb=openpyxl.load_workbook('testfile.xlsx')
>>> wb.get_sheet_names()
['S1, 'S2', 'S3']

we can access any sheet at one time. Lets suppose we want to access Sheet3. Following code should be written

>>> import openpyxl
>>> wb=openpyxl.load_workbook('testfile.xlsx')
>>> wb.get_sheet_names()
['Sheet1', 'Sheet2', 'Sheet3']
>>> sheet=wb.get_sheet_by_name('Sheet3')

The function get_sheet_by_name('Sheet3') is used to access a particular sheet. This function takes the name of sheet as argument and returns a sheet object. We store that in a variable and can use it like...

>>> sheet
<Worksheet "Sheet3">
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title
'Sheet3'
>>>

and eventually:

worksheet = workbook.get_sheet_by_name('Sheet3')    
for row_cells in worksheet.iter_rows():
    for cell in row_cells:
       print('%s: cell.value=%s' % (cell, cell.value) )
user_51734260

For simplicity, lets say we had two workbooks with the first sheet in this format:

enter image description here

You can iterate over each .xlsx file in the directory with glob.glob(), and append the dataframe of the first sheet with pandas.ExcelFile.parse() to a list:

from glob import glob
import pandas as pd

sheets = []

# Go through each xlsx file
for xlsx_file in glob("*.xlsx"):

    # Convert sheet to dataframe
    xlsx = pd.ExcelFile(xlsx_file)

    # Get first sheet and append it
    sheet_1 = xlsx.parse(0)
    sheets.append(sheet_1)

print(sheets)

Which prints two dataframes contained in a list:

[   x  y
0  1  2
1  1  2,    x  y
0  1  2
1  1  2]

You can also write the above as a list comprehension:

[pd.ExcelFile(xlsx_file).parse(0) for xlsx_file in glob("*.xlsx")]

You could also store the dataframes into a dictionary with filenames as the key:

{xlsx_file: pd.ExcelFile(xlsx_file).parse(0) for xlsx_file in glob("*.xlsx")}
user_52389150
Related