<jupyter_start><jupyter_text>Part 3: Exploratory Data Analysis**In this notebook,** I explore several aspects of the dataframe and also prepare the dataframe objects for further modeling. Importing the required libraries:<jupyter_code>import requests
import json
import pandas as pd
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

import pickle

## for geospatial
import folium
import geopy
import datetime
from mpl_toolkits.basemap import Basemap

## for machine learning
from sklearn import preprocessing, cluster

import pandas as pd
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import silhouette_score
from sklearn.datasets import load_iris, load_wine
from sklearn.cluster import DBSCAN
import seaborn as sns
import matplotlib.pyplot as plt

from sklearn import metrics

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Importing the saved clean time-series earthquake data<jupyter_code>file_path = "../datasets/" + "combined_eq_california" + "_timeseries" + ".csv"

df_eq = pd.read_csv(file_path)

df_eq = df_eq[["time", "mag", "sig", "longitude", "latitude", "depth"]]

df_eq["time"] = pd.to_datetime(df_eq["time"])
df_eq["timestamps"] = df_eq["time"]
# df_eq.set_index('time', inplace=True)


df_eq
df_eq.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 313332 entries, 0 to 313331
Data columns (total 7 columns):
 #   Column      Non-Null Count   Dtype         
---  ------      --------------   -----         
 0   time        313332 non-null  datetime64[ns]
 1   mag         313332 non-null  float64       
 2   sig         313332 non-null  int64         
 3   longitude   313332 non-null  float64       
 4   latitude    313332 non-null  float64       
 5   depth       313332 non-null  float64       
 6   timestamps  313332 non-null  datetime64[ns]
dtypes: datetime64[ns](2), float64(4), int64(1)
memory usage: 16.7 MB<jupyter_text>Important: need to drop repetitive rows (10 rows). There were some overlaps in original data.<jupyter_code>df_eq[df_eq.duplicated('timestamps')]
# Dropping rows
df_eq = df_eq.drop_duplicates(subset=['timestamps'])

df_eq["timestamps"].diff().describe()
df_eq.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 313332 entries, 0 to 313331
Data columns (total 7 columns):
 #   Column      Non-Null Count   Dtype         
---  ------      --------------   -----         
 0   time        313332 non-null  datetime64[ns]
 1   mag         313332 non-null  float64       
 2   sig         313332 non-null  int64         
 3   longitude   313332 non-null  float64       
 4   latitude    313332 non-null  float64       
 5   depth       313332 non-null  float64       
 6   timestamps  313332 non-null  datetime64[ns]
dtypes: datetime64[ns](2), float64(4), int64(1)
memory usage: 19.1 MB<jupyter_text>Feature engineering<jupyter_code># Number 1: Time intervals between consecutive earthquakes.
df_eq["time_diff"] = df_eq["timestamps"].diff()

df_eq["time_diff_float"] = df_eq["time_diff"].apply(lambda x: x.total_seconds())

#Number 2: the rolling of magnitudes from the last 10 earthquakes

df_eq["mag_roll_10"] = df_eq["mag"].rolling(window=10).mean()

df_eq.dropna(inplace = True)
df_eq.describe().T<jupyter_output><empty_output><jupyter_text>600 days without any recorded earthquakes:<jupyter_code>df_eq[df_eq["time_diff_float"] > 86400].shape
df_eq[df_eq["time_diff_float"] > 86400]
min_date = df_eq["time"].min().date()

min_date
max_date = df_eq["time"].max().date()

max_date
type(df_eq["time"].max().date())
#https://stackoverflow.com/questions/993358/creating-a-range-of-dates-in-python

start_date = min_date

number_of_days = (max_date - min_date).days
print("number_of_days", number_of_days)
date_list = []
for day in range(number_of_days):
  a_date = (start_date + datetime.timedelta(days = day))
  date_list.append(a_date)

