KOMCC

import numpy as np
import pandas as pd

#url = 'https://docs.google.com/spreadsheets/d/1PD9qEi3iQPpowmV70V7dEGc3SzAmam2ePtYsszWA21I/edit?usp=sharing'

#Load csv
club = pd.read_csv('Club_Source 1802 - data-2023-2-4-14 (1).csv')
 
#Drop useless
#club = club.drop(columns=['Athlete','Pace','Unit','Calo','EstPace','EstSpeed'])

#Filter date
club['Date'] = pd.to_datetime(club['Date'], utc=True)
club.drop(club[club['Date'] < '2023-01-01'].index, inplace=True)

#Filter hashtag
club['Location'] = club['Location'].fillna('0')
club = club.loc[club['Location'].str.contains('velody_komcc',case=False)]

#Add double for 5 teammates
club['Double'] = club['Double'].fillna(1)


#Add Instagram + Photobonus
club['Inst_Bonus'] = club['Inst_Bonus'].fillna(0)


#Elevation rating
club['Elev'] = club['Elev'].fillna(0)
club['Elev_rating'] = club[club['Elev'] >= 100]['Elev'].apply(pd.to_numeric) /10
club['Elev_rating'] = club['Elev_rating'].fillna(0)

#Cycling rating
club['Cyc_rating'] = club.query("Type == ['Virtual Ride','Ride']")['Distance'].apply(pd.to_numeric)
club['Cyc_rating'] = club['Cyc_rating'].fillna(0)
club['Cyc_rating'] = club['Cyc_rating'].round(1)
club['Cyc_rating'] = club['Cyc_rating'] * club['Double']

#Running rating
club['Run_rating'] = club.query("Type == 'Run'")['Distance'].apply(pd.to_numeric) *3
club['Run_rating'] = club['Run_rating'].fillna(0)
club['Run_rating'] = club['Run_rating'].round(1)

#Swim rating
#club['Swim_rating'] = club[club['Type'] == 'Swim']['Distance']
#club['Swim_rating'] = club['Swim_rating'].str.replace(',', '.').astype(float) * 20
#club['Swim_rating'] = club['Swim_rating'].fillna(0)

#Ski rating
#club['Ski_rating'] = club.query("Type == ['Nordic Ski','Backcountry Ski']")['Distance'].apply(pd.to_numeric) *3
#club['Ski_rating'] = club['Ski_rating'].fillna(0)
#club['Ski_rating'] = club['Ski_rating'].round(1)

#Multi rating
club['Multi_rating'] = club.query("Type != ['Virtual Ride','Run','Ride']")['Duration']
club['Multi_rating'] = pd.to_timedelta(club['Multi_rating'])
club['Multi_rating'] = club['Multi_rating'].dt.total_seconds() /120
club['Multi_rating'] = club['Multi_rating'].fillna(0)
club['Multi_rating'] = club['Multi_rating'].round(1)

#Total Rating
club['Total'] = club['Cyc_rating'] + club['Run_rating'] + club['Multi_rating'] + club['Elev_rating'] +club['Inst_Bonus']
club['Total'] = club['Total'].round(1)


#Weekend subset
weekend = (club['Date'] >= '2022-12-24 00:00:00+04:00') & (club['Date'] <= '2022-12-31 23:59:59+04:00')
weekend_df = club.loc[weekend]
weekend_df = weekend_df[weekend_df.Type == 'Virtual Ride']
weekend_df.Distance = weekend_df.Distance.astype(float)
#Festive500
#print(weekend_df.groupby('Name').Distance.sum().sort_values(ascending=False))
                
#Teams
#oleg_list = ['Карнаухов Олег','Антон Матишак VCC','Maxim Korobtsev']
#tema_list = ['Tema Skalaban', 'Andrey Komov | VCC', 'Mark Frolov']
#nastya_list = ['Анастасия Петрашкова','Стас Пустов','Yaroslav Teletsky']

#team_oleg = weekend_df[weekend_df['Name'].isin(oleg_list)]
#team_tema = weekend_df[weekend_df['Name'].isin(tema_list)]
#team_nastya = weekend_df[weekend_df['Name'].isin(nastya_list)]

#print('Team Oleg: ' + str(team_oleg['Total'].sum()))
#print('Team Tema: ' + str(team_tema['Total'].sum()))
#print('Team Nastya: ' + str(team_nastya['Total'].sum()))


#Final Top, Swim, Ski
top = club.groupby('Name')['Total'].sum().sort_values(ascending=False)
#swim = club.groupby('Name')['Swim_rating'].sum().sort_values(ascending=False)
#ski = club.groupby('Name')['Ski_rating'].sum().sort_values(ascending=False)
multi = club.groupby('Name')['Multi_rating'].sum().sort_values(ascending=False)

#Final Cycle
cycle_top = club[club['Cyc_rating'] != 0]
cycle_top['Cyc_rating'] = cycle_top['Cyc_rating'] + cycle_top['Elev_rating']
cycle = cycle_top.groupby('Name')['Cyc_rating'].sum().sort_values(ascending=False) #Final table to print

#Final Run
run_top = club[club['Run_rating'] != 0]
run_top['Run_rating'] = run_top['Run_rating'] + run_top['Elev_rating']
run = run_top.groupby('Name')['Run_rating'].sum().sort_values(ascending=False) #Final table to print

#Final Multi
multi_top = club[club['Multi_rating'] != 0]
multi_top['Multi_rating'] = multi_top['Multi_rating'] + multi_top['Elev_rating']
multi = multi_top.groupby('Name')['Multi_rating'].sum().sort_values(ascending=False) #Final table to print

#print(club)

#print(club.groupby('Name')['Location'].count().sort_values(ascending=False))


print(multi)