<jupyter_start><jupyter_text><jupyter_code># import pandas and numpy
import pandas as pd
import numpy as np

# Loading dataset and previewing
df = pd.read_csv('/content/Autolib_dataset.csv')
df.head()<jupyter_output><empty_output><jupyter_text>**CLEANING OUR DATASET :** **1. VALIDITY**<jupyter_code># Drop irrelevant columns
df.drop(["Displayed comment", "Scheduled at"], axis=1, inplace=True)
df.head()
# Remove trailing and leading spaces
df['Address'] = df['Address'].str.strip()
df['City'] = df['City'].str.strip()
df['ID'] = df['ID'].str.strip()
df['Public name'] = df['Public name'].str.strip()<jupyter_output><empty_output><jupyter_text>**2. COMPLETENESS**<jupyter_code># check for missing values
df.isnull().sum()
# drop duplicates
df.drop_duplicates<jupyter_output><empty_output><jupyter_text>**3. UNIFORMITY**<jupyter_code># checking datatypes
df.info()
# export to csv
df.to_csv('df4.csv')<jupyter_output><empty_output><jupyter_text>**CONNECTING TO OUR DATABASE**<jupyter_code># We will first load the sql extension into our environment
#
%load_ext sql

# Then connect to our in memory sqlite database
# NB: This database will cease to exist as soon as the database connection is closed. 
#
%sql sqlite://
# Importing the pandas library
# We will use a function read_csv from pandas to read our datasets as shown
#
import pandas as pd 
# Loading our table from the respective CSV files
# We will export the Households dataset that we have cleaned using python
#
with open('/content/df4.csv','r') as f:
    Electric = pd.read_csv(f, index_col=0, encoding='utf-8')
%sql DROP TABLE if EXISTS Electric;
%sql PERSIST Electric;
%sql SELECT * FROM Electric LIMIT 5;<jupyter_output>* sqlite://
Done.
 * sqlite://
 * sqlite://
Done.<jupyter_text>**Research Question****Identify the most popular hour of the day for picking up a shared electric car (Bluecar) in the city of Paris over the month of April 2018.**<jupyter_code>%%sql
SELECT hour, COUNT(hour) from Electric
where "Bluecar counter" = 0 and "City" = "Paris"
GROUP BY hour
ORDER BY COUNT(hour) Desc limit 1;
# What is the most popular hour for returning cars?
%%sql
SELECT hour, COUNT(hour) from Electric
where "Slots" = 0 
GROUP BY hour
ORDER BY COUNT(hour) DESC LIMIT 1 ;
# What station is the most popular overall?
%%sql
SELECT "Public name", COUNT("Public name") from Electric
where "Cars" = 0 
GROUP BY "Public name"
ORDER BY COUNT("Public name") DESC limit 1;
# What station is the most popular at the most popular picking hour?
%%sql
SELECT "Public name", COUNT("Public name") from Electric
where "Cars" = 0 and hour = 2
GROUP BY "Public name" 
ORDER BY Count("Public name") DESC;
# What postal code is the most popular for picking up Blue cars overall?
%%sql
SELECT "Postal code", COUNT("Postal code") from Electric
where "Bluecar counter" = 0 
GROUP BY "Postal code"
ORDER BY COUNT("Postal code") Desc limit 1;
# Does the most popular station belong to that postal code?
%%sql
Select "Postal code" from Electric
where "Public name" = "Paris/Grenelle 31/Parking";<jupyter_output>* sqlite://
Done.<jupyter_text>No the most popular station does not belong to that postal code since the postal code for the most popular station is 75015 and the postal code for the station that's most popluar for picking blue cars is 75008<jupyter_code># What postal code is the most popular for picking up Blue cars at the most popular picking hour?
%%sql
SELECT "Postal code", COUNT("Postal code") from Electric
where "Bluecar counter" = 0 and hour = 2
GROUP BY "Postal code"
ORDER BY COUNT("Postal code") Desc limit 1;
# Does the most popular station belong to that postal code?
%%sql
Select "Postal code" from Electric
where "Public name" = "Paris/Grenelle 31/Parking";<jupyter_output>* sqlite://
Done.<jupyter_text>No the most popular station does not belong to that postal code since the postal code for the most popular station is 75015 and the postal code for the station that's most popluar for picking blue cars at the most popular picking hour is 75006 Do the results change if you consider Utilib and Utilib 1.4 instead of Blue cars?<jupyter_code># What postal code is the most popular for picking up Utilib cars overall?
%%sql
SELECT "Postal code", COUNT("Postal code") from Electric
where "Utilib counter" = 0 
GROUP BY "Postal code"
ORDER BY COUNT("Postal code") Desc limit 1;
# Does the most popular station belong to that postal code?
%%sql
Select "Postal code" from Electric
where "Public name" = "Paris/Grenelle 31/Parking";<jupyter_output>* sqlite://
Done.<jupyter_text>Yes the most popular station belongs to that postal code<jupyter_code># What postal code is the most popular for picking up Utilib 1.4 cars overall?
%%sql
SELECT "Postal code", COUNT("Postal code") from Electric
where "Utilib 1.4 counter" = 0 
GROUP BY "Postal code"
ORDER BY COUNT("Postal code") Desc limit 1;
# Does the most popular station belong to that postal code?
%%sql
Select "Postal code" from Electric
where "Public name" = "Paris/Grenelle 31/Parking";<jupyter_output>* sqlite://
Done.<jupyter_text>Yes the most popular station belongs to that postal code<jupyter_code># What postal code is the most popular for picking up Utilib cars at the most popular picking hour?
%%sql
SELECT "Postal code", COUNT("Postal code") from Electric
where "Utilib counter" = 0 and hour = 2
GROUP BY "Postal code"
ORDER BY COUNT("Postal code") Desc limit 1;
# Does the most popular station belong to that postal code?
%%sql
Select "Postal code" from Electric
where "Public name" = "Paris/Grenelle 31/Parking";<jupyter_output>* sqlite://
Done.<jupyter_text>No the most popular station does not belong to that postal code since the postal code for the most popular station is 75015 and the postal code for the station that's most popluar for picking Utilib cars at the most popular picking hour is 75005<jupyter_code># What postal code is the most popular for picking up Utilib 1.4 cars at the most popular picking hour?
%%sql
SELECT "Postal code", COUNT("Postal code") from Electric
where "Utilib 1.4 counter" = 0 and hour = 2
GROUP BY "Postal code"
ORDER BY COUNT("Postal code") Desc limit 1;
# Does the most popular station belong to that postal code?
%%sql
Select "Postal code" from Electric
where "Public name" = "Paris/Grenelle 31/Parking";<jupyter_output>* sqlite://
Done.
{'fraction_non_alphanumeric': 0.09526073862735775, 'fraction_numerical': 0.013948327785702964, 'mean_word_length': 5.2848605577689245, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 38, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '96732', 'license': 'Unlicense', 'n_tokens_mistral': 2027, 'n_tokens_neox': 1884, 'n_words': 856, 'path': 'John_Njagi_Notebook.ipynb', 'repo_name': 'BrianGakungi/Brian-Njagi-Autolib-Analysis', 'size': '6309'}