{ "cells": [ { "cell_type": "code", "execution_count": 74, "metadata": { "collapsed": true }, "outputs": [], "source": [ "#Place imports here\n", "import pandas as pd\n", "import numpy as np\n", "import folium" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "For this practice, we will try to explore if there is net migration of bikes from one bike station to another and if this migration depends on the time of day. Read in the bike share data from August of 2018, this will be plenty of data. Read in the columns \"starttime\" and \"stoptime\" as dates using the parse_dates input. Also change the column names so all spaces are replaced with underscores." ] }, { "cell_type": "code", "execution_count": 75, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
tripdurationstarttimestoptimestart_station_idstart_station_namestart_station_latitudestart_station_longitudeend_station_idend_station_nameend_station_latitudeend_station_longitudebikeidusertypebirth_yeargender
06812018-08-01 00:00:07.3212018-08-01 00:11:28.9923162.0W 78 St & Broadway40.783400-73.9809313383.0Cathedral Pkwy & Broadway40.804213-73.96699127770Subscriber19861
16252018-08-01 00:00:19.7482018-08-01 00:10:45.0293260.0Mercer St & Bleecker St40.727064-73.9966212012.0E 27 St & 1 Ave40.739445-73.97680625938Subscriber19691
213192018-08-01 00:00:21.1752018-08-01 00:22:20.637403.0E 2 St & 2 Ave40.725029-73.990697285.0Broadway & E 14 St40.734546-73.99074128679Subscriber19701
32202018-08-01 00:00:26.4702018-08-01 00:04:06.8193637.0Fulton St & Waverly Ave40.683239-73.965996399.0Lafayette Ave & St James Pl40.688515-73.96476328075Subscriber19821
43982018-08-01 00:00:30.2912018-08-01 00:07:09.2813662.031 Ave & Steinway St40.761294-73.9169173517.031 St & Hoyt Ave N40.771153-73.91700725002Subscriber19871
\n", "
" ], "text/plain": [ " tripduration starttime stoptime \\\n", "0 681 2018-08-01 00:00:07.321 2018-08-01 00:11:28.992 \n", "1 625 2018-08-01 00:00:19.748 2018-08-01 00:10:45.029 \n", "2 1319 2018-08-01 00:00:21.175 2018-08-01 00:22:20.637 \n", "3 220 2018-08-01 00:00:26.470 2018-08-01 00:04:06.819 \n", "4 398 2018-08-01 00:00:30.291 2018-08-01 00:07:09.281 \n", "\n", " start_station_id start_station_name start_station_latitude \\\n", "0 3162.0 W 78 St & Broadway 40.783400 \n", "1 3260.0 Mercer St & Bleecker St 40.727064 \n", "2 403.0 E 2 St & 2 Ave 40.725029 \n", "3 3637.0 Fulton St & Waverly Ave 40.683239 \n", "4 3662.0 31 Ave & Steinway St 40.761294 \n", "\n", " start_station_longitude end_station_id end_station_name \\\n", "0 -73.980931 3383.0 Cathedral Pkwy & Broadway \n", "1 -73.996621 2012.0 E 27 St & 1 Ave \n", "2 -73.990697 285.0 Broadway & E 14 St \n", "3 -73.965996 399.0 Lafayette Ave & St James Pl \n", "4 -73.916917 3517.0 31 St & Hoyt Ave N \n", "\n", " end_station_latitude end_station_longitude bikeid usertype \\\n", "0 40.804213 -73.966991 27770 Subscriber \n", "1 40.739445 -73.976806 25938 Subscriber \n", "2 40.734546 -73.990741 28679 Subscriber \n", "3 40.688515 -73.964763 28075 Subscriber \n", "4 40.771153 -73.917007 25002 Subscriber \n", "\n", " birth_year gender \n", "0 1986 1 \n", "1 1969 1 \n", "2 1970 1 \n", "3 1982 1 \n", "4 1987 1 " ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_bikes_share = pd.read_csv(\"Data/201808-citibike-tripdata.csv\", parse_dates=[\"starttime\", \"stoptime\"] )\n", "df_bikes_share.columns = df_bikes_share.columns.str.replace(\" \", \"_\")\n", "\n", "df_bikes_share.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Create columns called \"start_hour\" and \"end_hour\", which gives the hour of the day that the bike was checked out and returned." ] }, { "cell_type": "code", "execution_count": 76, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
tripdurationstarttimestoptimestart_station_idstart_station_namestart_station_latitudestart_station_longitudeend_station_idend_station_nameend_station_latitudeend_station_longitudebikeidusertypebirth_yeargenderstart_hourend_hour
06812018-08-01 00:00:07.3212018-08-01 00:11:28.9923162.0W 78 St & Broadway40.783400-73.9809313383.0Cathedral Pkwy & Broadway40.804213-73.96699127770Subscriber1986100
16252018-08-01 00:00:19.7482018-08-01 00:10:45.0293260.0Mercer St & Bleecker St40.727064-73.9966212012.0E 27 St & 1 Ave40.739445-73.97680625938Subscriber1969100
213192018-08-01 00:00:21.1752018-08-01 00:22:20.637403.0E 2 St & 2 Ave40.725029-73.990697285.0Broadway & E 14 St40.734546-73.99074128679Subscriber1970100
32202018-08-01 00:00:26.4702018-08-01 00:04:06.8193637.0Fulton St & Waverly Ave40.683239-73.965996399.0Lafayette Ave & St James Pl40.688515-73.96476328075Subscriber1982100
43982018-08-01 00:00:30.2912018-08-01 00:07:09.2813662.031 Ave & Steinway St40.761294-73.9169173517.031 St & Hoyt Ave N40.771153-73.91700725002Subscriber1987100
\n", "
" ], "text/plain": [ " tripduration starttime stoptime \\\n", "0 681 2018-08-01 00:00:07.321 2018-08-01 00:11:28.992 \n", "1 625 2018-08-01 00:00:19.748 2018-08-01 00:10:45.029 \n", "2 1319 2018-08-01 00:00:21.175 2018-08-01 00:22:20.637 \n", "3 220 2018-08-01 00:00:26.470 2018-08-01 00:04:06.819 \n", "4 398 2018-08-01 00:00:30.291 2018-08-01 00:07:09.281 \n", "\n", " start_station_id start_station_name start_station_latitude \\\n", "0 3162.0 W 78 St & Broadway 40.783400 \n", "1 3260.0 Mercer St & Bleecker St 40.727064 \n", "2 403.0 E 2 St & 2 Ave 40.725029 \n", "3 3637.0 Fulton St & Waverly Ave 40.683239 \n", "4 3662.0 31 Ave & Steinway St 40.761294 \n", "\n", " start_station_longitude end_station_id end_station_name \\\n", "0 -73.980931 3383.0 Cathedral Pkwy & Broadway \n", "1 -73.996621 2012.0 E 27 St & 1 Ave \n", "2 -73.990697 285.0 Broadway & E 14 St \n", "3 -73.965996 399.0 Lafayette Ave & St James Pl \n", "4 -73.916917 3517.0 31 St & Hoyt Ave N \n", "\n", " end_station_latitude end_station_longitude bikeid usertype \\\n", "0 40.804213 -73.966991 27770 Subscriber \n", "1 40.739445 -73.976806 25938 Subscriber \n", "2 40.734546 -73.990741 28679 Subscriber \n", "3 40.688515 -73.964763 28075 Subscriber \n", "4 40.771153 -73.917007 25002 Subscriber \n", "\n", " birth_year gender start_hour end_hour \n", "0 1986 1 0 0 \n", "1 1969 1 0 0 \n", "2 1970 1 0 0 \n", "3 1982 1 0 0 \n", "4 1987 1 0 0 " ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Write your code here\n", "df_bikes_share[\"start_hour\"] = df_bikes_share.starttime.dt.hour\n", "df_bikes_share[\"end_hour\"] = df_bikes_share.stoptime.dt.hour\n", "\n", "df_bikes_share.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pick an hour of the day you would like to analyze and let's only consider rides starting and ending in this hour." ] }, { "cell_type": "code", "execution_count": 130, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
tripdurationstarttimestoptimestart_station_idstart_station_namestart_station_latitudestart_station_longitudeend_station_idend_station_nameend_station_latitudeend_station_longitudebikeidusertypebirth_yeargenderstart_hourend_hour
01542018-08-01 18:00:00.7842018-08-01 18:02:35.0893259.09 Ave & W 28 St40.749370-73.9992343255.08 Ave & W 31 St40.750585-73.99468531191Subscriber198211818
15812018-08-01 18:00:01.4882018-08-01 18:09:42.871346.0Bank St & Hudson St40.736529-74.006180483.0E 12 St & 3 Ave40.732233-73.98890033065Subscriber199211818
211632018-08-01 18:00:00.8302018-08-01 18:19:24.225316.0Fulton St & William St40.709560-74.006536459.0W 20 St & 11 Ave40.746745-74.00775629717Subscriber199111818
34722018-08-01 18:00:01.5932018-08-01 18:07:54.3223467.0W Broadway & Spring Street40.724947-74.001659312.0Allen St & Stanton St40.722055-73.98911133282Subscriber198621818
47512018-08-01 18:00:01.8422018-08-01 18:12:33.828327.0Vesey Pl & River Terrace40.715338-74.016584368.0Carmine St & 6 Ave40.730386-74.00215018155Subscriber199111818
\n", "
" ], "text/plain": [ " tripduration starttime stoptime \\\n", "0 154 2018-08-01 18:00:00.784 2018-08-01 18:02:35.089 \n", "1 581 2018-08-01 18:00:01.488 2018-08-01 18:09:42.871 \n", "2 1163 2018-08-01 18:00:00.830 2018-08-01 18:19:24.225 \n", "3 472 2018-08-01 18:00:01.593 2018-08-01 18:07:54.322 \n", "4 751 2018-08-01 18:00:01.842 2018-08-01 18:12:33.828 \n", "\n", " start_station_id start_station_name start_station_latitude \\\n", "0 3259.0 9 Ave & W 28 St 40.749370 \n", "1 346.0 Bank St & Hudson St 40.736529 \n", "2 316.0 Fulton St & William St 40.709560 \n", "3 3467.0 W Broadway & Spring Street 40.724947 \n", "4 327.0 Vesey Pl & River Terrace 40.715338 \n", "\n", " start_station_longitude end_station_id end_station_name \\\n", "0 -73.999234 3255.0 8 Ave & W 31 St \n", "1 -74.006180 483.0 E 12 St & 3 Ave \n", "2 -74.006536 459.0 W 20 St & 11 Ave \n", "3 -74.001659 312.0 Allen St & Stanton St \n", "4 -74.016584 368.0 Carmine St & 6 Ave \n", "\n", " end_station_latitude end_station_longitude bikeid usertype \\\n", "0 40.750585 -73.994685 31191 Subscriber \n", "1 40.732233 -73.988900 33065 Subscriber \n", "2 40.746745 -74.007756 29717 Subscriber \n", "3 40.722055 -73.989111 33282 Subscriber \n", "4 40.730386 -74.002150 18155 Subscriber \n", "\n", " birth_year gender start_hour end_hour \n", "0 1982 1 18 18 \n", "1 1992 1 18 18 \n", "2 1991 1 18 18 \n", "3 1986 2 18 18 \n", "4 1991 1 18 18 " ] }, "execution_count": 130, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Write your code here\n", "hour = 18\n", "df_bikes_share_hour = df_bikes_share.loc[(df_bikes_share.start_hour == hour) & (df_bikes_share.end_hour == hour), :].reset_index(drop =True)\n", "\n", "df_bikes_share_hour.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ " Now we need the number of rides that started from and ended at each station and merge the two results. " ] }, { "cell_type": "code", "execution_count": 131, "metadata": { "collapsed": false }, "outputs": [], "source": [ "#Get start and end counts for each station\n", "start_count = df_bikes_share_hour.start_station_id.value_counts().reset_index(drop = False).sort_values(by = \"index\").reset_index(drop = True)\n", "start_count.columns = [\"start_station_id\", \"count_start\"]\n", "end_count = df_bikes_share_hour.end_station_id.value_counts().reset_index(drop = False).sort_values(by = \"index\").reset_index(drop = True)\n", "end_count.columns = [\"end_station_id\", \"count_end\"]\n", "merged_counts = start_count.merge(end_count, how = \"outer\", left_on=\"start_station_id\", right_on=\"end_station_id\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Now create a single station_id column and fill the NaNs of the count columns with zeros." ] }, { "cell_type": "code", "execution_count": 132, "metadata": { "collapsed": false }, "outputs": [], "source": [ "#Get the id for each trip\n", "merged_counts[\"station_id\"] = merged_counts.apply(lambda x: x['start_station_id'] if x['start_station_id']>=0 else x['end_station_id'], axis =1)\n", "#replace Nas with 0s\n", "merged_counts.count_start.fillna(0, inplace=True)\n", "merged_counts.count_end.fillna(0, inplace=True)\n", "df_count = merged_counts.loc[:, [\"station_id\", \"count_start\", \"count_end\"]]\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Merge this with the full data set to get the info for each station. Also create a column called \"net\" which is count_start - count_end" ] }, { "cell_type": "code", "execution_count": 133, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
station_idcount_startcount_endstart_station_idstart_station_namestart_station_latitudestart_station_longitudenet
072.032331572.0W 52 St & 11 Ave40.767272-73.9939298
179.033019279.0Franklin St & W Broadway40.719116-74.006667138
282.0786682.0St James Pl & Pearl St40.711174-74.00016512
383.01629583.0Atlantic Ave & Fort Greene Pl40.683826-73.97632367
4119.02328119.0Park Ave & St Edwards St40.696089-73.978034-5
\n", "
" ], "text/plain": [ " station_id count_start count_end start_station_id \\\n", "0 72.0 323 315 72.0 \n", "1 79.0 330 192 79.0 \n", "2 82.0 78 66 82.0 \n", "3 83.0 162 95 83.0 \n", "4 119.0 23 28 119.0 \n", "\n", " start_station_name start_station_latitude \\\n", "0 W 52 St & 11 Ave 40.767272 \n", "1 Franklin St & W Broadway 40.719116 \n", "2 St James Pl & Pearl St 40.711174 \n", "3 Atlantic Ave & Fort Greene Pl 40.683826 \n", "4 Park Ave & St Edwards St 40.696089 \n", "\n", " start_station_longitude net \n", "0 -73.993929 8 \n", "1 -74.006667 138 \n", "2 -74.000165 12 \n", "3 -73.976323 67 \n", "4 -73.978034 -5 " ] }, "execution_count": 133, "metadata": {}, "output_type": "execute_result" } ], "source": [ "#Get the station info\n", "df_station_info = df_bikes_share.loc[:,['start_station_id', 'start_station_name' ,'start_station_latitude','start_station_longitude']].drop_duplicates(keep = \"first\")\n", "\n", "df_count_station_info = df_count.merge(df_station_info, how = \"inner\", left_on = \"station_id\", right_on = \"start_station_id\")\n", "df_count_station_info[\"net\"] = df_count_station_info.count_start- df_count_station_info.count_end\n", "df_count_station_info.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Use folium to create plot" ] }, { "cell_type": "code", "execution_count": 134, "metadata": { "collapsed": false }, "outputs": [], "source": [ "folium_map = folium.Map(location=[40.738, -73.98],\n", " zoom_start=13,\n", " tiles=\"CartoDB dark_matter\")\n", "for index, row in df_count_station_info.iterrows():\n", " net_departures = row[\"net\"]\n", " radius = net_departures/20\n", " if net_departures>0:\n", " color=\"#E37222\" # tangerine\n", " else:\n", " color=\"#0A8A9F\" # teal\n", " \n", " folium.CircleMarker(location=(row[\"start_station_latitude\"],\n", " row[\"start_station_longitude\"]),\n", " radius=radius,\n", " color=color,\n", " fill=True).add_to(folium_map)\n", " \n", "folium_map.save(\"my_map_6_pm.html\")\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.0" }, "toc": { "colors": { "hover_highlight": "#DAA520", "navigate_num": "#000000", "navigate_text": "#333333", "running_highlight": "#FF0000", "selected_highlight": "#FFD700", "sidebar_border": "#EEEEEE", "wrapper_background": "#FFFFFF" }, "moveMenuLeft": true, "nav_menu": { "height": "12px", "width": "252px" }, "navigate_menu": true, "number_sections": true, "sideBar": true, "threshold": 4, "toc_cell": false, "toc_section_display": "block", "toc_window_display": false, "widenNotebook": false } }, "nbformat": 4, "nbformat_minor": 2 }