converting dataframe values into a particular json-style format (list of dictionaries)

Question

I am new to python and not being able to format the output in json style-format.

i have a dataframe df as

    col1    col2
0   ABC     2429
1   DEF     702
2   XYZ     2912
3   ABC     619
4   XYZ     3106
5   DEF     1511

I want to generate a list of dictionaries wherein the output is supposed to look something like this:

[
 {
  "col1":"ABC",
  "col2":[2429,619]
 },
 {
  "col1":"DEF",
  "col2":[702,1511]
 },
 {
  "col1":"XYZ",
  "col2":[2912,3106]
 }
]

MaxU - stand with Ukraine · Accepted Answer · 2017-12-25 12:34:41Z

1

you can group by col1, aggregate values of col2 into lists and finally generate a JSON:

In [64]: j = df.groupby('col1')['col2'].apply(list).reset_index().to_json(orient='records')

In [65]: j
Out[65]: '[{"col1":"ABC","col2":[2429,619]},{"col1":"DEF","col2":[702,1511]},{"col1":"XYZ","col2":[2912,3106]}]'

to make it nicer (human friendly):

In [66]: print(json.dumps(json.loads(j), indent=2))
[
  {
    "col1": "ABC",
    "col2": [
      2429,
      619
    ]
  },
  {
    "col1": "DEF",
    "col2": [
      702,
      1511
    ]
  },
  {
    "col1": "XYZ",
    "col2": [
      2912,
      3106
    ]
  }
]

answered Dec 25, 2017 at 12:34

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

Collectives™ on Stack Overflow

converting dataframe values into a particular json-style format (list of dictionaries)

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