I am trying to extract nested dicts into my data frame, the data is current stored in a column in the data frame and struggling too extract in to the row with attributes as column headers. I have tried breaking it down further to stats from data but get errors. Can anyone help please?
text_last5610 = responseteamlast5610.content
tree_last5610 = json.loads(text_last5610)
d_last5610 = tree_last5610["data"]
df_last5610 = pd.DataFrame(d_last5610)
df_last5610.head()
enter image description here
enter image description here
{
"data": [
{
"alt_names": [
"The Clarets"
],
"competition_id": -1,
"continent": null,
"country": "England",
"english_name": "Burnley FC",
"founded": "1882",
"full_name": "Burnley FC",
"id": 145,
"image": "https://cdn.footystats.org/img/teams/england-burnley-fc.png",
"last_updated_match_timestamp": 1639317600,
"last_x_home_away_or_overall": "0",
"last_x_match_num": 5,
"name": "Burnley",
"official_sites": [
"https://www.burnleyfootballclub.com/"
],
"performance_rank": 0,
"risk": 60,
"season": "2021",
"season_format": "Domestic League",
"stats": {
"AVGHT_away": 0.67,
"AVGHT_home": 2.5,
"AVGHT_overall": 1.4,
"AVG_2hg_away": 0.33,
"AVG_2hg_home": 0.5,
"AVG_2hg_overall": 0.4,
"BTTS_and_draw_away": 1,
"BTTS_and_draw_home": 1,
"BTTS_and_draw_overall": 2,
"BTTS_and_draw_percentage_away": 33,
"BTTS_and_draw_percentage_home": 50,
"BTTS_and_draw_percentage_overall": 40,
"BTTS_and_lose_away": 0,
"BTTS_and_lose_home": 0,
"BTTS_and_lose_overall": 0,
"BTTS_and_lose_percentage_away": 0,
"BTTS_and_lose_percentage_home": 0,
"BTTS_and_lose_percentage_overall": 0,
"BTTS_and_win_away": 0,
"BTTS_and_win_home": 0,
"BTTS_and_win_overall": 0,
"BTTS_and_win_percentage_away": 0,
"BTTS_and_win_percentage_home": 0,
"BTTS_and_win_percentage_overall": 0,
"BTTS_both_halves_away": 0,
"BTTS_both_halves_home": 0,
"BTTS_both_halves_overall": 0,
"BTTS_both_halves_percentage_away": 0,
"BTTS_both_halves_percentage_home": 0,
"BTTS_both_halves_percentage_overall": 0,
"GoalDifferenceHT_away": -2,
"GoalDifferenceHT_home": -1,
"GoalDifferenceHT_overall": -3,
"GoalsHT_away": 2,
"GoalsHT_home": 5,
"GoalsHT_overall": 7,
"HTPPG_away": 0.33,
"HTPPG_home": 0.5,
"HTPPG_overall": 0.4,
"HTPoints_away": 1,
"HTPoints_home": 1,
"HTPoints_overall": 2,
"additional_info": {
"2h_cards_against_avg_away": 1,
"2h_cards_against_avg_home": 1.5,
"2h_cards_against_avg_overall": 1.2,
"2h_cards_against_away": 3,
"2h_cards_against_home": 3,
"2h_cards_against_overall": 6,
"2h_cards_for_avg_away": 2,
"2h_cards_for_avg_home": 0.5,
"2h_cards_for_avg_overall": 1.4,
"2h_cards_for_away": 6,
"2h_cards_for_home": 1,
"2h_cards_for_over05_percentage_away": 100,
"2h_cards_for_over05_percentage_home": 50,
"2h_cards_for_over05_percentage_overall": 80,
"2h_cards_for_overall": 7,
"2h_cards_total_avg_away": 3,
"2h_cards_total_avg_home": 2,
"2h_cards_total_avg_overall": 2.6,
"2h_cards_total_away": 9,
"2h_cards_total_home": 4,
"2h_cards_total_overall": 13,
"2h_half_with_most_cards_total_percentage_away": 67,
"2h_half_with_most_cards_total_percentage_home": 50,
"2h_half_with_most_cards_total_percentage_overall": 60,
"2h_total_cards_2to3_percentage_away": 67,
"2h_total_cards_2to3_percentage_home": 50,
"2h_total_cards_2to3_percentage_overall": 60,
"2h_total_cards_over3_percentage_away": 33,
"2h_total_cards_over3_percentage_home": 0,
"2h_total_cards_over3_percentage_overall": 20,
"2h_total_cards_under2_percentage_away": 0,
"2h_total_cards_under2_percentage_home": 50,
"2h_total_cards_under2_percentage_overall": 20,
"attack_num_recoded_matches_away": 3,
"attack_num_recoded_matches_home": 1,
"attacks_num_away": 302,
"attacks_num_home": 103,
"btts_1h2h_no_no_num_away": 3,
"btts_1h2h_no_no_num_home": 1,
"btts_1h2h_no_no_num_overall": 4,
"btts_1h2h_no_no_percentage_away": 100,
"btts_1h2h_no_no_percentage_home": 50,
"btts_1h2h_no_no_percentage_overall": 80,
"btts_1h2h_no_yes_num_away": 0,
"btts_1h2h_no_yes_num_home": 0,
"btts_1h2h_no_yes_num_overall": 0,
"btts_1h2h_no_yes_percentage_away": 0,
"btts_1h2h_no_yes_percentage_home": 0,
"btts_1h2h_no_yes_percentage_overall": 0,
"btts_1h2h_yes_no_num_away": 0,
"btts_1h2h_yes_no_num_home": 1,
"btts_1h2h_yes_no_num_overall": 1,
"btts_1h2h_yes_no_percentage_away": 0,
"btts_1h2h_yes_no_percentage_home": 50,
"btts_1h2h_yes_no_percentage_overall": 20,
"btts_1h2h_yes_yes_num_away": 0,
"btts_1h2h_yes_yes_num_home": 0,
"btts_1h2h_yes_yes_num_overall": 0,
"btts_1h2h_yes_yes_percentage_away": 0,
"btts_1h2h_yes_yes_percentage_home": 0,
"btts_1h2h_yes_yes_percentage_overall": 0,
"cardTimingRecorded_matches_away": 3,
"cardTimingRecorded_matches_home": 2,
"cardTimingRecorded_matches_overall": 5,
"cardsRecorded_matches_away": 3,
"cardsRecorded_matches_home": 2,
"cardsRecorded_matches_overall": 5,
"cards_against_avg_away": 1.33,
"cards_against_avg_home": 2,
"cards_against_avg_overall": 1.6,
"cards_against_away": 4,
"cards_against_home": 4,
"cards_against_overall": 8,
"cards_for_avg_away": 2.67,
"cards_for_avg_home": 2,
"cards_for_avg_overall": 2.4,
"cards_for_away": 8,
"cards_for_home": 4,
"cards_for_overall": 12,
"cards_total_avg_away": 4,
"cards_total_avg_home": 4,
"cards_total_avg_overall": 4,
"cards_total_away": 12,
"cards_total_home": 8,
"cards_total_overall": 20,
"corners_earned_1h_2_to_3_num_away": 1,
"corners_earned_1h_2_to_3_num_home": 0,
"corners_earned_1h_2_to_3_num_overall": 1,
"corners_earned_1h_2_to_3_percentage_away": 33,
"corners_earned_1h_2_to_3_percentage_home": 0,
"corners_earned_1h_2_to_3_percentage_overall": 25,
"corners_earned_1h_avg_away": 1.67,
"corners_earned_1h_avg_home": 1,
"corners_earned_1h_avg_overall": 1.5,
"corners_earned_1h_num_away": 5,
"corners_earned_1h_num_home": 1,
"corners_earned_1h_num_overall": 6,
"corners_earned_1h_over2_num_away": 1,
"corners_earned_1h_over2_num_home": 0,
"corners_earned_1h_over2_num_overall": 1,
"corners_earned_1h_over2_percentage_away": 33,
"corners_earned_1h_over2_percentage_home": 0,
"corners_earned_1h_over2_percentage_overall": 25,
"corners_earned_1h_over3_num_away": 0,
"corners_earned_1h_over3_num_home": 0,
"corners_earned_1h_over3_num_overall": 0,
"corners_earned_1h_over3_percentage_away": 0,
"corners_earned_1h_over3_percentage_home": 0,
"corners_earned_1h_over3_percentage_overall": 0,
"corners_earned_2h_2_to_3_num_away": 2,
"corners_earned_2h_2_to_3_num_home": 1,
"corners_earned_2h_2_to_3_num_overall": 3,
"corners_earned_2h_2_to_3_percentage_away": 67,
"corners_earned_2h_2_to_3_percentage_home": 100,
"corners_earned_2h_2_to_3_percentage_overall": 75,
"corners_earned_2h_avg_away": 1.67,
"corners_earned_2h_avg_home": 2,
"corners_earned_2h_avg_overall": 1.75,
"corners_earned_2h_num_away": 5,
"corners_earned_2h_num_home": 2,
"corners_earned_2h_num_overall": 7,
"corners_earned_2h_over2_num_away": 0,
"corners_earned_2h_over2_num_home": 0,
"corners_earned_2h_over2_num_overall": 0,
"corners_earned_2h_over2_percentage_away": 0,
"corners_earned_2h_over2_percentage_home": 0,
"corners_earned_2h_over2_percentage_overall": 0,
"corners_earned_2h_over3_num_away": 0,
"corners_earned_2h_over3_num_home": 0,
"corners_earned_2h_over3_num_overall": 0,
"corners_earned_2h_over3_percentage_away": 0,
"corners_earned_2h_over3_percentage_home": 0,
"corners_earned_2h_over3_percentage_overall": 0,
"dangerous_attacks_num_away": 108,
"dangerous_attacks_num_home": 35,
"draw_0_10_num_away": 3,
"draw_0_10_num_home": 1,
"draw_0_10_num_overall": 4,
"draw_0_10_percentage_away": 100,
"draw_0_10_percentage_home": 50,
"draw_0_10_percentage_overall": 80,
"exact_team_goals_0_ft_away": 2,
"exact_team_goals_0_ft_overall": 3,
"exact_team_goals_0_ft_percentage_away": 67,
"exact_team_goals_0_ft_percentage_home": 50,
"exact_team_goals_0_ft_percentage_overall": 60,
"exact_team_goals_1_ft_away": 1,
"exact_team_goals_1_ft_home": 0,
"exact_team_goals_1_ft_overall": 1,
"exact_team_goals_1_ft_percentage_away": 33,
"exact_team_goals_1_ft_percentage_home": 0,
"exact_team_goals_1_ft_percentage_overall": 20,
"exact_team_goals_2_ft_away": 0,
"exact_team_goals_2_ft_home": 0,
"exact_team_goals_2_ft_overall": 0,
"exact_team_goals_2_ft_percentage_away": 0,
"exact_team_goals_2_ft_percentage_home": 0,
"exact_team_goals_2_ft_percentage_overall": 0,
"exact_team_goals_3_ft_away": 0,
"exact_team_goals_3_ft_home": 1,
"exact_team_goals_3_ft_overall": 1,
"exact_team_goals_3_ft_percentage_away": 0,
"exact_team_goals_3_ft_percentage_home": 50,
"exact_team_goals_3_ft_percentage_overall": 20,
"exact_total_goals_0_ft_away": 1,
"exact_total_goals_0_ft_home": 1,
"exact_total_goals_0_ft_overall": 2,
"exact_total_goals_1_ft_away": 1,
"exact_total_goals_1_ft_home": 0,
"exact_total_goals_1_ft_overall": 1,
"exact_total_goals_2_ft_away": 1,
"exact_total_goals_2_ft_home": 0,
"exact_total_goals_2_ft_overall": 1,
"exact_total_goals_3_ft_away": 0,
"exact_total_goals_3_ft_home": 0,
"exact_total_goals_3_ft_overall": 0,
"exact_total_goals_4_ft_away": 0,
"exact_total_goals_4_ft_home": 0,
"exact_total_goals_4_ft_overall": 0,
"exact_total_goals_5_ft_away": 0,
"exact_total_goals_5_ft_home": 0,
"exact_total_goals_5_ft_overall": 0,
"exact_total_goals_6_ft_away": 0,
"exact_total_goals_6_ft_home": 1,
"exact_total_goals_6_ft_overall": 1,
"exact_total_goals_7_ft_away": 0,
"exact_total_goals_7_ft_home": 0,
"exact_total_goals_7_ft_overall": 0,
"fh_cards_against_avg_away": 0.33,
"fh_cards_against_avg_home": 0.5,
"fh_cards_against_avg_overall": 0.4,
"fh_cards_against_away": 1,
"fh_cards_against_home": 1,
"fh_cards_against_overall": 2,
"fh_cards_for_avg_away": 0.67,
"fh_cards_for_avg_home": 1.5,
"fh_cards_for_avg_overall": 1,
"fh_cards_for_away": 2,
"fh_cards_for_home": 3,
"fh_cards_for_over05_percentage_away": 33,
"fh_cards_for_over05_percentage_home": 100,
"fh_cards_for_over05_percentage_overall": 60,
"fh_cards_for_overall": 5,
"fh_cards_total_avg_away": 1,
"fh_cards_total_avg_home": 2,
"fh_cards_total_avg_overall": 1.4,
"fh_cards_total_away": 3,
"fh_cards_total_home": 4,
"fh_cards_total_overall": 7,
"fh_half_with_most_cards_total_percentage_away": 33,
"fh_half_with_most_cards_total_percentage_home": 50,
"fh_half_with_most_cards_total_percentage_overall": 40,
"fh_total_cards_2to3_percentage_away": 33,
"fh_total_cards_2to3_percentage_home": 100,
"fh_total_cards_2to3_percentage_overall": 60,
"fh_total_cards_over3_percentage_away": 0,
"fh_total_cards_over3_percentage_home": 0,
"fh_total_cards_over3_percentage_overall": 0,
"fh_total_cards_under2_percentage_away": 67,
"fh_total_cards_under2_percentage_home": 0,
"fh_total_cards_under2_percentage_overall": 40,
"formRun_away": "ddl",
"formRun_home": "dd",
"formRun_overall": "dddld",
"fouls_against_avg_away": 6.67,
"fouls_against_avg_home": 15,
"fouls_against_avg_overall": 8.75,
"fouls_against_num_away": 20,
"fouls_against_num_home": 15,
"fouls_against_num_overall": 35,
"fouls_recorded_away": 3,
"fouls_recorded_home": 1,
"fouls_recorded_overall": 4,
"freekicks_recorded_matches_away": 0,
"freekicks_recorded_matches_home": 0,
"freekicks_recorded_matches_overall": 0,
"freekicks_team_avg_away": 0,
"freekicks_team_avg_home": 0,
"freekicks_team_avg_overall": 0,
"freekicks_team_num_away": 0,
"freekicks_team_num_home": 0,
"freekicks_team_num_overall": 0,
"freekicks_team_over105_away": 0,
"freekicks_team_over105_home": 0,
"freekicks_team_over105_overall": 0,
"freekicks_team_over115_away": 0,
"freekicks_team_over115_home": 0,
"freekicks_team_over115_overall": 0,
"freekicks_team_over125_away": 0,
"freekicks_team_over125_home": 0,
"freekicks_team_over125_overall": 0,
"freekicks_team_over135_away": 0,
"freekicks_team_over135_home": 0,
"freekicks_team_over135_overall": 0,
"freekicks_team_over145_away": 0,
"freekicks_team_over145_home": 0,
"freekicks_team_over145_overall": 0,
"freekicks_team_over155_away": 0,
"freekicks_team_over155_home": 0,
"freekicks_team_over155_overall": 0,
"freekicks_team_over165_away": 0,
"freekicks_team_over165_home": 0,
"freekicks_team_over165_overall": 0,
"freekicks_team_over175_away": 0,
"freekicks_team_over175_home": 0,
"freekicks_team_over175_overall": 0,
"freekicks_team_over75_away": 0,
"freekicks_team_over75_home": 0,
"freekicks_team_over75_overall": 0,
"freekicks_team_over85_away": 0,
"freekicks_team_over85_home": 0,
"freekicks_team_over85_overall": 0,
"freekicks_team_over95_away": 0,
"freekicks_team_over95_home": 0,
"freekicks_team_over95_overall": 0,
"freekicks_total_avg_away": 0,
"freekicks_total_avg_home": 0,
"freekicks_total_avg_overall": 0,
"freekicks_total_num_away": 0,
"freekicks_total_num_home": 0,
"freekicks_total_num_overall": 0,
"freekicks_total_over205_away": 0,
"freekicks_total_over205_home": 0,
"freekicks_total_over205_overall": 0,
"freekicks_total_over215_away": 0,
"freekicks_total_over215_home": 0,
"freekicks_total_over215_overall": 0,
"freekicks_total_over225_away": 0,
"freekicks_total_over225_home": 0,
"freekicks_total_over225_overall": 0,
"freekicks_total_over235_away": 0,
"freekicks_total_over235_home": 0,
"freekicks_total_over235_overall": 0,
"freekicks_total_over245_away": 0,
"freekicks_total_over245_home": 0,
"freekicks_total_over245_overall": 0,
"freekicks_total_over255_away": 0,
"freekicks_total_over255_home": 0,
"freekicks_total_over255_overall": 0,
"freekicks_total_over265_away": 0,
"freekicks_total_over265_home": 0,
"freekicks_total_over265_overall": 0,
"freekicks_total_over275_away": 0,
"freekicks_total_over275_home": 0,
"freekicks_total_over275_overall": 0,
"freekicks_total_over285_away": 0,
"freekicks_total_over285_home": 0,
"freekicks_total_over285_overall": 0,
"freekicks_total_over295_away": 0,
"freekicks_total_over295_home": 0,
"freekicks_total_over295_overall": 0,
"freekicks_total_over305_away": 0,
"freekicks_total_over305_home": 0,
"freekicks_total_over305_overall": 0,
"goal_kicks_recorded_matches_away": 0,
"goal_kicks_recorded_matches_home": 0,
"goal_kicks_recorded_matches_overall": 0,
"goal_kicks_team_avg_away": 0,
"goal_kicks_team_avg_home": 0,
"goal_kicks_team_avg_overall": 0,
"goal_kicks_team_num_away": 0,
"goal_kicks_team_num_home": 0,
"goal_kicks_team_num_overall": 0,
"goal_kicks_team_over105_away": 0,
"goal_kicks_team_over105_home": 0,
"goal_kicks_team_over105_overall": 0,
"goal_kicks_team_over115_away": 0,
"goal_kicks_team_over115_home": 0,
"goal_kicks_team_over115_overall": 0,
"goal_kicks_team_over35_away": 0,
"goal_kicks_team_over35_home": 0,
"goal_kicks_team_over35_overall": 0,
"goal_kicks_team_over45_away": 0,
"goal_kicks_team_over45_home": 0,
"goal_kicks_team_over45_overall": 0,
"goal_kicks_team_over55_away": 0,
"goal_kicks_team_over55_home": 0,
"goal_kicks_team_over55_overall": 0,
"goal_kicks_team_over65_away": 0,
"goal_kicks_team_over65_home": 0,
"goal_kicks_team_over65_overall": 0,
"goal_kicks_team_over75_away": 0,
"goal_kicks_team_over75_home": 0,
"goal_kicks_team_over75_overall": 0,
"goal_kicks_team_over85_away": 0,
"goal_kicks_team_over85_home": 0,
"goal_kicks_team_over85_overall": 0,
"goal_kicks_team_over95_away": 0,
"goal_kicks_team_over95_home": 0,
"goal_kicks_team_over95_overall": 0,
"goal_kicks_total_avg_away": 0,
"goal_kicks_total_avg_home": 0,
"goal_kicks_total_avg_overall": 0,
"goal_kicks_total_num_away": 0,
"goal_kicks_total_num_home": 0,
"goal_kicks_total_num_overall": 0,
"goal_kicks_total_over105_away": 0,
"goal_kicks_total_over105_home": 0,
"goal_kicks_total_over105_overall": 0,
"goal_kicks_total_over115_away": 0,
"goal_kicks_total_over115_home": 0,
"goal_kicks_total_over115_overall": 0,
"goal_kicks_total_over125_away": 0,
"goal_kicks_total_over125_home": 0,
"goal_kicks_total_over125_overall": 0,
"goal_kicks_total_over135_away": 0,
"goal_kicks_total_over135_home": 0,
"goal_kicks_total_over135_overall": 0,
"goal_kicks_total_over145_away": 0,
"goal_kicks_total_over145_home": 0,
"goal_kicks_total_over145_overall": 0,
"goal_kicks_total_over155_away": 0,
"goal_kicks_total_over155_home": 0,
"goal_kicks_total_over155_overall": 0,
"goal_kicks_total_over165_away": 0,
"goal_kicks_total_over165_home": 0,
"goal_kicks_total_over165_overall": 0,
"goal_kicks_total_over175_away": 0,
"goal_kicks_total_over175_home": 0,
"goal_kicks_total_over175_overall": 0,
"goal_kicks_total_over185_away": 0,
"goal_kicks_total_over185_home": 0,
"goal_kicks_total_over185_overall": 0,
"goal_kicks_total_over85_away": 0,
"goal_kicks_total_over85_home": 0,
"goal_kicks_total_over85_overall": 0,
"goal_kicks_total_over95_away": 0,
"goal_kicks_total_over95_home": 0,
"goal_kicks_total_over95_overall": 0,
"half_with_most_corners_is_1h_num_away": 1,
"half_with_most_corners_is_1h_num_home": 0,
"half_with_most_corners_is_1h_num_overall": 1,
"half_with_most_corners_is_1h_percentage_away": 33,
"half_with_most_corners_is_1h_percentage_home": 0,
"half_with_most_corners_is_1h_percentage_overall": 25,
"half_with_most_corners_is_2h_num_away": 1,
"half_with_most_corners_is_2h_num_home": 1,
"half_with_most_corners_is_2h_num_overall": 2,
"half_with_most_corners_is_2h_percentage_away": 33,
"half_with_most_corners_is_2h_percentage_home": 100,
"half_with_most_corners_is_2h_percentage_overall": 50,
"half_with_most_corners_is_draw_num_away": 1,
"half_with_most_corners_is_draw_num_home": 0,
"half_with_most_corners_is_draw_num_overall": 1,
"half_with_most_corners_is_draw_percentage_away": 33,
"half_with_most_corners_is_draw_percentage_home": 0,
"half_with_most_corners_is_draw_percentage_overall": 25,
"half_with_most_goals_is_1h_num_away": 1,
"half_with_most_goals_is_1h_num_home": 1,
"half_with_most_goals_is_1h_num_overall": 2,
"half_with_most_goals_is_1h_percentage_away": 33,
"half_with_most_goals_is_1h_percentage_home": 50,
"half_with_most_goals_is_1h_percentage_overall": 40,
"half_with_most_goals_is_2h_num_away": 0,
"half_with_most_goals_is_2h_num_home": 0,
"half_with_most_goals_is_2h_num_overall": 0,
CodePudding user response：
Similar problem was few times on Stackoverflow.
You can use .apply(pd.Series)
stats = df['stats'].apply(pd.Series)
to convert dictionary in column stats into new DataFrame with all values in separated columns.
And later you can use .join()
df = df.join(stats)
to append these columns from new DataFrame to original DataFrame
I also used del to remove column stats from original DataFrame
Minimal working example which everyone can simply copy and run.
json = {
"data": [
{
"id": 145,
"stats": {
"AVGHT_away": 0.67,
"AVGHT_home": 2.5,
"AVGHT_overall": 1.4,
}
},
{
"id": 146,
"stats": {
"AVGHT_away": 1.67,
"AVGHT_home": 0.5,
"AVGHT_overall": 3.4,
}
}
]
}
import pandas as pd
df = pd.DataFrame(json["data"])
print('--- before ---')
print(df)
stats = df['stats'].apply(pd.Series)
print('--- stats ---')
print(stats)
df = df.join(stats)
del df['stats']
print('--- after ---')
print(df)
Result:
--- before ---
id stats
0 145 {'AVGHT_away': 0.67, 'AVGHT_home': 2.5, 'AVGHT...
1 146 {'AVGHT_away': 1.67, 'AVGHT_home': 0.5, 'AVGHT...
--- stats ---
AVGHT_away AVGHT_home AVGHT_overall
0 0.67 2.5 1.4
1 1.67 0.5 3.4
--- after ---
id AVGHT_away AVGHT_home AVGHT_overall
0 145 0.67 2.5 1.4
1 146 1.67 0.5 3.4
EDIT:
It seems sictionary stats has also nested dictionary additional_info so you will have to repeat it for additional_info.
json = {
"data": [
{
"id": 145,
"stats": {
"AVGHT_away": 0.67,
"AVGHT_home": 2.5,
"additional_info": {
"2h_cards_against_avg_away": 1,
"2h_cards_against_avg_home": 1.5,
}
}
},
{
"id": 146,
"stats": {
"AVGHT_away": 1.67,
"AVGHT_home": 0.5,
"additional_info": {
"2h_cards_against_avg_away": 1,
"2h_cards_against_avg_home": 1.5,
}
}
}
]
}
import pandas as pd
df = pd.DataFrame(json["data"])
print('--- before ---')
print(df.to_string())
# ---
stats = df['stats'].apply(pd.Series)
print('--- stats ---')
print(stats.to_string())
# ---
df = df.join(stats)
del df['stats']
print('--- after ---')
print(df.to_string())
# ---
additional_info = df['additional_info'].apply(pd.Series)
print('--- additional_info ---')
print(additional_info)
# ---
df = df.join(additional_info)
del df['additional_info']
print('--- after ---')
print(df.to_string())
Result:
--- before ---
id stats
0 145 {'AVGHT_away': 0.67, 'AVGHT_home': 2.5, 'additional_info': {'2h_cards_against_avg_away': 1, '2h_cards_against_avg_home': 1.5}}
1 146 {'AVGHT_away': 1.67, 'AVGHT_home': 0.5, 'additional_info': {'2h_cards_against_avg_away': 1, '2h_cards_against_avg_home': 1.5}}
--- stats ---
AVGHT_away AVGHT_home additional_info
0 0.67 2.5 {'2h_cards_against_avg_away': 1, '2h_cards_against_avg_home': 1.5}
1 1.67 0.5 {'2h_cards_against_avg_away': 1, '2h_cards_against_avg_home': 1.5}
--- after ---
id AVGHT_away AVGHT_home additional_info
0 145 0.67 2.5 {'2h_cards_against_avg_away': 1, '2h_cards_against_avg_home': 1.5}
1 146 1.67 0.5 {'2h_cards_against_avg_away': 1, '2h_cards_against_avg_home': 1.5}
--- additional_info ---
2h_cards_against_avg_away 2h_cards_against_avg_home
0 1.0 1.5
1 1.0 1.5
--- after ---
id AVGHT_away AVGHT_home 2h_cards_against_avg_away 2h_cards_against_avg_home
0 145 0.67 2.5 1.0 1.5
1 146 1.67 0.5 1.0 1.5