"""
## Introduction

In this Jupyter Notebook, I will use Python to clean two datasets that will be used to perform [exploratory analysis](https://www.kaggle.com/ericramoscastillo/cyclistic-exploratory-analysis) and present [key findings](https://www.kaggle.com/ericramoscastillo/cyclistic-key-findings-and-recommendations) to stakeholders.

The raw datasets can be accessed [here](https://www.kaggle.com/ericramoscastillo/cyclistic-data?select=weather_data.csv).
The cleaned datasets can be accessed [here](https://www.kaggle.com/ericramoscastillo/clean-cyclistic-data?select=clean_bike_data.csv).
The oringal Cyclistic historical trip data was collected and aggregated from [here](https://divvy-tripdata.s3.amazonaws.com/index.html). 
The original weather data was collected and filtered for Chicago details from [Visual Crossing Weather Service](https://www.visualcrossing.com/weather/weather-data-services#/login).
"""
"""
# Data Integrity

I chose to use the Visual Crossing Weather dataset because they have a rigourous data collection process, detail how they document this data, and keep daily data formats uniform. To read more on their process, visit their [data documentaion page](https://www.visualcrossing.com/resources/documentation/weather-data/weather-data-documentation/). 

The Cyclistic bike data is more prone to error. This is due to a variety of factors, for example, all bicycles have data regarding their GPS coordinates. This means if a malfunction occurs in the GPS device, that portion of the data will be unreliable. Most of the cleaning in this document will pertain to this dataset.

To make my methodology more clear, I will include written text to provide more context.
"""
#Import libraries.

import pandas as pd
import numpy as np
from numpy import radians, cos, sin, arcsin, sqrt
#Import raw bike and weather data.

df = pd.read_csv('../input/cyclistic-data/raw_data.csv',low_memory=False)
df1 = pd.read_csv('../input/cyclistic-data/weather_data.csv')
"""
## Cleaning Weather Data

For this dataset, I'm interested in temperature readings and weather conditions. This is because I believe those are the two largest influences over users choosing to ride on a given day. 

I will, however, take additional variables from this column to potentially explore in the [exploratory analysis](https://www.kaggle.com/ericramoscastillo/cyclistic-exploratory-analysis) phase. 
"""
#Add columns to weather data.

df1['conditions'] = df1.Conditions.apply(lambda x: str(x.split(',')[0]))
df1['Date time'] = pd.to_datetime(df1['Date time'])
df1['month'] = df1['Date time'].dt.month
#Rename weather columns for clarity.

df1.rename(columns = {'Date time':'date','Temperature':'temp','Relative Humidity':'humidity','Wind Chill':'wind_chill'},inplace=True)

#Keep specific columns that may influence users.

df1 = df1[['date','month','temp','wind_chill','conditions','humidity']]

df1.isnull().sum()
"""
What's windchill and why is it relevant? 

Wind chill is important because this is how the temperature feels to our body, which is relevant considering our data is based on users going outside to use a bicycle. The wind chill calculations are accurate for temperatures at or below 50 degrees farenheight and for windspeeds above 3 mph. Any readings with null values are readings that are higher than that temperature, in which case, the windchill is the same as the standard temperature reading. For days with temperatures above 50 degress, we can substitute the traditional temperature reading.
"""
#Create dataframe where we replace missing windchill values with temperature values.

df1temp = df1[df1.wind_chill.isnull()]
df1temp.wind_chill = df1temp.temp

#Drop missing windchill values from original dataset.

df1.dropna(subset=['wind_chill'],how='any',inplace=True)

#Combine the two weather datasets to form one dataset without missing values

df1 = pd.concat([df1,df1temp])

#Check

df1.isnull().sum()
"""
We are finished cleaning the weather dataset. Let's now focus our attention on the raw bike dataset.
"""
"""
## Cleaning Bike Dataset. Part 1: Addition

For this dataset, lets start by creating additional data based on what user data we have. Here are the columns we will be constructing:
- Ride Duration
- Month
- Hour Ride Started
- Day of Week
- Distance Estimate
- Speed Estimate
- Return Location

Most of these are self explanatory but I'll share my rationale about the last 3 columns. 

- Distance Estimate: The dataset provides starting and ending coordinates for each ride. This data does not included the route the user took, and thus not a comprehensive distance metric. However, starting and ending coordinates is a decent approximation. We will not put all our faith in this reading but with additional data this could be useful.
- Speed Estimate: Using the distance estimate and the ride duration, we can calculate speed.
- Return Lcoation: This is a boolean to see how frequently a bike is returned to the starting location. This could be useful in seeing if users among different bike types or station locations are more likely to return where they started the ride.
"""
#Drop and rename selected columns.

df.drop(['Unnamed: 0'],axis=1,inplace=True)
df.rename(columns = {'rideable_type':'bike_type','member_casual':'user_type'},inplace=True)

#Convert datatypes of two columns.

df.started_at = pd.to_datetime(df.started_at)
df.ended_at = pd.to_datetime(df.ended_at)

#Create new columns.

df['ride_time'] = df.ended_at - df.started_at
df['month'] = df.started_at.dt.month
df['start_hour'] = df.started_at.dt.hour
df['day_of_week'] = df.started_at.dt.day_name()
#Haversine formula. This formula calculates distance between two points on a oblong sphere (the category that our planet falls under).

