Question 239817 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Pandas DataFrame group by and save columns as json strings

I have a pandas dataframe with following schema:

import pandas as pd

# Create a list of data
data = [['market1', 2023, 100, 200],
        ['market2', 2022, 300, 400],
        ['market1', 2021, 500, 600],
        ['market2', 2020, 700, 800]]

# Create a DataFrame
df = pd.DataFrame(data, columns=['market', 'year', 'val1', 'val2'])

Printout:

    market  year    val1    val2
0   market1 2023    100     200
1   market2 2022    300     400
2   market1 2021    500     600
3   market2 2020    700     800

I’d like to groupby market column and create a mapping year–val1 and year–val2 columns and save them as json strings. where year is the key and val1 is the value.

Expected output:

market  val1_json                   val2_json

market1 {"2021": 500, "2023": 100}  {"2021": 600, "2023": 200}
market2 {"2020": 700, "2022": 300}  {"2020": 800, "2022": 400}

Note that the json is sorted ascending by the year key.

Tags:

3

Answers


  1. 0

    Maybe not the most elegant solution

    df.pivot(index='year', columns=['market'], values = ['val1', 'val2']).unstack().droplevel(0).dropna()
    Login or Signup to reply.
  2. 0

    Try groupby:

    (df.drop(columns='market')
   .groupby(df['market'])
   .apply(lambda x: x.set_index('year').apply(lambda y: y.to_json()))
   .add_suffix('_json')
   .reset_index()
)

    Output:

        market                val1_json                val2_json
0  market1  {"2023":100,"2021":500}  {"2023":200,"2021":600}
1  market2  {"2022":300,"2020":700}  {"2022":400,"2020":800}
    Login or Signup to reply.
  3. 0

    You can construct a complex output for apply function by forming a pd.Series:

    def get_json(group):
    return pd.Series({
        'val1_json': {row['year']: row['val1'] for _, row in group.iterrows()},
        'val2_json': {row['year']: row['val2'] for _, row in group.iterrows()}
    })

df 
    .groupby('market') 
    .apply(get_json)

    Output:

                          val1_json               val2_json
market                                                 
market1  {2023: 100, 2021: 500}  {2023: 200, 2021: 600}
market2  {2022: 300, 2020: 700}  {2022: 400, 2020: 800}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search