Iterate through the dictionary using pandas

Question

I'm trying to optimize the code when working with the Pandas in order to get a table with data read from csv-file.

This code works correctly, but it is quite cumbersome:

pd_table = pd.DataFrame(

    {
     10: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 10],
     20: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 20],
     30: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 30],
     40: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 40],
     50: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 50],
     60: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 60],
     70: [sheet['B' + str(item)].value for item in range(1,29) if sheet['A' + str(item)].value == 70]
      }

The result is a table with data

I reduced this code to this:

values= [10, 20, 30, 40, 50, 60, 70]

pd_table = pd.DataFrame(

dict(zip(values, [[sheet['B' + str(item)].value for item in range(6,29) if sheet['A' + str(item)].value == 10]]*len(values))), index = ['10.08', '20.08', '30.08']

)

Could you please tell how replace 10 in the construction ...sheet['A' + str(item)].value == 10... with "N", which would iterate numbers from the sequence 10, 20...70 from the list " values" ?

If I use sheet['A' + str(item)].value == i for i in values, then I get an error: local variable 'i' referenced before assignment

Answer 1

Can't you use a dictionary comprehension?

pd_table = pd.DataFrame(
  {val: [sheet['B' + str(item)].value for item in range(1,29)
         if sheet['A' + str(item)].value == val]
  for val in values},
  index = ['10.08', '20.08', '30.08']
)

Depending on the nature of sheet it might be possible to simplify this logic even further.