<jupyter_start><jupyter_text>This Notebook Will be for Cleaning/Transforming Data from Raw CSV files and Loading into a PostGIS database<jupyter_code># List of filenames
a, b, c = extract.build_paths(csv_url='csv')

csv_paths = extract.compile_paths(a,b,c)

raw_dfs = extract.import_csvs(csv_paths)<jupyter_output><empty_output><jupyter_text>The Columns Are Not All The Same- Observe recent difference in col naming convention for the set<jupyter_code>col_names = [df.columns for df in raw_dfs]
col_names<jupyter_output><empty_output><jupyter_text>But the Information Is Roughly The Same- Notably different items are:    - Rideable Type (All of the old sets are presumed to be Docked Bikes)    - Missing Lat/Lng (need an imputation strategy)    - Different Station ID/Numbering Convention (not a huge deal)    - No Unique Ride ID (we'll want to add this if using this Data)<jupyter_code>legacy_list = raw_dfs[:-4]
legacy_list[-1].head()<jupyter_output><empty_output><jupyter_text>Quick check to make sure column names are consistent<jupyter_code>def check_col_names(df1, df2, cols_only=True):
    """Return True or False indicating whether or not the column names are the same between two dataframes"""
    # Default is that columns are passed, for ease of use
    # with check_col_list below
    # will return truth value for each column pair, so we achieve
    # one truth value for the whole array/list by calling all
    if cols_only:
        result = all(df1 == df2)
    # Also possible to pass two dataframes directly by specifying
    # cols_only=False
    else:
        result = all(df1.columns == df2.columns)
    # Return True/False result
    return result

def check_col_list(df_list):
    """Accepts list of dataframes, calls check_col_names on the df columns
    returns False if any False values, true otherwise"""
    
    # Build dict of df columns keyed by position in list
    # Only pull the columns to save some space
    # Save as dict to make returning the mismatches easier on
    # later edit
    col_dict = {i: df.columns for i, df in enumerate(df_list)}
    
    # build list of keys for cleaner reading below
    keys = list(col_dict.keys())
    
    
    # Because we are only interested in if there are any False values
    # we can compare one df to all others by the logic that if a==b,
    # a==c, a==d, etc. then a==b==c==d and so on
#     results = [check_col_names(col_dict[0], col_dict[key]) for key in keys]
    
#     # return True only if all are true
    
#     return all(results)
    return col_dict[0]
    
    
# print(check_col_names(historical_list[0], historical_list[-1], cols_only=False))
# print(check_col_names(historical_list[0].columns, historical_list[-1].columns))

check_col_list(raw_dfs[:-4])
      
historical_list[0].columns == historical_list[-1].columns
def drop_legacy_cols(df):
    """Accepts df in raw format from prior to April of 2020
    Returns same df without the columns ['Duration', 'Start station number', 
    'End station number', 'Bike number'] Duration is created later in
    cleaning process to ensure consistent results Station Number cols
    duplicate information in station name cols and are of different 
    format to current numbering scheme, bike number is unavailable in
    current datasets"""
    return df.drop(['Duration', 'Start station number', 'End station number', 'Bike number'], axis=1)


drop_legacy_cols(legacy_list[0])
def rename_legacy_cols(df):
    """Renames columns of dataframe in format consistent with the current naming scheme"""
    return df.rename(
        columns={'Start date':'started_at',
                 'End date': 'ended_at',
                 'Start station':'start_station_name',
                 'End station': 'end_station_name',
                 'Member type': 'member_casual'}
    )

# rename_legacy_cols(historical_list[0])
# Drop incompatible columns
legacy_list = [df.drop(['Duration', 'Start station number', 'End station number', 'Bike number'], axis=1) for df in legacy_list]
legacy_list[0].head()
historical_list = [df.rename(columns={'Start date':'started_at',
                                     'End date': 'ended_at',
                                     'Start station':'start_station_name',
                                     'End station': 'end_station_name',
                                     'Member type': 'member_casual'}) for df in historical_list]
historical_df = pd.concat(historical_list)
historical_df


def legacy_to_recent(df):
    """Returns df formatted in the same style as employed from April of 2020
    by calling drop/rename legacy cols funcs in succession, adding a rideable_type
    and correcting type-case of the member_casual column 
    """
    new_df = drop_legacy_cols(df)
    new_df = rename_legacy_cols(new_df)
    # impute rideable type since ebikes were not rolled out until after
    # any of these data sets were released
    new_df['rideable_type'] = 'docked_bike'
    # convert values to lower case
    new_df['member_casual'] = new_df.member_casual.str.lower()
    
    return new_df

# legacy_to_recent(historical_list[-1])
    
historical_list = [legacy_to_recent(df) for df in historical_list]
historical_list
historical_df['rideable_type'] = 'docked_bike'
historical_df['member_casual'] = historical_df.member_casual.str.lower()
cols = [col for col in historical_df.columns]
cols
cols = [cols[-1]] + cols[:-1]
cols
def join_legacy(df_list):
    """Returns one DataFrame in current format from list of legacy
    formatted dataframes"""
    # format each dataframe in the list, combine them into one new dataframe with a new index
    full = pd.concat([legacy_to_recent(df) for df in df_list], ignore_index=True)
    # Allowing us to keep the ride_id column for the newer dataframes since none of the existing
    # ride ids are integers
    full['ride_id'] = pd.Series(range(len(full)))
    # The columns are slightly out of order, since we defined a new column 'rideable_type'
    # with our legacy_to_recent function which is the second column in the new format.
    # and then defined a ride_id which is the first. We first reorder the columns:
    cols = [col for col in full.columns]
    cols = [cols[-1], cols[-2]] + cols[:-2]
    # And then reindex the new dataframe
    full = full[cols]
    
    return full
    
join_legacy(raw_dfs[:-4])
historical_df = historical_df[cols]
def drop_recent_cols(df):
    """Accepts a dataframe from April of 2020 or later
    Returns dataframe suitable for a temporary join with historical data
    to demonstrate decisions made in EDA process as discussed in Technical
    Notebook"""
    return df.drop(['start_station_id', 'end_station_id', 'start_lat', 'start_lng', 'end_lat', 'end_lng'], axis=1)

def drop_equity(df):
    """This column appears in only one dataframe and is no longer reported"""
    if 'is_equity' in df.columns:
        result = df.drop('is_equity', axis=1)
    else:
        result = df
    
    return result
    

print(raw_dfs[-3].columns)
drop_equity(raw_dfs[-3])
recent_list = raw_dfs[-4:]
recent_list[1] = recent_list[1].drop('is_equity', axis=1)

# Drop incompatible columns
recent_list = [df.drop(['ride_id', 'start_station_id', 'end_station_id', 'start_lat', 'start_lng', 'end_lat', 'end_lng'], axis=1) for df in recent_list]
recent_list[0]
recent_df = pd.concat(recent_list)
recent_df
merged = clean.merge_all(raw_dfs[:-4], raw_dfs[-4:])
merged
def clean_merged(df):
    """Quick wrapper to allow datetime operations during EDA"""
    df['started_at'] = pd.to_datetime(df['started_at'])
    df['ended_at'] = pd.to_datetime(df['ended_at'])
    merged_df = df.set_index('started_at')
    merged_df = merged_df.sort_index()
    return merged_df
merged = clean_merged(merged)
merged.info()
merged.describe(datetime_is_numeric=False)
merged[merged.ended_at == merged.ended_at.max()]
merged
merged.to_pickle('../data/wip/merged.pkl')
import matplotlib.pyplot as plt
import datetime as dt

daily_counts = merged.resample('D').count()

def plot_daily_counts(daily_counts):
    """Helper function for streamlining technical notebook"""
    fig, (axe, axe1, axe2) = plt.subplots(3, 1, sharey=True, figsize=(24, 9))

    plt.style.use('seaborn-deep')


    daily_counts = daily_counts.rideable_type

    nineteen = daily_counts[(daily_counts.index < '2020-01-01') & (daily_counts.index > '2019-01-01')]
    eighteen = daily_counts[daily_counts.index < '2019-01-01']
    twenty = daily_counts[daily_counts.index >= '2020-01-01']

    # Moving averages
    rolling_eighteen = eighteen.rolling(window=15).mean()
    rolling_nineteen = nineteen.rolling(window=15).mean()
    rolling_twenty = twenty.rolling(window=15).mean()


    axe.plot(eighteen)
    axe.plot(rolling_eighteen, color = 'green')
    axe.axhline(y=12500, ls = '--', color='navy')
    axe.axhline(y=2500, ls = '--', color='orangered')
    axe.set_xlim([dt.date(2018, 1, 1), dt.date(2018, 12, 31)])

    axe1.plot(nineteen)
    axe1.plot(rolling_nineteen, color = 'green')
    axe1.axhline(y=12500, ls = '--', color='navy')
    axe1.axhline(y=2500, ls = '--', color='orangered')
    axe1.set_xlim([dt.date(2019, 1, 1), dt.date(2019, 12, 31)])


    axe2.plot(twenty)
    axe2.plot(rolling_twenty, color = 'red')
    axe2.axhline(y=12500, ls = '--', color='navy')
    axe2.axhline(y=2500, ls = '--', color='orangered')
    axe2.set_xlim([dt.date(2020, 1, 1), dt.date(2020, 12, 31)])





    fig.suptitle('Daily Capital Bikeshare Ride Counts, 2018-2020', y=1.06, fontsize=28)
    fig.text(s='Green and Red Lines are 15 Day Moving Averages', x=0.5, horizontalalignment='center', y=.99, fontsize=20)
    fig.tight_layout()
    # fig.savefig('../figures/dailyridecounts.png', bbox_inches='tight')

    return fig, (axe, axe1, axe2)

fig, ax = plt.subplots(figsize=(10,10))
twenty.plot()
ax.set_xlim(dt.date)<jupyter_output><empty_output><jupyter_text>For Now, Let's Just Use the Recent Data<jupyter_code>april_to_july_list = raw_dfs[-4:]
april_to_july_list[1] = april_to_july_list[1].drop('is_equity', axis=1)

raw_apr_to_jul_df = pd.concat(april_to_july_list)
raw_apr_to_jul_df<jupyter_output><empty_output><jupyter_text>About 35s to run clean_frame or to_geo on 100000 rows<jupyter_code>%%time
clean.full_transform(test_df)
%%time
cleaned_recent = clean.clean_frame(raw_apr_to_jul_df)
cleaned_recent
geo = clean.to_geo(cleaned_recent)
geo = clean.rename_columns(geo)
long_geo = clean.full_transform(raw_apr_to_jul_df)
long_geo
# Creating SQLAlchemy's engine to use
engine = pass


geodataframe = long_geo.copy()
#... [do something with the geodataframe]

geodataframe['geom_coord'] = geodataframe['coord'].apply(lambda x: WKTElement(x.wkt, srid=4326))


                                                               
#drop the geometry column as it is now duplicative
geodataframe.drop('coord', axis=1, inplace=True)

# Use 'dtype' to specify column's type
# For the geom column, we will use GeoAlchemy's type 'Geometry'
geodataframe.to_sql(
    'trips_long',
    engine,
    if_exists='append',
    index=True,
    index_label='time',
    chunksize=10000,
    dtype={
        'geom_coord': Geometry(
            geometry_type='POINT',
            srid=4326)
    })
plus_minus = t.to_plus_minus(long_geo)
plus_minus['1A'].rolling('3H').sum()
plus_minus.to_sql(
    'plus_minus',
    engine,
    if_exists='append',
    index=True,
    index_label='time'
)

# Ensure connections are closed

engine.dispose()<jupyter_output><empty_output><jupyter_text>There are no lat/lng coords in the Historical DataFrame: Can we Impute Coords from Recent Station Info? Second Note on Approach -- Should we be modeling checkin and checkouts instead of gain/loss?<jupyter_code>def prop_top(col):
    """Return the proportion of the total observations that the mode observation makes up for a given pandas Series"""
    rounded = round(col, 4)
    count_top = rounded.value_counts().values[0]
    total = len(rounded)
    prop = count_top / total
    return prop

def count_obs(col, name):
    """Return number of instances of name in col"""
    
    count = len(col[col == name])
    return count

stations = list(raw_apr_to_jul_df.start_station_name.value_counts().index)
stations.remove('Motivate Tech Office')
prop_lats = {name: prop_top(raw_apr_to_jul_df[raw_apr_to_jul_df.start_station_name == name].start_lat)\
             for name in stations}

prop_lngs = {name: prop_top(raw_apr_to_jul_df[raw_apr_to_jul_df.start_station_name == name].start_lng)\
             for name in stations}

prop_lats = {k: v for k, v in sorted(prop_lats.items(), key=lambda item: item[1])}
prop_lngs = {k: v for k, v in sorted(prop_lngs.items(), key=lambda item: item[1])}


# station_counts = {station: count_obs(raw_apr_to_jul_df['start_station_name'], station) for station in stations}
# station_counts<jupyter_output><empty_output><jupyter_text>In General It Seems very reasonable to impute start_lat/lng for each station from top value_count, there are 16 stations for which either lat or lng impute strategy is less than 90% accurate- Saving these in a list, visualize to look for pattern or outliers<jupyter_code>inaccurate_keys = []
for key in prop_lats.keys():
    if (prop_lats[key] < 0.9) | (prop_lngs[key] < 0.9):
        print(f'{key}: {prop_lats[key]}, {prop_lngs[key]}')
        inaccurate_keys.append(key)

inaccurate_keys
    
# raw_july = pd.read_csv(csv_paths[-1])

# raw_dockless = raw_july[raw_july.rideable_type == 'electric_bike']
# pd.to_pickle(raw_dockless, '../data/wip/raw_dockless.pkl')
# pd.to_pickle(gdf, '../data/wip/gdf_anc.pkl')

gdf = get_data.anc_gdf()

def plot_trips(df, axe):
    gdf.plot(figsize=(10, 10), alpha=0.5, edgecolor='k', ax=axe)
    sns.scatterplot(x='start_lng', y='start_lat', data=df, ax=axe)<jupyter_output><empty_output><jupyter_text>No Readily Discernable Pattern Geographically to the no end lat/lng trips<jupyter_code>fig, ax = plt.subplots(figsize=(10,10))

plot_trips(raw_apr_to_jul_df[raw_apr_to_jul_df.end_lat.isna()], axe=ax)
fig, axes = plt.subplots(8,2, figsize=(10, 30))
axes = axes.ravel()

for i, key in enumerate(inaccurate_keys):
    df = raw_apr_to_jul_df.loc[
        raw_apr_to_jul_df['start_station_name'] == key, ['start_lng', 'start_lat']]
    sns.scatterplot(x='start_lng', y='start_lat', data=df, ax=axes[i])
    axes[i].set_title(key)

fig.tight_layout()
plt.show()
pd.to_pickle(raw_apr_to_jul_df, '../data/wip/raw_apr_to_jul_df.pkl')
df = raw_apr_to_jul_df.loc[
    raw_apr_to_jul_df['start_station_name'] == key, ['start_lng', 'start_lat']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11527873365450791, 'fraction_numerical': 0.017962835512732277, 'mean_word_length': 4.12195981670779, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 39, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '608072', 'license': 'MIT', 'n_tokens_mistral': 5266, 'n_tokens_neox': 4950, 'n_words': 1295, 'path': 'notebooks/drafts/ETL.ipynb', 'repo_name': 'jmillerbrooks/capital_bikeshare', 'size': '14530'}