LumpyBat1636/aggregate-users

1import pandas as pd2from tqdm import tqdm3from dotenv import load_dotenv4import json5import os6from google import genai7from google.genai import types8import getpass9 10load_dotenv()11 12# Tries to get the API key from an environment variable. 13# If not found, it will securely prompt you to enter it.14api_key = os.environ.get("GEMINI_API_KEY")15if not api_key:16    try:17        api_key = getpass.getpass("Please enter your Gemini API key: ")18    except Exception as e:19        print(f"Could not read API key: {e}")20        exit()21 22client = genai.Client()23MODEL_NAME = 'gemini-2.5-flash'24 25INPUT_FILE = 'potential_testers.xlsx'26OUTPUT_FILE = 'potential_testers_processed.xlsx'27 28PROMPT_TEMPLATE = """29Analyze the following Reddit comment to determine if the user is a good candidate for a beta test and generate an appropriate outreach message.30 31**Your Tasks:**321.  **Analyze & Qualify:** First, determine if the comment expresses a PERSONAL negative experience, frustration, dismissal, or complaint about a doctor, clinic, or the medical system. The user must be the patient or a close caregiver. This qualifies as a "Good Fit". Any other comment (e.g., general discussion, news, questions) is "Not a Good Fit".332.  **Generate Message based on Qualification:**34    * **If it's a "Good Fit":** Extract a concise quote (1-2 sentences or 15-30 words) that captures their negative sentiment and use it to populate **TEMPLATE A**.35    * **If it's "Not a Good Fit":** Use the subreddit name to populate **TEMPLATE B**.363.  **Format Output:** Respond ONLY with a single JSON object containing "is_good_fit" (boolean) and the appropriate "generated_message".37 38---39**TEMPLATE A (For a Good Fit)**40Hi, I saw your comment in r/{subreddit} where you mentioned, "{{quote}}"41 42That really resonated. My name's Sabih, and I'm building an app for this exact reason.43 44A few years ago, my mom was dismissed by her doctor about a UTI that turned into a serious kidney infection. It was a scary wake-up call. So I'm building Doctor Shadow. It's a private app that records your visit and gives you an expert second opinion right after, so you can feel more confident asking the right questions.45 46I'm looking for a few people to test the beta version. Would you be open to trying it out?47---48**TEMPLATE B (For a Not a Good Fit - More Human & Story-led)**49Hi, I saw your comment in the r/{subreddit} community and thought I'd reach out.50 51My name is Sabih. A couple of years ago, my mom had a scary experience where her doctor dismissed a concern that later became a serious kidney infection.52 53That experience pushed me to build an app called Doctor Shadow. It gives you a private, expert second opinion right after a doctor's visit to help you feel more prepared and confident in your healthcare.54 55Since you're part of the r/{subreddit} community, I thought the idea might resonate with you. I'm looking for beta testers soon and would love to know if you'd be interested in learning more.56---57 58**Comment Details to Analyze:**59- Subreddit: r/{subreddit}60- Comment Text: "{comment_text}"61 62**Required JSON Output Format:**63- For a Good Fit: `{{ "is_good_fit": true, "generated_message": "[Message from TEMPLATE A]" }}`64- For a Not a Good Fit: `{{ "is_good_fit": false, "generated_message": "[Message from TEMPLATE B]" }}`65"""66 67 68def analyze_and_generate(row):69    """70    Applies the Gemini prompt to a single row of the DataFrame.71    """72    try:73        prompt = PROMPT_TEMPLATE.format(74            subreddit=row['subreddit'],75            comment_text=row['comment_text']76        )77        response = client.models.generate_content(78            model=MODEL_NAME,79            contents=prompt,80            config=types.GenerateContentConfig(81                system_instruction="You are a JSON generating machine. You must respond only with a valid JSON object."82            )83        )84        cleaned_response = response.text.strip()85        if cleaned_response.startswith('```'):86            cleaned_response = cleaned_response.strip('`').lstrip('json').strip()87        result = json.loads(cleaned_response)88        return pd.Series([89            result.get('is_good_fit', False), 90            result.get('generated_message', "Error: Parsing failed")91        ])92    except Exception as e:93        error_message = f"Error: {str(e)[:100]}"94        print(f"\nError on row for user '{row.get('username', 'N/A')}'. Details: {error_message}")95        return pd.Series([False, error_message])96 97 98if __name__ == "__main__":99    try:100        df = pd.read_excel(INPUT_FILE)101        print(f"Successfully loaded '{INPUT_FILE}' with {len(df)} rows.")102    except FileNotFoundError:103        print(f"Error: The file '{INPUT_FILE}' was not found in the current directory.")104        exit()105 106    tqdm.pandas(desc="🤖 Analyzing Comments")107    new_columns = df.progress_apply(analyze_and_generate, axis=1)108    new_columns.columns = ['is_good_fit', 'generated_message']109    110    df_results = pd.concat([df, new_columns], axis=1)111    df_results.to_excel(OUTPUT_FILE, index=False)112    113    good_fit_count = df_results['is_good_fit'].sum()114    not_good_fit_count = len(df_results) - good_fit_count115    print("\n---")116    print(f"✅ Processing complete!")117    print(f"Found {good_fit_count} 'Good Fit' candidates (direct complaints).")118    print(f"Generated {not_good_fit_count} general outreach messages for other health-related comments.")119    print(f"Results have been saved to '{OUTPUT_FILE}'.")