How to export pandas dataframe to json in specific format

Question

My dataframe is

  'col1' , 'col2'
    A    ,   89
    A    ,   232
    C    ,   545
    D    ,   998

and would like to export as follow :

{
  'A' : [ 89, 232 ],
  'C' : [545],
  'D' : [998]   
}

However, all the to_json does not fit this format (orient='records', ...). Is there a way to ouput like this ?

jezrael · Accepted Answer · 2018-02-28 08:10:54Z

2

Use groupby for convert to list and then to_json:

json = df.groupby('col1')['col2'].apply(list).to_json()
print (json)
{"A":[89,232],"C":[545],"D":[998]}

Detail:

print (df.groupby('col1')['col2'].apply(list))
col1
A    [89, 232]
C        [545]
D        [998]
Name: col2, dtype: object

answered Feb 28, 2018 at 8:10

jezrael

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

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

1 Answer 1