1st_Functions.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-20T02:03:01.950365Z",
     "start_time": "2021-07-20T02:03:01.912870Z"
    }
   },
   "outputs": [],
   "source": [
    "import  psycopg2 \n",
    "import config as creds\n",
    "\n",
    "def connect():    \n",
    "    # Set up a connection to the postgres server.\n",
    "    conn_string = \"host=\"+ creds.pg_rds_endpoint +\" port=\"+ \"5432\" +\" dbname=\"+ creds.pg_db_name +\" user=\" + creds.pg_user \\\n",
    "                  +\" password=\"+ creds.pg_pwd\n",
    "    \n",
    "    conn = psycopg2.connect(conn_string)\n",
    "    print(conn)\n",
    "    print(\"Connected!\")\n",
    "\n",
    "    # Create a cursor object\n",
    "    cursor = conn.cursor()\n",
    "    \n",
    "    return conn, cursor\n",
    "\n",
    "def disconnect(conn, cursor):\n",
    "    cursor.close()\n",
    "    conn.close()\n",
    "      \n",
    "def load_data_onto_db(cursor):\n",
    "    with open('./resources/fifa_world_players_alt.csv', 'r', encoding=\"UTF8\") as row:\n",
    "        next(row) # Skip the header row.\n",
    "        cursor.copy_from(row, 'players', sep=',')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-20T02:03:01.972114Z",
     "start_time": "2021-07-20T02:03:01.953876Z"
    }
   },
   "outputs": [],
   "source": [
    "# Purpose:\n",
    "# To house common functions\n",
    "\n",
    "def create_fifa_players_dataframe(dataset_location):\n",
    "    import pandas\n",
    "\n",
    "    return pandas.read_csv(dataset_location, encoding='UTF-8')\n",
    "\n",
    "def drop_columns_from_dataframe(data, columns):\n",
    "    data_copy = data.copy()\n",
    "\n",
    "    for column in data.columns:\n",
    "        if column not in columns:\n",
    "            del data[column]\n",
    "\n",
    "    return data\n",
    "\n",
    "def create_players_dataframes(data, columns_to_keep):\n",
    "    \n",
    "    filtered_data = drop_columns_from_dataframe(data, columns_to_keep)\n",
    "        \n",
    "    return filtered_data\n",
    "\n",
    "def create_schema_off_dataset(data, file_name):\n",
    "    import json\n",
    "    import pandas\n",
    "    schema = pandas.io.json.build_table_schema(data, index=False, primary_key=None, version=True)\n",
    "    # print(schema)\n",
    "    with open(file_name, 'w') as file:\n",
    "        file.write(json.dumps(schema))\n",
    "    \n",
    "def encode_field(data, column):\n",
    "    from sklearn.preprocessing import LabelEncoder\n",
    "    le = LabelEncoder()\n",
    "    data_copy = data.copy()\n",
    "    data_copy[column] = le.fit_transform(data[column])\n",
    "    # display(data_copy)\n",
    "    return data_copy\n",
    "\n",
    "columns_to_keep = [\"overall\", \"potential\", \"field_position\", \"height\",\"weight\",\"crossing\",\"finishing\",\"heading_accuracy\",\"short_passing\",\"volleys\",\n",
    "\"dribbling\",\"curve\",\"fk_accuracy\",\"long_passing\",\"ball_control\",\"acceleration\",\"sprint_speed\",\"agility\",\n",
    "\"reactions\",\"balance\",\"shot_power\",\"jumping\",\"stamina\",\"strength\",\"long_shots\",\"aggression\",\"interceptions\",\n",
    "\"positioning\",\"vision\",\"penalties\",\"composure\",\"marking\",\"standing_tackle\",\"sliding_tackle\",\"gk_diving\",\n",
    "\"gk_handling\",\"gk_kicking\",\"gk_positioning\",\"gk_reflexes\", 'work_rate', \"rating\"]\n",
    "\n",
    "# cols_to_drop_for_keepers = ['photo', 'flag',\n",
    "#     'international_reputation', 'Weak Foot', 'Skill Moves', 'Work Rate'\n",
    "#      'Body Type', 'Real Face',  'Jersey Number',\n",
    "#     'Joined', 'Loaned From', 'Contract Valid Until',\n",
    "#      'Crossing', 'Finishing', 'HeadingAccuracy', 'ShortPassing', 'Volleys',\n",
    "#        'Dribbling', 'Curve', 'FKAccuracy', 'LongPassing', 'BallControl',\n",
    "#        'Acceleration', 'SprintSpeed',\n",
    "#      'Club Logo', 'Release Clause', 'Special', 'Preferred Foot',\n",
    "#      'Marking', 'StandingTackle', 'SlidingTackle']\n",
    "\n",
    "# cols_to_drop_for_players = ['Photo', 'Flag',\n",
    "#     'International Reputation', 'Weak Foot', 'Jersey Number', 'Work Rate'\n",
    "#     'Joined', 'Loaned From', 'Contract Valid Until', 'Body Type', 'Real Face', 'Skill Moves', \n",
    "#      'Photo', 'Flag', 'Club Logo', 'Release Clause', 'Special', 'Preferred Foot', 'GKDiving', 'GKHandling', 'GKKicking', 'GKPositioning', 'GKReflexes']\n",
    "\n",
    "def create_fifa_players_dataset(data_in, file_loc, csv_file):\n",
    "    \n",
    "    #data_in['rating'] = (data_in['overall'].astype(int) + data_in['potential'].astype(int)) /2\n",
    "    \n",
    "    \n",
    "    from pathlib import Path\n",
    "    \n",
    "    # check and drop nulls\n",
    "    count_na = data_in.isna().sum().sum()\n",
    "   \n",
    "    if count_na > 0:\n",
    "        data_in = data_in.dropna()\n",
    "\n",
    "        \n",
    "#     # source: https://stackoverflow.com/questions/48366506/calculate-new-column-as-the-mean-of-other-columns-pandas/48366525\n",
    "#     col = data_in[[\"overall\",\"potential\"]]\n",
    "#     data_in['rating'] = col.mean(axis=1)\n",
    "        \n",
    "            \n",
    "    data_in = data_in.infer_objects()\n",
    "    # display(data_in.head)\n",
    "\n",
    "    %time data_in.to_csv(Path(file_loc, csv_file), index = False, line_terminator='\\n')\n",
    "\n",
    "    # if show:\n",
    "    #     print(col.mean(axis=1))\n",
    "    #     print(data_in.info())\n",
    "    #     print(data_in.describe(exclude =[object, int]))\n",
    "\n",
    "    return data_in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-20T02:03:01.982134Z",
     "start_time": "2021-07-20T02:03:01.974617Z"
    }
   },
   "outputs": [],
   "source": [
    "def adjust_rating(x):\n",
    "    if  x > 85:\n",
    "        return \"Excellent\"\n",
    "    # elif 70 < x <= 85:\n",
    "    #     return \"Good\"\n",
    "    return \"Decent\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-20T02:03:01.997150Z",
     "start_time": "2021-07-20T02:03:01.985637Z"
    }
   },
   "outputs": [],
   "source": [
    "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "goal_keepers = ['GK']\n",
    "defenders = ['RWB', 'RCB', 'RB', 'LCB', 'CB', 'LWB', 'LB']\n",
    "def_mid_fielders = ['LDM', 'CDM','RDM']\n",
    "mid_fielders = ['CM', 'RCM', 'RM', 'LCM', 'LM']\n",
    "attack_mid_fielders = ['LAM', 'CAM', 'RAM']\n",
    "forwards = ['RF', 'CF','LF', 'LW', 'RW']\n",
    "strikers = ['ST', 'RS', 'LS'] # Striker or Center Forward"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "c8664c37d20d1a667b792eed6f68d26a151ec74784f499ac31579ebbdd16e46a"
  },
  "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.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}