python - Create variables in loop then assign

Question

I'm trying to create variables inside a loop, then assign a value to them in the same loop iteration. My code is failing, I can see why, but cannot figure out how to fix it. I'm using Python 3.6 with current versions of all imported packages. Here's my code:

import openpyxl

months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

for month in months:
    filepath = 'C:\codes\Furnace_time_log\\2019\\' + month + '_2019.xlsx'
    workbook_name = month+'_wb'
    workbook_name =  openpyxl.load_workbook(filepath)
    sheet_name = month+'_sheet'
    sheet_name = workbook_name.get_sheet_by_name('Sheet1')

What I want out of this is 12 openpyxl sheet objects named "January_sheet", "February_sheet", ...

Earlier in my code I have the following sample which does exactly what it's supposed to. This is what I'm basing the logic of my erroneous code on:

head_wb = openpyxl.load_workbook('C:\codes\Furnace_time_log\head.xlsx')
head_sheet = head_wb.get_sheet_by_name('Sheet 1')

I'm working with an Excel file where every row contains a date and a lot of other useful information. If row x's date is in January, I need to place the adjacent data in a separate XLSX document ("January_2019.xlsx"), which I can edit via the January_sheet object.

Any advice on how accomplish this? Also, I am not concerned with performance. This code only has to run once on the full data set. I realize there's probably a more efficient way of doing this than concurrently opening 12 openpyxl workbooks and sheets. That being said, any better ways of accomplishing this are welcome.

Usually, it would help to present the error message and the place the code fails. — artdanil
– artdanil, Commented Oct 2, 2019 at 20:52
See this question for lots of options, such as dictionary, namedtuple, etc: stackoverflow.com/questions/1373164/… — neutrino_logic
– neutrino_logic, Commented Oct 2, 2019 at 20:56
1. There is only one Excel file with Sheet 1. 2. You want to create multiple files, based on the date in a column of Sheet 1. Please confirm. Also, post the header and top 5 rows of data. — Trenton McKinney
– Trenton McKinney, Commented Oct 2, 2019 at 21:34

Braaedy · Accepted Answer · 2019-10-02 20:42:00Z

1

I don't know your interface at all, but this is what I've gathered:

import openpyxl

months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

sheets = {}

for month in months:
    filepath = 'C:\\codes\\Furnace_time_log\\2019\\' + month + '_2019.xlsx'
    workbook =  openpyxl.load_workbook(filepath)
    sheet_name = month + '_sheet'
    sheet = workbook_name.get_sheet_by_name(sheet_name)
    sheets[month] = sheet

This is assuming that your sheets are named xxxx_month(e.g. January_month, case-sensitive) and not Sheet1 as you were originally using. When this is done you'll have a dictionary mapping month name to sheet object.

Let me know if something I've done doesn't make sense and I'll edit.

answered Oct 2, 2019 at 20:42

Braaedy

5601 gold badge5 silver badges19 bronze badges

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

1 Comment

Bill Over a year ago

Thank you, this did exactly what I needed!

artdanil · Accepted Answer · 2019-10-02 21:35:41Z

I can see several problems with your code:

Improper escape sequence in the file name.
Reassigning variables.
You are trying to create two different sets of dynamic variables: workbooks and sheets, without establishing relation between them.

In the code below, the first line stores value to variable 'sheet_name'. The second stores new value into same variable changing its value:

sheet_name = month+'_sheet'
sheet_name = workbook_name.get_sheet_by_name('Sheet1')

What you are trying to do is to dynamically create a variable name and value. Unless there is an overwhelming need to create such set of variables, I would recommend using either list or dictionary for this:

months = ['January', 'February', 'March','April', 'May', 'June', 'July', 
          'August', 'September', 'October', 'November', 'December']
wb_format = r'C:\codes\Furnace_time_log\2019\{}_2019.xlsx'
workbooks = dict((month, wb_format.format(month)) for month in months)

If you really want to create those variables, look up locals() and globals(), but do use caution.

Collectives™ on Stack Overflow

python - Create variables in loop then assign

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related