Python Creating Dictionary from Excel Data

Python Creating Dictionary from excel data

d = {}
wb = xlrd.open_workbook('foo.xls')
sh = wb.sheet_by_index(2)   
for i in range(138):
    cell_value_class = sh.cell(i,2).value
    cell_value_id = sh.cell(i,0).value
    d[cell_value_class] = cell_value_id

Creating a dictionary from excel file with openpyxl

Method 1 using a for loop.

from openpyxl import load_workbook

wb = load_workbook('openpyxl.xlsx')
dict1 = {}
dict2 = {}
for row in wb.worksheets[0].iter_rows(min_row=2, max_row=4, max_col=3):
    dict1[int(row[0].value)] = row[1].value
    dict2[int(row[0].value)] = row[2].value

print(dict1)
print(dict2)

Method 2 using Dictionary Comprehension

from openpyxl import load_workbook

wb = load_workbook('openpyxl.xlsx')
dict1 = {(int(row[0].value), row[1].value) for row in wb.worksheets[0].iter_rows(min_row=2, max_row=4, max_col=3)}
dict2 = {(int(row[0].value), row[2].value) for row in wb.worksheets[0].iter_rows(min_row=2, max_row=4, max_col=3)}

print(dict1)
print(dict2)

results:

{1142: 'Apple', 1143: 'Banana', 1144: 'Kiwi'}
{1142: '17,5', 1143: '13,55', 1144: '52,3'}

how can I make a python dictionary from excel file using openpyxl?

I wrote demo code as below, hope is helps.

import openpyxl
workbook = openpyxl.load_workbook('./test.xlsx')
worksheet = workbook.active

dictionary = {}

for row in range(1, worksheet.max_row + 1):
  key = worksheet.cell(row, 1).value
  value = worksheet.cell(row, 2).value
  dictionary[key] = value

print(dictionary)

How to create dictionary with multiple column from excel in python?

You can use pandas to read your Excel file. Then use groupby ('University, 'Year') and agg to calculate the count for each University/Year.

Format your DataFrame with pivot then export to dictionary:

import pandas as pd
df = pd.read_excel("your_excel_file.xlsx")
df['count'] = 0
df = df.groupby(['University', 'Year'], as_index=False)['count'].agg('count')
df = df.pivot(index="Year", columns="University", values="count")
output = df.to_dict()
print(output)

Output:

{'BZU': {2013: 2.0, 2014: 1.0, 2015: nan, 2016: nan}, 'IUB': {2013: 3.0, 2014: 1.0, 2015: 1.0, 2016: nan}, 'UCP': {2013: 1.0, 2014: 1.0, 2015: nan, 2016: 2.0}}

You'll have to remove nan values manually if necessary:

for uni, year in output.items():
   for y, count in list(year.items()):
      if pd.isna(count):
         del year[y]

print(output)

Output:

{'BZU': {2013: 2.0, 2014: 1.0}, 'IUB': {2013: 3.0, 2014: 1.0, 2015: 1.0}, 'UCP': {2013: 1.0, 2014: 1.0, 2016: 2.0}}

Retrieve values of excel as python dictionary

A .to_dict() will create a dictionary where the keys are the names of the columns, and the values lists that contain the values.

Indeed, for the given dataframe, we get:

>>> df.to_dict()
{'Sr. No': {0: 1, 1: 2, 2: 3}, 'Name': {0: 'a', 1: 'b', 2: 'c'}}

You probably want to convert it to a list of dictionaries. For older versions of pandas, you can do that by specifying 'records', for older versions, this is record:

>>> df.to_dict('records')
[{'Sr. No': 1, 'Name': 'a'}, {'Sr. No': 2, 'Name': 'b'}, {'Sr. No': 3, 'Name': 'c'}]

for reader in pandas.read_excel(csvfile).to_dict('records'):
    print(reader['Name'])

Python: create a dictionary using data from excel file

import csv
d = {}
with open('input.csv', 'rb') as csvfile:
    reader = csv.reader(csvfile, delimiter=',')
    for row in reader:  # type(row) = <list>
        d[(row[0], row[1])] = row[2]

print(d)

Notice that I set the csv delimiter to ',' and file name to 'input.csv'. Change it if you need.

When I used this as an input file:

1142,Apple,17
1143,Banana,13
1144,Kiwi,52

This was the output:

{('1143', 'Banana'): '13', ('1142', 'Apple'): '17', ('1144', 'Kiwi'): '52'}

---

Related Topics