how to create a dataframe to generate json in the given format

Question

I need to generate a json from my dataframe but I have tried many formats of df but still I am not able get the required json format.

My required json format is,

[
    {
        "Keyword": "Red", 
        "values": [
            {
                "value": 5, 
                "TC": "Color"
            }            
        ]
    }, 
     {
        "Keyword": "Orange", 
        "values": [
            {
                "value": 5, 
                "TC": "Color"
            }            
        ]
    }, 
     {
        "Keyword": "Violet", 
        "values": [
            {
                "value": 5, 
                "TC": "Color"
            }            
        ]
    }
]

I want a df to generate this json. Please help.

but currently im getting df.to_json:

 {"Names":{"0":"Ram","1":"pechi","2":"Sunil","3":" Ravi","4":"sri"},"Values":{"0":"[{'value':2,'TC': 'TC Count'}]","1":"[{'value':2,'TC': 'TC Count'}]","2":"[{'value':1,'TC': 'TC Count'}]","3":"[{'value':1,'TC': 'TC Count'}]","4":"[{'value':1,'TC': 'TC Count'}]"}}

A function or set of functions that turns your dataframe onto this kind of json maybe, but "a dataframe to generate that json", no. I'm pretty sure it exists on the internet, something like "from dataframe to json" on Google. — Clement B, Oct 30 '17 at 09:52
ya there is a method df.to_json but I am not able to get this json. I am in need of a df to generate a json with the above format — Pyd, Oct 30 '17 at 09:54
above json in my question is my desired_df.to_json ,help me to create my deisred_df — Pyd, Oct 30 '17 at 10:02
You're not answering my question but anyway : try `[df.to_json]` or `list(df.to_json)` — Clement B, Oct 30 '17 at 10:05
can you tell me what is the need for this question, I doubt you misread the question — Pyd, Oct 30 '17 at 10:28

jezrael · Accepted Answer · 2017-10-30T11:34:08.540

1

I think you need:

set_index for columns not in nested dictionaries
create dicts by apply with to_dict
reset_index for column from index
create json by to_json

print (df)

  Keyword     TC  value
0     Red  Color      5
1  Orange  Color      5
2  Violet  Color      5

j = (df.set_index('Keyword')
        .apply(lambda x: [x.to_dict()], axis=1)
        .reset_index(name='values')
        .to_json(orient='records'))
print (j)

[{"Keyword":"Red","values":[{"TC":"Color","value":5}]},
 {"Keyword":"Orange","values":[{"TC":"Color","value":5}]},
 {"Keyword":"Violet","values":[{"TC":"Color","value":5}]}]

For write to file:

(df.set_index('Keyword')
   .apply(lambda x: [x.to_dict()], axis=1)
   .reset_index(name='values')
   .to_json('myfile.json', orient='records'))

edited Oct 30 '17 at 11:34

answered Oct 30 '17 at 11:10

jezrael

822,522
95
1,334
1,252

but when I am writing the df to new json file df.to_json I am getting the below error, AttributeError: 'str' object has no attribute 'to_json' – Pyd Oct 30 '17 at 11:26
You need change `.to_json(orient='records')` to `.to_json(filename.json, orient='records')` – jezrael Oct 30 '17 at 11:26
I changed but still same error, `AttributeError Traceback (most recent call last) in () ----> 1 df_graph.to_json("G:\\json_df.json",orient='records') AttributeError: 'str' object has no attribute 'to_json'` and also my type of df is , `is str – Pyd Oct 30 '17 at 11:30
Your input dataframe is like in my answer? – jezrael Oct 30 '17 at 11:32
yes the same one but after performing `df = (df.set_index('Keyword') .apply(lambda x: [x.to_dict()], axis=1) .reset_index(name='values') .to_json(orient='records'))` my df changed to str after that im writing it to json – Pyd Oct 30 '17 at 11:32
Hmmm, do you use last version of pandas? Because for me it working nice :( – jezrael Oct 30 '17 at 11:36
are you a freelancer @Jezrael – Pyd Oct 30 '17 at 11:53
No, not, I am working in full job, but I have some time for answering ;) – jezrael Oct 30 '17 at 11:54
I followed your solution one by one, first i did set index with keyword column for df, then i reset_index(name="Values"). But i am gettin `TypeError: reset_index() got an unexpected keyword argument 'name'` – Pyd Oct 30 '17 at 12:07
Then use `reset_index().rename(columns={'index':'Values')`, if need rename new index called `index` – jezrael Oct 30 '17 at 12:09
If new column is called something like `level_1`, need change `reset_index().rename(columns={'level_1':'Values')`. – jezrael Oct 30 '17 at 12:10
but you used Set_index(), then a reset index () how come that works – Pyd Oct 30 '17 at 12:31
I have one more json file similar to this question, I tried the same solution. But I am getting in a different format. could you please check when you're free https://pastebin.com/3Jkjzp8P – Pyd Oct 30 '17 at 13:34
It is nested :( More complicated. I have idea - Can you create new question with sample dataframe, desired json output and what you try (e.g. this answer solution) ? Because with guesing data it is really hard. – jezrael Oct 30 '17 at 13:36
But dont forget for input data ;) – jezrael Oct 30 '17 at 13:39
here is the question https://stackoverflow.com/questions/47016779/creating-df-to-generate-json-in-the-given-format – Pyd Oct 30 '17 at 13:43
Yes, but there is no input data :( Or need generate json to datafarme? – jezrael Oct 30 '17 at 13:43
I have got only this json, creating df is my question like this above question – Pyd Oct 30 '17 at 13:45
I need to generate that json but my question is the required df to generate that json – Pyd Oct 30 '17 at 13:46
As you said, I edited the question with the input data, pls check https://stackoverflow.com/questions/47016779/creating-df-to-generate-json-in-the-given-format – Pyd Oct 31 '17 at 05:38

how to create a dataframe to generate json in the given format

1 Answers1

Linked