1

I have a nested loop that I'm trying to pass values from a list into, but it will not recognize the list value. If I replace the value[col] with any list value like value['OpNo'] it works. Is there a specific wrapper or something I need around either the list values or the assignment code?

I tried the lists like col_list = ["'OpNo'", "'StationNo'", "'Spindle'", "'OpDescription'"] and wrapping the col callout like value[[col]]

The code below is getting the following exception: KeyError: 'OpNo'

row_indexer = 0
col_indexer = 1
iloc_indexer = 0
count_row = operationData.shape[0]
col_list = ['OpNo', 'StationNo', 'Spindle', 'OpDescription']
while row_indexer < count_row:    
    value = operationData.iloc[[row_indexer],[iloc_indexer]]
    for col in col_list:
        value = value[col].values[0]
        wb['OneOpSheet'].cell(row = (row_indexer + 12), column = (col_indexer + 1)).value = value
        col_indexer = (col_indexer + 1)
    row_indexer = (row_indexer + 1)
    iloc_indexer = (iloc_indexer + 1)
Improve this question
2
  • Can you provide a sample or dtypes listing from operationData? You can use operationData.dtypes for the info listing. Also row_indexer = (row_indexer + 1) and similar calls can be replaced by row_indexer += 1. Commented Jan 5, 2019 at 19:55
  • I just checked operationData.dtypes, every column is an object. And thanks for the += tip! Commented Jan 5, 2019 at 19:58

3 Answers 3

Reset to default
2

I'm not sure if this is going to help you exactly, but maybe it'll get you in the right direction. You can use Pandas.DataFrame.itertuples() to run across all rows in your dataframe, picking off values as you need them.

I went a bit further and created a quick column label dictionary to help sync the nested loops.

I tried to comment where necessary, but if you don't understand something, let me know!

import pandas as pd
import openpyxl
wb = load_workbook(filename='./generic_workbook_name.xlsx')

# Created smoe data for a dataframe.
fake_data_dict = {
    'OpNo':['1','2','3','4',],
    'StationNo':['11','22','33','44',],
    'Spindle':['S1','S2','S3','S4',],
    'OpDescription':['This','is','a','description',]
    }

# Create the dataframe.
data = pd.DataFrame(fake_data_dict)

Our dataframe:

  OpNo StationNo Spindle OpDescription
0    1        11      S1          This
1    2        22      S2            is
2    3        33      S3             a
3    4        44      S4   description

The rest of the script:

col_list = ['OpNo','StationNo','Spindle','OpDescription']


# Create a column label dictionary; Add 1 to index for Excel cells
col_dict = {i+1:v for i, v in enumerate(col_list)}

# Iterate over each row
for idx, row in enumerate(data.itertuples(), start = 1):
    # For each key in our column dictionary [0, 1, 2, 3]
    for key in col_dict.keys():
        print('Row: {a}\n\tColumn: {b}\n\t\tValue: {c}'.format(a = idx, b = key,
                                                               # Reduce the index by 1; Get column name based on key value.
                                                               c = data.loc[idx - 1, col_dict[key]]))

Output:

Row: 1
    Column: 1
        Value: 1
Row: 1
    Column: 2
        Value: 11
Row: 1
    Column: 3
        Value: S1
Row: 1
    Column: 4
        Value: This
Row: 2
    Column: 1
        Value: 2
Row: 2
    Column: 2
        Value: 22
Row: 2
    Column: 3
        Value: S2
Row: 2
    Column: 4
        Value: is
Row: 3
    Column: 1
        Value: 3
Row: 3
    Column: 2
        Value: 33
Row: 3
    Column: 3
        Value: S3
Row: 3
    Column: 4
        Value: a
Row: 4
    Column: 1
        Value: 4
Row: 4
    Column: 2
        Value: 44
Row: 4
    Column: 3
        Value: S4
Row: 4
    Column: 4
        Value: description

With that in mind, this could simplify your script:

for idx, row in enumerate(data.itertuples(), start = 1):
    for key in col_dict.keys():
        wb['OneOpSheet'].cell(row = (idx + 11), column = (key + 1)).value = data.loc[idx - 1, col_dict[key]]
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for that, I am not familiar with enumerate or iterturples. I'm trying to get more pythonic with my code but still default to looping the old way a lot. I could see how this was useful though, I appreciate the effort.
This is fantastic. Much more elegant than what I came up with. I don't fully understand the enumerate with intertuples yet but it gives me something to read into. Thank you! Much better than my solution
0

I think value = operationData.iloc[[row_indexer],[iloc_indexer]] returns a dataframe. Try value = value.loc[col].values[0].

Improve this answer

3 Comments

This returned KeyError: "the label [OpNo] is not in the [index]", I tried adding the extra " " around the list values but got the same exception but with ['OpNo']
In that case, it would seem to me that operationData.iloc[[row_indexer],[iloc_indexer]] returns a single value with no key. In that case drop the iloc indexer and replace with operationData.iloc[[row_indexer],:].I am not really sure why you are using an iloc indexer in the first place.
The idea was I had a 1 x 4 df that I then turned to a 1x1 df using value = operationData.iloc[[row_indexer],[iloc_indexer]], but I want to use that single value not as a df later, hence the value = value[col].values[0] to capture the value itself
0

I needed to have both commands inside the for loop and reset a couple of the indexers after that finished. The below nested loop accomplishes what I needed:

while row_indexer < count_row:    
    for col in col_list:
        value = operationData.iloc[[row_indexer],[iloc_indexer]]
        value = value[col].values[0]
        wb['OneOpSheet'].cell(row = (row_indexer + 12), column = (col_indexer + 2)).value = value
        col_indexer += 1
        iloc_indexer += 1
    row_indexer += 1
    iloc_indexer = 0
    col_indexer = 0
Improve this answer

1 Comment

See my edited comment. I pdated the dictionary and indexing to accommodate Excel cells, but if you run the function it will print each row, column, and cell value to help visualize the output and better optimize your script.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.