def haversine(lon1, lat1, lon2, lat2):
    """
    Calculate the great circle distance between two points 
    on the earth (specified in decimal degrees)
    """

    #Convert decimal degrees to Radians:
    lon1 = np.radians(lon1.values)
    lat1 = np.radians(lat1.values)
    lon2 = np.radians(lon2.values)
    lat2 = np.radians(lat2.values)

    #Implementing Haversine Formula: 
    dlon = np.subtract(lon2, lon1)
    dlat = np.subtract(lat2, lat1)

    a = np.add(np.power(np.sin(np.divide(dlat, 2)), 2),  
                          np.multiply(np.cos(lat1), 
                                      np.multiply(np.cos(lat2), 
                                                  np.power(np.sin(np.divide(dlon, 2)), 2))))
    c = np.multiply(2, np.arcsin(np.sqrt(a)))
    r = 6371

    return c*r
#Apply function and convert results from kilometers into miles.

x = haversine(df.start_lng,df.start_lat,df.end_lng,df.end_lat)

#Create additional columns.

df['distance_estimate'] = np.round(x*0.6213712,2)
df['mph_estimate'] = df.distance_estimate / (df.ride_time.dt.seconds / 3600)
df['returned_location'] = df.distance_estimate.apply(lambda x: 'same location' if x == 0  else 'new location')
df['date'] = df.started_at.dt.date
"""
## Cleaning Bike Dataset. Part 2: Subraction

Let's finish by removing data that appears unreliable. Here are items we should consider removing:
- Duplicate data.
    - Duplicate data points can potentially bias an analysis depending on how prevelant the duplicates are.
- Data with start coordinates but no ending coordinates.
    - Data with start coordinates but without ending coordinates could mean that the ride has not completed or that something about that bicycle's tracking device is not working correctly.
- Unrealistic ride durations.
    - Rides lasting over 1 day are infrequent and could be a mistake.
    - Ride times cannot be negative because time machines haven't been invented yet.
- Unrealistic ride speeds.
    - According to the [I love bicycling website,](https://ilovebicycling.com/average-bike-speed/) a pro cyclist travels downhill from 69 - 81 MPH. 

There are data points without start station names, end station names, or their corresponding identification to a station. This is okay because not all bikes are returned to station. Many of Cyclistic's bikes are waiting in a convenient location, just not the docked bike variety.
"""
#Check for null values.

df.isnull().sum()
#Check timeframe of dataset.

print(df.started_at.min())
print(df.started_at.max())
#Reorder columns.

df = df[['date','ride_id', 'bike_type', 'started_at', 'ended_at', 'start_station_name',
       'start_station_id', 'end_station_name', 'end_station_id', 'start_lat',
       'start_lng', 'end_lat', 'end_lng', 'user_type', 'ride_time', 'month',
       'start_hour', 'day_of_week', 'distance_estimate', 'mph_estimate',
       'returned_location']]

#Convert data type.

df.date = pd.to_datetime(df.date)

#Check total amount of datapoints before data cleaning.

df.shape
#Duplicate data check.

print('Duplicates?\n')
print(df.drop_duplicates(keep=False).shape)

#Remove data without ending location coordinates.

print('Ending Coordinates?\n')
df.dropna(subset=['end_lat','end_lng'],how='any',inplace=True)
print(df.shape)

#Check the min, max, and average of the dataset.

print('Ride min,max, and mean?\n')
print(df.ride_time.agg(['min','max','mean']))

#Check data points that contain negative time duration and datapoints longer than 1 day. 

print('Less than 0 days or more than 1 day?\n')
ts1 = pd.Timedelta('0 days')
ts2 = pd.Timedelta('1 days')
print(df[df.ride_time <= ts1].shape)
print(df[df.ride_time >= ts2].shape)
#Remove data with negative time length durations and data with more than one day of the bicycle rented.

df = df[df.ride_time >= ts1]
df = df[df.ride_time <= ts2]
print(df.shape)

#Convert ride time into minutes rounded to nearest whole minute.

df.ride_time = df.ride_time.dt.total_seconds()/60
df.ride_time = df.ride_time.round()

#Remove rides that are less than 1 minute in duration.
df = df[df.ride_time >= 1]
print(df.shape)

#Remove rides where estimated mph is over 85 miles per hour.
df = df[df.mph_estimate <= 85]
print(df.shape)
#View timeframe of dataset.

df.started_at.agg(['min','max'])
"""
## Summary of Removed Data:

I started with 4,073,561 data points and removed the following:
- Removed duplicate data. 0 data points (0%)
- Removed data without ending GPS coordinates. 5,037 data points removed (<1%)
- Removed data with unreaslistic ride durations. 12,987 data points (<1%)
- Removed data with ride durations less than 1 minute. 41,092 data points (~1%)
- Removed data with estimated mph is greater than 85mph. 15 data points removed (<1%)

I ended with 4,014,850, removing 58,711 data points. We removed ~1.5% of all original data.
"""
"""
This dataset has been cleaned to my requirements. If you wish to access it, [click here](https://www.kaggle.com/ericramoscastillo/clean-cyclistic-data?select=clean_bike_data.csv).
"""
"""
## Other  Considerations

I was considering removing entries where the ride duration was less than 3 minutes or where the ride times exceeded 3 hours, but I have no proof that those are faulty data points. Moving into the [exploratory analysis](https://www.kaggle.com/ericramoscastillo/cyclistic-exploratory-analysis), let's remember this as some data may have outliers that effect the calculation of averages.
"""