CODEWITHSUNDEEP
pythonspyderxlrdxlwt
mevada.ravikumar
mevada.ravikumar

Reputation: 109

Creating a dictionary from Excel cell in Python

I have an Excel file where every cell is having a title and its values (int, string or nothing) separated by : sign. I want to make a dictionary where a key can be a title.

The column in excel file looks something like this: 

Procedure: PicProc  #cell 1
Mod: avB            #cell 2
ImageFile: av.jpg   #so on 
Odor: 1
OlfClass: 1
Jitter: 9500
ScaleDur: 4500
OdorList.Cycle: 1
OdorList.Sample: 10
Running: OdorList
FixationBlack.OnsetDelay: 2893
FixationBlack.OnsetTime: 217369
FixationBlack.RESP: 
FixationBlack1.OnsetDelay: 27
FixationBlack1.OnsetTime: 226896
FixationBlack1.RESP: 
FixationYellow.RESP: 
PicPresent.OnsetDelay: 34
PicPresent.OnsetTime: 227547
PicPresent.RESP: 
RatingOnset: 230558

I would like to have something like this: 

{'Procedure': 'PicProc','Mod': 'avB', 'ImageFile': 'av.jpg','Odor': '1'...  }

How should I do that?

Upvotes: 0

Views: 855

Answers (3)

drec4s
drec4s

Reputation: 8077

Assuming your data is placed in the first sheet of your workbook, inside column A you may extract the data like this:

import xlrd


wb = xlrd.open_workbook("path/to/file")
sheet = wb.sheet_by_index(0) # First sheet of workbook
arr = sheet.col_values(0) # Column A

print({k:v.strip() for k, v in dict(s.split(':', 1) for s in arr).items()})

Output

{'Procedure': 'PicProc', 'Mod': 'avB', 'ImageFile': 'av.jpg', 'Odor': '1', 'OlfClass': '1', 'Jitter': '9500', 'ScaleDur': '4500', 'OdorList.Cycle': '1', 'OdorList.Sample': '10', 'Running': 'OdorList', 'FixationBlack.OnsetDelay': '2893', 'FixationBlack.OnsetTime': '217369', 'FixationBlack.RESP': '', 'FixationBlack1.OnsetDelay': '27', 'FixationBlack1.OnsetTime': '226896', 'FixationBlack1.RESP': '', 'FixationYellow.RESP': '', 'PicPresent.OnsetDelay': '34', 'PicPresent.OnsetTime': '227547', 'PicPresent.RESP': '', 'RatingOnset': '230558'}

Upvotes: 1

Synapsis
Synapsis

Reputation: 1017

enter image description here

You can use this code to access the excel and get the values As you can see in the image you can get the first row of titles so you will have : Area; Sotto-area1 and so on And get the second, third and so on rows for the values. As a result you'll have Area="Campalto"; Sotto-area1="" etc

# Reading an excel file using Python 
import xlrd 

# Give the location of the file 
loc = ("path of file") 

# To open Workbook 
wb = xlrd.open_workbook(loc) 
sheet = wb.sheet_by_index(0) 

# For row 0 and column 0 
sheet.cell_value(0, 0) //this will return the value of the first cell ... that has position 0,0

Upvotes: 0

Andrea Ebano
Andrea Ebano

Reputation: 573

my_dict = {}
loc = ("path of file")
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
for i in range(1, sheet.nrows):

    row = sheet.row_values(i)
    my_dict[row[0]] = row[1]

print(my_dict)

Hope it helps.

Upvotes: 0

Related Questions