<jupyter_start><jupyter_text>**Loading Datasets**---  **Merging all months data into One single Dataframe**<jupyter_code>final_data = pd.DataFrame()
attributes = []
for file in files:
  data = pd.read_csv(file)
  # print(data.columns)
  attributes.append(list(data.columns),)
  final_data = final_data.append(data)
  # print(f"{file} columns considered!")
  print(f"\nFile:{file}, Total Rows:{data.shape[0]}, Total Attribues:{data.shape[1]}")
all_columns = list(set(list(itertools.chain(*attributes))))
all_columns
print("Total Records:",final_data.shape[0])
print("Total Attributes:",final_data.shape[1])
final_data.head()
list(final_data.rideable_type.unique())<jupyter_output><empty_output><jupyter_text>**Data Integrity Check**---  **Quality Report Preparation**<jupyter_code># def quality_report(df):
#     """
#         Purpose : to create a report of column wise missing value and unique value counts and datatypes 
#         Input : Dataframe
#         Output : Dataframe with columns names, 
#         types, no of observations, no of missing rows, No of unique values,
#         % of data avaliable, % of data missing and 
#         "Average","SD","Minimum Value","25%","50%","75%","Maximum Value"
#         Usage : quality_report(df)

#     """
#     df = df.apply(lambda col: pd.to_datetime(col, errors='ignore') if col.dtypes == object else col,axis=0)
#     quality_df = pd.DataFrame()
#     quality_df["Column Name"]=df.columns.sort_values()
# #     for cols in quality_df["Column /Name"]:
# #         quality_df.loc[quality_df["Column Name"]==cols,"Maximum Characters Occupied"]=get_length(df,cols)
# #         quality_df.loc[quality_df["Column Name"]==cols,"Minimim Characters Occupied"]=get_length(df,cols,max_=False)
# #     quality_df['Maximum Characters Occupied']=quality_df['Maximum Characters Occupied'].astype(int)
# #     quality_df['Minimim Characters Occupied']=quality_df['Minimim Characters Occupied'].astype(int)
#     sorted_cols = df.columns.sort_values()
#     quality_df["Data Type"] = df[sorted_cols].dtypes.values
#     quality_df['Data Type'] = quality_df['Data Type'].apply(lambda x:x.name)
#     quality_df['Data Kind'] = quality_df['Data Type'].map({'object':'text','int64':'numeric','float64':'numeric','datetime64[ns]':'datetime'})
#     quality_df = quality_df.drop(['Data Type'],axis=1)
# #     quality_df["Data Type"] = df[sorted_cols].apply(lambda x:x.name)
#     quality_df["Total Rows"]=df[sorted_cols].apply(lambda x:len(x)).values
#     quality_df["Total Nulls"] = df[sorted_cols].apply(lambda x:np.sum(pd.isnull(x))).values
#     quality_df["Total Distinct"] = df[sorted_cols].apply(lambda x:len(x.unique())).values
#     quality_df["Data_Avaliable"] = quality_df["Total Rows"]-quality_df["Total Nulls"]
#     quality_df["Data Availability(Perc.)"]=np.round(quality_df["Data_Avaliable"]/quality_df["Total Rows"],2)*100
#     quality_df["Data Missing(Perc.)"]=100 - quality_df["Data Availability(Perc.)"]
#     summary = df.describe(include='all').T.reset_index()  
#     try:
#         #summary.columns=["Column","count","","Average","SD","Minimum Value","25%","50%","75%","Maximum Value"]
#         summary.rename(columns={"index":"Column Name","min":"Minimum Value","max":"Maximum Value",'mean':"Average","std":"Standard Deviation"},inplace=True)
#         summary.drop(["count",'unique',"25%","50%","75%"],axis=1,inplace=True)
#     except Exception as e:
#         print(str(e))
#         summary.rename(columns={"index":"Column Name"},inplace=True)
#         summary.drop(['count',"25%","50%","75%"],axis=1,inplace=True)
#         summary["Minimum Value"]="-"
#         summary["Maximum Value"]="-"
#     quality_df=pd.merge(quality_df,summary,left_on="Column Name",right_on="Column Name",how="outer")
#     quality_df = quality_df.fillna("-")
#     return quality_df
# qt_report = quality_report(final_data)<jupyter_output><empty_output><jupyter_text>**Saving Quality Report**<jupyter_code># qt_report.to_csv("divvy-tripdata_Quality_Report.csv",index=False)
# qt_report<jupyter_output><empty_output><jupyter_text>**Data** **Cleaning**--- **Missing Values Imputation**<jupyter_code>final_data.isnull().sum()<jupyter_output><empty_output><jupyter_text>▶**Observation**:---* start_staion_name and start_station_id null values are not matching* We can impute the missed station_names by referring theie ID's in rest of the records* We can't impute the location data using mean/median/mode since it's geographical data which doen't make any sesnse* So, we can exclude those records where no geographics information available.<jupyter_code>missing_columns = final_data.isnull().sum()[final_data.isnull().sum()>0].index
list(missing_columns)
# Removing the rows where lattitude not present
final_data = final_data[~pd.isnull(final_data['end_lat'])]
final_data.isnull().sum()
# To find if any station names are not there even station id's are there
station_ids = list(final_data.loc[(pd.isnull(final_data['start_station_name'])) & (~pd.isnull(final_data['start_station_id'])),'start_station_id'].values)
station_ids
for ids in station_ids:
  station_nm = list(set(final_data[(final_data.start_station_id==ids) & (~pd.isnull(final_data.start_station_name))]['start_station_name'].values))
  if len(station_nm)==1:
    final_data.loc[(final_data.start_station_id==ids )& (pd.isnull(final_data.start_station_name)),'start_station_name']=station_nm[0]
    # final_data.loc[(pd.isnull(final_data['start_station_name'])) & (pd.isnull(final_data['start_station_id'])),'start_station_name']=station_nm
    print(f"station id:{ids} station name:{station_nm[0]} value imputed")
