murder.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Murder Game"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "crime_report = pd.read_csv('crime_scene_report.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                          0     \\\n",
      "date                                                  20180115   \n",
      "type                                                   robbery   \n",
      "description  A Man Dressed as Spider-Man Is on a Robbery Spree   \n",
      "city                                                       NYC   \n",
      "\n",
      "                                          1     \\\n",
      "date                                  20180115   \n",
      "type                                    murder   \n",
      "description  Life? Dont talk to me about life.   \n",
      "city                                    Albany   \n",
      "\n",
      "                                                          2     \\\n",
      "date                                                  20180115   \n",
      "type                                                    murder   \n",
      "description  Mama, I killed a man, put a gun against his he...   \n",
      "city                                                      Reno   \n",
      "\n",
      "                                   3     \\\n",
      "date                           20180215   \n",
      "type                             murder   \n",
      "description  REDACTED REDACTED REDACTED   \n",
      "city                           SQL City   \n",
      "\n",
      "                                                          4     \\\n",
      "date                                                  20180215   \n",
      "type                                                    murder   \n",
      "description  Someone killed the guard! He took an arrow to ...   \n",
      "city                                                  SQL City   \n",
      "\n",
      "                                        5     \\\n",
      "date                                20180115   \n",
      "type                                   theft   \n",
      "description  Big Bully stole my lunch money!   \n",
      "city                                 Chicago   \n",
      "\n",
      "                                                          6     \\\n",
      "date                                                  20180115   \n",
      "type                                                     fraud   \n",
      "description  Lorem ipsum dolor sit amet, consectetur adipis...   \n",
      "city                                                   Seattle   \n",
      "\n",
      "                                                          7     \\\n",
      "date                                                  20170712   \n",
      "type                                                     theft   \n",
      "description  A lone hunter stalks the night, firing arrows ...   \n",
      "city                                                  SQL City   \n",
      "\n",
      "                                                          8     \\\n",
      "date                                                  20170820   \n",
      "type                                                     arson   \n",
      "description  Wield the Hammer of Sol with honor, Titan, it ...   \n",
      "city                                                  SQL City   \n",
      "\n",
      "                                                          9     ...  \\\n",
      "date                                                  20171110  ...   \n",
      "type                                                   robbery  ...   \n",
      "description  The Gjallarhorn shoulder-mounted rocket system...  ...   \n",
      "city                                                  SQL City  ...   \n",
      "\n",
      "                                                          1218  \\\n",
      "date                                                  20180428   \n",
      "type                                                     arson   \n",
      "description  remained some time after the rest of it had go...   \n",
      "city                                                    Denton   \n",
      "\n",
      "                                   1219  \\\n",
      "date                           20180428   \n",
      "type                          blackmail   \n",
      "description  other side of the court.\\n   \n",
      "city                             Lorain   \n",
      "\n",
      "                                                          1220  \\\n",
      "date                                                  20180429   \n",
      "type                                                    murder   \n",
      "description  (she was obliged to say ‘creatures,’ you see, ...   \n",
      "city                                                    Toledo   \n",
      "\n",
      "                                             1221  \\\n",
      "date                                     20180430   \n",
      "type                                       murder   \n",
      "description  ‘But what am I to do?’ said Alice.\\n   \n",
      "city                                      Spokane   \n",
      "\n",
      "                                                          1222          1223  \\\n",
      "date                                                  20180430      20180430   \n",
      "type                                                   robbery       bribery   \n",
      "description  ‘And how did you manage on the twelfth?’ Alice...            \\n   \n",
      "city                                                   Trenton  Garden Grove   \n",
      "\n",
      "                                          1224      1225  \\\n",
      "date                                  20180430  20180430   \n",
      "type                                     fraud   assault   \n",
      "description  ‘Why not?’ said the March Hare.\\n        \\n   \n",
      "city                                     Houma   Fontana   \n",
      "\n",
      "                                                          1226  \\\n",
      "date                                                  20180501   \n",
      "type                                                   assault   \n",
      "description  be NO mistake about it: it was neither more no...   \n",
      "city                                                   Trenton   \n",
      "\n",
      "                                                          1227  \n",
      "date                                                  20180115  \n",
      "type                                                    murder  \n",
      "description  Security footage shows that there were 2 witne...  \n",
      "city                                                  SQL City  \n",
      "\n",
      "[4 rows x 1228 columns]\n"
     ]
    }
   ],
   "source": [
    "print(data_crime.T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_condition = 20180115\n",
    "type_condition = \"murder\"\n",
    "city_condition = \"SQL City\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Security footage shows that there were 2 witnesses. The first witness lives at the last house on \"Northwestern Dr\". The second witness, named Annabel, lives somewhere on \"Franklin Ave\".']\n"
     ]
    }
   ],
   "source": [
    "#我的方法\n",
    "filtered_data_crime = crime_report.loc[(crime_report['date'] == date_condition) \n",
    "                                       & (crime_report['type'] == type_condition) \n",
    "                                       & (crime_report['city'] == city_condition)]\n",
    "print(filtered_data_crime['description'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fiding the info of the witness"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "witness = pd.read_csv('person.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        id            name  license_id  address_number address_street_name  \\\n",
      "665  16371  Annabel Miller      490173             103        Franklin Ave   \n",
      "\n",
      "           ssn  \n",
      "665  318771143  \n"
     ]
    }
   ],
   "source": [
    "#The info of the second witness\n",
    "name_condition = \"Annabel\"\n",
    "address_street_name_condition = \"Franklin Ave\"\n",
    "\n",
    "filtered_witness = witness[(witness['name'].str.contains(name_condition, case=False, na=False)) \n",
    "                           & (witness['address_street_name'] == \"Franklin Ave\")]\n",
    "print(filtered_witness)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the info of the first witness\n",
    "\n",
    "filtered_witness2 = witness[(witness['address_street_name'] == \"Northwestern Dr\")]                            \n",
    "print(filtered_witness2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        id            name  license_id  address_number address_street_name  \\\n",
      "499  14887  Morty Schapiro      118009            4919     Northwestern Dr   \n",
      "\n",
      "           ssn  \n",
      "499  111564949  \n"
     ]
    }
   ],
   "source": [
    "filtered_witness3 = filtered_witness2[(filtered_witness2['address_number'] == filtered_witness2['address_number'].max())]\n",
    "print(filtered_witness3)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Interview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "interview = pd.read_csv('interview.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[14887\n",
      "  'I heard a gunshot and then saw a man run out. He had a \"Get Fit Now Gym\" bag. The membership number on the bag started with \"48Z\". Only gold members have those bags. The man got into a car with a plate that included \"H42W\".']]\n",
      "[[16371\n",
      "  'I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.']]\n"
     ]
    }
   ],
   "source": [
    "print(interview.loc[(interview['person_id'] == 14887)].values)\n",
    "print(interview.loc[(interview['person_id'] == 16371)].values)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clue 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "driver = pd.read_csv('drivers_license.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          id  age  height eye_color hair_color  gender plate_number car_make  \\\n",
      "1105  202298   68      66     green        red  female       500123    Tesla   \n",
      "2054  291182   65      66      blue        red  female       08CM64    Tesla   \n",
      "6428  682231   51      80     green       grey  female       3Z0M62    Tesla   \n",
      "9078  918773   48      65     black        red  female       917UU3    Tesla   \n",
      "\n",
      "     car_model  \n",
      "1105   Model S  \n",
      "2054   Model S  \n",
      "6428   Model S  \n",
      "9078   Model S  \n"
     ]
    }
   ],
   "source": [
    "filtered_driver = driver[(driver['car_model'] == \"Model S\") & (driver['gender'] == \"female\")]\n",
    "print(filtered_driver)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "event = pd.read_csv('facebook_event_check_in.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       person_id  age  height eye_color hair_color gender plate_number  \\\n",
      "15         97207  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "16         97207  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "113        92433  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "114        92433  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "300        88952  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "...          ...  ...     ...       ...        ...    ...          ...   \n",
      "20007      24556  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20010      99716  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20011      99716  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20012      99716  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20014      67318  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "\n",
      "      car_make car_model  event_id            event_name        date  \n",
      "15         NaN       NaN    1143.0  SQL Symphony Concert  20170322.0  \n",
      "16         NaN       NaN    1143.0  SQL Symphony Concert  20180226.0  \n",
      "113        NaN       NaN    1143.0  SQL Symphony Concert  20170821.0  \n",
      "114        NaN       NaN    1143.0  SQL Symphony Concert  20180327.0  \n",
      "300        NaN       NaN    1143.0  SQL Symphony Concert  20170918.0  \n",
      "...        ...       ...       ...                   ...         ...  \n",
      "20007      NaN       NaN    1143.0  SQL Symphony Concert  20171224.0  \n",
      "20010      NaN       NaN    1143.0  SQL Symphony Concert  20171206.0  \n",
      "20011      NaN       NaN    1143.0  SQL Symphony Concert  20171212.0  \n",
      "20012      NaN       NaN    1143.0  SQL Symphony Concert  20171229.0  \n",
      "20014      NaN       NaN    1143.0  SQL Symphony Concert  20171206.0  \n",
      "\n",
      "[212 rows x 12 columns]\n"
     ]
    }
   ],
   "source": [
    "driver2 = filtered_driver.rename({'id': 'person_id'}, axis='columns')\n",
    "merged_data = driver2.merge(event, on = 'person_id', how = 'outer')\n",
    "filtered_data4 = merged_data[merged_data['event_name'] == 'SQL Symphony Concert']\n",
    "print(filtered_data4)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       person_id  age  height eye_color hair_color gender plate_number  \\\n",
      "4091       62596  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "4396       19260  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "6556       58898  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "6624       69699  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "6721       19292  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "7270       43366  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "8399       92343  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "10913      28582  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "10914      28582  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "13723      81526  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "13749      24397  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "14316      11173  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "15309      79312  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "15900      69325  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20005      24556  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20006      24556  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20007      24556  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20010      99716  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20011      99716  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20012      99716  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "20014      67318  NaN     NaN       NaN        NaN    NaN          NaN   \n",
      "\n",
      "      car_make car_model  event_id            event_name        date  \n",
      "4091       NaN       NaN    1143.0  SQL Symphony Concert  20171225.0  \n",
      "4396       NaN       NaN    1143.0  SQL Symphony Concert  20171214.0  \n",
      "6556       NaN       NaN    1143.0  SQL Symphony Concert  20171220.0  \n",
      "6624       NaN       NaN    1143.0  SQL Symphony Concert  20171214.0  \n",
      "6721       NaN       NaN    1143.0  SQL Symphony Concert  20171213.0  \n",
      "7270       NaN       NaN    1143.0  SQL Symphony Concert  20171207.0  \n",
      "8399       NaN       NaN    1143.0  SQL Symphony Concert  20171212.0  \n",
      "10913      NaN       NaN    1143.0  SQL Symphony Concert  20171220.0  \n",
      "10914      NaN       NaN    1143.0  SQL Symphony Concert  20171215.0  \n",
      "13723      NaN       NaN    1143.0  SQL Symphony Concert  20171202.0  \n",
      "13749      NaN       NaN    1143.0  SQL Symphony Concert  20171208.0  \n",
      "14316      NaN       NaN    1143.0  SQL Symphony Concert  20171223.0  \n",
      "15309      NaN       NaN    1143.0  SQL Symphony Concert  20171203.0  \n",
      "15900      NaN       NaN    1143.0  SQL Symphony Concert  20171206.0  \n",
      "20005      NaN       NaN    1143.0  SQL Symphony Concert  20171207.0  \n",
      "20006      NaN       NaN    1143.0  SQL Symphony Concert  20171221.0  \n",
      "20007      NaN       NaN    1143.0  SQL Symphony Concert  20171224.0  \n",
      "20010      NaN       NaN    1143.0  SQL Symphony Concert  20171206.0  \n",
      "20011      NaN       NaN    1143.0  SQL Symphony Concert  20171212.0  \n",
      "20012      NaN       NaN    1143.0  SQL Symphony Concert  20171229.0  \n",
      "20014      NaN       NaN    1143.0  SQL Symphony Concert  20171206.0  \n"
     ]
    }
   ],
   "source": [
    "date_condition = '201712'\n",
    "filtered_data5 = filtered_data4[filtered_data4['date'].astype(str).str.contains(date_condition, case=False, na=False)]\n",
    "print(filtered_data5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "lr_venv",
   "language": "python",
   "name": "lr_venv"
  },
  "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.10.9"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}