<jupyter_start><jupyter_text>Importing Libraries<jupyter_code>import sys
sys.path
sys.path.append('/home/prashant.singh1/.local/lib/python3.5/site-packages')
! pip install pandas --user
! pip install numpy --user
! pip install scipy --user
! pip install scikit-learn --user
! pip install matplotlib --user
! pip install seaborn --user
! pip install datetime --user
import pandas
import psycopg2 
import pandas.io.sql as psql
import matplotlib
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns
import datetime<jupyter_output><empty_output><jupyter_text>Connecting to database<jupyter_code>conn = psycopg2.connect("dbname='postgres' user='msuuser' host='10.1.2.60' password='<PASSWORD>'")
tripdata2017 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_2017" """, conn) 
tripdata201801 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201801" """, conn) 
tripdata201802 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201802" """, conn) 
tripdata201803 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201803" """, conn) 
tripdata201804 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201804" """, conn) 
tripdata201805 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201805" """, conn)
tripdata201806 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201806" """, conn)
tripdata201807 = psql.read_sql("""SELECT * FROM "msu_greco"."fordgobike_tripdata_201807" """, conn)

tripdata201801 = tripdata201801.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata201802 = tripdata201802.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata201803 = tripdata201803.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata201804 = tripdata201804.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata201805 = tripdata201805.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata201806 = tripdata201806.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata201807 = tripdata201807.drop(columns = ["bike_share_for_all_trip","row.names"])
tripdata2017 = tripdata2017.drop(columns = "row.names")<jupyter_output><empty_output><jupyter_text>Merging all tables togehter<jupyter_code>Final_Table = pandas.concat([tripdata2017,tripdata201801,tripdata201802,tripdata201803,tripdata201804,tripdata201805,tripdata201806,tripdata201807])<jupyter_output><empty_output><jupyter_text>Changing date to appropriate format<jupyter_code>Final_Table['start_time'] =  pandas.to_datetime(Final_Table['start_time'], format='%Y-%m-%d %H:%M:%S.%f')
Final_Table['end_time'] =  pandas.to_datetime(Final_Table['end_time'], format='%Y-%m-%d %H:%M:%S.%f')

q75, q25 = np.percentile(Final_Table.duration_sec, [75 ,25])
iqr = q75 - q25

min = q25 - (iqr*3)
max = q75 + (iqr*3)

sns.boxplot(x=Final_Table['duration_sec'])<jupyter_output><empty_output><jupyter_text>Before outlier treatment<jupyter_code>Final_Table = Final_Table[~((Final_Table.duration_sec < min) | (Final_Table.duration_sec > max))]
sns.boxplot(x= Final_Table['duration_sec'])<jupyter_output><empty_output><jupyter_text>After outlier treatment<jupyter_code>#Understanding data types of final dataset

Final_Table.dtypes

Period_Start = Final_Table.start_time.min()
Period_End = Final_Table.start_time.max()
Period_Duration = Period_End - Period_Start
timePeriodStat = pandas.DataFrame({"Analysis Start Date":[Period_Start.date()],
                                   "Analysis End Date" :[Period_End.date()],
                                   "Analysis Duration" :[Period_Duration]})
timePeriodStat  

total_Bike_rides = len(Final_Table.duration_sec)
female_ride_count = len(Final_Table.member_gender[Final_Table['member_gender']=="Female"])
male_ride_count = len(Final_Table.member_gender[Final_Table['member_gender']=="Male"])
subscriber_ride_count = len(Final_Table.member_gender[Final_Table['user_type']=="Subscriber"])
customer_ride_count = len(Final_Table.member_gender[Final_Table['user_type']=="Customer"])

rideStat = pandas.DataFrame({"Total Bike rides":[total_Bike_rides],
                                   "Female " :[female_ride_count],
                                   "Male" :[male_ride_count],
                                    "Subscriber" :[subscriber_ride_count],
                                     "Customer" :[customer_ride_count]
                            })
rideStat

currYear = datetime.date.today().year
Final_Table['age'] = currYear - Final_Table.member_birth_year   

ageStat = pandas.DataFrame({"Age >35":[len(Final_Table.age[Final_Table['age']>35])],
                            "Age >=20 & <35":[ len( Final_Table.age[(Final_Table['age']<35) & (Final_Table['age']>=20)])],
                            "Age <20":[len(Final_Table.age[Final_Table['age']<20])]                                  
                            })
ageStat
 




Gender_Trend = Final_Table[['start_time','member_gender']]
Gender_Trend = Gender_Trend[(Gender_Trend['member_gender']=="Female") | (Gender_Trend['member_gender']=="Male")| (Gender_Trend['member_gender']=="Other")]
Gender_Trend['Year'] = Gender_Trend['start_time'].dt.year
Gender_Trend['Month'] = Gender_Trend['start_time'].dt.month
Gender_Trend = Gender_Trend.groupby(['Year','Month','member_gender']).size().groupby(['Month','member_gender']).mean().reset_index(name ='Ride_Count')
Gender_Trend = Gender_Trend.pivot_table(index=['Month'], columns='member_gender',values='Ride_Count').reset_index()
Gender_Trend