final_data.isnull().sum()
final_data.groupby(['start_station_name','start_station_id']).size()
#Inconsisten, under one station_id there are many stations available.
final_data.groupby(['end_station_name','end_station_id']).size()
# inconsistent, under one station_id there are many stations available.<jupyter_output><empty_output><jupyter_text>▶**Observations**---* There is clear inconsistency in station_ids and station names* So many stations are fell under stations id's which is strange.* Station Id's are having different length values i.e some are plain alpahbets and some are numeric etc.<jupyter_code>#start_station imputation:
for i in ['start','end']:
  station_id_mode = final_data[[f'{i}_station_id']].mode().values[0][0]
  station_nm = list(set(final_data.loc[final_data[f'{i}_station_id']==station_id_mode,f'{i}_station_name']))
  if len(station_nm)==1:
    final_data.loc[pd.isnull(final_data[f'{i}_station_id']),f'{i}_station_id']=station_id_mode
    final_data.loc[pd.isnull(final_data[f'{i}_station_name']),f'{i}_station_name']=station_nm[0]
    print(f'{i}_station_name value imputed with:{station_nm[0]}')
# final_data[pd.isnull(final_data['start_station_id'])]
# final_data[final_data.start_station_id]
## Final Check if any nulls present in any of the columns
final_data.isnull().sum()<jupyter_output><empty_output><jupyter_text>**Data Wrangling**  **Data Types Conversion**<jupyter_code>final_data['started_at'] = pd.to_datetime(final_data['started_at'])
final_data['ended_at'] = pd.to_datetime(final_data['ended_at'])
final_data.dtypes #Review<jupyter_output><empty_output><jupyter_text>**Feature Engineering**<jupyter_code>final_data['ride_lenght'] = final_data['ended_at']-final_data['started_at']
final_data['ride_lenght_in_seconds'] =  final_data['ride_lenght'].apply(lambda x: x.total_seconds())
final_data['ride_lenght_in_mins'] = final_data['ride_lenght_in_seconds']/60
final_data['ride_lenght_in_hrs'] = final_data['ride_lenght_in_mins']/60
final_data.loc[final_data['ride_lenght_in_mins']<0,['started_at','ended_at']].head()<jupyter_output><empty_output><jupyter_text>▶**Observation**---* Start datetime shouldn't be greaterthan ended_datetime* There are around **143** records where entries are incorrect.* We can remove those records since the observations are also negligible compared to entire volume of the data.<jupyter_code>final_data['ride_lenght_in_mins'].head()#Review
final_data['day_of_week'] = final_data['started_at'].apply(lambda x:x.strftime("%A"))#Monday, Tuesday ,...etc.
final_data['start_month'] = final_data['started_at'].apply(lambda x:x.strftime("%B"))
final_data['start_year'] = final_data['started_at'].apply(lambda x:x.strftime("%Y"))
final_data['started_hour'] = final_data.started_at.dt.hour
final_data[['started_at','started_hour','start_month','day_of_week','start_year','member_casual']].head()#Review
plt.figure(figsize=(10,5))
final_data['day_of_week'].value_counts().plot(kind='bar')#Trend Findings
plt.xticks(size=12)
plt.xlabel("Month",size=13)
plt.ylabel("Total Riders",size=13)
plt.show()<jupyter_output><empty_output><jupyter_text>▶**Observation**:---* Weekends both saturday and sunday are the most rides has been taken.* Let;s dig down further to see the difference w.r.t membership types (casual and annual members)<jupyter_code>plt.figure(figsize=(10,5))
final_data['start_month'].value_counts().plot(kind='bar')# July has highest rides taken
plt.xticks(size=12)
plt.xlabel("Month",size=13)
plt.ylabel("Total Riders",size=13)
plt.show()<jupyter_output><empty_output><jupyter_text>▶**Observation**:---* July,August and September months are most popular usage months<jupyter_code>#Removing all the records where ride length is negative.
final_data.loc[final_data['ride_lenght_in_mins']<0,['started_at','ended_at','ride_lenght','ride_lenght_in_mins']].sort_values(['ride_lenght_in_mins'])
final_data = final_data.loc[final_data['ride_lenght_in_mins']>0,]<jupyter_output><empty_output><jupyter_text>**Aggregations**  **Descriptive Analysis**<jupyter_code># Calculate the mean of ride_length
# Calculate the max ride_length
# Calculate the mode of day_of_week

