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?

drec4s · Accepted Answer · 2019-06-26 13:39:21Z

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

answered Jun 26, 2019 at 13:39

drec4s

8,1178 gold badges35 silver badges56 bronze badges

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

1 Comment

mevada.ravikumar Over a year ago

Thanks, It is working with your code, but my file is too big and your code prints selective results. There are many repetitions of the keys with different values. How can I append multiple values into a single key? For ex: line 10 is Odor: 1, line 54 is Odor = 23, line 1054 is Odor = 102 and so on. and I want to get a dictionary where the key is 'Odor' and the values are '1', '23', '102' and so on. If you got my point.

Andrea Ebano · Accepted Answer · 2019-06-26 13:19:15Z

0

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.

answered Jun 26, 2019 at 13:19

Andrea Ebano

5711 gold badge4 silver badges17 bronze badges

Comments

Synapsis · Accepted Answer · 2019-06-26 13:20:50Z

0

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

edited Jun 26, 2019 at 13:20

answered Jun 26, 2019 at 12:54

Synapsis

1,0471 gold badge8 silver badges26 bronze badges

3 Comments

mevada.ravikumar Over a year ago

thanks, but how can I divide that values (Ex: 'ScaleDur: 4500') which are separated by ':'. and make a dictionary where the first part must be key (ScaleDur)and the second part must be a value (4500).

Synapsis Over a year ago

you can get all the values of the first row as "Titles" or "ID" or whatever you like and then get the second line value as attribute of the ID you got before

Synapsis Over a year ago

@mevada.ravikumar I updated my answer so you can see better what I mean

Collectives™ on Stack Overflow

Creating a dictionary from Excel cell in Python

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related