<jupyter_start><jupyter_text>Data inproc and cleaning script<jupyter_code>import numpy as np
import pandas as pd
from zipfile import ZipFile
import os
import shutil
import pyarrow as pa
import pyarrow.parquet as pq
import datetime<jupyter_output><empty_output><jupyter_text>Source data from Capital Bikeshare data repos: https://s3.amazonaws.com/capitalbikeshare-data/index.html<jupyter_code>zips = os.listdir(".././rawdata")
for zipfile in zips:
    zipfile = ".././rawdata/" + zipfile
    with ZipFile(zipfile, "r") as zipObj:
        fns = zipObj.namelist()
        for fn in fns:
            if fn.endswith('.csv') or fn.endswith('tripdata'):
                zipObj.extract(fn, 'temp')
for fn in os.listdir("./temp"):
    if fn.endswith('tripdata'):
        fn = "./temp/" + fn
        os.rename(fn, fn + ".csv")
try:
    shutil.rmtree("./temp/__MACOSX", ignore_errors=False, onerror=None)
except OSError as e:
    print ("Error: %s - %s." % (e.filename, e.strerror))
temp_dir = "./temp"
os.path.join(temp_dir, os.listdir(temp_dir)[0])
temp_files = []
for fn in os.listdir(temp_dir):
    temp_files.append(os.path.join(temp_dir, fn))
df = pd.concat((pd.read_csv(f) for f in temp_files))
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 8396576 entries, 0 to 251542
Data columns (total 23 columns):
 #   Column                Dtype  
---  ------                -----  
 0   Duration              float64
 1   Start date            object 
 2   End date              object 
 3   Start station number  float64
 4   Start station         object 
 5   End station number    float64
 6   End station           object 
 7   Bike number           object 
 8   Member type           object 
 9   ride_id               object 
 10  rideable_type         object 
 11  started_at            object 
 12  ended_at              object 
 13  start_station_name    object 
 14  start_station_id      float64
 15  end_station_name      object 
 16  end_station_id        float64
 17  start_lat             float64
 18  start_lng             float64
 19  end_lat               float64
 20  end_lng               float64
 21  member_casual         object 
 22  is_equity             object 
dtypes: float[...]<jupyter_text>Format changes at some point, and results in half the data with null values for each format. We can split-apply-combine this into the correct format for analysis.<jupyter_code>dfearly = df[['Start date', 'End date', 'Start station number', 'Start station', 'End station number', 'End station']]
dfearly = dfearly.dropna(axis=0)
dfearly.shape
dflate = df[['started_at', 'ended_at', 'start_station_id', 'start_station_name', 'end_station_id', 'end_station_name']]
dflate = dflate.dropna(axis=0)
dflate.shape
dfearly.columns = dflate.columns
df_fixed = dfearly.append(dflate).dropna(axis=0).drop_duplicates(keep='last')
df_fixed.shape
df_fixed.head()
df_fixed.tail()<jupyter_output><empty_output><jupyter_text>Write this file to parquet format for easier sharing / processing.<jupyter_code>tripdata_table = pa.Table.from_pandas(df_fixed)
pq.write_table(tripdata_table, ".././data/tripdata.parquet")<jupyter_output><empty_output><jupyter_text>Pull out only the duration data by day<jupyter_code>duration = df_fixed[['started_at', 'ended_at']].copy()
duration.head()
duration['trip_start'] = pd.to_datetime(duration['started_at'])
duration['trip_end'] = pd.to_datetime(duration['ended_at'])
duration['duration'] = (duration['trip_end'] - duration['trip_start'])
duration.drop(columns=['started_at', 'ended_at'], inplace=True)
duration.head()
duration['duration_seconds'] = duration['duration'].dt.total_seconds()
duration.drop(columns = ['duration'], inplace=True)
duration.head()
duration_table = pa.Table.from_pandas(duration)
pq.write_table(duration_table, ".././data/duration-data.parquet")<jupyter_output><empty_output><jupyter_text>Clean up the temp files and directory<jupyter_code>try:
    shutil.rmtree("./temp", ignore_errors=False, onerror=None)
except OSError as e:
    print ("Error: %s - %s." % (e.filename, e.strerror))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12042012701514411, 'fraction_numerical': 0.01880801172447484, 'mean_word_length': 3.5148842337375963, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '353064', 'license': 'MIT', 'n_tokens_mistral': 1441, 'n_tokens_neox': 1342, 'n_words': 323, 'path': 'src/bikeshare.data_exp.01.ipynb', 'repo_name': 'burch-cm/bikeshare', 'size': '4094'}