Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- {
- "cells": [
- {
- "cell_type": "code",
- "execution_count": 4,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "data": {
- "text/html": [
- "<div>\n",
- "<style>\n",
- " .dataframe thead tr:only-child th {\n",
- " text-align: right;\n",
- " }\n",
- "\n",
- " .dataframe thead th {\n",
- " text-align: left;\n",
- " }\n",
- "\n",
- " .dataframe tbody tr th {\n",
- " vertical-align: top;\n",
- " }\n",
- "</style>\n",
- "<table border=\"1\" class=\"dataframe\">\n",
- " <thead>\n",
- " <tr style=\"text-align: right;\">\n",
- " <th></th>\n",
- " <th>assist_scores</th>\n",
- " </tr>\n",
- " </thead>\n",
- " <tbody>\n",
- " <tr>\n",
- " <th>count</th>\n",
- " <td>283.000000</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>mean</th>\n",
- " <td>9.190813</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>std</th>\n",
- " <td>1.679767</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>min</th>\n",
- " <td>0.000000</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>25%</th>\n",
- " <td>9.000000</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>50%</th>\n",
- " <td>10.000000</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>75%</th>\n",
- " <td>10.000000</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <th>max</th>\n",
- " <td>10.000000</td>\n",
- " </tr>\n",
- " </tbody>\n",
- "</table>\n",
- "</div>"
- ],
- "text/plain": [
- " assist_scores\n",
- "count 283.000000\n",
- "mean 9.190813\n",
- "std 1.679767\n",
- "min 0.000000\n",
- "25% 9.000000\n",
- "50% 10.000000\n",
- "75% 10.000000\n",
- "max 10.000000"
- ]
- },
- "execution_count": 4,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "import psycopg2\n",
- "from collections import Counter\n",
- "import numpy as np\n",
- "import pandas as pd\n",
- "from scipy import stats\n",
- "import matplotlib.pyplot as plt\n",
- "\n",
- "\n",
- "db_user = 'tyler.engel'\n",
- "db_password = 'zedt7CTM8pr3'\n",
- "redshift_address = 'fulla.cth9cfyvuxeq.us-east-1.redshift.amazonaws.com'\n",
- "redshift_port = '5439'\n",
- "redshift_DBName = 'dev'\n",
- "connection = 'postgres://{}:{}@{}:{}/{}'.format(db_user, db_password, redshift_address, redshift_port, redshift_DBName)\n",
- "\n",
- "\n",
- "conn = psycopg2.connect(dbname=redshift_DBName, user=db_user, host=redshift_address, password=db_password, port=redshift_port)\n",
- "\n",
- "cur = conn.cursor()\n",
- "\n",
- "assist = cur.execute(\"SELECT u.teamid, sport, score FROM delighted_responses d JOIN user_user_roles u ON u.userid = d.userid WHERE created_at > '2017-03-01' AND d.sport IN ('Football', 'Basketball') AND ROLE IN (2, 3) AND u.teamid IN (SELECT DISTINCT teamid FROM assist_teams_and_invoices)\")\n",
- "assist_teams = cur.fetchall()\n",
- "\n",
- "assist_teams_list = []\n",
- "assist_sports_list = []\n",
- "assist_scores_list = []\n",
- "\n",
- "def make_team_list(team_list, new_list):\n",
- " for i in range(0, len(team_list)):\n",
- " new_list.append(team_list[i][0])\n",
- " return new_list\n",
- "\n",
- "def make_sport_list(team_list, new_list):\n",
- " for i in range(0, len(team_list)):\n",
- " new_list.append(team_list[i][1])\n",
- " return new_list\n",
- "\n",
- "def make_score_list(team_list, new_list):\n",
- " for i in range(0, len(team_list)):\n",
- " new_list.append(team_list[i][2])\n",
- " return new_list\n",
- " \n",
- "assist_teams_pd = make_team_list(assist_teams, assist_teams_list)\n",
- "assist_sports_pd = make_sport_list(assist_teams, assist_sports_list)\n",
- "assist_scores_pd = make_score_list(assist_teams, assist_scores_list)\n",
- "\n",
- "non_assist = cur.execute(\"SELECT u.teamid, sport, score FROM delighted_responses d JOIN user_user_roles u ON u.userid = d.userid WHERE created_at > '2017-03-01' AND d.sport IN ('Football', 'Basketball') AND ROLE IN (2, 3) AND u.teamid not IN (SELECT DISTINCT teamid FROM assist_teams_and_invoices)\")\n",
- "non_assist_teams = cur.fetchall()\n",
- "\n",
- "non_assist_teams_list = []\n",
- "non_assist_sports_list = []\n",
- "non_assist_scores_list = []\n",
- "\n",
- "non_assist_teams_pd = make_team_list(non_assist_teams, non_assist_teams_list)\n",
- "non_assist_sports_pd = make_sport_list(non_assist_teams, non_assist_sports_list)\n",
- "non_assist_scores_pd = make_score_list(non_assist_teams, non_assist_scores_list)\n",
- "\n",
- "assist_df = pd.DataFrame(np.column_stack([assist_teams_pd, assist_sports_pd, assist_scores_pd]), columns = ['assist_teams', 'assist_sports', 'assist_scores']) \n",
- "# Convert score to numeric\n",
- "assist_df[['assist_scores']] = assist_df[['assist_scores']].apply(pd.to_numeric)\n",
- "non_assist_df = pd.DataFrame(np.column_stack([non_assist_teams_pd, non_assist_sports_pd, non_assist_scores_pd]), columns = ['non_assist_teams', 'non_assist_sports', 'non_assist_scores']) \n",
- "# Convert score to numeric\n",
- "non_assist_df[['assist_scores']] = non_assist_df[['non_assist_scores']].apply(pd.to_numeric)\n",
- "assist_df.describe()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 9,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "data": {
- "text/plain": [
- "(array([ 2., 0., 4., 0., 1., 8., 4., 11., 22., 231.]),\n",
- " array([ 0., 1., 2., 3., 4., 5., 6., 7., 8., 9., 10.]),\n",
- " <a list of 10 Patch objects>)"
- ]
- },
- "execution_count": 9,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "plt.hist(assist_scores_pd)\n",
- "plt.hist(non_assist_scores_pd)"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 8,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " assist_scores\n",
- "assist_sports \n",
- "Basketball 8.518987\n",
- "Football 9.450980\n",
- " assist_scores\n",
- "non_assist_sports \n",
- "Basketball 8.882570\n",
- "Football 9.371036\n"
- ]
- }
- ],
- "source": [
- "print(assist_df.groupby('assist_sports').mean())\n",
- "print(non_assist_df.groupby('non_assist_sports').mean())"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": true
- },
- "outputs": [],
- "source": []
- }
- ],
- "metadata": {
- "kernelspec": {
- "display_name": "Python 3",
- "language": "python",
- "name": "python3"
- },
- "language_info": {
- "codemirror_mode": {
- "name": "ipython",
- "version": 3
- },
- "file_extension": ".py",
- "mimetype": "text/x-python",
- "name": "python",
- "nbconvert_exporter": "python",
- "pygments_lexer": "ipython3",
- "version": "3.6.0"
- }
- },
- "nbformat": 4,
- "nbformat_minor": 2
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement