import pandas as pd
cdr = pd.read_excel("cdr_data_export.xlsx")
cdr.head()

"""
Explanation: Solcing Crimes with Data Science
Markus Harrer
INNOQ Deutschland
Facts of the case

a white bus with a red sign on the side window was stolen
police did an innovative mpbile phone investigation
there was only phone number of unknown identity: 04638472273

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
"""


towers = pd.read_csv("darknet.io/hacks/infrastructure/mobile_net/texas_towers.csv", index_col=0)
towers.head()

"""
Explanation: 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
"""


call_data = cdr.join(towers, on='TowerID')
call_data.head()

call_data[['Caller', 'Symbol', 'Callee']] = call_data['Call'].str.split("(->|-X)", expand=True)
call_data.head()

call_data['Event'] = call_data['Symbol'].map(
{
    "->" : "Incoming",
    "-X" : "Missed"
})
call_data.head()

"""
Explanation: Join
Bringing datasets together
End of explanation
"""


suspect_data = call_data[(call_data['Callee'] == '04638472273') | (call_data['Caller'] == '04638472273')].copy()
suspect_data.head()

suspect_data['Start'] = pd.to_datetime(suspect_data['Start'])
suspect_data.head()

suspect_data['DoW'] = suspect_data['Start'].dt.weekday_name
suspect_data.head()

suspect_data.plot.scatter('TowerLon', "TowerLat");

suspect_on_weekend = suspect_data[suspect_data['DoW'].isin(['Saturday', 'Sunday'])].copy()
suspect_on_weekend.head()

suspect_on_weekend.plot.scatter('TowerLon', "TowerLat");

suspect_on_weekend['Start']
suspect_on_weekend['hour'] = suspect_on_weekend['Start'].dt.hour
suspect_on_weekend.head()

suspect_on_weekend_night = suspect_on_weekend[
    (suspect_on_weekend['hour'] < 6)  | (suspect_on_weekend['hour'] > 22)]
suspect_on_weekend_night.head()

ax = suspect_on_weekend_night.plot.scatter('TowerLat', 'TowerLon')

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_

ax.scatter(x = centroids[:, 0], y = centroids[:, 1], c = 'r', marker = 'x')
ax.figure

centroids

"""
Explanation: Filtering
End of explanation
"""


