Transform Pandas DataFrame to custom JSON format

Question

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

Good question! My bad, should have been more clear. I want to create a new column in the Pandas DataFrame with name: 'JSON Structure'. I want to transform each instance and save the transformation for in the column 'JSON Structure'. Is that more clear? @jezrael — Brian
– Brian, Commented Jun 27, 2022 at 8:03

jezrael · Accepted Answer · 2022-06-27 08:09:41Z

1

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...

edited Jun 27, 2022 at 8:09

answered Jun 27, 2022 at 7:52

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transform Pandas DataFrame to custom JSON format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related