Customer json string formatting for pandas dataframe

Question

I'm trying to produce a customer json string from the values grouped by another column.

Consider following data:

>>> df = pd.DataFrame(
    [
                ["c1", "a1", "123"],
                ["c1", "a2", "456"],
                ["c2", "a1", "789"]
        ],
    index=["row1", "row2", "row3"],
    columns=["col1", "col2", "col3"],
)

From the above dataframe, I want to generate rows grouped by col1 where the next column is a json string representing a list of the items from col2, and col3.

For example,

>>> new_df
      key_id  json_string
row1      c1  '{"values":[{"col2":"a1", "col3":"123"}, {"col2":"a2", "col3":"456"}]'
row2      c2  '{"values":[{"col2":"a1", "col3":"789"}]'

I'm new to pandas but it appears that a combination of apply and to_json will achieve what I want. Can someone help me figure this out?

Thanks!

jezrael · Accepted Answer · 2021-03-04 06:04:40Z

2

Use custom lambda function to convert to dictionary all columns without col1, add values key and convert to json:

import json

f = lambda x: json.dumps({"values": x.to_dict(orient='records')})
df = (df.set_index('col1')
        .groupby('col1')
        .apply(f)
        .rename_axis('key_id')
        .reset_index(name='json_string'))
print (df)
  key_id                                        json_string
0     c1  {"values": [{"col2": "a1", "col3": "123"}, {"c...
1     c2        {"values": [{"col2": "a1", "col3": "789"}]}

If use to_json output is different:

f = lambda x: x.to_json(orient='records')
df = (df.set_index('col1')
        .groupby('col1')
        .apply(f)
        .rename_axis('key_id')
        .reset_index(name='json_string'))
print (df)
  key_id                                        json_string
0     c1  [{"col2":"a1","col3":"123"},{"col2":"a2","col3...
1     c2                       [{"col2":"a1","col3":"78

edited Mar 4, 2021 at 6:04

answered Mar 4, 2021 at 5:58

jezrael

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

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

1 Comment

Chris Over a year ago

In either case you're using apply against the grouping associated with grouping col1. To customize the json you use to_dict and json.dumps to produce a customer string. Thank you!

Collectives™ on Stack Overflow

Customer json string formatting for pandas dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related