Python从excel数据创建字典

Python Creating Dictionary from excel data

提问by tuna

I want to create a dictionary from the values, i get from excel cells, My code is below,

我想从值创建一个字典，我从 excel 单元格中获取，我的代码如下，

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

and I want to create a dictionary, like below, that consists of the values coming from the excel cells;

我想创建一个字典，如下所示，它包含来自 excel 单元格的值；

{'class1': 1, 'class2': 3, 'class3': 4, 'classN':N}

Any idea on how I can create this dictionary?

关于如何创建这本词典的任何想法？

采纳答案by eumiro

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

回答by root

or you can try pandas

或者你可以试试熊猫

from pandas import *
xls = ExcelFile('path_to_file.xls')
df = xls.parse(xls.sheet_names[0])
print df.to_dict()

回答by Jon Clements

I'd go for:

我会去：

wb = xlrd.open_workbook('foo.xls')
sh = wb.sheet_by_index(2)   
lookup = dict(zip(sh.col_values(2, 0, 138), sh.col_values(0, 0, 138)))

回答by hamed

if you can convert it to csv this is very suitable.

如果您可以将其转换为 csv，这是非常合适的。

import dataconverters.commas as commas
filename = 'test.csv'
with open(filename) as f:
      records, metadata = commas.parse(f)
      for row in records:
            print 'this is row in dictionary:'+row

回答by khelili miliana

This script allows you to transform an excel data table to a list of dictionaries:

此脚本允许您将 excel 数据表转换为字典列表：

import xlrd

workbook = xlrd.open_workbook('foo.xls')
workbook = xlrd.open_workbook('foo.xls', on_demand = True)
worksheet = workbook.sheet_by_index(0)
first_row = [] # The row where we stock the name of the column
for col in range(worksheet.ncols):
    first_row.append( worksheet.cell_value(0,col) )
# transform the workbook to a list of dictionaries
data =[]
for row in range(1, worksheet.nrows):
    elm = {}
    for col in range(worksheet.ncols):
        elm[first_row[col]]=worksheet.cell_value(row,col)
    data.append(elm)
print data

回答by Amog Chandrashekar

You can use Pandas to do this. Import pandas and Read the excel as a pandas dataframe.

您可以使用 Pandas 来执行此操作。导入熊猫并将 excel 作为熊猫数据框读取。

import pandas as pd
file_path = 'path_for_your_input_excel_sheet'
df = pd.read_excel(file_path, encoding='utf-16')

You can use pandas.DataFrame.to_dictto convert a pandas dataframe to a dictionary. Find the documentation for the same here

您可以使用pandas.DataFrame.to_dict将熊猫数据框转换为字典。在此处查找相同的文档

df.to_dict()

This would give you a dictionary of the excel sheet you read.

这将为您提供您阅读的 Excel 工作表的字典。

Generic Example :

通用示例：

df = pd.DataFrame({'col1': [1, 2],'col2': [0.5, 0.75]},index=['a', 'b'])

>>> df

>>> df

col1 col2 a 1 0.50 b 2 0.75

col1 col2 a 1 0.50 b 2 0.75

>>> df.to_dict()

>>> df.to_dict()

{'col1': {'a': 1, 'b': 2}, 'col2': {'a': 0.5, 'b': 0.75}}

{'col1': {'a': 1, 'b': 2}, 'col2': {'a': 0.5, 'b': 0.75}}