<jupyter_start><jupyter_text>Data Wrangling of Fremont Bridge's data set  Import Fremont Bridge's data into local directory<jupyter_code># import urllib.request as req
# req.urlretrieve('https://data.seattle.gov/api/views/65db-xm6k/rows.csv?accessType=DOWNLOAD', 'data/FremontBridge.csv')<jupyter_output><empty_output><jupyter_text>Load Data and  start exploring<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import seaborn as sns
sns.set()
fb = pd.read_csv('../input/bicycle-data/FremontBridge.csv', index_col='Date', parse_dates=True)
print(fb.head(5))
print(fb.tail(5))
fb.shape
fb.dtypes
fb.columns = ['East','West']
fb.head(5)
# fb['Total'] = fb['East'] + fb['West']
fb['Total'] = fb.eval('East + West')
fb.head(5)
# Check for null values
fb.isna().sum()
fb.dropna(inplace=True)
fb.count()
fb.describe()
fb.plot(kind='line', figsize=(16,6))
plt.legend(loc='upper left')
plt.ylabel('Hourly Bicycle Count')
# Obsevation: The 30k+ samples are way too dense to make sense, because we hardly see East and West data ata the bottom.
# How about if we resample the data from hourly to daily?
fb_daily = fb.resample('d').sum()
print('fb_daily size :',fb_daily.size)
fb_daily.plot(kind='line', figsize=(16,6))
plt.ylabel('Daily Bicycle Count')
# Observation: It's still dense with daily data.
fb
# How about if we resample the data from daily to weekly? 53 weeks a year isn't much dense after  all, right?
fb_weekly = fb_daily.resample('W').sum()
print('fb_weekly size :',fb_weekly.size)
fb_weekly.plot(kind='line', figsize=(16,6))
plt.ylabel('Weekly Bicycle Count')
# Observation: Life is better now..for data analysis
# The frequency line varies widely. What do we infer?
# People bicycle more in summer (see the peaks evevry year in the graph) than in winter
# Even within a season, there are wide-fluctuations than near-consistency in the counts from one week to another. Why?
# Perhaps because of temperature, precipitation and other factors..
fb_weekly

# The same weekly  plot can be pllot from daily dataset like below
fb_daily.rolling(window=7).mean().plot(kind='line', figsize=(16,6))
plt.title('Aggregating the data using a weekly(window = 7 days)  rolling mean')
plt.ylabel('Weekly Bicycle Count')
plt.show()
# The jaggedness of the result is due to the hard cutoff of the window. 
# We can get a smoother version of a rolling mean using a window function—for example, a Gaussian window.
# The above weekly plot is a little jagged and can be smoothened like below:
# The following code specifies both the width of the window (we chose 7 days) and 
# the width of the Gaussian within the window (we chose 3 days):
fb_daily.rolling(window=7, center=True, win_type='gaussian').mean(std=5).plot(figsize=(16,6))
plt.title('Aggregating the data using a weekly(window = 7 days)  rolling mean')
plt.ylabel('Weekly Bicycle Count')
plt.show()
# Observation: Smoothening isn't really so good in this case, likee how it used to be :-/
# We might want to look at the average traffic as a function of the time of day.
gby_time = fb.groupby(fb.index.time).mean()
hourly_ticks = 60 * 60 * np.arange(24)
print('Hourly Ticks :', hourly_ticks)
gby_time.plot(xticks=hourly_ticks,figsize=(12,6))
plt.xticks(rotation='vertical')
# Observation: The hourly traffic is a strongly bimodal distribution (do yu see 2 mountains in the graph?), 
# with peaks around 8:00 in the morning and 5:00 in the evening.
# We also might be curious about how things change based on the day of the week. 
# Again, we can do this with a simple groupby
gby_week = fb.groupby(fb.index.dayofweek).mean()
# print(gby_week)
weekdays = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
# print('gby_week index =',gby_week.index.tolist())
# gby_week.index = ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
# print('gby_week index =',gby_week.index.tolist())
gby_week.plot()
plt.xticks(range(len(weekdays)),weekdays, rotation=45)
plt.show()
# Observation: This shows a strong distinction between weekday and weekend totals, 
# with around twice as many average riders crossing the bridge on Monday through Friday than on Saturday and Sunday.
weekday_mask = np.where(fb_daily.index.weekday < 5, 'Weekday', 'Weekend') # 5 and 6 are Sat and Sun => Weekend
len(weekday_mask)
weekday_mask
# See how the hourly trend looks like between weekdays and weekends
# Now that  means we arer grouping-by weekday-mask and hourly-time
weekday_mask = np.where(fb.index.weekday < 5, 'weekday','weekend')
gby_hourly = fb.groupby([weekday_mask,fb.index.time]).mean()
gby_hourly
hourly_ticks = 60 * 60 * np.arange(24)
fig, ax = plt.subplots(1,2,figsize=(16,6))
gby_hourly.loc['weekday',:].plot(ax=ax[0], xticks=hourly_ticks, title='Weekdays', style=[':','--','-.'])
gby_hourly.loc['weekend',:].plot(ax=ax[1], xticks=hourly_ticks, title='Weekends', style=[':','--','-.'])


