Creating a dictionary from Excel cell in Python

Question

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?

Answer 1

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'}