Create dataframe from excel by reading sheet names python pandas

Question

I have an excel workbook with 4 worksheets with different names. I want to read them into pandas dataframe only if they are called in the variable sheet_names. For example, the entire workbook's sheet names can be ['banana','orange','apple','grape']. Each sheet has 5 columns that I want to read into Python.

import pandas as pd

sheet_names =['grapes','orange'] #sheet_names is what I control... it can contain any number of sheets between 1 to 4.

xlsx = pd.ExcelFile('C:\\Users\\Ken\\Desktop\\Df.xlsx')

df = []

for x in sheet_names:
    df.append(xlsx.parse(sheetname=x,index_col=0,parse_cols='B:F'))

However the code returns a list with len = 2.

The desired output is a dataframe with 10 columns. Any help please?

MaxU - stand with Ukraine · Accepted Answer · 2016-10-23 13:12:08Z

1

Each call of xlsx.parse() returns a DataFrame, which you are appending to the df list. So in your code df is a list of DFs. If you want to merge selected sheets you can use pd.concat() method:

df = pd.concat([xlsx.parse(sheetname=x,index_col=0,parse_cols='B:F') for x in sheet_names],
               axis=1,
               ignore_index=True)

PS you may want to preserver original indexes - in this case change ignore_index=True to ignore_index=False

answered Oct 23, 2016 at 13:12

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create dataframe from excel by reading sheet names python pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related