<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Solving Crimes with Data Science
<br/>
<br/>
<br/>
<b>Markus Harrer</b>
@feststelltaste
<small>PyDay 1, 19. Dezember 2019</small>
<img src="resources/innoq_logo.jpg" width=20% height="20%" align="right"/>
Facts of the case
a white bus with a red "g" sign on the side window was stolen
police did an innovative mpbile phone investigation
there was only phone number of unknown identity
Step1:  Let's look into the dataset
Step2:  Data Cleaning
Convert the text in Start to a date data type
Step3:  Filtering
We know the suspect's phone number (4638472273) and the TowerID of the Caller
Step4:  But...
Unfortunately
Step5:  Join
Bringing datasets together
Step6:  Explore the data
Let's take a look at the locations
Step7:  Modeling
Make some hypothesis about the suspect's home
Hypothesis 1
Step8:  Let's take a look at the weekend's locations
Step9:  Hypothesis 2
Step10:  Keep only the sleeping hours
Step11:  Let's see where the suspect sleeps on weekends
Step12:  Clustering
Cell phones connect to various towers over time
Therefore
Step13:  Let's plot the main center of sleeping activity.
Step14:  Results
Let's check the result in Google Maps!
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
cdr = pd.read_excel("cdr_data.xlsx")
cdr.head()
Explanation: Solving Crimes with Data Science
<br/>
<br/>
<br/>
<b>Markus Harrer</b>
@feststelltaste
<small>PyDay 1, 19. Dezember 2019</small>
<img src="resources/innoq_logo.jpg" width=20% height="20%" align="right"/>
Facts of the case
a white bus with a red "g" sign on the side window was stolen
police did an innovative mpbile phone investigation
there was only phone number of unknown identity: +14638472273
Our approach: Where are the whereabouts / place of residence of the mobile phone owner?
What do we have?
CDRs (Call Data Records) in an Excel file!
That means: Information about the cell towers used for the phone calls!
Import and Load
Using pandas to read an Excel file into a Dataframe.
End of explanation
cdr.info()
Explanation: Let's look into the dataset
End of explanation
cdr['Start'] = pd.to_datetime(cdr['Start'])
cdr.head()
Explanation: Data Cleaning
Convert the text in Start to a date data type
End of explanation
suspect = cdr[cdr['Caller'] == 4638472273]
suspect = suspect[suspect['Event'] == 'Incoming']
suspect.head()
Explanation: Filtering
We know the suspect's phone number (4638472273) and the TowerID of the Caller
End of explanation
towers = pd.read_csv("darknet.io/hacks/infrastructure/mobile_net/texas_towers.csv", index_col=0)
towers.head()
Explanation: But...
Unfortunately: Information about the tower's locations are missing!
We need a second data source from the DARKNET!
Load another dataset
This time: a CSV file
End of explanation
suspect_loc = suspect.join(towers, on='TowerID')
suspect_loc.head()
Explanation: Join
Bringing datasets together
End of explanation
suspect_loc.plot.scatter('TowerLon', "TowerLat");
Explanation: Explore the data
Let's take a look at the locations
End of explanation
suspect_loc['DoW'] = suspect_loc['Start'].dt.weekday_name
suspect_loc.head()
suspect_on_weekend = suspect_loc[suspect_loc['DoW'].isin(['Saturday', 'Sunday'])].copy()
suspect_on_weekend.head()
Explanation: Modeling
Make some hypothesis about the suspect's home
Hypothesis 1: Home at weekends
End of explanation
suspect_on_weekend.plot.scatter('TowerLon', "TowerLat");
Explanation: Let's take a look at the weekend's locations
End of explanation
suspect_on_weekend['hour'] = suspect_on_weekend['Start'].dt.hour
suspect_on_weekend.head()
Explanation: Hypothesis 2: Sleeping at night
End of explanation
suspect_on_weekend_night = suspect_on_weekend[
    (suspect_on_weekend['hour'] < 6)  | (suspect_on_weekend['hour'] > 22)]
suspect_on_weekend_night.head()
Explanation: Keep only the sleeping hours
End of explanation
ax = suspect_on_weekend_night.plot.scatter('TowerLat', 'TowerLon')
Explanation: Let's see where the suspect sleeps on weekends
End of explanation
from sklearn.cluster import KMeans
kmeans = KMeans(n_clusters = 1)
data = suspect_on_weekend_night[['TowerLat', 'TowerLon']]
kmeans.fit_predict(data)
centroids = kmeans.cluster_centers_
centroids
Explanation: Clustering
Cell phones connect to various towers over time
Therefore: Find the main center of his sleeping activities
End of explanation
ax.scatter(x = centroids[:, 0], y = centroids[:, 1], c = 'r', marker = 'x')
ax.figure
Explanation: Let's plot the main center of sleeping activity.
End of explanation
print("https://www.google.com/maps/search/?api=1&query={},{}".format(centroids[0][0], centroids[0][1]))
Explanation: Results
Let's check the result in Google Maps!
End of explanation
<END_TASK>