print(date_list[1], type(date_list[0]))
df_eq
df_eq.set_index('time', inplace=True)
df_daily = pd.DataFrame() 
# AS is year-start frequency
df_daily['mag_day_max'] = df_eq.mag.resample('D').max() # What is the max of events
df_daily['event_day_count'] = df_eq.mag.resample('D').count() #How many events happened
df_daily['mag_day_mean'] = df_eq.mag.resample('D').mean() #What is the mean of values for that particular day
df_daily['mag_day_sum'] = df_eq.mag.resample('D').sum() # What is the sum of moments
df_daily['mag_day_scatter'] = (df_eq.mag.resample('D').std()) # What is the scatter of event magnitudes
df_daily["mag_day_roll_10"] = df_daily["mag_day_mean"].rolling(window=10).mean()


df_daily['longitude_day_mean'] = df_eq.longitude.resample('D').mean() #Mean location of events 
df_daily['longitude_day_std'] = df_eq.longitude.resample('D').std() #Std location of events 

df_daily['latitude_day_mean'] = df_eq.latitude.resample('D').mean() #Mean location of events 
df_daily['latitude_day_std'] = df_eq.latitude.resample('D').std() #Std location of events 

df_daily['depth_day_mean'] = df_eq.depth.resample('D').mean() #Mean location of events 
df_daily['depth_day_std'] = df_eq.depth.resample('D').std() #Std location of events 

df_daily['time_diff_float_day_mean'] = df_eq.time_diff_float.resample('D').mean() #Event spacing 
df_daily['time_diff_float_day_std'] = df_eq.time_diff_float.resample('D').std() #Std location of events 


df_daily
df_daily.info()
plt.figure(figsize = (8, 5))

sns.heatmap(df_daily.isnull(), cbar=False)
df_daily_clean = df_daily[df_daily.index > "1972-01-01"]

df_daily_clean.drop(columns = ["mag_day_roll_10"], inplace = True)

df_daily_clean
plt.figure(figsize = (8, 5))

sns.heatmap(df_daily_clean.isnull(), cbar=False)
df_daily_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 17806 entries, 1972-01-02 to 2020-10-01
Freq: D
Data columns (total 13 columns):
 #   Column                    Non-Null Count  Dtype  
---  ------                    --------------  -----  
 0   mag_day_max               17498 non-null  float64
 1   event_day_count           17806 non-null  int64  
 2   mag_day_mean              17498 non-null  float64
 3   mag_day_sum               17806 non-null  float64
 4   mag_day_scatter           17308 non-null  float64
 5   longitude_day_mean        17498 non-null  float64
 6   longitude_day_std         17308 non-null  float64
 7   latitude_day_mean         17498 non-null  float64
 8   latitude_day_std          17308 non-null  float64
 9   depth_day_mean            17498 non-null  float64
 10  depth_day_std             17308 non-null  float64
 11  time_diff_float_day_mean  17498 non-null  float64
 12  time_diff_float_day_std   17308 non-null  float64
dtypes: float64(12), int64(1)
memory usag[...]<jupyter_text>Imputing the missing values:<jupyter_code>#Source: https://medium.com/@drnesr/filling-gaps-of-a-time-series-using-python-d4bfddd8c460
df_daily_clean.interpolate('time', inplace = True)
df_daily_clean.head(2)
df_daily_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 17806 entries, 1972-01-02 to 2020-10-01
Freq: D
Data columns (total 13 columns):
 #   Column                    Non-Null Count  Dtype  
---  ------                    --------------  -----  
 0   mag_day_max               17806 non-null  float64
 1   event_day_count           17806 non-null  int64  
 2   mag_day_mean              17806 non-null  float64
 3   mag_day_sum               17806 non-null  float64
 4   mag_day_scatter           17806 non-null  float64
 5   longitude_day_mean        17806 non-null  float64
 6   longitude_day_std         17806 non-null  float64
 7   latitude_day_mean         17806 non-null  float64
 8   latitude_day_std          17806 non-null  float64
 9   depth_day_mean            17806 non-null  float64
 10  depth_day_std             17806 non-null  float64
 11  time_diff_float_day_mean  17806 non-null  float64
 12  time_diff_float_day_std   17806 non-null  float64
