risk-level-auto.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import json\n",
    "import time\n",
    "import datetime\n",
    "from importlib import reload\n",
    "\n",
    "import requests\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.cluster import DBSCAN\n",
    "\n",
    "import external as ext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "desert_main = pd.read_csv('miramar.csv')\n",
    "baltic_main = pd.read_csv('erangel.csv')\n",
    "savage_main = pd.read_csv('sanhok.csv')\n",
    "summerland_main = pd.read_csv('karakin.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "desert_main = desert_main.iloc[:,1:]\n",
    "baltic_main = baltic_main.iloc[:,1:]\n",
    "savage_main = savage_main.iloc[:,1:]\n",
    "summerland_main = summerland_main.iloc[:,1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [],
   "source": [
    "def risklevel(x,y,mapname):\n",
    "    if mapname == 'Desert_Main':\n",
    "        try:\n",
    "            risk = desert_main.iloc[y,x]\n",
    "        except:\n",
    "            risk = 0\n",
    "    elif mapname == 'Baltic_Main':\n",
    "        try:\n",
    "            risk = baltic_main.iloc[y,x] \n",
    "        except:\n",
    "            risk = 0\n",
    "    elif mapname == 'Savage_Main':\n",
    "        try:\n",
    "            risk = savage_main.iloc[y,x] \n",
    "        except:\n",
    "            risk = 0\n",
    "    elif mapname == 'Summerland_Main':\n",
    "        try:\n",
    "            risk = summerland_main.iloc[y,x]\n",
    "        except:\n",
    "            risk = 0\n",
    "        # correction due to lower max number of players\n",
    "        risk = risk * 1.5625\n",
    "    else:\n",
    "        risk = np.nan\n",
    "        \n",
    "    lst.append(risk)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color=red> Preparing DataFrame </font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-251-b8b642fa38ee>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m    114\u001b[0m             \u001b[1;31m# if not, we interpolate it\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    115\u001b[0m             \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 116\u001b[1;33m                 \u001b[0mx_coord\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0my_coord\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0min_aircraft\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgetNewCoord\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mt\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    117\u001b[0m                 \u001b[0mnames\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    118\u001b[0m                 \u001b[0mtimes\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mt\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m<ipython-input-251-b8b642fa38ee>\u001b[0m in \u001b[0;36mgetNewCoord\u001b[1;34m(t, n)\u001b[0m\n\u001b[0;32m     68\u001b[0m     \u001b[1;31m# we can expect that this player will be at position (x+1,y+1) at second 11.0, and so on\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     69\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mgetNewCoord\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mt\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m---> 70\u001b[1;33m         \u001b[0maa\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdata_new_coords\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata_new_coords\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'elapsedTime'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m<\u001b[0m \u001b[0mt\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m&\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mdata_new_coords\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;31m# get previous observations\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     71\u001b[0m         \u001b[0mbb\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdata_new_coords\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdata_new_coords\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'elapsedTime'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m>\u001b[0m \u001b[0mt\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m&\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mdata_new_coords\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0mn\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;31m# get following observations\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     72\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mbool\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0maa\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'location.x'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0many\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m&\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mbool\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mbb\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m'location.x'\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0many\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;31m# if we have both previous and following observation\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\ops\\__init__.py\u001b[0m in \u001b[0;36mwrapper\u001b[1;34m(self, other)\u001b[0m\n\u001b[0;32m   1299\u001b[0m         \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mother\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mABCSeries\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mABCIndexClass\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1300\u001b[0m             \u001b[0mis_other_int_dtype\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mis_integer_dtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mother\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1301\u001b[1;33m             \u001b[0mother\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mfill_int\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mother\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;32mif\u001b[0m \u001b[0mis_other_int_dtype\u001b[0m \u001b[1;32melse\u001b[0m \u001b[0mfill_bool\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mother\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1302\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1303\u001b[0m             \u001b[0movalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mother\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\ops\\__init__.py\u001b[0m in \u001b[0;36m<lambda>\u001b[1;34m(x)\u001b[0m\n\u001b[0;32m   1285\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1286\u001b[0m     \u001b[0mfill_int\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfillna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1287\u001b[1;33m     \u001b[0mfill_bool\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mlambda\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mx\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfillna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mbool\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1288\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1289\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mwrapper\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mother\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\series.py\u001b[0m in \u001b[0;36mfillna\u001b[1;34m(self, value, method, axis, inplace, limit, downcast, **kwargs)\u001b[0m\n\u001b[0;32m   4343\u001b[0m             \u001b[0mlimit\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mlimit\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   4344\u001b[0m             \u001b[0mdowncast\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdowncast\u001b[0m\u001b[1;33m,\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 4345\u001b[1;33m             \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   4346\u001b[0m         )\n\u001b[0;32m   4347\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mfillna\u001b[1;34m(self, value, method, axis, inplace, limit, downcast)\u001b[0m\n\u001b[0;32m   6256\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   6257\u001b[0m                 new_data = self._data.fillna(\n\u001b[1;32m-> 6258\u001b[1;33m                     \u001b[0mvalue\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mvalue\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mlimit\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mlimit\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0minplace\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0minplace\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdowncast\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdowncast\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   6259\u001b[0m                 )\n\u001b[0;32m   6260\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mfillna\u001b[1;34m(self, **kwargs)\u001b[0m\n\u001b[0;32m    573\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    574\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mfillna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 575\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"fillna\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    576\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    577\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mdowncast\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)\u001b[0m\n\u001b[0;32m    436\u001b[0m                     \u001b[0mkwargs\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mk\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mreindex\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mb_items\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0maxis\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0malign_copy\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    437\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 438\u001b[1;33m             \u001b[0mapplied\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mb\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    439\u001b[0m             \u001b[0mresult_blocks\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0m_extend_blocks\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mapplied\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mresult_blocks\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    440\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\internals\\blocks.py\u001b[0m in \u001b[0;36mfillna\u001b[1;34m(self, value, limit, inplace, downcast)\u001b[0m\n\u001b[0;32m    404\u001b[0m         \u001b[0minplace\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalidate_bool_kwarg\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0minplace\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m\"inplace\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    405\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 406\u001b[1;33m         \u001b[0mmask\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0misna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    407\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mlimit\u001b[0m \u001b[1;32mis\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    408\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mis_integer\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mlimit\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36misna\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    120\u001b[0m     \u001b[0mName\u001b[0m\u001b[1;33m:\u001b[0m \u001b[1;36m1\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mbool\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    121\u001b[0m     \"\"\"\n\u001b[1;32m--> 122\u001b[1;33m     \u001b[1;32mreturn\u001b[0m \u001b[0m_isna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    123\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    124\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36m_isna_new\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    145\u001b[0m         ),\n\u001b[0;32m    146\u001b[0m     ):\n\u001b[1;32m--> 147\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0m_isna_ndarraylike\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    148\u001b[0m     \u001b[1;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mABCGeneric\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    149\u001b[0m         \u001b[1;32mreturn\u001b[0m \u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_constructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0misna\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mfunc\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0misna\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\dtypes\\missing.py\u001b[0m in \u001b[0;36m_isna_ndarraylike\u001b[1;34m(obj)\u001b[0m\n\u001b[0;32m    254\u001b[0m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mvalues\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mview\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"i8\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m==\u001b[0m \u001b[0miNaT\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    255\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 256\u001b[1;33m         \u001b[0mresult\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0misnan\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    257\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    258\u001b[0m     \u001b[1;31m# box\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "import os\n",
    "import glob\n",
    "\n",
    "path = 'telemetry_data/2020-04-30/pc'\n",
    "for filename in glob.glob(os.path.join(path, '*.json')): #only process .JSON files in folder.      \n",
    "    with open(filename, mode='r') as file:\n",
    "        json_data = file.read()\n",
    "        file.close()\n",
    "    data = []\n",
    "    head = []\n",
    "    for i in json.loads(json_data):\n",
    "        if i[\"_T\"] in (\"LogPlayerPosition\", \"LogParachuteLanding\"):\n",
    "            data.append(i)\n",
    "        if i[\"_T\"] in (\"LogMatchStart\"):\n",
    "            head.append(i)\n",
    "    \n",
    "\n",
    "    # get events data into pandas dataframe\n",
    "    data = pd.read_json(json.dumps(data))\n",
    "    head = pd.read_json(json.dumps(head))\n",
    "    \n",
    "    # get map name\n",
    "    mn = head.loc[0, 'mapName']\n",
    "    \n",
    "    # copy match id to all rows and drop LogMatchDefinition event\n",
    "    # we will use this later to add use it for future merging tasks\n",
    "    matchid = json.loads(json_data)[0][\"MatchId\"]\n",
    "    data['MatchId'] = matchid\n",
    "\n",
    "    #reset indexes\n",
    "    data.reset_index(drop=True, inplace=True)\n",
    "\n",
    "    # get player information\n",
    "    from pandas.io.json import json_normalize\n",
    "    \n",
    "    # we need to expand the json documents on the character column\n",
    "    data = pd.concat([data, json_normalize(data['character'])], axis=1).drop(['character', 'zone'], axis=1)\n",
    "    \n",
    "    def isGame(x):\n",
    "        return x['isGame']\n",
    "\n",
    "    data['isGame'] = data['common'].apply(isGame)\n",
    "    data = data.drop('common', axis=1)\n",
    "\n",
    "    # we will use the vehicle to check what players are still in the airplane\n",
    "    data.vehicle = data.vehicle.apply(lambda x: {} if pd.isna(x) else x)\n",
    "    data['vehicle'] = json_normalize(data.vehicle)['vehicleType']\n",
    "\n",
    "    # check how many players' locations we have at each point in time\n",
    "    # we want to make sure that after this process, all players have harmonized times and locations\n",
    "    #for i in data.elapsedTime.unique():\n",
    "        #obs = len(data[data['elapsedTime'] == i])\n",
    "        #print(f'{i} has {obs} observations')\n",
    "\n",
    "    # we create a new dataframe with the necessary information to interpolate locations\n",
    "    data_new_coords = data[['elapsedTime','name','location.x','location.y', 'vehicle']].sort_values(['name','elapsedTime']).drop_duplicates()\n",
    "    data_new_coords = data_new_coords.reset_index().drop('index', axis=1)\n",
    "    data_new_coords = data_new_coords.dropna(subset = ['elapsedTime'])\n",
    "    \n",
    "    # we round coordinates for ease of calculations, and because a difference in location smaller than a cm is negligible\n",
    "    data_new_coords['location.x'] = data_new_coords['location.x'].apply(round)\n",
    "    data_new_coords['location.y'] = data_new_coords['location.y'].apply(round)\n",
    "\n",
    "    data_new_coords.elapsedTime.unique().astype(int)\n",
    "    \n",
    "    # since different elapsed times don't show the location of all players, we want to interpolate the position of the players at each\n",
    "    # point in time. e.g. if player a is in position (x,y) at second 10.0 and in position (x+10,y+10) at second 20.0\n",
    "    # we can expect that this player will be at position (x+1,y+1) at second 11.0, and so on\n",
    "    def getNewCoord(t, n):\n",
    "        aa = data_new_coords[(data_new_coords['elapsedTime'] < t) & (data_new_coords['name'] == n)] # get previous observations\n",
    "        bb = data_new_coords[(data_new_coords['elapsedTime'] > t) & (data_new_coords['name'] == n)] # get following observations\n",
    "        if (bool(aa['location.x'].any())) & (bool(bb['location.x'].any())): # if we have both previous and following observation\n",
    "            a = aa.iloc[-1] # get last previous observation\n",
    "            b = bb.iloc[0] # get first following observation\n",
    "            time = b[0] - a[0] # calculate the time difference\n",
    "            # get x and y coordinate by calculating movement speed in cm/s and get full coordinate\n",
    "            new_coord_x = a[2] + ((b[2] - a[2]) / time)*(t-a[0])  \n",
    "            new_coord_y = a[3] + ((b[3] - a[3]) / time)*(t-a[0])\n",
    "            # we also get the vehicle to check if a player has jumped from the airplane or not\n",
    "            in_aircraft = b['vehicle']\n",
    "            return new_coord_x, new_coord_y, in_aircraft\n",
    "        elif (not bool(aa['location.x'].any())) & (bool(bb['location.x'].any())):\n",
    "            # if there is no previous observation, set first following location to be current location\n",
    "            in_aircraft = b['vehicle']\n",
    "            return b.iat[0,2], b.iat[0,3], in_aircraft\n",
    "\n",
    "        # If a player doesn't have a future location, that indicates the player is dead\n",
    "        elif (bool(aa['location.x'].any())) & (not bool(bb['location.x'].any())):\n",
    "            # if there is no following observation, we assume the player has died (we hope that only in the match)\n",
    "            return 'dead', 'dead', 'dead'\n",
    "\n",
    "    names=[]\n",
    "    times=[]\n",
    "    x_coords=[]\n",
    "    y_coords=[]\n",
    "    aircraft=[]\n",
    "    # we will get the location every 10 seconds from second 1 (e.g. 1.0, 11.0, 21.0), and at the last second\n",
    "    times_list = data_new_coords.elapsedTime.unique().astype(int)\n",
    "    # we get the times in intervals of 10s from second 1 to the last second of the game\n",
    "    unique_times_10s = np.arange(1, max(times_list), 10).tolist()\n",
    "    # we also append the last second, regardless of the 10s interval\n",
    "    unique_times_10s.append(max(times_list))\n",
    "    for t in unique_times_10s:\n",
    "        for n in data_new_coords.name.unique():\n",
    "            # for every player, we get their location at these times\n",
    "            a = data_new_coords[(data_new_coords['elapsedTime'] == t) & (data_new_coords['name'] == n)]\n",
    "            # if the player has a location, we take it\n",
    "            if a['location.x'].any():\n",
    "                names.append(n)\n",
    "                times.append(t)\n",
    "                x_coords.append(a.iat[0,2])\n",
    "                y_coords.append(a.iat[0,3])\n",
    "                aircraft.append(a['vehicle'])\n",
    "            # if not, we interpolate it\n",
    "            else:\n",
    "                x_coord, y_coord, in_aircraft = getNewCoord(t,n)\n",
    "                names.append(n)\n",
    "                times.append(t)\n",
    "                x_coords.append(x_coord)\n",
    "                y_coords.append(y_coord)\n",
    "                aircraft.append(in_aircraft)\n",
    "\n",
    "    # we create the dataframe from these locations\n",
    "    players_location = pd.DataFrame(list(zip(times,names,x_coords, y_coords, aircraft)),\n",
    "                 columns=['time','name','location.x','location.y', 'inAircraft'])\n",
    "    # we also check if the players are or aren't in the aircraft with a binary variable\n",
    "    players_location['inAircraft'] = players_location['inAircraft'].apply(lambda x: 1 if str(x) == 'TransportAircraft' else 0)\n",
    "\n",
    "\n",
    "    # we make sure that harmonization of times and observations has been done\n",
    "    #for i in players_location.time.unique():\n",
    "        #obs = len(players_location[players_location['time'] == i])\n",
    "        #print(f'{i} has {obs} observations')\n",
    "\n",
    "    # encode player names\n",
    "    # we do this to get the teamIds in the main dataframe\n",
    "    players = data[['name', 'accountId','teamId']].drop_duplicates()\n",
    "    players = players.sort_values('teamId').reset_index().drop(['index'], axis=1)\n",
    "\n",
    "\n",
    "    # we merge these names to get accountId and teamId in the dataframe\n",
    "    players_location = pd.merge(players_location,\n",
    "                                players,\n",
    "                                left_on = 'name',\n",
    "                                right_on = 'name',\n",
    "                                how = 'left')\n",
    "    # moreover, since we are goint to later \"asign\" a number to the players (e.g. player1, player2), we want to make sure\n",
    "    # that the criteria is consistent for all teams. Therefore, we do it with the .sort_values method\n",
    "    # players on each team are ordered with this method inside the team\n",
    "    players_location = players_location[players_location['time'] >= 0].sort_values(['teamId','time', 'name']).reset_index().drop(['index'], axis=1)\n",
    "\n",
    "    players_location = players_location.replace('dead',0)\n",
    "\n",
    "    # convert to 10 meters from cms\n",
    "    players_location[['location.x','location.y']] = players_location[['location.x','location.y']].apply(lambda x: x.astype(int))\n",
    "    players_location[['location.x','location.y']] = players_location[['location.x','location.y']].apply(lambda x: x // 1000)\n",
    "\n",
    "    data = players_location[['location.x','location.y']]\n",
    "\n",
    "    # convert df to list of tuples\n",
    "    records = data.to_records(index=False)\n",
    "    result = list(records)\n",
    "\n",
    "    cols = ['risk']\n",
    "    lst = []\n",
    "\n",
    "    try:\n",
    "        for i in result:\n",
    "            risklevel(*i, mapname=mn)\n",
    "\n",
    "        riskordered = pd.DataFrame(lst, columns=cols)\n",
    "    \n",
    "        # export match telemetry data into .csv format\n",
    "        riskordered.to_csv(path_or_buf=f'{matchid}.csv')\n",
    "    except:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-09\n",
      "2020-04-10\n",
      "2020-04-11\n",
      "2020-04-12\n",
      "2020-04-13\n",
      "2020-04-15\n",
      "2020-04-16\n",
      "2020-05-01\n",
      "2020-05-03\n",
      "2020-05-04\n",
      "2020-05-05\n",
      "2020-05-06\n",
      "2020-05-07\n",
      "2020-05-08\n",
      "2020-05-09\n",
      "2020-05-10\n",
      "2020-05-11\n",
      "2020-05-12\n",
      "2020-05-13\n",
      "2020-05-14\n",
      "2020-05-16\n"
     ]
    }
   ],
   "source": [
    "for direct in os.listdir('D:/PUBG/telemetry_data'):\n",
    "    print(direct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "!!! 684ca49a-c3e5-49e5-98d4-57d671266288.json: 'character'\n",
      "!!! b38bd5f5-eca8-4f91-a44c-cfeea9005172.json: 'character'\n",
      "!!! 2ed63e56-7c7f-4b45-9813-97decefebab0.json: 'character'\n",
      "!!! da2dbdcb-2c44-4778-b085-149330c26f09.json: 'character'\n",
      "!!! 2c90b98f-a032-4d93-84b9-4c46fa68f2ef.json: 'character'\n",
      "!!! 205dc2eb-1368-4b96-8765-95ff72baa676.json: 'character'\n"
     ]
    },
    {
     "ename": "NotADirectoryError",
     "evalue": "[WinError 267] El nombre del directorio no es válido: '../telemetry_data/clean/0005067a-838c-4037-8109-1e3852c23166.csv'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNotADirectoryError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-275-89ff079600c9>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mdirect\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlistdir\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'../telemetry_data'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m     \u001b[1;32mfor\u001b[0m \u001b[0md2\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlistdir\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf'../telemetry_data/{direct}'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 6\u001b[1;33m         \u001b[1;32mfor\u001b[0m \u001b[0mfilename\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mlistdir\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34mf'../telemetry_data/{direct}/{d2}'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      7\u001b[0m         \u001b[1;31m#for filename in glob.glob(os.path.join(path, '*.json')): #only process .JSON files in folder.\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m             \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNotADirectoryError\u001b[0m: [WinError 267] El nombre del directorio no es válido: '../telemetry_data/clean/0005067a-838c-4037-8109-1e3852c23166.csv'"
     ]
    }
   ],
   "source": [
    "import os\n",
    "import glob\n",
    "\n",
    "for direct in os.listdir('../telemetry_data'):\n",
    "    for d2 in os.listdir(f'../telemetry_data/{direct}'):\n",
    "        for filename in os.listdir(f'../telemetry_data/{direct}/{d2}'):\n",
    "        #for filename in glob.glob(os.path.join(path, '*.json')): #only process .JSON files in folder.  \n",
    "            try:\n",
    "                with open(f'../telemetry_data/{direct}/{d2}/{filename}', mode='r') as file:\n",
    "                    json_data = file.read()\n",
    "                    file.close()\n",
    "                data = []\n",
    "                head = []\n",
    "                for i in json.loads(json_data):\n",
    "                    if i[\"_T\"] in (\"LogPlayerPosition\", \"LogParachuteLanding\"):\n",
    "                        data.append(i)\n",
    "                    if i[\"_T\"] in (\"LogMatchStart\"):\n",
    "                        head.append(i)\n",
    "\n",
    "\n",
    "                # get events data into pandas dataframe\n",
    "                data = pd.read_json(json.dumps(data))\n",
    "                head = pd.read_json(json.dumps(head))\n",
    "\n",
    "                # get map name\n",
    "                mn = head.loc[0, 'mapName']\n",
    "\n",
    "                # copy match id to all rows and drop LogMatchDefinition event\n",
    "                # we will use this later to add use it for future merging tasks\n",
    "                matchid = json.loads(json_data)[0][\"MatchId\"]\n",
    "                matchid = matchid.split('.')[-1]\n",
    "                data['MatchId'] = matchid\n",
    "\n",
    "                #reset indexes\n",
    "                data.reset_index(drop=True, inplace=True)\n",
    "\n",
    "                # get player information\n",
    "                from pandas.io.json import json_normalize\n",
    "\n",
    "                # we need to expand the json documents on the character column\n",
    "                data = pd.concat([data, json_normalize(data['character'])], axis=1).drop(['character', 'zone'], axis=1)\n",
    "\n",
    "                def isGame(x):\n",
    "                    return x['isGame']\n",
    "\n",
    "                data['isGame'] = data['common'].apply(isGame)\n",
    "                data = data.drop('common', axis=1)\n",
    "\n",
    "                # we will use the vehicle to check what players are still in the airplane\n",
    "                data.vehicle = data.vehicle.apply(lambda x: {} if pd.isna(x) else x)\n",
    "                if 'vehicleType' in data.columns:\n",
    "                    data['vehicle'] = json_normalize(data.vehicle)['vehicleType']\n",
    "                else:\n",
    "                    data['vehicle'] = np.nan\n",
    "\n",
    "                # check how many players' locations we have at each point in time\n",
    "                # we want to make sure that after this process, all players have harmonized times and locations\n",
    "                #for i in data.elapsedTime.unique():\n",
    "                    #obs = len(data[data['elapsedTime'] == i])\n",
    "                    #print(f'{i} has {obs} observations')\n",
    "\n",
    "                # we create a new dataframe with the necessary information to interpolate locations\n",
    "                data_new_coords = data[['_T','elapsedTime','name','location.x','location.y', 'vehicle']].sort_values(['name','elapsedTime']).drop_duplicates()\n",
    "                data_new_coords = data_new_coords.reset_index().drop('index', axis=1)\n",
    "                #data_new_coords = data_new_coords.dropna(subset = ['elapsedTime'])\n",
    "\n",
    "                # we round coordinates for ease of calculations, and because a difference in location smaller than a cm is negligible\n",
    "                data_new_coords['location.x'] = data_new_coords['location.x'].apply(round)\n",
    "                data_new_coords['location.y'] = data_new_coords['location.y'].apply(round)\n",
    "\n",
    "                event = data_new_coords._T\n",
    "                names= data_new_coords.name\n",
    "                times= data_new_coords.elapsedTime\n",
    "                x_coords = data_new_coords['location.x']\n",
    "                y_coords= data_new_coords['location.y']\n",
    "                aircraft= data_new_coords.vehicle\n",
    "\n",
    "                # we create the dataframe from these locations\n",
    "                players_location = pd.DataFrame(list(zip(event, times,names,x_coords, y_coords, aircraft)),\n",
    "                             columns=['event','time','name','location.x','location.y', 'inAircraft'])\n",
    "                # we also check if the players are or aren't in the aircraft with a binary variable\n",
    "                players_location['inAircraft'] = players_location['inAircraft'].apply(lambda x: 1 if str(x) == 'TransportAircraft' else 0)\n",
    "\n",
    "\n",
    "                # we make sure that harmonization of times and observations has been done\n",
    "                #for i in players_location.time.unique():\n",
    "                    #obs = len(players_location[players_location['time'] == i])\n",
    "                    #print(f'{i} has {obs} observations')\n",
    "\n",
    "                # encode player names\n",
    "                # we do this to get the teamIds in the main dataframe\n",
    "                players = data[['name', 'accountId','teamId']].drop_duplicates()\n",
    "                players = players.sort_values('teamId').reset_index().drop(['index'], axis=1)\n",
    "\n",
    "\n",
    "                # we merge these names to get accountId and teamId in the dataframe\n",
    "                players_location = pd.merge(players_location,\n",
    "                                            players,\n",
    "                                            left_on = 'name',\n",
    "                                            right_on = 'name',\n",
    "                                            how = 'left')\n",
    "                # moreover, since we are goint to later \"asign\" a number to the players (e.g. player1, player2), we want to make sure\n",
    "                # that the criteria is consistent for all teams. Therefore, we do it with the .sort_values method\n",
    "                # players on each team are ordered with this method inside the team\n",
    "\n",
    "                players_location = players_location.sort_values(['teamId','time', 'name']).reset_index().drop(['index'], axis=1)\n",
    "\n",
    "                players_location = players_location.replace('dead',0)\n",
    "\n",
    "                # convert to 10 meters from cms\n",
    "                players_location[['location.x','location.y']] = players_location[['location.x','location.y']].apply(lambda x: x.astype(int))\n",
    "                players_location[['location.x','location.y']] = players_location[['location.x','location.y']].apply(lambda x: x // 1000)\n",
    "\n",
    "                data = players_location[['location.x','location.y']]\n",
    "\n",
    "                # convert df to list of tuples\n",
    "                records = data.to_records(index=False)\n",
    "                result = list(records)\n",
    "\n",
    "                cols = ['risk']\n",
    "                lst = []\n",
    "\n",
    "                try:\n",
    "                    for i in result:\n",
    "                        risklevel(*i, mapname=mn)\n",
    "\n",
    "                    riskordered = pd.DataFrame(lst, columns=cols)\n",
    "\n",
    "                    # export match telemetry data into .csv format\n",
    "                    #riskordered.to_csv(path_or_buf=f'{matchid}.csv')\n",
    "                    abc = pd.concat([players_location, riskordered], axis=1, sort=False)\n",
    "                    abc_mean = abc[abc.event != 'LogParachuteLanding'].groupby('name').mean().reset_index()\n",
    "                    risk_std = abc[abc.event != 'LogParachuteLanding'].groupby('name').std()['risk'].to_list()\n",
    "                    abc_mean['risk_std'] = risk_std\n",
    "                    landing_risk = abc[abc.event == 'LogParachuteLanding'][['name','risk']]\n",
    "                    landing_risk.columns = ['name','landing_risk']\n",
    "                    abc_final = pd.merge(abc_mean, landing_risk, how='left', on='name')\n",
    "                    abc_final.insert(0, 'matchId', matchid)\n",
    "                    abc_final = abc_final.sort_values(['teamId','name'])\n",
    "                    for team in abc_final.teamId.unique():\n",
    "                        df = abc_final[abc_final.teamId == team]\n",
    "                        if len(df) > 4:\n",
    "                            ghost_players = df[df.time == 0.0]['name'].to_list()\n",
    "                            df = df[~df.name.isin(ghost_players)]\n",
    "                            abc_final = abc_final[~abc_final.name.isin(ghost_players)]\n",
    "                        player_dict = {}\n",
    "                        for i, player in enumerate(df.name.unique()):\n",
    "                            player_dict[player] = f'player{i+1}'\n",
    "                        abc_final = abc_final.replace(player_dict)\n",
    "                    z = abc_final.pivot_table(values = ['risk','risk_std','landing_risk'], index = ['matchId','teamId'], columns = ['name'])\n",
    "                    z = z.reset_index()\n",
    "                    z.columns = z.columns.droplevel(1)\n",
    "                    if len(z.columns) == 14:\n",
    "                        z.columns = ['matchId','teamId', 'landing_risk1', 'landing_risk2', 'landing_risk3', 'landing_risk4',\n",
    "                                    'risk1', 'risk2', 'risk3', 'risk4', 'risk_std1', 'risk_std2', 'risk_std3', 'risk_std4']\n",
    "                    elif len(z.columns) == 11:\n",
    "                        z.columns = ['matchId','teamId', 'landing_risk1', 'landing_risk2', 'landing_risk3',\n",
    "                                    'risk1', 'risk2', 'risk3', 'risk_std1', 'risk_std2', 'risk_std3']\n",
    "                    elif len(z.columns) == 8:\n",
    "                        z.columns = ['matchId','teamId', 'landing_risk1', 'landing_risk2',\n",
    "                                    'risk1', 'risk2', 'risk_std1', 'risk_std2']\n",
    "                    elif len(z.columns) == 5:\n",
    "                        z.columns = ['matchId','teamId', 'landing_risk1',\n",
    "                                    'risk1', 'risk_std1']\n",
    "                    z.to_csv(f'D:/PUBG/risk_data/{matchid}.csv')\n",
    "                except (KeyboardInterrupt, SystemExit):\n",
    "                    raise\n",
    "            except Exception as e:\n",
    "                print(f'!!! {filename}: {e}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_filenames = [f'D:/PUBG/risk_data/{i}' for i in os.listdir('D:/PUBG/risk_data')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\admin\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "#combine all files in the list\n",
    "risk_csv = pd.concat([pd.read_csv(f, index_col=0) for f in all_filenames ])\n",
    "#export to csv\n",
    "risk_csv.to_csv( \"D:/PUBG/risk_csv.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "risk_csv = pd.read_csv('D:/PUBG/risk_csv.csv')\n",
    "risk_csv = risk_csv.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "risk_raw = risk_csv.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "risk_csv = risk_csv[['matchId','teamId', 'landing_risk1', 'landing_risk2', 'landing_risk3', 'landing_risk4',\n",
    "                                    'risk1', 'risk2', 'risk3', 'risk4', 'risk_std1', 'risk_std2', 'risk_std3', 'risk_std4']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>matchId</th>\n",
       "      <th>teamId</th>\n",
       "      <th>landing_risk1</th>\n",
       "      <th>landing_risk2</th>\n",
       "      <th>landing_risk3</th>\n",
       "      <th>landing_risk4</th>\n",
       "      <th>risk1</th>\n",
       "      <th>risk2</th>\n",
       "      <th>risk3</th>\n",
       "      <th>risk4</th>\n",
       "      <th>risk_std1</th>\n",
       "      <th>risk_std2</th>\n",
       "      <th>risk_std3</th>\n",
       "      <th>risk_std4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0001759f-70d8-4d4b-8d6d-81def45f2ee3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>35.9375</td>\n",
       "      <td>1.5625</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>12.5</td>\n",
       "      <td>12.687500</td>\n",
       "      <td>27.760417</td>\n",
       "      <td>6.122820</td>\n",
       "      <td>13.728762</td>\n",
       "      <td>23.564241</td>\n",
       "      <td>27.901662</td>\n",
       "      <td>12.887414</td>\n",
       "      <td>24.937355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0001759f-70d8-4d4b-8d6d-81def45f2ee3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>150.0000</td>\n",
       "      <td>46.8750</td>\n",
       "      <td>31.2500</td>\n",
       "      <td>37.5</td>\n",
       "      <td>27.812500</td>\n",
       "      <td>24.657012</td>\n",
       "      <td>80.989583</td>\n",
       "      <td>20.632102</td>\n",
       "      <td>22.759540</td>\n",
       "      <td>20.721578</td>\n",
       "      <td>53.007736</td>\n",
       "      <td>16.145332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0001759f-70d8-4d4b-8d6d-81def45f2ee3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>200.0000</td>\n",
       "      <td>200.0000</td>\n",
       "      <td>4.6875</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.535194</td>\n",
       "      <td>13.108553</td>\n",
       "      <td>9.086772</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.961613</td>\n",
       "      <td>16.731584</td>\n",
       "      <td>9.776795</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0001759f-70d8-4d4b-8d6d-81def45f2ee3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0001759f-70d8-4d4b-8d6d-81def45f2ee3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>21.8750</td>\n",
       "      <td>7.8125</td>\n",
       "      <td>10.9375</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.468202</td>\n",
       "      <td>5.963010</td>\n",
       "      <td>10.984375</td>\n",
       "      <td>4.890625</td>\n",
       "      <td>7.089531</td>\n",
       "      <td>7.920307</td>\n",
       "      <td>12.189270</td>\n",
       "      <td>6.929788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>666000</td>\n",
       "      <td>fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12</td>\n",
       "      <td>200.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.076923</td>\n",
       "      <td>0.021053</td>\n",
       "      <td>0.062893</td>\n",
       "      <td>0.158730</td>\n",
       "      <td>0.268543</td>\n",
       "      <td>0.144321</td>\n",
       "      <td>0.268270</td>\n",
       "      <td>0.409813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>666001</td>\n",
       "      <td>fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12</td>\n",
       "      <td>201.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.387755</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.663551</td>\n",
       "      <td>3.096154</td>\n",
       "      <td>1.230041</td>\n",
       "      <td>1.609630</td>\n",
       "      <td>1.665091</td>\n",
       "      <td>13.665844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>666002</td>\n",
       "      <td>fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12</td>\n",
       "      <td>202.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.905405</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.044776</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.293648</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.270496</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>666003</td>\n",
       "      <td>fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12</td>\n",
       "      <td>203.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.016667</td>\n",
       "      <td>1.391304</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.201923</td>\n",
       "      <td>0.129099</td>\n",
       "      <td>1.767496</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.685920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>666004</td>\n",
       "      <td>fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12</td>\n",
       "      <td>204.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.045455</td>\n",
       "      <td>0.054054</td>\n",
       "      <td>0.082192</td>\n",
       "      <td>23.910000</td>\n",
       "      <td>0.213201</td>\n",
       "      <td>0.328798</td>\n",
       "      <td>0.276557</td>\n",
       "      <td>33.204325</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>666005 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     matchId  teamId  landing_risk1  \\\n",
       "0       0001759f-70d8-4d4b-8d6d-81def45f2ee3     1.0        35.9375   \n",
       "1       0001759f-70d8-4d4b-8d6d-81def45f2ee3     2.0       150.0000   \n",
       "2       0001759f-70d8-4d4b-8d6d-81def45f2ee3     3.0       200.0000   \n",
       "3       0001759f-70d8-4d4b-8d6d-81def45f2ee3     4.0            NaN   \n",
       "4       0001759f-70d8-4d4b-8d6d-81def45f2ee3     5.0        21.8750   \n",
       "...                                      ...     ...            ...   \n",
       "666000  fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12   200.0         0.0000   \n",
       "666001  fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12   201.0         0.0000   \n",
       "666002  fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12   202.0         0.0000   \n",
       "666003  fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12   203.0         0.0000   \n",
       "666004  fffef9cf-5b06-4af6-b8f6-5a5aa80dbd12   204.0         0.0000   \n",
       "\n",
       "        landing_risk2  landing_risk3  landing_risk4      risk1      risk2  \\\n",
       "0              1.5625         0.0000           12.5  12.687500  27.760417   \n",
       "1             46.8750        31.2500           37.5  27.812500  24.657012   \n",
       "2            200.0000         4.6875            NaN  16.535194  13.108553   \n",
       "3                 NaN            NaN            NaN   0.000000        NaN   \n",
       "4              7.8125        10.9375            0.0   4.468202   5.963010   \n",
       "...               ...            ...            ...        ...        ...   \n",
       "666000         0.0000         0.0000            0.0   0.076923   0.021053   \n",
       "666001         0.0000         0.0000            0.0   0.387755   0.250000   \n",
       "666002         0.0000         0.0000            0.0   0.905405   0.000000   \n",
       "666003         0.0000         0.0000            0.0   0.016667   1.391304   \n",
       "666004         0.0000         0.0000            0.0   0.045455   0.054054   \n",
       "\n",
       "            risk3      risk4  risk_std1  risk_std2  risk_std3  risk_std4  \n",
       "0        6.122820  13.728762  23.564241  27.901662  12.887414  24.937355  \n",
       "1       80.989583  20.632102  22.759540  20.721578  53.007736  16.145332  \n",
       "2        9.086772        NaN  12.961613  16.731584   9.776795        NaN  \n",
       "3             NaN        NaN   0.000000        NaN        NaN        NaN  \n",
       "4       10.984375   4.890625   7.089531   7.920307  12.189270   6.929788  \n",
       "...           ...        ...        ...        ...        ...        ...  \n",
       "666000   0.062893   0.158730   0.268543   0.144321   0.268270   0.409813  \n",
       "666001   0.663551   3.096154   1.230041   1.609630   1.665091  13.665844  \n",
       "666002   0.044776   0.000000   0.293648   0.000000   0.270496   0.000000  \n",
       "666003   0.000000   1.201923   0.129099   1.767496   0.000000   1.685920  \n",
       "666004   0.082192  23.910000   0.213201   0.328798   0.276557  33.204325  \n",
       "\n",
       "[666005 rows x 14 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "risk_csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "a = risk_raw.iloc[:,0:8]\n",
    "index1 = a.dropna(how='all').index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>risk</th>\n",
       "      <th>risk.1</th>\n",
       "      <th>risk.2</th>\n",
       "      <th>risk.3</th>\n",
       "      <th>risk.4</th>\n",
       "      <th>risk.5</th>\n",
       "      <th>risk.6</th>\n",
       "      <th>risk.7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5770</td>\n",
       "      <td>5.413408</td>\n",
       "      <td>6.067010</td>\n",
       "      <td>4.540541</td>\n",
       "      <td>2.993631</td>\n",
       "      <td>4.718391</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13426</td>\n",
       "      <td>10.802083</td>\n",
       "      <td>0.611940</td>\n",
       "      <td>1.153846</td>\n",
       "      <td>1.962963</td>\n",
       "      <td>1.437500</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14919</td>\n",
       "      <td>17.931818</td>\n",
       "      <td>1.259259</td>\n",
       "      <td>0.924528</td>\n",
       "      <td>2.307692</td>\n",
       "      <td>2.714286</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20607</td>\n",
       "      <td>54.241379</td>\n",
       "      <td>0.233161</td>\n",
       "      <td>17.803922</td>\n",
       "      <td>0.105263</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24103</td>\n",
       "      <td>0.783333</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.725806</td>\n",
       "      <td>0.793651</td>\n",
       "      <td>0.935484</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>657274</td>\n",
       "      <td>14.242424</td>\n",
       "      <td>27.153846</td>\n",
       "      <td>2.066116</td>\n",
       "      <td>39.585714</td>\n",
       "      <td>13.692308</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>657357</td>\n",
       "      <td>2.052288</td>\n",
       "      <td>26.887640</td>\n",
       "      <td>17.678161</td>\n",
       "      <td>1.483660</td>\n",
       "      <td>30.440476</td>\n",
       "      <td>1.588235</td>\n",
       "      <td>28.954545</td>\n",
       "      <td>1.401316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>658127</td>\n",
       "      <td>19.243243</td>\n",
       "      <td>19.609756</td>\n",
       "      <td>12.260000</td>\n",
       "      <td>1.351351</td>\n",
       "      <td>2.475524</td>\n",
       "      <td>4.135417</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>658128</td>\n",
       "      <td>3.842105</td>\n",
       "      <td>1.213415</td>\n",
       "      <td>1.485294</td>\n",
       "      <td>1.054264</td>\n",
       "      <td>1.694805</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>663233</td>\n",
       "      <td>12.269231</td>\n",
       "      <td>24.527027</td>\n",
       "      <td>30.575221</td>\n",
       "      <td>49.090909</td>\n",
       "      <td>32.056075</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>139 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             risk     risk.1     risk.2     risk.3     risk.4    risk.5  \\\n",
       "5770     5.413408   6.067010   4.540541   2.993631   4.718391       NaN   \n",
       "13426   10.802083   0.611940   1.153846   1.962963   1.437500       NaN   \n",
       "14919   17.931818   1.259259   0.924528   2.307692   2.714286       NaN   \n",
       "20607   54.241379   0.233161  17.803922   0.105263   0.200000       NaN   \n",
       "24103    0.783333   0.100000   0.725806   0.793651   0.935484  0.454545   \n",
       "...           ...        ...        ...        ...        ...       ...   \n",
       "657274  14.242424  27.153846   2.066116  39.585714  13.692308       NaN   \n",
       "657357   2.052288  26.887640  17.678161   1.483660  30.440476  1.588235   \n",
       "658127  19.243243  19.609756  12.260000   1.351351   2.475524  4.135417   \n",
       "658128   3.842105   1.213415   1.485294   1.054264   1.694805       NaN   \n",
       "663233  12.269231  24.527027  30.575221  49.090909  32.056075       NaN   \n",
       "\n",
       "           risk.6    risk.7  \n",
       "5770          NaN       NaN  \n",
       "13426         NaN       NaN  \n",
       "14919         NaN       NaN  \n",
       "20607         NaN       NaN  \n",
       "24103    0.600000       NaN  \n",
       "...           ...       ...  \n",
       "657274        NaN       NaN  \n",
       "657357  28.954545  1.401316  \n",
       "658127        NaN       NaN  \n",
       "658128        NaN       NaN  \n",
       "663233        NaN       NaN  \n",
       "\n",
       "[139 rows x 8 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = risk_raw.iloc[:,13:21]\n",
    "b.dropna(subset=['risk.4'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>risk_std</th>\n",
       "      <th>risk_std.1</th>\n",
       "      <th>risk_std.2</th>\n",
       "      <th>risk_std.3</th>\n",
       "      <th>risk_std.4</th>\n",
       "      <th>risk_std.5</th>\n",
       "      <th>risk_std.6</th>\n",
       "      <th>risk_std.7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>5770</td>\n",
       "      <td>13.907279</td>\n",
       "      <td>16.777441</td>\n",
       "      <td>9.238379</td>\n",
       "      <td>9.703619</td>\n",
       "      <td>12.955951</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13426</td>\n",
       "      <td>18.284901</td>\n",
       "      <td>1.476639</td>\n",
       "      <td>1.603032</td>\n",
       "      <td>4.269077</td>\n",
       "      <td>3.788987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14919</td>\n",
       "      <td>15.761888</td>\n",
       "      <td>2.069294</td>\n",
       "      <td>1.639055</td>\n",
       "      <td>5.140929</td>\n",
       "      <td>5.171619</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20607</td>\n",
       "      <td>41.071316</td>\n",
       "      <td>1.119216</td>\n",
       "      <td>23.099457</td>\n",
       "      <td>0.311012</td>\n",
       "      <td>0.987756</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24103</td>\n",
       "      <td>1.059128</td>\n",
       "      <td>0.307794</td>\n",
       "      <td>1.161815</td>\n",
       "      <td>1.597970</td>\n",
       "      <td>1.491736</td>\n",
       "      <td>1.184313</td>\n",
       "      <td>1.846761</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>657274</td>\n",
       "      <td>26.171347</td>\n",
       "      <td>39.783104</td>\n",
       "      <td>5.317684</td>\n",
       "      <td>29.415313</td>\n",
       "      <td>16.877842</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>657357</td>\n",
       "      <td>4.101975</td>\n",
       "      <td>26.156607</td>\n",
       "      <td>18.500738</td>\n",
       "      <td>2.690394</td>\n",
       "      <td>27.617295</td>\n",
       "      <td>3.029524</td>\n",
       "      <td>28.219916</td>\n",
       "      <td>3.241412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>658127</td>\n",
       "      <td>14.449924</td>\n",
       "      <td>18.873100</td>\n",
       "      <td>13.113025</td>\n",
       "      <td>5.427380</td>\n",
       "      <td>4.320221</td>\n",
       "      <td>5.570340</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>658128</td>\n",
       "      <td>5.816288</td>\n",
       "      <td>2.095175</td>\n",
       "      <td>2.403656</td>\n",
       "      <td>1.737950</td>\n",
       "      <td>2.479532</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>663233</td>\n",
       "      <td>11.658765</td>\n",
       "      <td>33.769250</td>\n",
       "      <td>39.085484</td>\n",
       "      <td>47.690379</td>\n",
       "      <td>39.663165</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>139 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         risk_std  risk_std.1  risk_std.2  risk_std.3  risk_std.4  risk_std.5  \\\n",
       "5770    13.907279   16.777441    9.238379    9.703619   12.955951         NaN   \n",
       "13426   18.284901    1.476639    1.603032    4.269077    3.788987         NaN   \n",
       "14919   15.761888    2.069294    1.639055    5.140929    5.171619         NaN   \n",
       "20607   41.071316    1.119216   23.099457    0.311012    0.987756         NaN   \n",
       "24103    1.059128    0.307794    1.161815    1.597970    1.491736    1.184313   \n",
       "...           ...         ...         ...         ...         ...         ...   \n",
       "657274  26.171347   39.783104    5.317684   29.415313   16.877842         NaN   \n",
       "657357   4.101975   26.156607   18.500738    2.690394   27.617295    3.029524   \n",
       "658127  14.449924   18.873100   13.113025    5.427380    4.320221    5.570340   \n",
       "658128   5.816288    2.095175    2.403656    1.737950    2.479532         NaN   \n",
       "663233  11.658765   33.769250   39.085484   47.690379   39.663165         NaN   \n",
       "\n",
       "        risk_std.6  risk_std.7  \n",
       "5770           NaN         NaN  \n",
       "13426          NaN         NaN  \n",
       "14919          NaN         NaN  \n",
       "20607          NaN         NaN  \n",
       "24103     1.846761         NaN  \n",
       "...            ...         ...  \n",
       "657274         NaN         NaN  \n",
       "657357   28.219916    3.241412  \n",
       "658127         NaN         NaN  \n",
       "658128         NaN         NaN  \n",
       "663233         NaN         NaN  \n",
       "\n",
       "[139 rows x 8 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = risk_raw.iloc[:,25:33]\n",
    "c.dropna(subset=['risk_std.4'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = c.dropna(subset=['risk_std.4']).index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Int64Index([  5770,  13426,  14919,  20607,  24103,  35521,  38588,  38805,\n",
       "             46168,  49877,\n",
       "            ...\n",
       "            629959, 632072, 632823, 641319, 645336, 657274, 657357, 658127,\n",
       "            658128, 663233],\n",
       "           dtype='int64', length=139)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_risk = risk_raw.drop(['landing_risk1', 'landing_risk2', 'landing_risk3', 'landing_risk4',\n",
    "                                    'risk1', 'risk2', 'risk3', 'risk4', 'risk_std1', 'risk_std2', 'risk_std3', 'risk_std4'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "extra_risk = extra_risk.iloc[index1,:]\n",
    "extra_risk = extra_risk.drop(index)\n",
    "extra_risk = extra_risk[['matchId','teamId', 'landing_risk', 'landing_risk.1', 'landing_risk.2', 'landing_risk.3',\n",
    "                                    'risk', 'risk.1', 'risk.2', 'risk.3', 'risk_std', 'risk_std.1', 'risk_std.2', 'risk_std.3']]\n",
    "extra_risk.columns = ['matchId','teamId', 'landing_risk1', 'landing_risk2', 'landing_risk3', 'landing_risk4',\n",
    "                                    'risk1', 'risk2', 'risk3', 'risk4', 'risk_std1', 'risk_std2', 'risk_std3', 'risk_std4']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "risk_csv = pd.concat([risk_csv, extra_risk])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "risk_csv.to_csv( \"D:/PUBG/risk_csv.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export to .csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'eb83b1e3-7ca6-46cd-aa45-a45cf6ab9ba0.json'"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export match telemetry data into .csv format\n",
    "players_location.to_csv(path_or_buf=f'{matchid}.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020-04-10 console 532\n"
     ]
    }
   ],
   "source": [
    "for direct in os.listdir('D:/PUBG/telemetry_data'):\n",
    "    for d2 in os.listdir(f'D:/PUBG/telemetry_data/{direct}'):\n",
    "        for i, filename in enumerate(os.listdir(f'D:/PUBG/telemetry_data/{direct}/{d2}')):\n",
    "            if filename == 'eb83b1e3-7ca6-46cd-aa45-a45cf6ab9ba0.json':\n",
    "                print(direct, d2, i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}