How to combine two csv files to create one JSON object with specific header names with pandas

Question

I currently have two csv tables. One is for Pharmaceuticals and the other is for Nutraceuticals:
here is Pharmaceuticals

                  Drug  Percentile
0     SZ Antipsychotic          57
1              AMG 811          57
2           Colchicine          57
3            Ibuprofen          29
4    Sleep Deprivation          29
5            Rofecoxib          29

and here is Nutraceuticals

                                             Drug  Percentile
0                                          Canova          57
1                             Omega-3 fatty acids          43
2                                        Luteolin          29
3   Ethanol Extract of Aurantiochytrium sp. (EEA)          14
4                                         Osthole          14
5                 Arisaema amurense var. serratum          14

I basically need to combine these two csv files into one JSON object such that each table is an array of dictionaries paired with their respective label. The expected output is this (the names are different, only structure is important here):

{
"nutraceuticals": [
        {
            "name": "Omega-3 fatty acids",
            "percentile": 38
        },
        {
            "name": "Luteolin",
            "percentile": 25
        },
        {
            "name": "Vitamin D",
            "percentile": 25
        },
        {
            "name": "Probiotics supplements",
            "percentile": 13
        }
    ],
    "pharmaceuticals": [
        {
            "name": "Dexamethasone",
            "percentile": 25
        },
        {
            "name": "Rofecoxib",
            "percentile": 25
        },
        {
            "name": "Ibuprofen",
            "percentile": 25
        },
        {
            "name": "Laquinimod",
            "percentile": 25
        },
        {
            "name": "Lumiliximab",
            "percentile": 25
        }
   ]
}

I currently have this code:

result = Drug_Scoring.to_json(orient="records")
    parsed = json.loads(result)
    parsed = json.dumps(parsed, indent=4)

which produces the correct format in dictionary code, here:

[
    {
        "Drug": " Canova",
        "Percentile": 57
    },
    {
        "Drug": " Omega-3 fatty acids",
        "Percentile": 43
    },
    {
        "Drug": " Luteolin",
        "Percentile": 29
    },
    {
        "Drug": " Ethanol Extract of Aurantiochytrium sp. (EEA)",
        "Percentile": 14
    }
]

and so on, but does not combine the two and does not have the proper labeling such as "pharmaceuticals": [ and "nutraceuticals": [ I basically need the two csv file names to be the array titles in the JSON object and then combine the two. Thank you.

Umar.H · Accepted Answer · 2021-08-16 14:44:38Z

first create a dictionary object from your two dataframes.

d = dict(zip(['Pharmaceuticals','Nutraceuticals'], 
                [Pharmaceuticals,Nutraceuticals]))

out = {k : v.rename(columns={'Drug' : 'name', 'Percentile' : 'percentile'}
        ).to_dict(orient='records')
     for k,v in d.items() }

print(out)

{'Pharmaceuticals': [{'name': 'SZ Antipsychotic', 'percentile': 57},
  {'name': 'AMG 811', 'percentile': 57},
  {'name': 'Colchicine', 'percentile': 57},
  {'name': 'Ibuprofen', 'percentile': 29},
  {'name': 'Sleep Deprivation', 'percentile': 29},
  {'name': 'Rofecoxib', 'percentile': 29}],
 'Nutraceuticals': [{'name': 'Canova', 'percentile': 57},
  {'name': 'Omega-3 fatty acids', 'percentile': 43},
  {'name': 'Luteolin', 'percentile': 29},
  {'name': 'Ethanol Extract of Aurantiochytrium sp. (EEA)', 'percentile': 14},
  {'name': 'Osthole', 'percentile': 14},
  {'name': 'Arisaema amurense var. serratum', 'percentile': 14}]}

then dump to json.

with open('yourfile.json','w') as fp:
    json.dump(out,fp,indent=4)

`yourfile.json`

{
    "Pharmaceuticals": [
        {
            "name": "SZ Antipsychotic",
            "percentile": 57
        },
        {
            "name": "AMG 811",
            "percentile": 57
        },
        {
            "name": "Colchicine",
            "percentile": 57
        },
        {
            "name": "Ibuprofen",
            "percentile": 29
        },
        {
            "name": "Sleep Deprivation",
            "percentile": 29
        },
        {
            "name": "Rofecoxib",
            "percentile": 29
        }
    ],
    "Nutraceuticals": [
        {
            "name": "Canova",
            "percentile": 57
        },
        {
            "name": "Omega-3 fatty acids",
            "percentile": 43
        },
        {
            "name": "Luteolin",
            "percentile": 29
        },
        {
            "name": "Ethanol Extract of Aurantiochytrium sp. (EEA)",
            "percentile": 14
        },
        {
            "name": "Osthole",
            "percentile": 14
        },
        {
            "name": "Arisaema amurense var. serratum",
            "percentile": 14
        }
    ]
}

Collectives™ on Stack Overflow

How to combine two csv files to create one JSON object with specific header names with pandas

1 Answer 1

`yourfile.json`

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

yourfile.json

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`yourfile.json`