<jupyter_start><jupyter_text>Add Timeline Events to Dataframe to Annotate Plot* 3/10/2018 - Scooters Introducted* 5/21/2018 - Shift to more scooters announced<jupyter_code>dates = [date(2018,3,10),
         date(2018,4,27),
         date(2018,5,21),
         date(2018,8,30)]
events = ['Scooters Intro  ',
          'DDOT extends pilot to Aug  ',
          'More scooters announced  ',
          'DDOT extends pilot to Dec  ']
timeline_df = pd.DataFrame({'date': dates, 'event': events})
timeline_df['Lime Vehicles'] = 'scooters'
timeline_df['date'] = pd.to_datetime(timeline_df['date'])


merge_df = melted_df.merge(timeline_df, on=['date', 'Lime Vehicles'], how='left')
merge_df['event'] =  merge_df['event'].fillna('')
line_chart = alt.Chart(merge_df).mark_line(opacity=0.6).encode(
             alt.X('date'),
             alt.Y('Daily Vehicles on Street'),
             alt.Color('Lime Vehicles',
                      legend=alt.Legend(title=None, orient='top-left'),
                      scale=alt.Scale(range=['#00ff26', 'blue']))
             )

# points
points = alt.Chart(merge_df[merge_df['event'] != '']
             ).mark_point(color='red'
                
             ).encode(
             alt.X('date'),
             alt.Y('Daily Vehicles on Street')
)

# Annotated points
text = points.mark_text(
                  align='right',
                  baseline='bottom',
                    
        ).encode(
            text='event'
        )


lime_plot = line_chart + points + text

lime_plot.save('lime.html')

lime_plot
df.to_csv("lime_count.csv")<jupyter_output><empty_output><jupyter_text>Plot AnalysisScooters are introduced on March 10, 2008 and their numbers on the street remain fairly flat until late May, when Lime announced that would start placing more scooters and removing a proportionate number of bikes to adhere to the DDOT dockless pilot 400 vehicle cap.  At about the same time, the average number of Lime bikes on the street reach their max for this time period at slightly more than 400 and takes a nose dive through the month of June.  At this point it looks like Lime made the decision to comprise their fleet of almost all scooters, which has persisted until today.Overall, we can see large swings in the number of vehicles on the street, as Lime is constantly replacing scooters and bikes.  This can most easily be seen in mid-July where right, only three weeks after putting 400+ scooters on the street, there are less than 200 scooters on the street, while the number of bikes stays relatively consistent.  It's possible that the popularity of the scooters caused wear and tear at a rate higher than Lime could replace them with new scooters.  We see an overcompensation for this phenomon in late August and early September where an average of over 500 scooters are on the street and then steadily declines over the following weeks.Between March 10th and mid September, Lime used at least 1,886 scooters and 3,960 total vehicles.  We don't know the exact numbers scooters used during this time period because Lime did not differentiate between scooters and bikes until June 13th, and we had to reverse engineer the scooter count prior to that date.  If a scooter was put on the street and subsequently removed prior to June 13th, we cannot identify that vehicle as a scooter, resulting in our scooter estimate being low. Notes to Integrate into analysis* On date that more scooters announced, number of bikes begins to increase rapidly* Download trend for Jump looks similar to Lime after going vast majority scooters<jupyter_code># Total Scooters

total_scooters= pd.read_sql("""SELECT DISTINCT count(distinct bike_id)
                               from bike_locations
                               where raw->'attributes'->>'vehicle_type'='scooter';
                """, con=conn)

total_scooters
# Total Limes
total_limes= pd.read_sql("""SELECT DISTINCT count(distinct bike_id)
                               from bike_locations
                               where provider = 'limebike'
                               ;
                """, con=conn)

total_limes

# Average Age of Scooters
scooter_age = pd.read_sql("""select
                            AVG(bike_age) as avg_bike_age
                            FROM
                            /*determine scooter age*/
                            (SELECT
                            locations.bike_id,
                            min(created) as start_date,
                            MAX(created) - MIN(created) as bike_age
                            FROM 
                            /*determine lime scooters*/ 
                            (SELECT DISTINCT
                              created, bike_id 
                              from bike_locations
                              where provider='limebike') as locations
                            JOIN
                            (SELECT DISTINCT bike_id
                                     from bike_locations
                                     where raw->'attributes'->>'vehicle_type'='scooter') as scooters
                            on locations.bike_id = scooters.bike_id
                            GROUP BY 1) as bike_age
                            where start_date <= '2018-08-18' """, con=conn)
scooter_age<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0735238811583302, 'fraction_numerical': 0.017299736743136517, 'mean_word_length': 2.108708357685564, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '176363', 'license': 'MIT', 'n_tokens_mistral': 1508, 'n_tokens_neox': 1369, 'n_words': 511, 'path': 'plots/lime.ipynb', 'repo_name': 'msussman/dc_dobi_extinction', 'size': '5318'}