Python Looping lambda functions

Question

I have tried looking for the answer on google and I am not sure if I am phrasing the question correctly as I am still a beginner, this being my first project.

I have a code that sorts through an excel document and it's sheets and creates new documents by aisle with the separated sheets. This works fine for me although it was a lot of repeating lines of code. But I want to be able to have a little more flexibility (Add or Remove the number of aisles it sorts out).

This is a snippet of what I have but it goes on for 23 Aisles. I want to sort it

import pandas as pd
import openpyxl
from openpyxl import load_workbook
import xlsxwriter

saveFolder = '/home/Work/Splitting/Aisles/'
aisles = pd.ExcelFile('Split_All.xlsx')
disco = pd.read_excel(aisles, 'Disco')
closed = pd.read_excel(aisles, 'Closed')
oi = pd.read_excel(aisles, 'OI+')
dropship = pd.read_excel(aisles, 'Dropship')
trueouts = pd.read_excel(aisles, 'True Outs')
largemiss = pd.read_excel(aisles, 'Large Missing')
twomiss = pd.read_excel(aisles, 'Two Missing')
onemiss = pd.read_excel(aisles, 'One Missing')

writer=pd.ExcelWriter(saveFolder+'Aisle 01.xlsx', engine='xlsxwriter')
disco2 = disco[disco['Loc'].map(lambda x: x.startswith('01'))]
closed2 = closed[closed['Loc'].map(lambda x: x.startswith('01'))]
oi2 = oi[oi['Loc'].map(lambda x: x.startswith('01'))]
dropship2 = dropship[dropship['Loc'].map(lambda x: x.startswith('01'))]
trueouts2 = trueouts[trueouts['Loc'].map(lambda x: x.startswith('01'))]
largemiss2 = largemiss[largemiss['Loc'].map(lambda x: x.startswith('01'))]
twomiss2 = twomiss[twomiss['Loc'].map(lambda x: x.startswith('01'))]
onemiss2 = onemiss[onemiss['Loc'].map(lambda x: x.startswith('01'))]
disco2.to_excel(writer, sheet_name='Disco', index=False)
closed2.to_excel(writer, sheet_name='Closed', index=False)
oi2.to_excel(writer, sheet_name='OI+', index=False)
dropship2.to_excel(writer, sheet_name='Dropship', index=False)
trueouts2.to_excel(writer, sheet_name='True Outs', index=False)
largemiss2.to_excel(writer, sheet_name='Large Missing', index=False)
twomiss2.to_excel(writer, sheet_name='Missing Two', index=False)
onemiss2.to_excel(writer, sheet_name='Missing One', index=False)
writer.save()

writer=pd.ExcelWriter(saveFolder+'Aisle 03.xlsx', engine='xlsxwriter')
disco4 = disco[disco['Loc'].map(lambda x: x.startswith('03'))]
closed4 = closed[closed['Loc'].map(lambda x: x.startswith('03'))]
oi4 = oi[oi['Loc'].map(lambda x: x.startswith('03'))]
dropship4 = dropship[dropship['Loc'].map(lambda x: x.startswith('03'))]
trueouts4 = trueouts[trueouts['Loc'].map(lambda x: x.startswith('03'))]
largemiss4 = largemiss[largemiss['Loc'].map(lambda x: x.startswith('03'))]
twomiss4 = twomiss[twomiss['Loc'].map(lambda x: x.startswith('03'))]
onemiss4 = onemiss[onemiss['Loc'].map(lambda x: x.startswith('03'))]
disco4.to_excel(writer, sheet_name='Disco', index=False)
closed4.to_excel(writer, sheet_name='Closed', index=False)
oi4.to_excel(writer, sheet_name='OI+', index=False)
dropship4.to_excel(writer, sheet_name='Dropship', index=False)
trueouts4.to_excel(writer, sheet_name='True Outs', index=False)
largemiss4.to_excel(writer, sheet_name='Large Missing', index=False)
twomiss4.to_excel(writer, sheet_name='Missing Two', index=False)
onemiss4.to_excel(writer, sheet_name='Missing One', index=False)
writer.save()