female = plt.plot(Gender_Trend['Month'],Gender_Trend['Female'])
male = plt.plot(Gender_Trend['Month'],Gender_Trend['Male'])
other = plt.plot(Gender_Trend['Month'],Gender_Trend['Other'])
plt.legend(["Female","Male","Other"])
plt.title('Monthly Trend of Bike Usage - Genderwise')
plt.ylabel('Number of Rides')
plt.xlabel('Month')
plt.xticks(np.arange(12),['January','February','March','April','May','June','July','August','September','October','November','December'], rotation=45)<jupyter_output><empty_output><jupyter_text>Findings     Male are more into biking     Less female are riding bike (So they need to promote biking in female)<jupyter_code>typeTrendTable = Final_Table[['start_time','user_type']]
typeTrendTable['Year'] = typeTrendTable['start_time'].dt.year
typeTrendTable['Month'] = typeTrendTable['start_time'].dt.month
typeTrendTable = typeTrendTable.groupby(['Year','Month','user_type']).size().groupby(['Month','user_type']).mean().reset_index(name ='Ride_Count')
typeTrendTable = typeTrendTable.pivot_table(index=['Month'], columns='user_type',values='Ride_Count').reset_index()
typeTrendTable




subscriber = plt.plot(typeTrendTable['Month'],typeTrendTable['Subscriber'])
cuustomer = plt.plot(typeTrendTable['Month'],typeTrendTable['Customer'])
plt.legend(["Subscriber","Customer"])
plt.title('Monthly Trend of Bike Usage - User Type')
plt.ylabel('Number of Rides')
plt.xlabel('Month')
plt.xticks(np.arange(12),['January','February','March','April','May','June','July','August','September','October','November','December'], rotation=45)<jupyter_output><empty_output><jupyter_text>Findings from above graph:     Subscribers use more rides     Peak in month of june(Summer Season people are riding more)  Rolling data to weekly level<jupyter_code>Weekly_Trend = Final_Table[['start_time','member_gender','user_type']]
Weekly_Trend = Weekly_Trend[(Weekly_Trend['member_gender']=="Female") | (Weekly_Trend['member_gender']=="Male")]
Weekly_Trend['Year'] = Weekly_Trend['start_time'].dt.year
Weekly_Trend['Month'] = Weekly_Trend['start_time'].dt.month
Weekly_Trend['WeekDay']=Weekly_Trend['start_time'].dt.weekday
Weekly_Trend = Weekly_Trend.groupby(['Year','Month','WeekDay','user_type','member_gender']).size().groupby(['Month','WeekDay','user_type','member_gender']).mean().reset_index(name ='Ride_Count')
Weekly_Trend = Weekly_Trend.pivot_table(index=['Month','user_type','member_gender'], columns='WeekDay',values='Ride_Count').reset_index()
Weekly_Trend['WeekDays'] = (Weekly_Trend[0]+Weekly_Trend[1]+Weekly_Trend[2]+Weekly_Trend[3]+Weekly_Trend[4]+Weekly_Trend[5])/5
Weekly_Trend['WeekEnds'] = (Weekly_Trend[5]+Weekly_Trend[6])/2
Weekly_Trend = Weekly_Trend.drop(columns =[0,1,2,3,4,5,6])
Weekly_Trend

weeklyTrendAnalysis = Weekly_Trend.groupby(['Month']).sum().reset_index()
weeklyTrendAnalysis

weekdays = plt.plot(weeklyTrendAnalysis['Month'],weeklyTrendAnalysis["WeekDays"])
weekends = plt.plot(weeklyTrendAnalysis['Month'],weeklyTrendAnalysis["WeekEnds"])
plt.legend(["WeekDays","WeekEnds"])
plt.title('Monthly Trend of Total Bike Usage - Week Analysis')
plt.ylabel('Number of Rides')
plt.xlabel('Month')
plt.xticks(np.arange(12),['January','February','March','April','May','June','July','August','September','October','November','December'], rotation=45)<jupyter_output><empty_output><jupyter_text>Findings:     People are riding more in weekdays     Promote riding bikes on weekends<jupyter_code># Analysis for female riders across week

femaleWeeklyAnalysis = Weekly_Trend[Weekly_Trend['member_gender']=="Female"]
width = 0.35
weekdays =plt.bar(femaleWeeklyAnalysis['Month'],femaleWeeklyAnalysis['WeekDays'],width)

weekends =plt.bar(femaleWeeklyAnalysis['Month']+ width,femaleWeeklyAnalysis['WeekEnds'],width)
plt.legend(['WeekDays','WeekEnds'])
plt.xticks(np.arange(12),['January','February','March','April','May','June','July','August','September','October','November','December'], rotation=45)<jupyter_output><empty_output><jupyter_text>Showing trends in weekdays and weekends per month for Females<jupyter_code># Analysis for female over weekly level

Female_Weekly = femaleWeeklyAnalysis.drop(columns = ['WeekEnds','member_gender'])

Female_Weekly= Female_Weekly.pivot_table(index=['Month'], columns='user_type',values='WeekDays').reset_index()
Female_Weekly
width = 0.35
customers =plt.bar(Female_Weekly['Month'],Female_Weekly['Customer'],width)
subscribers =plt.bar(Female_Weekly['Month']+ width,Female_Weekly['Subscriber'],width)
plt.legend(['Customers','Subscribers'])
plt.xticks(np.arange(12),['January','February','March','April','May','June','July','August','September','October','November','December'], rotation=45)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16464131713053703, 'fraction_numerical': 0.031164249313994512, 'mean_word_length': 7.633671742808799, 'pattern_counts': {'":': 5, '<': 47, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '440614', 'license': 'MIT', 'n_tokens_mistral': 3870, 'n_tokens_neox': 3426, 'n_words': 444, 'path': 'Prashant.Singh.11711.ipynb', 'repo_name': 'shubham1262/Linear-Regression', 'size': '10206'}