Question 202103 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

Json – Make a table from dictionares

player_name awards
Sergei Belov {‘2009-2010’: [‘Russia2 Silver Medal’], ‘2013-2014’: [‘VHL Silver Medal’]}
Rafael Khakimov {‘2010-2011’: [‘MHL All-Star Game’,’MHL Best GAA (1.79)’,’MHL Best Goaltender’,’MHL Goaltender of the Month (February)’,’MHL Goaltender of the Month (September)’],’2017-2018′: [‘VHL Playoffs Best GAA (1.39)’], ‘2021-2022’: [‘VHL Goaltender of the Month (November)’]}

How to unpack awards in python into the column of the name of the awards and into the value of the year of the season when it was received.

Sample result:

player_name russia2_silver_medal mhl_allstar_game
Sergei Belov 2009-2010 NaN
Rafael Khakimov NaN 2010-2011

players_personal[‘awards’] = players_personal[‘awards’].apply(lambda x: json.loads(x))

First, I converted from strings to json, and then tried to collect them into a dataframe through a loop, but not successfully

Tags:

2

Answers


  1. 0

    Here is one way. Note that I T transposed the data to make it visible here. You can get rid of the T at the very end

    import pandas as pd

dat1 = [{'player_name':row.player_name, 'award': award, 'date': date} 
            for row in df.itertuples() 
               for date, awards in row.awards.items() for award in awards]

pd.DataFrame(dat1).pivot('player_name', 'award', 'date').T

player_name                             Rafael Khakimov Sergei Belov
award                                                               
MHL All-Star Game                             2010-2011          NaN
MHL Best GAA (1.79)                           2010-2011          NaN
MHL Best Goaltender                           2010-2011          NaN
MHL Goaltender of the Month (February)        2010-2011          NaN
MHL Goaltender of the Month (September)       2010-2011          NaN
Russia2 Silver Medal                                NaN    2009-2010
VHL Goaltender of the Month (November)        2021-2022          NaN
VHL Playoffs Best GAA (1.39)                  2017-2018          NaN
VHL Silver Medal                                    NaN    2013-2014
    Login or Signup to reply.
  2. 0

    Another version:

    df['awards'] = df['awards'].apply(lambda x: {v[0]: k for k, v in x.items()})
df = pd.concat([df, df.pop('awards').apply(pd.Series)], axis=1)

print(df)

    Prints:

           player_name Russia2 Silver Medal VHL Silver Medal MHL All-Star Game VHL Playoffs Best GAA (1.39) VHL Goaltender of the Month (November)
0     Sergei Belov            2009-2010        2013-2014               NaN                          NaN                                    NaN
1  Rafael Khakimov                  NaN              NaN         2010-2011                    2017-2018                              2021-2022
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search