writer=pd.ExcelWriter(saveFolder+'Aisle 04.xlsx', engine='xlsxwriter')
disco2 = disco[disco['Loc'].map(lambda x: x.startswith('04'))]
closed2 = closed[closed['Loc'].map(lambda x: x.startswith('04'))]
oi2 = oi[oi['Loc'].map(lambda x: x.startswith('04'))]
dropship2 = dropship[dropship['Loc'].map(lambda x: x.startswith('04'))]
trueouts2 = trueouts[trueouts['Loc'].map(lambda x: x.startswith('04'))]
largemiss2 = largemiss[largemiss['Loc'].map(lambda x: x.startswith('04'))]
twomiss2 = twomiss[twomiss['Loc'].map(lambda x: x.startswith('04'))]
onemiss2 = onemiss[onemiss['Loc'].map(lambda x: x.startswith('04'))]
disco2.to_excel(writer, sheet_name='Disco', index=False)
closed2.to_excel(writer, sheet_name='Closed', index=False)
oi2.to_excel(writer, sheet_name='OI+', index=False)
dropship2.to_excel(writer, sheet_name='Dropship', index=False)
trueouts2.to_excel(writer, sheet_name='True Outs', index=False)
largemiss2.to_excel(writer, sheet_name='Large Missing', index=False)
twomiss2.to_excel(writer, sheet_name='Missing Two', index=False)
onemiss2.to_excel(writer, sheet_name='Missing One', index=False)
writer.save()

I am fairly certain this can be cleaned up and reduced in the amount of lines it is using but this is what I did to get it to work for me.

I was thinking the answer was loops but most looping information I am finding just shows me how to loop a list or strings, where I want to loop the following with different aisles based upon user input (User states they have 28 Aisles, so it loops this 28 times instead of the 23 I have):

`writer=pd.ExcelWriter(saveFolder+'Aisle 04.xlsx', engine='xlsxwriter')
disco2 = disco[disco['Loc'].map(lambda x: x.startswith('04'))]
closed2 = closed[closed['Loc'].map(lambda x: x.startswith('04'))]
oi2 = oi[oi['Loc'].map(lambda x: x.startswith('04'))]
dropship2 = dropship[dropship['Loc'].map(lambda x: x.startswith('04'))]
trueouts2 = trueouts[trueouts['Loc'].map(lambda x: x.startswith('04'))]
largemiss2 = largemiss[largemiss['Loc'].map(lambda x: x.startswith('04'))]
twomiss2 = twomiss[twomiss['Loc'].map(lambda x: x.startswith('04'))]
onemiss2 = onemiss[onemiss['Loc'].map(lambda x: x.startswith('04'))]
disco2.to_excel(writer, sheet_name='Disco', index=False)
closed2.to_excel(writer, sheet_name='Closed', index=False)
oi2.to_excel(writer, sheet_name='OI+', index=False)
dropship2.to_excel(writer, sheet_name='Dropship', index=False)
trueouts2.to_excel(writer, sheet_name='True Outs', index=False)
largemiss2.to_excel(writer, sheet_name='Large Missing', index=False)
twomiss2.to_excel(writer, sheet_name='Missing Two', index=False)
onemiss2.to_excel(writer, sheet_name='Missing One', index=False)
writer.save()`

alani · Accepted Answer · 2020-08-05 20:18:32Z

1

There are two obvious repeating patterns in this code, so they could both be replaced by for loops. You can also use a dictionary of input dataframes (with the sheet name as the key) to replace the different variables used for these. It appears that you could do something like the following, although without the input data this is not something that I can test:

import os
import pandas as pd

saveFolder = '/home/Work/Splitting/Aisles/'

aisles = pd.ExcelFile('Split_All.xlsx')

sheets = ['Disco', 'Closed', 'OI+', 'Dropship', 'True Outs',
          'Large Missing', 'Two Missing', 'One Missing']

dataframes = {}
for sheet in sheets:
    dataframes[sheet] = pd.read_excel(aisles, sheet)

for num in range(1, 29):

    nn = "{:02d}".format(num)  # e.g. 02
    
    filename = os.path.join(saveFolder, 'Aisle {}.xlsx'.format(nn))

    writer = pd.ExcelWriter(filename, engine='xlsxwriter')

    for sheet in sheets:
        df = dataframes[sheet]
        df2 = df[df['Loc'].map(lambda x: x.startswith(nn))]
        df2.to_excel(writer, sheet_name=sheet, index=False)

    writer.save()

The lambda function could be defined outside the loop over sheets, but it is not very important.

edited Aug 5, 2020 at 20:18

answered Aug 5, 2020 at 20:12

alani

13.2k3 gold badges18 silver badges34 bronze badges

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

1 Comment

Avindale Over a year ago

Thank you for your answer! This make a lot of sense and cleans my code up! Looking at your answer I see what was confusing me from the other data, it just helps seeing something I have been working on personally instead of examples of other data as it is just not close enough to what I need.

Collectives™ on Stack Overflow

Python Looping lambda functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related