Creating Python Dictionary from excel

Question

I want to create a dictionary from the values that imported from excel file, My code is below:

import xlrd
file_location = "data.xlsx"
workbook = xlrd.open_workbook(file_location)
M_Sheet = workbook.sheet_by_name("MM")
D_Sheet = workbook.sheet_by_name("DD")
F_sheet = workbook.sheet_by_name("FF")

M = []
for i in range(M_Sheet.nrows):
    value = (M_Sheet.cell(i,0).value)
    M.append(value)
D = []
for j in range(D_Sheet.nrows):
    value = (D_Sheet.cell(j,0).value)
    D.append(value)
F = []
for f in range(F_Sheet.nrows):
    value = (F_Sheet.cell(f,0).value)
    F.append(value)

I want to create a dictionary, like DICT, that consists of the values coming from the excel file, While

M=['s1', 's2',… 'si'],

D=['d1', 'd2',….. 'dj'] and

F=[ c1, c2, … cf] built from excel file.

for 2 value of M, D and F :

DICT={'s1': {'d1': c1, 'd2': c2}, 's2': {'d1': c3, 'd2': c4}}

Any idea on how I can create this dictionary(DICT)?

Answer 1

if i have understood your logic, here my solution:

#method to create samples list
nb_s = 3;nb_d = 2

S = ['s' + str(x) for x in range(1, nb_s + 1)]
D = ['d' + str(x) for x in range(1, nb_d + 1)]
C = ['c' + str(x) for x in range(1, (len(S) * len(D)) + 1)]

print(S);print(D);print(C)

dico_s = {}
for s in S:
    dico_d = {}
    for d in D:
        idx = D.index(d) + len(D) * S.index(s)
        dico_d[d] = C[idx]
    dico_s[s] = dico_d

print(dico_s)

output:

S ->['s1', 's2', 's3']
D ->['d1', 'd2']
C->['c1', 'c2', 'c3', 'c4', 'c5', 'c6']

DICO->{'s1': {'d1': 'c1', 'd2': 'c2'}, 
       's2': {'d1': 'c3', 'd2': 'c4'}, 
       's3': {'d1': 'c5', 'd2': 'c6'}}
 --------------------------

**for nb_s = 4 and nb_d = 6**

S -> ['s1', 's2', 's3', 's4']
D -> ['d1', 'd2', 'd3', 'd4', 'd5', 'd6']
C -> ['c1', 'c2', 'c3', 'c4', 'c5', 'c6', 'c7', 'c8', 'c9', 'c10', 'c11', 'c12', 
      'c13', 'c14', 'c15', 'c16', 'c17', 'c18', 'c19', 'c20', 'c21', 'c22', 'c23', 'c24']

DICO -> {'s1': {'d1': 'c1', 'd2': 'c2', 'd3': 'c3', 'd4': 'c4', 'd5': 'c5', 'd6': 'c6'}, 
         's2': {'d1': 'c7', 'd2': 'c8', 'd3': 'c9', 'd4': 'c10', 'd5': 'c11', 'd6': 'c12'}, 
         's3': {'d1': 'c13', 'd2': 'c14', 'd3': 'c15', 'd4': 'c16', 'd5': 'c17', 'd6': 'c18'}, 
         's4': {'d1': 'c19', 'd2': 'c20', 'd3': 'c21', 'd4': 'c22', 'd5': 'c23', 'd6': 'c24'}}