# plt.xticks(rotation=90) only affects the last subplot whose reference is active
# To change the xticks on every sub-plot
for x in ax:
    x.xaxis.set_tick_params(rotation=90)
    
# Observation: We see a bimodal commute pattern during the work week, and a unimodal recreational pattern during the weekends.
# It would be interesting to dig through this data in more detail, and examine the effect of weather, temperature, time of year, and other factors on people’s commuting patterns<jupyter_output><empty_output><jupyter_text>Data Wrangling by merging Datasets (Fremont Bridge and Weather Data)<jupyter_code># Load Daily Weather data of Fremont Bridge
wd = pd.read_csv('data/1404899.csv', index_col='DATE', parse_dates=True)
wd.shape
fb_daily.shape # The number of days/rows match in both datasets - fb_daily and wd
wd.head(5)
wd.columns
wd[['STATION','NAME','PRCP','SNOW','TMAX','TMIN']].isnull().sum()
wd = wd.filter(['STATION','NAME','PRCP','SNOW','TMAX','TMIN'], axis=1)
wd.head(5)
# How Snowy days are recorded? how do the values look like?
np.sort( wd.SNOW.unique() ) # So the values range between 0 and 5.1
np.sort( wd.PRCP.unique() ) # So the values range between 0 and 2.2
wd.loc[ (wd['SNOW'] > 0) & (wd['PRCP'] == 0) ] # No rows available. So, the precipitation (PRCP) cannot be 0, when it Snows (SNOW > 0).
# df = fb_daily['Total'].copy()
df = fb_daily.filter(['Total'], axis=1)
print('Shape : ',df.shape)
df.head(5)<jupyter_output><empty_output><jupyter_text>Feature Engineering : Categorical Values* We saw previously that the patterns of use generally vary from day to day; let’s account for this in our data by adding binary columns that indicate the day of the week.* Similarly, we might expect riders to behave differently on holidays; let’s add an indicator of this as well.<jupyter_code>print(weekdays)
print(list(range(7)))
# Feature  Engineering : Categorical Values : adding binary columns that indicate the day of the week
for i in range(7):
    df[weekdays[i]] = (df.index.weekday ==  i).astype(int)
df.head(7)    
# df.columns['Total'] = df.columns['TotalBikesCount']
df = df.rename(columns={'Total':'TotalBikesCount'})
df.head(5)
# Assuming, riders to behave differently on holidays.; we add an indicator for this by incorporating National Holiday Calendar:
from pandas.tseries.holiday import USFederalHolidayCalendar # Can't believe panda has a class for this!
cal = USFederalHolidayCalendar()
holidays = cal.holidays('2012', '2018')
df = df.join(pd.Series(1, index=holidays, name='holiday'))
df['holiday'].fillna(0, inplace=True)
df.head()
# df.drop(['holiday'], axis=1, inplace=True)
df['holiday'] = df['holiday'].astype(int)
df['holiday'].unique()<jupyter_output><empty_output><jupyter_text>Accounting for the Duration of Daylight<jupyter_code># We also suspect that the hours of daylight would affect how many people ride; 
# let’s use the standard astronomical calculation to add this information
def hours_of_daylight(date, axis=23.44, latitude=47.61):
    """Compute the hours of daylight for the given date"""
    days = (date - pd.datetime(2000, 12, 21)).days
    m = (1. - np.tan(np.radians(latitude)) * np.tan(np.radians(axis) * np.cos(days * 2 * np.pi / 365.25)))
    return 24. * np.degrees(np.arccos(1 - np.clip(m, 0, 2))) / 180.
df['daylight_hrs'] = list(map(hours_of_daylight, df.index))
df[['daylight_hrs']].plot(fig=(16,4))
plt.ylim(8,17) # Increased y-axis limit max range by 1. Helps in placing the legend neatly without overlapping on the curve
plt.legend(loc='upper right')
plt.title('Hours of daylight in Seattle')
plt.show()
# Observation: The daylight varies between ~8 hrs (during Dececmber) and ~16 hrs (June).
df.head(5)
# Does day-light duration affect bicycle traffic? Let's validate that assumption with scatter plot.

