<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 PID SCP Stats Report
This script runs stat totals for SCP and UCSD up to a specific date. This is requested yearly by the UCSD SCP Group.
Create export from database
Imports
Step1:  Define SQL Query
Step2:  Get Connection & Credentials
Enter database connection information. Everything is treated like a password to avoid accidental leaking into jupyter notebook.
Step3:  Running Querying
This is going to take awhile.
<ASSISTANT_TASK:>
Python Code:
import datetime
import collections
import getpass
import json
import os
import yaml
import pandas
import pymysql
Explanation: PID SCP Stats Report
This script runs stat totals for SCP and UCSD up to a specific date. This is requested yearly by the UCSD SCP Group.
Create export from database
Imports
End of explanation
select_stmt_base = "select id, deactivated, group_id, created_at, notes from pids where created_at < '{}'"
Explanation: Define SQL Query
End of explanation
if os.path.exists("../conf/db.yml"):
    print("Using conf/db.yml for configuration")
    db = yaml.safe_load(open("../conf/db.yml", "r"))
    host = db["db_host"]
    user = db["db_username"]
    password = db["db_password"]
else:
    print("Could not find db conf, asking user for credentials")
    host = input("Host: ")
    user = input("Username: ")
    password = getpass.getpass("Password: ")
# create connection
conn = pymysql.connect(
    host=host,
    port=int(3306),
    user=user,
    passwd=password,
    db="pid")
Explanation: Get Connection & Credentials
Enter database connection information. Everything is treated like a password to avoid accidental leaking into jupyter notebook.
End of explanation
date = input("Enter a date (YYYY-MM-DD): ")
# run query to get dataframe
df = pandas.read_sql_query(select_stmt_base.format(date),
    conn)
filter_scp = df["group_id"]=="SCP"
filter_ucsd = df["group_id"]=="UCSD"
filter_scp_notes = df["notes"].str.lower().str.contains("scp")
filter_ucsd_notes = df["notes"].str.lower().str.contains("ucsd")
filter_de = df["deactivated"]==0
scp_all = df[(filter_scp)]['id'].count()
scp_de = df[(filter_scp) & (filter_de)]['id'].count()
ucsd_all = df[(filter_ucsd)]['id'].count()
ucsd_de = df[(filter_ucsd) & (filter_de)]['id'].count()
shared_de = df[((filter_ucsd & filter_scp_notes) | (filter_scp & filter_ucsd_notes)) & filter_de ]['id'].count()
shared_all = df[((filter_ucsd & filter_scp_notes) | (filter_scp & filter_ucsd_notes)) ]['id'].count()
print("SCP Total: {}".format(scp_all))
print("SCP Active: {}".format(scp_de))
print("UCSD Total: {}".format(ucsd_all))
print("UCSD Active: {}".format(ucsd_de))
print("Shared Total: {}".format(shared_all))
print("Shared Active: {}".format(shared_de))
Explanation: Running Querying
This is going to take awhile.
End of explanation
<END_TASK>