2

If I have an excel file that has no row/column labels that looks like this:

enter image description here

and I have a dictionary that looks like this:

dict = {a:1, b:2, c:3}

How can I combine them into a dictionary that combines the values and that looks like this:

dict_result = {a:2, b:3, c:4}
Brian Breeden
  • 91
  • 1
  • 3
  • 8
  • Have you tried to actually read the excel file using python? If your excel file is as simple as a csv file then you should be able to use the `csv` module, otherwise you can use an excel specific library or `pandas`.What you have is essentially two steps. Read the file and create the dict to be merged, then do the merge. – Paul Rooney Jun 26 '18 at 04:17
  • Can you show what you have done so far? – min2bro Jun 26 '18 at 04:18
  • Whats the extension of this excel file? – RoadRunner Jun 26 '18 at 04:26
  • For the second part https://stackoverflow.com/questions/11011756/is-there-any-pythonic-way-to-combine-two-dicts-adding-values-for-keys-that-appe – Paul Rooney Jun 26 '18 at 04:27
  • xlsx or xls. I can format to either. – Brian Breeden Jun 26 '18 at 04:27
  • Paul, I was able to get two dictionaries to combine correctly. I am struggling with importing a xlsx/xls/csv into the same format as the dictionary presented. Sorry if you think this is too basic for you. – Brian Breeden Jun 26 '18 at 04:35
  • 1
    If you're "_struggling with importing a xlsx/xls/csv into the same format as the dictionary presented_", please post the code you have tried so far and to what unwanted result it leads, so that there is sth to correct or complete. – SpghttCd Jun 26 '18 at 04:49

2 Answers2

2

Solution 1

If your excel file is in .xlsx format, you can use openpyxl:

import openpyxl

letter_map = {'a':1, 'b':2, 'c':3}

# open workbook
workbook = openpyxl.load_workbook('book1.xlsx')

# get worksheet by index
worksheet = workbook.worksheets[0]

result = {}

# loop over column pairs
for k, v in zip(worksheet['A'], worksheet['B']):

    # assign new values to keys
    result[k.internal_value] = v.internal_value + letter_map[k.internal_value]

print(result)

Output

{'a': 2, 'b': 3, 'c': 4}

Solution 2

If you have your excel file in .xls format, you can use xlrd:

import xlrd

letter_map = {'a':1, 'b':2, 'c':3}

# open work book
workbook = xlrd.open_workbook('book1.xls', on_demand=True)

# get sheet by index
worksheet = workbook.sheet_by_index(0)

result = {}

# loop over row indices
for row in range(worksheet.nrows):

    # assign new values to keys
    k, v = worksheet.cell(row, 0).value, worksheet.cell(row, 1).value
    result[k] = int(v) + letter_map[k]

print(result)

Output

{'a': 2, 'b': 3, 'c': 4}
RoadRunner
  • 25,803
  • 6
  • 42
  • 75
0

This solution works for csv file having columns A and B

import pandas as pd

actual_dict = {'a': 1, 'b': 1, 'c': 1}

cs = pd.read_csv(r'.\dict.csv')
keys = cs.A.tolist()
vals = cs.B.tolist()
csv_dict = {k:v for k,v in zip(keys,vals)}

for k in actual_dict.keys():
    actual_dict[k] += csv_dict[k] #updating the actual dict
letmecheck
  • 1,183
  • 8
  • 17