Convert excel to json with python

Question

[Kristina]

Im new to programing and I'm trying to convert my excel file into a json file but I don't get the output I desire.

The json file I want should be formatted like this

"rrs_A514X" : ["STM"],
"rrs_C1402X" :  ["AMI","CAP", "KAN"],
"ethA_R207G" : ["ETH"], 
 ..... 
 "katG_S315T" : ["INH"]

}

When I'm converting my excel file to json with pandas with the following code

import pandas as pd  d = pd.read_excel('/Desktop/Excel_col.xlsx', index_col=0) dout = '/Desktop/1_new.json'
d1 = d.transpose().to_dict(orient='list')
df2 = pd.DataFrame(d1)
df3 = df2.to_json(dout, orient='records')

This is what the json file looks like

[{"rrs_a1401g":"KAN","eis_c-14t":"KAN","tlyA_N236K":"CAP","rrs_c1402t":"CAP","rrs_g1484t":"KAN","tlyA_L74P":"CAP","embB_M306V":"EMB","embB_M306I":"EMB","embB_Q497R":"EMB","embA_c-12t":"EMB","embB_D354A":"EMB","embB_G406A":"EMB","embB_Y319S":"EMB","embB_G406D":"EMB","embB_G406S":"EMB","embB_M306L":"EMB","embB_Q497K":"EMB","embB_D328Y":"EMB","embB_G406C":"EMB","embB_Y319C":"EMB","inhA_c-777t (fabG1_c-15t)":"INH","ethA_111_del_1_ct_c":"ETH"

I also tried to run

df = pd.read_excel('/Desktop/Excel_col.xlsx')
dict_out = df.groupby('Variant')['Drug'].agg(list).to_dict
print(dict_out)  

And get the following output in the interactive window

eis_-7_del_1_cg_c         [KAN]
eis_c-14t            [AMI, KAN]
eis_g-10a                 [KAN]
                    ...    
rrs_c517t                 [STM]
rrs_g1484t           [CAP, KAN]
rrs_g878a                 [STM]

Which is closer to the out put I want.

How can I go from an excel file to create a data frame to_dict (using orient as list) to out put a new json file with the values as being captured in brackets?

[Shred]

Please attach also an example of the input xlsx