dtypes: float64(12), int64(1)
memory usag[...]<jupyter_text>Putting back the dataframe into original DataFrame aamd do stuff<jupyter_code>df_eq = df_daily_clean
df_eq.shape<jupyter_output><empty_output><jupyter_text>Loma Prieta<jupyter_code>df_eq_sel_range = df_eq['1985-09-19':'1989-10-19']
cluster_data = {"cluster_density": 0, "cluster_mag_max_mean": 0, "cluster_mag_max_max": 0, 
                "cluster_long_cent": 0, "cluster_long_std": 0, "cluster_lat_cent": 0, "cluster_lat_std": 0,
                "cluster_dept_cent": 0, "cluster_dept_std": 0
       }


cluster_data = pd.DataFrame(cluster_data, index=[0])




df_cluster_daily = df_eq_sel_range.copy()


eps_param = 0.33
min_sample = 7


for i in range(5 * min_sample, 5 * min_sample+1):#len(df_cluster_daily)):

    # Building cluster
    df_ = df_cluster_daily.iloc[:i,:]
    
    ss = StandardScaler()

    X_scaled = ss.fit_transform(df_.loc[:,["longitude_day_mean", "latitude_day_mean", "depth_day_mean"]])

    dbscan = DBSCAN(
                    eps = eps_param, 
                    min_samples = min_sample
                    )

    dbscan.fit(X_scaled);

    df_['cluster'] = dbscan.labels_

    # Getting the top cluster
    Top_clusters = df_.groupby("cluster")["cluster"].count().sort_values(ascending = False)[:].index

    if (len(Top_clusters) == 1) & (Top_clusters[0] == -1):
        rows = [-1] * (cluster_data).shape[1]
    else:
        
        if (Top_clusters[0] < 0):
            cluster_mask = Top_clusters[1]
        else:
            cluster_mask = Top_clusters[0]

        df_selected = df_[df_["cluster"] == cluster_mask]


        max_long = df_selected["longitude_day_mean"].max()
        min_long = df_selected["longitude_day_mean"].min()
        max_lat = df_selected["latitude_day_mean"].max()
        min_lat = df_selected["latitude_day_mean"].min()
        area = (max_lat - min_lat)*(max_long - min_long)
        cluster_density = area / len(df_selected)

        cluster_mag_max_mean = df_selected["mag_day_max"].mean()
        cluster_mag_max_max = df_selected["mag_day_max"].max()

        cluster_long_cent = df_selected["longitude_day_mean"].mean()

        cluster_long_std = df_selected["longitude_day_mean"].std()

        cluster_lat_cent = df_selected["latitude_day_mean"].mean()

        cluster_lat_std = df_selected["latitude_day_mean"].std()

        cluster_dept_cent = df_selected["depth_day_mean"].mean()

        cluster_dept_std = df_selected["depth_day_mean"].std()

        rows = [cluster_density, cluster_mag_max_mean, cluster_mag_max_max,
                cluster_long_cent, cluster_long_std, cluster_lat_cent, cluster_lat_std,
                cluster_dept_cent, cluster_dept_std
                          ]
    
    cluster_data.loc[i] = rows
#     cluster_df.loc[len(cluster_df)] = rows

# cluster_df.drop(cluster_df.index[0], inplace=True)


# df_cluster_daily[""]




# cluster_df["long_mov"] = cluster_df["long_cent"].diff()
# cluster_df["lat_mov"] = cluster_df["long_cent"].diff()

# cluster_df.dropna(inplace = True)


# cluster_df.reset_index(inplace=True)

# cluster_df.drop(columns = "index", inplace=True)
cluster_data<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11890381321554133, 'fraction_numerical': 0.04985125754980618, 'mean_word_length': 3.4607961399276235, 'pattern_counts': {'":': 9, '<': 34, '<?xml version=': 0, '>': 36, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '152929', 'license': 'MIT', 'n_tokens_mistral': 4279, 'n_tokens_neox': 3826, 'n_words': 792, 'path': 'codes/P07_DBSCAN_clustering-on-timeseries.ipynb', 'repo_name': 'saiedmighani/SES_project', 'size': '11093'}