如何结合两个csv文件创建一个具有特定头名的JSON对象

Question

我现在有两个csv表。一个用于Pharmaceuticals，另一个用于Nutraceuticals

这是Pharmaceuticals

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

这是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

基本上，我需要将这两个csv文件组合成一个JSON对象，以便每个表都是一个字典数组，并与它们各自的标签配对。预期的输出是这样的(名称不同，只有结构在这里很重要)：

{
"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
        }
   ]
}

我现在有这样的代码：

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

它在字典代码中生成正确的格式，在这里：

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

以此类推，但没有将两者结合起来，并且没有适当的标记，例如"pharmaceuticals": [和"nutraceuticals": [，我基本上需要两个csv文件名作为JSON对象中的数组标题，然后将两者组合起来。谢谢。

Answer 1

首先，从两个数据中创建一个字典对象。

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

那就扔给杰森。

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