print("Average of ride_length(Mins)",round(final_data['ride_lenght_in_mins'].mean(),2))
print("Min of ride_length(Mins)",round(final_data['ride_lenght_in_mins'].min(),2))
print("Max of ride_length(hrs)",round(final_data['ride_lenght_in_hrs'].max(),2))<jupyter_output>Average of ride_length(Mins) 20.79
Min of ride_length(Mins) 0.02
Max of ride_length(hrs) 932.4<jupyter_text>**Observation**:---* **~932 Hours.** or **1 Month 8 Day(s)** being used which is looks like an outlier<jupyter_code>final_data[['ride_lenght_in_hrs']].describe()
final_data[['ride_lenght_in_mins']].plot.box()
# final_data.loc[final_data['ride_lenght_in_hrs']<1,['ride_lenght_in_hrs']].plot.box()
plt.show()
# final_data[['ride_lenght_in_hrs']].plot.box()<jupyter_output><empty_output><jupyter_text>Observations:* The above box plot showing that many observations are outliers or suspected values.* Need to look into futher.<jupyter_code>round(final_data.loc[(final_data['ride_lenght_in_hrs']>2),].shape[0]/final_data.shape[0]*100,2)
final_data = final_data[final_data['ride_lenght_in_hrs']<2]<jupyter_output><empty_output><jupyter_text>Observations:* Aournd **99.99%** of the users are using the service less than 2 Hours.* So discarding those records which travelled greater than **2hrs** as treating them as outliers. Let's tream them sepereately<jupyter_code># final_data[final_data['ride_lenght_in_hrs']>240]<jupyter_output><empty_output><jupyter_text>**Data Wranglings**<jupyter_code>final_data.member_casual.value_counts(normalize=True)
final_data.member_casual.value_counts().plot(kind='bar',width=0.2)
plt.xlabel("Type",size=12)
plt.xticks(size=15)
plt.yticks(size=12)
plt.title("Riders Membership Distribution",size=15)
plt.show()<jupyter_output><empty_output><jupyter_text>**Observations**:---* **55.70%** of the customers are opted for Annual Memebership plan.* **44.29%** of the customers are not opted for *Annual Memebership plan* i.e still they are *causal memebers*.<jupyter_code># Calculate the average ride_length for members and casual riders
final_data.groupby(['member_casual']).agg({'ride_lenght_in_mins':['mean','min','max']})#.plot(kind='bar')
final_data.pivot_table(index='member_casual', values='ride_lenght_in_mins',aggfunc=np.mean)
final_data.pivot_table(index='member_casual', values='ride_lenght_in_mins',aggfunc=np.mean).plot(kind='barh',width=0.5)
plt.xticks(size=12)
plt.yticks(size=12)
plt.title("Average ride duration",size=16)
plt.show()
# Calculate the average ride_length for users by day_of_week.
final_data.pivot_table(index='member_casual', columns='day_of_week', values='ride_lenght_in_mins',aggfunc=np.mean)<jupyter_output><empty_output><jupyter_text>**Observation**---* More rides being taken by Casual members on **Sunday's** whereas Annual membership users taking more rides on **Saturday's**<jupyter_code>final_data.pivot_table(index='member_casual', columns='day_of_week', values='ride_lenght_in_mins',aggfunc=np.mean).T.plot()
plt.xticks(rotation=90)
plt.ylabel("Avg.ride duration",size=12)
plt.xlabel("Weekday",size=12)
plt.show()
final_data.pivot_table(index='member_casual', columns='started_hour', values='ride_lenght_in_mins',aggfunc=np.mean).T.plot()
plt.xticks(rotation=90)
plt.ylabel("Avg.ride duration",size=12)
plt.xlabel("Hour",size=12)
plt.show()<jupyter_output><empty_output><jupyter_text>* Compare to Annaul Members, Casual members are using the commute from **7 AM** to **5 PM**.<jupyter_code>final_data.pivot_table(index='start_month', values='ride_id',aggfunc='count').sort_values(['ride_id'])
final_data.pivot_table(index='start_month', values='ride_id',aggfunc='count').sort_values(['ride_id']).plot()
plt.show()<jupyter_output><empty_output><jupyter_text>**Recommendations:**---* Launch marketing campaigns in Summer (from June to August).* Special weekend rates beneficial for longer rides* Member rewards programs based on covered distance, ride duration, and ride frequencies.<jupyter_code># Calculate the number of rides for users by day_of_week
final_data.pivot_table(index='day_of_week',columns='start_month',values='ride_id',aggfunc='count')

