Convert excel to json with python
0
0
Entering edit mode
18 months ago
Kristina • 0

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?

Python • 939 views
ADD COMMENT
0
Entering edit mode

Please attach also an example of the input xlsx

ADD REPLY

Login before adding your answer.

Traffic: 1268 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6