While loop for excel parsing-CodePudding

error message screenshotI'm quite new to Python and I need to create a nested loop for excel parsing. I have a spreadsheet with 4 columns ID, Model, Part Number, Part Description, Year and I need a parser to go through each line and to return in format: Part Number, Toyota > Model > Year | Toyota > Model > Year etc... so that part number is returned only once listing all of the multiple fitting models and years. I was able to achieve the same through the code below but it is not switching to the second part Part Number

import pandas as pd
import xlrd
workbook = pd.read_excel('Query1.xls')
workbook.head()

    

i = 0
l = int(len(workbook))
a = workbook['Part Number'].iloc[i]


while i < l:
    b = 0
    c = workbook['Part Number'].iloc[b]
    print(a)
    while c == a:
        #print(c)
        print(b, 'TOYOTA >', workbook['Model'].iloc[b], ' > ', workbook['Year'].iloc[b], ' | ', end = ' ') 
        b = b   1
    print()
    
    i = i   b

CodePudding user response：

Your code gets stuck in an infinite loop, because you do not update the value c as you iterate through the rows. Here's how you could implement this better:

part_number_group = None
for i in range(len(df)):  # or `for i, row in df.iterrows():`
    part_number = df.loc[i, "Part Number"]
    if part_number != part_number_group:
        if part_number_group is not None:
            print()
        print(part_number)
        part_number_group = part_number
    print(i, 'TOYOTA >', df.loc[i, 'Model'], ' > ', df.loc[i, 'Year'], ' | ', end = ' ')

But instead, you should use groupby, which saves the need to iterate through rows at all:

df["Model-Year"] = df.index.astype(str)   " TOYOTA > "   df["Model"]   " > "   df["Year"].astype(str)
for part_number, group in df.groupby("Part Number"):
    print(part_number)
    print(*group["Model-Year"], sep=" | ")

CodePudding user response：

Trying to reuse some of your code, you may go over all unique part numbers using a for loop. For loops make it easier to no get stuck in an infinite loop because you specify the start and stop conditions upfront. Then you can query all entries with that same part number and print them with your suggested print function:

import pandas as pd
import xlrd
workbook = pd.read_excel('Query1.xls')

for num in pd.unique(workbook["Part Number"]):
    print('\n', num)
    part_df = workbook.query("`Part Number` == @num")
    for i in range(len(part_df)):
        print(i, 'TOYOTA >', part_df['Model'].iloc[i], ' > ', part_df['Year'].iloc[i], ' | ', end=' ')