# Plotting with Seaborn
# sns.lmplot('daylight_hrs','TotalBikesCount',data=df, fit_reg=False, size=5, scatter=True)

# plotting with Matplotlib
# plt.plot(df['daylight_hrs'],df['TotalBikesCount'])
plt.figure(figsize=(12,6)) # This should be set before drawing the plot
plt.scatter(df['daylight_hrs'],df['TotalBikesCount'])
plt.show()
# While see that our assumption turning out to be truthy, we see the plot too dense with data.
# Drawing the plot with resampled data
weekly = df.resample('W').mean()
# weekly.head()
plt.scatter(weekly['daylight_hrs'],weekly['TotalBikesCount'])
# So this clears the density to a large extent and we can see the trruthiness of our assumption - More daylight hours, more traffic
# sample = df.sample(n=500)
sample = df.sample(frac=0.1) # 10% of data ffrom the df
plt.scatter(weekly['daylight_hrs'],weekly['TotalBikesCount'])
# Can we see how the change in the duration affects the cycle traffic quantitatively?
# We can with LR's coefficient
from sklearn.linear_model import LinearRegression

x = sample[['daylight_hrs']]
y = sample['TotalBikesCount']
clf = LinearRegression(fit_intercept=True).fit(x, y)
ypred = clf.predict(x)
print(clf.coef_) # OP: ~295
# Now that means on any given day, each extra hour of daylight leads to about 300 more cyclers using the bridge<jupyter_output><empty_output><jupyter_text>Now be a data scientist and ask yourself?So is the seasonal increase daylight duration the real factor for the rise in bicyclers?Or do we actually see a trend in the rise in bicyclers?The way we can find answers to this is by doing a de-trending on the data. This is what I mean by "de-trended" data. We've basically removed the component of the data which correlates with the number of hours in a day, so that what is left is in some way agnostic to this quantity. The "adjusted weekly count" plotted here can be thought of as the number of cyclists we'd expect to see if the hours of daylight were not a factor.**With the data de-trended, we get a better idea of how bicycling in Seattle has changed over time, corrected for the seasonal variation.**<jupyter_code># Now that we have fit this trend, let's subtract it off and replace it by the mean:
trend = clf.predict(weekly[['daylight_hrs']]) # Trend is the weekly-predictions
detrended = trend.mean() + (weekly['TotalBikesCount'] - trend )
plt.figure(figsize=(8,4))
plt.scatter(weekly['daylight_hrs'],detrended)
plt.title('Weekly De-trended Traffic')
plt.show()
# Let's visualize this another way. in 2 steps.
# Step 1: Instead of plotting the number of riders vs daylight hours, 
# we'll again plot the number of riders vs the day of the year, along with the trend
weekly['daylight_trend'] = trend
weekly[['TotalBikesCount','daylight_trend']].plot(figsize=(14,7))
# Step 2: We can similarly view the adjusted total number of riders over time by subtracting this green line from the blue line:
detrended.plot(figsize=(14,7))
print('The STD of the detrended cyclists is {0:.0f}'.format(detrended.std()))
# Adding average temperature
# # temperatures are in 1/10 deg C; convert to C
tmin = wd['TMIN'] / 10
tmax = wd['TMAX'] / 10
wd['AvgTempInC'] = (tmin + tmax) / 2
df = df.join(wd['AvgTempInC'])
df.head(5)
# precip is in 1/10 mm; convert to inches
wd['PRCP_IN'] = wd['PRCP'] / 254
wd['DryDay'] = (wd['PRCP'] == 0).astype(int)
df = df.join(wd[['PRCP_IN','DryDay']])
df.head(15)
df = df.rename(columns={'daylight_hrs':'DayLightHrs'})
df.columns
# Finally, let’s add a counter that increases from day 1, and measures how many years have passed. 
# This will let us measure any observed annual increase or decrease in daily crossings:
df['YearsCount'] = (df.index - df.index[0]).days / 365.
df.head(5)<jupyter_output><empty_output><jupyter_text>Persist Processed Data<jupyter_code>df = df.reset_index()
df.head(5)
df.to_csv('data/processed_data.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12505989458552946, 'fraction_numerical': 0.01645104615876058, 'mean_word_length': 5.431946584488958, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 32, 'https://': 1, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '640794', 'license': 'MIT', 'n_tokens_mistral': 4367, 'n_tokens_neox': 4084, 'n_words': 1433, 'path': 'data-wrangling-of-fremont-bridge-s-data-set.ipynb', 'repo_name': 'jakariamd/ML-Practice', 'size': '12522'}