EDA: NY City Bikes

import pandas as pd
import matplotlib.pyplot as plt

# Read the CSV file into a DataFrame
bikes = pd.read_csv("New York Citi Bikes_Raw Data - NYCitiBikes.csv")

# Print the column names of the DataFrame
print(bikes.columns)
print()

# Convert start time and stop time columns to datetime format
bikes['Start Time'] = pd.to_datetime(bikes['Start Time'])
bikes['Stop Time'] = pd.to_datetime(bikes['Stop Time'])

# Create a new DataFrame `bikes_df` by dropping duplicate rows from `bikes`
bikes_df = bikes.drop_duplicates()

# Fill any missing values in `bikes_df` with 0
bikes_df = bikes_df.dropna()

# Convert "Trip_Duration_in_min" column to numeric and replace commas with blank space
bikes_df["Trip_Duration_in_min"] = pd.to_numeric(bikes_df["Trip_Duration_in_min"].str.replace(',', ''))

# Display the updated DataFrame `bikes_df`
display(bikes_df.head())


# Calculate the mean, median, maximum, and minimum trip durations
trip_duration_mean_ = bikes_df["Trip_Duration_in_min"].mean()
trip_duration_median_ = bikes_df["Trip_Duration_in_min"].median()
trip_duration_max_ = bikes_df["Trip_Duration_in_min"].max()
trip_duration_min_ = bikes_df["Trip_Duration_in_min"].min()

# Print the statistics
print("Trip Duration Statistic:")
print("Trip_duration_mean:", round(trip_duration_mean_, 0))
print("Trip_duration_median:", round(trip_duration_median_, 0))
print("Trip_duration_min:", round(trip_duration_min_, 0))
print("Trip_duration_max:", round(trip_duration_max_, 0))


# Exclude the outlier (max value) in column "Trip_Duration_in_min"
bikes_df = bikes_df[bikes_df["Trip_Duration_in_min"] != trip_duration_max_]

# Recalculate the statistic data
trip_duration_mean = bikes_df["Trip_Duration_in_min"].mean()
trip_duration_median = bikes_df["Trip_Duration_in_min"].median()
trip_duration_max = bikes_df["Trip_Duration_in_min"].max()
trip_duration_min = bikes_df["Trip_Duration_in_min"].min()

# Print the updated statistic
print()
print("Updated Trip Duration Statistic:")
print("Trip duration mean:", round(trip_duration_mean, 0))
print("Trip duration median:", round(trip_duration_median, 0))
print("Trip duration min:", round(trip_duration_min, 0))
print("Trip duration max:", round(trip_duration_max, 0))


# Calculate age statistic
age_mean = bikes_df["Age"].mean()
age_median = bikes_df["Age"].median()
age_max = bikes_df["Age"].max()
age_min = bikes_df["Age"].min()

# Print age statistic
print()
print("Age statistic:")
print("Age mean:", round(age_mean, 0))
print("Age median:", round(age_median, 0))
print("Age min:", round(age_min, 0))
print("Age max:", round(age_max, 0))


# Create a table of trip duration and age statistics
statistics_table = pd.DataFrame({
    "Statistic": ["Mean", "Median", "Max", "Min"],
    "Trip Duration": [round(trip_duration_mean, 0), round(trip_duration_median, 0), round(trip_duration_min, 0), round(trip_duration_max, 0)],
    "Age": [round(age_mean, 0), round(age_median, 0), round(age_min, 0), round(age_max, 0)]
})

# Display the table
display(statistics_table)

# Calculate the top 10 most popular pick-up locations
pickup_locations_top = bikes_df['Start Station Name'].value_counts().head(10)
display(pickup_locations_top)

# Calculate the total trip duration for each age group
trip_duration = bikes_df.groupby('Age Groups')['Trip_Duration_in_min'].mean().round(0)
display(trip_duration)

# Calculate the number of rentals for each age group
most_rents = bikes_df["Age Groups"].value_counts()
display(most_rents)

# Calculate the total number of bike rentals for each user type and weekday
rental_vary_user = bikes_df.groupby(["User Type","Weekday"])['Bike ID'].count()

# Create a dataframe with user type in the first column and weekdays as rows
table_1 = rental_vary_user.unstack(level=0)

# Highlight the highest count in table_1 and change color to grey
highlighted_table_1 = table_1.style.highlight_max(axis=0).set_table_styles([{
    'selector': 'tr:hover',
    'props': [('background-color', 'yellow')]
}])
display(highlighted_table_1)

# Calculate the number of one-time users
one_time_users = (bikes_df["User Type"]=="One-time user").sum()

# Calculate the percentage of one-time users
one_time_users_percent = one_time_users/bikes_df["User Type"].count()*100

# Calculate the percentage of subscribers
subscribers = -one_time_users_percent + 1*100

# Print the percentage of subscribers
print("Subscribers:",str(subscribers.round(2)) + "%")

# Print the percentage of one-time users
print("One-time users:",str(one_time_users_percent.round(2))+"%")

# Visualize the top 10 most popular pick-up locations
plt.figure(figsize=(10, 6))
pickup_locations_top.sort_values(ascending=True).plot(kind='barh')
plt.title('Top 10 Most Popular Pick-up Locations')
plt.xlabel('Number of Rentals')
plt.ylabel('Pick-up Locations')
plt.xticks(rotation=45)
plt.show()

# Visualize the average trip duration across different age groups
plt.figure(figsize=(10, 6))
trip_duration.plot(kind='bar')
plt.title('Average Trip Duration Across Age Groups')
plt.xlabel('Age Groups')
plt.ylabel('Average Trip Duration (minutes)')
plt.xticks(rotation=0)
plt.show()

# Visualize the number of rentals for each age group
plt.figure(figsize=(10, 6))
most_rents.plot(kind='bar')
plt.title('Number of Rentals by Age Group')
plt.xlabel('Age Groups')
plt.ylabel('Number of Rentals')
plt.xticks(rotation=0)
plt.show()

# Visualize the total number of bike rentals for each user type and weekday
plt.figure(figsize=(10, 6))
table_1.loc[['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']].plot(kind='area', stacked=True)
plt.title('Total Bike Rentals by User Type and Weekday')
plt.xlabel('Weekday')
plt.ylabel('Number of Rentals')
plt.xticks(rotation=0)
plt.show()

bikes_df.plot(x="Age",y='Trip_Duration_in_min', kind="scatter")
plt.title("Age and trip duration")
plt.xlabel("Age")
plt.ylabel("Trip duration [min]")
plt.show()