Processing data with Pandas

process.py

# HOMEWORK: 
# The data processed by this code cannot be consumed by my event sender application
# WHY? It should be pretty obvious - drop the comment.
# Here is how the data looks like:
# [
#  {
#    "minute": 1,
#    "event_type": "Foul",
#    "side": "Home",
#    "event_team": "Thunder FC",
#    "opponent": "The Lion",
#    "player": "Diego Weber",
#    "is_goal": false,
#    "location": NaN,
#    "event_type2": NaN,
#    "assisted_by_player": NaN,
#    "shot_place": NaN,
#    "shot_outcome": NaN,
#    "bodypart": NaN,
#    "assist_method": NaN,
#    "situation": NaN,
#    "shot_speed": NaN,
#    "goal_probability": NaN,
#    "distance_to_goal": NaN,
#    "player_in": NaN,
#    "player_out": NaN,
#    "team": "Thunder FC",
#    "minutes_played": 1530.0,
#    "games_played": 17.0,
#    "goals": 8.0
#  },
#  ...
# ]

import pandas as pd
import json

# Load data
with open('event.json', 'r') as f:
    events_df = pd.DataFrame(json.load(f))

#print(events_df.head())

with open('player_stats.json', 'r') as f:
    stats = json.load(f)

#print(stats)

# Flatten player statistics
player_stats = []
for team, data in stats.items():
    for player, stats in data['players'].items():
        player_stats.append({
            'team': team,
            'player': player,
            **stats
        })
#print(json.dumps(player_stats))

# Convert to DataFrame and merge
player_stats_df = pd.DataFrame(player_stats)
#print(player_stats_df.head())

merged = events_df.merge(
    player_stats_df, 
    left_on=['player', 'event_team'],
    right_on=['player', 'team'],
    how='left'
)
print(merged.head())

# Convert merged DataFrame to JSON and save
merged_json = merged.to_dict(orient='records')
with open('merged_football_data.json', 'w') as f:
    json.dump(merged_json, f, indent=2)