Transform Pandas DataFrame to custom JSON format-CodePudding

I currently have a Python Pandas DataFrame that I would like to convert to a custom JSON structure. Does anyone know how I can achieve this? I'd love to hear it, thanks in advance!

Current Pandas DataFrame structure:

ArticleNumber | Brand | Stock | GroupCode
-----------------------------------
1  |    Adidas     | 124      | 20.0

I would like to transform the above dataframe into a JSON structure below:

{
  "Attributes": [
    {
      "Key": “ArticleNumber”,
      "Values": [
        “1”
      ]
    },
    {
      "Key": “Brand”,
      "Values": [
        “Adidas”
      ]
    },
    {
      "Key": “Stock”,
      "Values": [
        “124”
      ]
    },
    {
      "Key": “GroupCode”,
      "Values": [
        “20.0”
      ]
    },
  ]
}

CodePudding user response：

Use list with dict comprehension for custom format:

import json

L = df.to_dict('records')


df['DICT'] = [{"Attributes":[{'Key':k,'Values':[v]} 
               for k, v in x.items()]} for x in L]

df['JSON'] = [json.dumps({"Attributes":[{'Key':k,'Values':[v]} 
                for k, v in x.items()]}) for x in L]
print (df)
   ArticleNumber    Brand  Stock  GroupCode  \
0              1   Adidas    124      20.00   
1              2  Adidas1   1241      20.01   

                                                DICT  \
0  {'Attributes': [{'Key': 'ArticleNumber', 'Valu...   
1  {'Attributes': [{'Key': 'ArticleNumber', 'Valu...   

                                                JSON  
0  {"Attributes": [{"Key": "ArticleNumber", "Valu...  
1  {"Attributes": [{"Key": "ArticleNumber", "Valu...