How to retrieve data from excel and format them in python?

Question

I am trying to retrieve data from excel and put them into the following format in python:

 dataset={
        'User A': {'Lady in the Water': 2.5, 
                        'Snakes on a Plane': 3.5,
                        'Just My Luck': 3.0, 
                        'Superman Returns': 3.5, 
                        'You, Me and Dupree': 2.5,
                        'The Night Listener': 3.0},

        'Gene Seymour': {'Lady in the Water': 3.0, 
                        'Snakes on a Plane': 3.5,
                        'Just My Luck': 1.5,
                         'Superman Returns': 5.0,
                         'You, Me and Dupree': 3.5, 
                         'The Night Listener': 3.0
                        }}

Where the excel file looks like

                User A  User B
 Lady in the Water  2.5 3
 Snakes on a Plane  3.5 3.5
 Just My Luck   3   1.5
 Superman Returns   3.5 5
 You, Me and Dupree 2.5 3.5
 The Night Listener 3   3

Xukrao · Accepted Answer · 2017-05-23 14:13:52Z

3

The pandas module makes this pretty easy:

import pandas as pd
df = pd.read_excel('workbook.xlsx', index_col=0)
dataset = df.to_dict()

In this code the pd.read_excel function collects all data from the excel file and stores it into a pandas DataFrame variable. Dataframes come with an enormous amount of very powerful built-in methods for data reorganization and manipulation. One of these methods is the to_dict, which is used in the code here to convert the data to nested dictionaries.

edited May 23, 2017 at 14:13

answered May 23, 2017 at 13:10

Xukrao

8,6745 gold badges29 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Drew Bennett · Accepted Answer · 2017-05-23 14:33:10Z

2

Another way is through openpyxl:

from openpyxl import Workbook
wb = load_workbook(filename = 'workbook.xlsx')
sheet_ranges = wb['cell range']
values = sheet_ranges['cell locations'].values()
data = values.to_dict()

answered May 23, 2017 at 14:33

Drew Bennett

4833 gold badges6 silver badges25 bronze badges

Collectives™ on Stack Overflow

How to retrieve data from excel and format them in python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related