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, Oct 02 '19 at 20:52
See this question for lots of options, such as dictionary, namedtuple, etc: https://stackoverflow.com/questions/1373164/how-do-i-create-a-variable-number-of-variables — neutrino_logic, Oct 02 '19 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, Oct 02 '19 at 21:34

score 1 · Accepted Answer · answered Oct 02 '19 at 20:42

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.

Thank you, this did exactly what I needed! – Bill Oct 03 '19 at 20:29 — Bill, Oct 03 '19 at 20:29

artdanil · Answer 2 · 2019-10-02T21:35:41.860

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.

python - Create variables in loop then assign

2 Answers2