## Source stations only rided by any customer only once in entire year.
final_data.start_station_name.value_counts()[final_data.start_station_name.value_counts()==1]
final_data.pivot_table(index='start_station_name',columns='member_casual',values='ride_id',aggfunc='count').sort_values(['casual','member'],ascending=False).head(15).plot(kind='barh')
plt.ylabel("Start Station Name",size=12)
plt.xlabel("Total Rides",size=12)
plt.show()<jupyter_output><empty_output><jupyter_text>* To make the casual mebers to annual members we can make arrange of Advertismet slides and all promotional activities in these areas as shown above.<jupyter_code>final_data.pivot_table(index='rideable_type',columns='member_casual',values='ride_lenght_in_mins',aggfunc=np.mean)
final_data.pivot_table(index='rideable_type',columns='member_casual',values='ride_lenght_in_mins',aggfunc=np.mean).plot(kind='bar')
final_data.pivot_table(index='rideable_type',columns='member_casual',values='ride_id',aggfunc='count').plot(kind='bar')
plt.ylabel("TOtal Customers per K(1000)",size=12)
plt.show()

final_data.pivot_table(index='rideable_type',columns='start_month',values='ride_id',aggfunc='count').T.plot()
plt.ylabel("Total rides")
plt.xticks(rotation=90)
plt.show()<jupyter_output><empty_output><jupyter_text>* Docked bikes were least popular compared to classic bike and Electric bikes* “Classic Bikes” were more popular over the months and more users tend to choose “Classic Bikes” over the “Docked Bikes”.* It is important to figure out why users tend to prefer “Classic Bikes” more than “Docked Bikes”.* In the same time, I shows the increase of the popularity of the “Electric Bikes” as well.   **Recommendations*** Implement a new annual membership tier at a lower price point where a total number of rides are allotted for a given time period (e.g. week, month), versus current annual membership structure (unlimited number of rides, 45-min. limit per ride), and given that on average, casual riders spend more time on their rides than current members, but on average only 1480 sec.(24.67 min.)* Implement a limited time promotion for annual membership that loosens limits on Friday, Saturday, Sunday rides, given those are the most popular days casual riders use Cyclistic bikes* Add more electric bikes to inventory given casual riders prefer them over classic bikes* Launch marketing campaigns in Summer (from June to August).<jupyter_code><jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15126978284873022, 'fraction_numerical': 0.011286958655379709, 'mean_word_length': 8.133333333333333, 'pattern_counts': {'":': 5, '<': 92, '<?xml version=': 0, '>': 92, 'https://': 0, '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': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '180571', 'license': 'MIT', 'n_tokens_mistral': 5729, 'n_tokens_neox': 5492, 'n_words': 1039, 'path': 'Mahesh/Capstone/Final_Case_Study.ipynb', 'repo_name': 'pavvankanamarlapudi/Challenges', 'size': '16302'}