How to use a list to get value from dict

user_14028769 0
Viewed 101

I am new at using python and am trying to get my code to use a list I pulled from an excel sheet (using openpyxl). I created a dict that stored these letters as keys(letter) with associated values(word). I want to use the value returned in the list to check that against the dictionary key(letter) to pull the matching value(word). I then hope to take the value(word) and add it to a column in my excel sheet. I am stuck on how to do this last part.

# Open excel document with OpenPyXL and define Sheet1 as ws, Sheet2 as ws2.

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.worksheets[0]
ws2 = wb.worksheets[1]

# create a mapping of first letter to word
letter_word = {
  'Q': 'Quick',
  'J': 'Jump',
  'P': 'Pass',
}

print(letter_word) # print to check it is working

# look at excel worksheet 1 and list all values in the A column by the 1st letter

first_letter = []

for x in range(2, ws.max_row+1):
  first_char = ws.cell(row=x, column=1).value[0]
  first_letter.append(first_char)
    
print(first_letter)  # print to check it is working output is ['Q', 'P','J']
excel python
1 Answers

I'm not sure if have your logic correct, but I'll give a try.

You start with an Excel document with a list of words:

JuneBug
Puddle
Qwerty
Cinema
Butter
Anthill

You also have a dictionary of letter\word mappings:

'Q': 'Quick',
'J': 'Jump',
'P': 'Pass',
'A': 'Alpha',
'B': 'Bravo',
'C': 'Charlie'

You want to find the matching word in the dictionary with the key as the first letter of the word in Excel. This would results in the following output in the Excel document:

JuneBug    Jump
Puddle     Pass
Qwerty     Quick
Cinema     Charlie
Butter     Bravo
Anthill    Alpha

Assuming this logic is correct, here's the code:

import openpyxl

wb = openpyxl.load_workbook('WordList.xlsx')
ws = wb.worksheets[0]

# create a mapping of first letter to word
letter_word = {
    'Q': 'Quick',
    'J': 'Jump',
    'P': 'Pass',
    'A': 'Alpha',
    'B': 'Bravo',
    'C': 'Charlie'
 }

# get words from first column of Excel sheet
for x in range(1, ws.max_row+1):
    first_char = ws.cell(row=x, column=1).value[0]
    ws.cell(row=x, column=2).value = letter_word[first_char] # get mapping
 
wb.save('WordList2.xlsx') # save to new document
user_138780340
Related