SstudizeSA/b2boutliner
0
1# # Import necessary libraries2# from fastapi import FastAPI, HTTPException3# from pydantic import BaseModel4# import gspread5# from google.oauth2.service_account import Credentials6# import pandas as pd7# from collections import defaultdict8# import os9 10# # Initialize the FastAPI app11# app = FastAPI()12 13# # Step 1: Define a function to get Google Sheets API credentials14# def get_credentials():15# """Get Google Sheets API credentials from environment variables."""16# try:17# # Construct the service account info dictionary18# service_account_info = {19# "type": os.getenv("SERVICE_ACCOUNT_TYPE"),20# "project_id": os.getenv("PROJECT_ID"),21# "private_key_id": os.getenv("PRIVATE_KEY_ID"),22# "private_key": os.getenv("PRIVATE_KEY").replace('\\n', '\n'),23# "client_email": os.getenv("CLIENT_EMAIL"),24# "client_id": os.getenv("CLIENT_ID"),25# "auth_uri": os.getenv("AUTH_URI"),26# "token_uri": os.getenv("TOKEN_URI"),27# "auth_provider_x509_cert_url": os.getenv("AUTH_PROVIDER_X509_CERT_URL"),28# "client_x509_cert_url": os.getenv("CLIENT_X509_CERT_URL"),29# "universe_domain": os.getenv("UNIVERSE_DOMAIN")30# }31# scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']32# creds = Credentials.from_service_account_info(service_account_info, scopes=scope)33# return creds34 35# except Exception as e:36# print(f"Error getting credentials: {e}")37# return None38 39# # Step 2: Authorize gspread using the credentials40# creds = get_credentials()41# client = gspread.authorize(creds)42 43# # Input the paths and coaching code44# journal_file_path = ''45# panic_button_file_path = ''46# test_file_path = ''47# coachingCode = '1919'48 49# if coachingCode == '1919':50# journal_file_path = 'https://docs.google.com/spreadsheets/d/1EFf2lr4A10nt4RhIqxCD_fxe-l3sXH09II0TEkMmvhA/edit?usp=drive_link'51# panic_button_file_path = 'https://docs.google.com/spreadsheets/d/1nFZGkCvRV6qS-mhsORhX3dxI0JSge32_UwWgWKl3eyw/edit?usp=drive_link'52# test_file_path = 'https://docs.google.com/spreadsheets/d/13PUHySUXWtKBusjugoe7Dbsm39PwBUfG4tGLipspIx4/edit?usp=drive_link'53 54# # Step 3: Open Google Sheets using the URLs55# journal_file = client.open_by_url(journal_file_path).worksheet('Sheet1')56# panic_button_file = client.open_by_url(panic_button_file_path).worksheet('Sheet1') # Fixed missing part57# test_file = client.open_by_url(test_file_path).worksheet('Sheet1')58 59# # Step 4: Convert the sheets into Pandas DataFrames60# journal_df = pd.DataFrame(journal_file.get_all_values())61# panic_button_df = pd.DataFrame(panic_button_file.get_all_values())62# test_df = pd.DataFrame(test_file.get_all_values())63 64# # Label the columns manually since there are no headers65# journal_df.columns = ['user_id', 'productivity_yes_no', 'productivity_rate']66# panic_button_df.columns = ['user_id', 'panic_button']67 68# # Initialize a list for the merged data69# merged_data = []70 71# # Step 5: Group panic buttons by user_id and combine into a single comma-separated string72# panic_button_grouped = panic_button_df.groupby('user_id')['panic_button'].apply(lambda x: ','.join(x)).reset_index()73 74# # Merge journal and panic button data75# merged_journal_panic = pd.merge(journal_df, panic_button_grouped, on='user_id', how='outer')76 77# # Step 6: Process the test data78# test_data = []79# for index, row in test_df.iterrows():80# user_id = row[0]81# i = 182# while i < len(row) and pd.notna(row[i]): # Process chapter and score pairs83# chapter = row[i].lower().strip()84# score = row[i + 1]85# if pd.notna(score):86# test_data.append({'user_id': user_id, 'test_chapter': chapter, 'test_score': score})87# i += 288 89# # Convert the processed test data into a DataFrame90# test_df_processed = pd.DataFrame(test_data)91 92# # Step 7: Merge the journal+panic button data with the test data93# merged_data = pd.merge(merged_journal_panic, test_df_processed, on='user_id', how='outer')94 95# # Step 8: Drop rows where all data (except user_id and test_chapter) is missing96# merged_data_cleaned = merged_data.dropna(subset=['productivity_yes_no', 'productivity_rate', 'panic_button', 'test_chapter'], how='all')97 98# # Group the merged DataFrame by user_id99# df = pd.DataFrame(merged_data_cleaned)100 101# # Function to process panic button counts and test scores102# def process_group(group):103# # Panic button counts104# panic_button_series = group['panic_button'].dropna()105# panic_button_dict = panic_button_series.value_counts().to_dict()106 107# # Test scores aggregation108# test_scores = group[['test_chapter', 'test_score']].dropna()109# test_scores['test_score'] = pd.to_numeric(test_scores['test_score'], errors='coerce')110 111# # Create the test_scores_dict excluding NaN values112# test_scores_dict = test_scores.groupby('test_chapter')['test_score'].mean().dropna().to_dict()113 114# return pd.Series({115# 'productivity_yes_no': group['productivity_yes_no'].iloc[0],116# 'productivity_rate': group['productivity_rate'].iloc[0],117# 'panic_button': panic_button_dict,118# 'test_scores': test_scores_dict119# })120 121# # Apply the group processing function122# merged_df = df.groupby('user_id').apply(process_group).reset_index()123 124# # Step 9: Calculate potential score125# # Panic button weightages126# academic_weights = {'BACKLOGS': -5, 'MISSED CLASSES': -4, 'NOT UNDERSTANDING': -3, 'BAD MARKS': -3, 'LACK OF MOTIVATION': -3}127# non_academic_weights = {'EMOTIONAL FACTORS': -3, 'PROCRASTINATE': -2, 'LOST INTEREST': -4, 'LACK OF FOCUS': -2, 'GOALS NOT ACHIEVED': -2, 'LACK OF DISCIPLINE': -2}128 129# # Max weighted panic score130# max_weighted_panic_score = sum([max(academic_weights.values()) * 3, max(non_academic_weights.values()) * 3])131 132# # Function to calculate potential score133# def calculate_potential_score(row):134# # Test score normalization (70% weightage)135# if row['test_scores']: # Check if test_scores is not empty136# avg_test_score = sum(row['test_scores'].values()) / len(row['test_scores'])137# test_score_normalized = (avg_test_score / 40) * 70 # Scale test score to 70138# else:139# test_score_normalized = 0 # Default value for users with no test scores140 141# # Panic score calculation (20% weightage)142# student_panic_score = 0143# if row['panic_button']: # Ensure panic_button is not NaN or empty144# for factor, count in row['panic_button'].items():145# if factor in academic_weights:146# student_panic_score += academic_weights[factor] * count147# elif factor in non_academic_weights:148# student_panic_score += non_academic_weights[factor] * count149# else:150# student_panic_score = 0 # Default if no panic button issues151 152# # Panic score normalized to 20153# panic_score = 20 * (1 - (student_panic_score / max_weighted_panic_score) if max_weighted_panic_score != 0 else 1)154 155# # Journal score calculation (10% weightage)156# if pd.notna(row['productivity_yes_no']) and row['productivity_yes_no'] == 'Yes':157# if pd.notna(row['productivity_rate']):158# journal_score = (float(row['productivity_rate']) / 10) * 10 # Scale journal score to 10159# else:160# journal_score = 0 # Default if productivity_rate is missing161# elif pd.notna(row['productivity_yes_no']) and row['productivity_yes_no'] == 'No':162# if pd.notna(row['productivity_rate']):163# journal_score = (float(row['productivity_rate']) / 10) * 5 # Scale journal score to 5 if "No"164# else:165# journal_score = 0 # Default if productivity_rate is missing166# else:167# journal_score = 0 # Default if productivity_yes_no is missing168 169# # Total score based on new weightages170# total_potential_score = test_score_normalized + panic_score + journal_score171# return total_potential_score172 173# # Apply potential score calculation to the dataframe174# merged_df['potential_score'] = merged_df.apply(calculate_potential_score, axis=1)175# merged_df['potential_score'] = merged_df['potential_score'].round(2)176 177# # Step 10: Sort by potential score178# sorted_df = merged_df[['user_id', 'potential_score']].sort_values(by='potential_score', ascending=False)179 180# # Step 11: Define API endpoint to get the sorted potential scores181# @app.get("/sorted-potential-scores")182# async def get_sorted_potential_scores():183# try:184# result = sorted_df.to_dict(orient="records")185# return {"sorted_scores": result}186# except Exception as e:187# raise HTTPException(status_code=500, detail=str(e))188 189 190# Import necessary libraries191# from fastapi import FastAPI, HTTPException, Query192# from pydantic import BaseModel193# import gspread194# from google.oauth2.service_account import Credentials195# import pandas as pd196# from collections import defaultdict197# import os198# from fastapi.middleware.cors import CORSMiddleware199# # Initialize the FastAPI app200# app = FastAPI()201# app.add_middleware(202# CORSMiddleware,203# allow_origins=["*"], # You can specify domains instead of "*" to restrict access204# allow_credentials=True,205# allow_methods=["*"], # Allows all HTTP methods (POST, GET, OPTIONS, etc.)206# allow_headers=["*"], # Allows all headers207# )208# # Step 1: Define a function to get Google Sheets API credentials209# def get_credentials():210# """Get Google Sheets API credentials from environment variables."""211# try:212# # Construct the service account info dictionary213# service_account_info = {214# "type": os.getenv("SERVICE_ACCOUNT_TYPE"),215# "project_id": os.getenv("PROJECT_ID"),216# "private_key_id": os.getenv("PRIVATE_KEY_ID"),217# "private_key": os.getenv("PRIVATE_KEY").replace('\\n', '\n'),218# "client_email": os.getenv("CLIENT_EMAIL"),219# "client_id": os.getenv("CLIENT_ID"),220# "auth_uri": os.getenv("AUTH_URI"),221# "token_uri": os.getenv("TOKEN_URI"),222# "auth_provider_x509_cert_url": os.getenv("AUTH_PROVIDER_X509_CERT_URL"),223# "client_x509_cert_url": os.getenv("CLIENT_X509_CERT_URL"),224# "universe_domain": os.getenv("UNIVERSE_DOMAIN")225# }226# scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']227# creds = Credentials.from_service_account_info(service_account_info, scopes=scope)228# return creds229 230# except Exception as e:231# print(f"Error getting credentials: {e}")232# return None233 234# # Step 2: Authorize gspread using the credentials235# creds = get_credentials()236# client = gspread.authorize(creds)237 238# # Function to get file paths based on coaching code239# def get_file_paths(coaching_code):240# if coaching_code == '1919':241# return {242# 'journal': 'https://docs.google.com/spreadsheets/d/1EFf2lr4A10nt4RhIqxCD_fxe-l3sXH09II0TEkMmvhA/edit?usp=drive_link',243# 'panic_button': 'https://docs.google.com/spreadsheets/d/1nFZGkCvRV6qS-mhsORhX3dxI0JSge32_UwWgWKl3eyw/edit?usp=drive_link',244# 'test': 'https://docs.google.com/spreadsheets/d/13PUHySUXWtKBusjugoe7Dbsm39PwBUfG4tGLipspIx4/edit?usp=drive_link'245# }246# if coaching_code == '0946':247# return {248# 'journal': 'https://docs.google.com/spreadsheets/d/1c1TkL7sOUvFn6UPz3gwp135UVjOou9u1weohWzpmx6I/edit?usp=drive_link',249# 'panic_button': 'https://docs.google.com/spreadsheets/d/1RhbPQnNNBUthKKJyoW4q6x3uaWl1YSqmsFlfJ2THphE/edit?usp=drive_link',250# 'test': 'https://docs.google.com/spreadsheets/d/1JO5wDkfl2fr2ZQenI8OEu48jkWm48veYN1Fsw5Ctkzw/edit?usp=drive_link'251# }252# # Panic button weightages253# academic_weights = {'BACKLOGS': -5, 'MISSED CLASSES': -4, 'NOT UNDERSTANDING': -3, 'BAD MARKS': -3, 'LACK OF MOTIVATION': -3}254# non_academic_weights = {'EMOTIONAL FACTORS': -3, 'PROCRASTINATE': -2, 'LOST INTEREST': -4, 'LACK OF FOCUS': -2, 'GOALS NOT ACHIEVED': -2, 'LACK OF DISCIPLINE': -2}255 256# # Max weighted panic score257# max_weighted_panic_score = sum([max(academic_weights.values()) * 3, max(non_academic_weights.values()) * 3])258 259# # Function to calculate potential score260# def calculate_potential_score(row):261# # Test score normalization (70% weightage)262# if row['test_scores']: # Check if test_scores is not empty263# avg_test_score = sum(row['test_scores'].values()) / len(row['test_scores'])264# test_score_normalized = (avg_test_score / 40) * 70 # Scale test score to 70265# else:266# test_score_normalized = 0 # Default value for users with no test scores267 268# # Panic score calculation (20% weightage)269# student_panic_score = 0270# if row['panic_button']: # Ensure panic_button is not NaN or empty271# for factor, count in row['panic_button'].items():272# if factor in academic_weights:273# student_panic_score += academic_weights[factor] * count274# elif factor in non_academic_weights:275# student_panic_score += non_academic_weights[factor] * count276# else:277# student_panic_score = 0 # Default if no panic button issues278 279# # Panic score normalized to 20280# panic_score = 20 * (1 - (student_panic_score / max_weighted_panic_score) if max_weighted_panic_score != 0 else 1)281 282# # Journal score calculation (10% weightage)283# if pd.notna(row['productivity_yes_no']) and row['productivity_yes_no'] == 'Yes':284# if pd.notna(row['productivity_rate']):285# journal_score = (float(row['productivity_rate']) / 10) * 10 # Scale journal score to 10286# else:287# journal_score = 0 # Default if productivity_rate is missing288# elif pd.notna(row['productivity_yes_no']) and row['productivity_yes_no'] == 'No':289# if pd.notna(row['productivity_rate']):290# journal_score = (float(row['productivity_rate']) / 10) * 5 # Scale journal score to 5 if "No"291# else:292# journal_score = 0 # Default if productivity_rate is missing293# else:294# journal_score = 0 # Default if productivity_yes_no is missing295 296# # Total score based on new weightages297# total_potential_score = test_score_normalized + panic_score + journal_score298# return total_potential_score299 300# # Step 11: Define API endpoint to get the sorted potential scores301# @app.get("/sorted-potential-scores")302# async def get_sorted_potential_scores(coaching_code: str = Query(..., description="Coaching code to determine file paths")):303# try:304# file_paths = get_file_paths(coaching_code)305# if not file_paths:306# raise HTTPException(status_code=400, detail="Invalid coaching code")307# print("A");308# # Open Google Sheets using the URLs309# journal_file = client.open_by_url(file_paths['journal']).worksheet('Sheet1')310# panic_button_file = client.open_by_url(file_paths['panic_button']).worksheet('Sheet1')311# test_file = client.open_by_url(file_paths['test']).worksheet('Sheet1')312# print("B");313# # Convert the sheets into Pandas DataFrames314# journal_df = pd.DataFrame(journal_file.get_all_values())315# panic_button_df = pd.DataFrame(panic_button_file.get_all_values())316# test_df = pd.DataFrame(test_file.get_all_values())317# print("C");318# # Label the columns manually since there are no headers319# journal_df.columns = ['user_id', 'productivity_yes_no', 'productivity_rate']320# panic_button_df.columns = ['user_id', 'panic_button']321# print("D")322# # Initialize a list for the merged data323# merged_data = []324 325# # Group panic buttons by user_id and combine into a single comma-separated string326# panic_button_grouped = panic_button_df.groupby('user_id')['panic_button'].apply(lambda x: ','.join(x)).reset_index()327# print("E")328# # Merge journal and panic button data329# merged_journal_panic = pd.merge(journal_df, panic_button_grouped, on='user_id', how='outer')330# print("F")331# # Process the test data332# test_data = []333# for index, row in test_df.iterrows():334# user_id = row[0]335# i = 1336# while i < len(row) and pd.notna(row[i]): # Process chapter and score pairs337# chapter = row[i].lower().strip()338# score = row[i + 1]339# if pd.notna(score):340# test_data.append({'user_id': user_id, 'test_chapter': chapter, 'test_score': score})341# i += 2342# print("G")343# # Convert the processed test data into a DataFrame344# test_df_processed = pd.DataFrame(test_data)345# print("H")346# # Merge the journal+panic button data with the test data347# merged_data = pd.merge(merged_journal_panic, test_df_processed, on='user_id', how='outer')348# print("I")349# # Drop rows where all data (except user_id and test_chapter) is missing350# merged_data_cleaned = merged_data.dropna(subset=['productivity_yes_no', 'productivity_rate', 'panic_button', 'test_chapter'], how='all')351# print("J")352# # Group the merged DataFrame by user_id353# df = pd.DataFrame(merged_data_cleaned)354# print("K")355# # Function to process panic button counts and test scores356# def process_group(group):357# # Panic button counts358# panic_button_series = group['panic_button'].dropna()359# panic_button_dict = panic_button_series.value_counts().to_dict()360 361# # Test scores aggregation362# test_scores = group[['test_chapter', 'test_score']].dropna()363# test_scores['test_score'] = pd.to_numeric(test_scores['test_score'], errors='coerce')364 365# # Create the test_scores_dict excluding NaN values366# test_scores_dict = test_scores.groupby('test_chapter')['test_score'].mean().dropna().to_dict()367 368# return pd.Series({369# 'productivity_yes_no': group['productivity_yes_no'].iloc[0],370# 'productivity_rate': group['productivity_rate'].iloc[0],371# 'panic_button': panic_button_dict,372# 'test_scores': test_scores_dict373# })374 375# # Apply the group processing function376# merged_df = df.groupby('user_id').apply(process_group).reset_index()377# print("L")378# # Calculate potential scores and sort379# merged_df['potential_score'] = merged_df.apply(calculate_potential_score, axis=1)380# merged_df['potential_score'] = merged_df['potential_score'].round(2)381# sorted_df = merged_df[['user_id', 'potential_score']].sort_values(by='potential_score', ascending=False)382# print("M")383# result = sorted_df.to_dict(orient="records")384# return {"sorted_scores": result}385# except Exception as e:386# raise HTTPException(status_code=500, detail=str(e))387 388 389 390 391from fastapi import FastAPI, HTTPException, Query392from pydantic import BaseModel393import gspread394from google.oauth2.service_account import Credentials395import pandas as pd396from collections import defaultdict397import os398from fastapi.middleware.cors import CORSMiddleware399app = FastAPI()400app.add_middleware(401 CORSMiddleware,402 allow_origins=["*"], # You can specify domains instead of "*" to restrict access403 allow_credentials=True,404 allow_methods=["*"], # Allows all HTTP methods (POST, GET, OPTIONS, etc.)405 allow_headers=["*"], # Allows all headers406)407 408# Model for request409class CoachingCodeRequest(BaseModel):410 coachingCode: str411 412# Function to get credentials413def get_credentials():414 """Get Google Sheets API credentials from environment variables."""415 try:416 # Construct the service account info dictionary417 service_account_info = {418 "type": os.getenv("SERVICE_ACCOUNT_TYPE"),419 "project_id": os.getenv("PROJECT_ID"),420 "private_key_id": os.getenv("PRIVATE_KEY_ID"),421 "private_key": os.getenv("PRIVATE_KEY").replace('\\n', '\n'),422 "client_email": os.getenv("CLIENT_EMAIL"),423 "client_id": os.getenv("CLIENT_ID"),424 "auth_uri": os.getenv("AUTH_URI"),425 "token_uri": os.getenv("TOKEN_URI"),426 "auth_provider_x509_cert_url": os.getenv("AUTH_PROVIDER_X509_CERT_URL"),427 "client_x509_cert_url": os.getenv("CLIENT_X509_CERT_URL"),428 "universe_domain": os.getenv("UNIVERSE_DOMAIN")429 }430 scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']431 creds = Credentials.from_service_account_info(service_account_info, scopes=scope)432 return creds433 434 except Exception as e:435 print(f"Error getting credentials: {e}")436 return None437 438 439# Select files based on coaching code440def select_files(coaching_code):441 creds = get_credentials()442 client = gspread.authorize(creds)443 444 if coaching_code == "1919":445 journal_file = client.open_by_url('https://docs.google.com/spreadsheets/d/1EFf2lr4A10nt4RhIqxCD_fxe-l3sXH09II0TEkMmvhA/edit?gid=0#gid=0').worksheet('Sheet1')446 panic_button_file = client.open_by_url('https://docs.google.com/spreadsheets/d/1nFZGkCvRV6qS-mhsORhX3dxI0JSge32_UwWgWKl3eyw/edit?gid=0#gid=0').worksheet('Sheet1')447 test_file = client.open_by_url('https://docs.google.com/spreadsheets/d/13PUHySUXWtKBusjugoe7Dbsm39PwBUfG4tGLipspIx4/edit?gid=0#gid=0').worksheet('Sheet1')448 elif coaching_code == "1099":449 journal_file = client.open_by_url('https://docs.google.com/spreadsheets/d/12UQzr7xy70-MvbKUuqM6YMUF-y2kY1rumX0vOj0hKXI/edit?gid=0#gid=0').worksheet('Sheet1')450 panic_button_file = client.open_by_url('https://docs.google.com/spreadsheets/d/1zaKSRKgf2Nd7lWIf315YzvQeTQ3gU_PIRIS_bEAhl90/edit?gid=0#gid=0').worksheet('Sheet1')451 test_file = client.open_by_url('https://docs.google.com/spreadsheets/d/1ms_SdloQqlXO85NK_xExhHT0LEeLsth0VBmdHQt55jc/edit?gid=0#gid=0').worksheet('Sheet1')452 else:453 raise HTTPException(status_code=404, detail="Invalid coaching code")454 455 return journal_file, panic_button_file, test_file456 457# Main route to get sorted scores458@app.post("/get_sorted_scores")459async def get_sorted_scores(data: CoachingCodeRequest):460 journal_file, panic_button_file, test_file = select_files(data.coachingCode)461 462 # Load data into DataFrames463 journal_df = pd.DataFrame(journal_file.get_all_values())464 panic_button_df = pd.DataFrame(panic_button_file.get_all_values())465 test_df = pd.DataFrame(test_file.get_all_values())466 467 # Processing logic468 panic_data = []469 for index, row in panic_button_df.iterrows():470 user_id = row[0]471 row_pairs = row[1:].dropna().to_list()[-5:]472 for i in range(0, len(row_pairs), 2):473 panic = row_pairs[i].upper().strip()474 if pd.notna(panic):475 panic_data.append({'user_id': user_id, 'panic_button': panic})476 panic_df_processed = pd.DataFrame(panic_data)477 478 test_data = []479 for index, row in test_df.iterrows():480 user_id = row[0]481 row_pairs = row[1:].dropna().to_list()482 chapter_scores = {}483 for i in range(0, len(row_pairs), 2):484 chapter = row_pairs[i].lower().strip()485 score = row_pairs[i + 1]486 if pd.notna(score):487 if chapter not in chapter_scores:488 chapter_scores[chapter] = []489 chapter_scores[chapter].append(score)490 for chapter, scores in chapter_scores.items():491 last_5_scores = scores[-5:]492 for score in last_5_scores:493 test_data.append({'user_id': user_id, 'test_chapter': chapter, 'test_score': score})494 test_df_processed = pd.DataFrame(test_data)495 496 journal_data = []497 for index, row in journal_df.iterrows():498 user_id = row[0]499 row_pairs = row[1:].dropna().to_list()[-10:]500 for i in range(0, len(row_pairs), 2):501 productivity_yes_no = row_pairs[i].lower().strip()502 productivity_rate = row_pairs[i + 1]503 if pd.notna(productivity_rate):504 journal_data.append({'user_id': user_id, 'productivity_yes_no': productivity_yes_no, 'productivity_rate': productivity_rate})505 journal_df_processed = pd.DataFrame(journal_data)506 507 merged_journal_panic = pd.merge(panic_df_processed, journal_df_processed, on='user_id', how='outer')508 merged_data = pd.merge(merged_journal_panic, test_df_processed, on='user_id', how='outer')509 merged_data_cleaned = merged_data.dropna(subset=['productivity_yes_no', 'productivity_rate', 'panic_button', 'test_chapter'], how='all')510 511 def process_group(group):512 # Panic button counts513 panic_button_series = group['panic_button'].dropna()514 panic_button_dict = panic_button_series.value_counts().to_dict()515 516 # Test scores aggregation517 test_scores = group[['test_chapter', 'test_score']].dropna()518 test_scores['test_score'] = pd.to_numeric(test_scores['test_score'], errors='coerce')519 520 # Create the test_scores_dict excluding NaN values521 test_scores_dict = test_scores.groupby('test_chapter')['test_score'].mean().dropna().to_dict()522 523 return pd.Series({524 'productivity_yes_no': group['productivity_yes_no'].iloc[0],525 'productivity_rate': group['productivity_rate'].iloc[0],526 'panic_button': panic_button_dict,527 'test_scores': test_scores_dict528 })529 530 # Define scoring weights531 academic_weights = {'BACKLOGS': -5, 'MISSED CLASSES': -4, 'NOT UNDERSTANDING': -3, 'BAD MARKS': -3, 'LACK OF MOTIVATION': -3}532 non_academic_weights = {'EMOTIONAL FACTORS': -3, 'PROCRASTINATE': -2, 'LOST INTEREST': -4, 'LACK OF FOCUS': -2, 'GOALS NOT ACHIEVED': -2, 'LACK OF DISCIPLINE': -2}533 max_weighted_panic_score = sum([max(academic_weights.values()) * 3, max(non_academic_weights.values()) * 3])534 535 def calculate_potential_score(row):536 if row['test_scores']:537 avg_test_score = sum(row['test_scores'].values()) / len(row['test_scores'])538 test_score_normalized = (avg_test_score / 40) * 70539 else:540 test_score_normalized = 0541 student_panic_score = 0542 if row['panic_button']:543 for factor, count in row['panic_button'].items():544 if factor in academic_weights:545 student_panic_score += academic_weights[factor] * count546 elif factor in non_academic_weights:547 student_panic_score += non_academic_weights[factor] * count548 else:549 student_panic_score = 0550 panic_score = 20 * (1 - (student_panic_score / max_weighted_panic_score) if max_weighted_panic_score != 0 else 1)551 if pd.notna(row['productivity_yes_no']) and row['productivity_yes_no'] == 'Yes':552 if pd.notna(row['productivity_rate']):553 journal_score = (float(row['productivity_rate']) / 10) * 10554 else:555 journal_score = 0556 elif pd.notna(row['productivity_yes_no']) and row['productivity_yes_no'] == 'No':557 if pd.notna(row['productivity_rate']):558 journal_score = (float(row['productivity_rate']) / 10) * 5559 else:560 journal_score = 0561 else:562 journal_score = 0563 total_potential_score = test_score_normalized + panic_score + journal_score564 return total_potential_score565 566 merged_df = merged_data_cleaned.groupby('user_id').apply(process_group).reset_index()567 merged_df['potential_score'] = merged_df.apply(calculate_potential_score, axis=1)568 merged_df['potential_score'] = merged_df['potential_score'].round(2)569 sorted_df = merged_df[['user_id', 'potential_score']].sort_values(by='potential_score', ascending=False)570 result = sorted_df.to_dict(orient="records")571 572 return {"sorted_scores": result}573 574