Loop through Excel sheets to extract individual cell values using Python Pandas

Question

I want to loop through multiple Excel sheets to extract cell values (in this example, it'd be A1, B2, A3), and display values from each sheet in a row (see examples below). The source file is a bit messy, with row labels ('Fruit name', 'object type', 'count') that has been removed for simplification.

I was able to extract cell values from a single column (see current code), but need helping getting values scattered throughout two columns. In my real-life project, I have many more cells, so it is important that the solution is scalable. I tried Openpyxl last week, but have reverted to pandas as pd seems to be more versatile. Ideas on how to write clean code for this would be much appreciated!

Sheet Format

    A     B
0 Apples
1       Fruit
2  5

Desired output

Values from multiple sheets displayed as rows in a new sheet:

 A       B       C
Apples  Fruit    5
Cilanto Herb     4

Current code

result=[]
for i in File.sheet_names:
    df = pd.read_excel(File.xlsx', sheet_name=i)
    cells = df.iloc[[0, 2], [1]]
    result.append(cells)
result = pd.concat(result, axis=1).T

Hey there! I don't fully understand your source excel format. I can't see any structure there: It seems like 'A' holds some object names and 'B' holds the type of the corresponding object. E.g. 'Apples' are 'Fruit'. So why is it in different rows? Doesn't seem to make any sense. Any why is a "5" there in Row 2? — Lukas Fink
– Lukas Fink, Commented Mar 22, 2021 at 14:01
You're right, the structure doesn't make sense - I've modified my post to clarify. "The source file is a bit messy, with row labels ('Fruit name', 'object type', 'count') that I have omitted for simplicity purposes." — firefly3224
– firefly3224, Commented Mar 22, 2021 at 14:35

Lukas Fink · Accepted Answer · 2021-03-22 18:33:54Z

1

Given we have one excel file with the following format:

Sheet 1

    A       B       C
1   Apples  Fruit   1
2   Bananas Fruit   7
3   Chives  Herbs   6

Sheet 2

    A           B       C
1   Cilanto     Fruit   1
2   Pineapple   Fruit   7

You could do something like this to have all data in one pandas data frame:

import pandas as pd
import string


character_number_mapping = {string.ascii_lowercase[i]:i for i in range(len(string.ascii_lowercase))}


def col(cell_name: str):
    character = cell_name.split('-')[0]
    
    return character_number_mapping[character.lower()]

    
def row(cell_name: str):
    row_number = int(cell_name.split('-')[1])
    
    return row_number - 1


excel_file = pd.ExcelFile('ExampleExcel.xlsx', engine='openpyxl')

df = pd.DataFrame()
for sheet_name in excel_file.sheet_names:
    sheet_data = {'A': [], 'B': [], 'C': []}
    df_tmp = pd.read_excel(excel_file, sheet_name, header=None)
    sheet_data['A'].append(df_tmp.loc[row('A-2'), col('A-2')])
    sheet_data['B'].append(df_tmp.loc[row('B-1'), col('B-1')])
    sheet_data['C'].append(df_tmp.loc[row('C-2'), col('C-2')])
    sheet_data['A'].append(df_tmp.loc[row('A-1'), col('A-1')])
    sheet_data['B'].append(df_tmp.loc[row('B-2'), col('B-2')])
    sheet_data['C'].append(df_tmp.loc[row('C-1'), col('C-1')])
    df = pd.concat([df, pd.DataFrame(sheet_data)])

df.columns = ['A', 'B', 'C']
    
df.head()

prints

    A           B       C
0   Bananas     Fruit   7
1   Apples      Fruit   1
0   Pineapple   Herbs   4
1   Cilanto     Fruit   2

If you want the index to be monotoneous, apply a reset_index(drop=True) afterwards.

edited Mar 22, 2021 at 18:33

answered Mar 22, 2021 at 14:15

Lukas Fink

6671 gold badge6 silver badges18 bronze badges

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

4 Comments

firefly3224 Over a year ago

Thanks for the example! How could the code be modified to extract specific cells (e.g. A1, B2, A3, A17, so forth)? In my project, I'm hoping to extract only relevant cell values that are spread out in different cells

Lukas Fink Over a year ago

This got a little bit messy now. Please consider introducing a function for extracting single rows. And please note: - This is only an approach, you could achieve that in a numerous different ways - This currently only works for a-z, if there are more than 26 cols, you have to change the code

firefly3224 Over a year ago

Thank you so much for the ideas! This also made me realize that cleaning up the source file will help me go a long way

Lukas Fink Over a year ago

Absolutely right :) feel free to mark the answer as right

Collectives™ on Stack Overflow

Loop through Excel sheets to extract individual cell values using Python Pandas

Sheet Format

Desired output

Current code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sheet Format

Desired output

Current code

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related