Standard pandas imports
In [2]:
from pandas import DataFrame, Series
import pandas as pd
import numpy as np
We'll be working with data from the bike rental setup:
In [3]:
weather = pd.read_table('daily_weather.tsv', parse_dates=['date'])
stations = pd.read_table('stations.tsv')
usage = pd.read_table('usage_2012.tsv', parse_dates=['time_start', 'time_end'])
Let's inspect it
In [4]:
usage.describe()
Out[4]:
duration_mins
count
2412524.000000
mean
17.115007
std
115.324935
min
0.000000
25%
6.000000
50%
11.000000
75%
18.000000
max
118480.000000
That must be the only numeric value, let's try again:
In [5]:
usage
Out[5]:
bike_id
time_start
time_end
duration_mins
station_start
station_end
cust_type
0
W01412
2012-01-01 00:04:00
2012-01-01 00:11:00
7
7th & R St NW / Shaw Library
7th & T St NW
Registered
1
W00524
2012-01-01 00:10:00
2012-01-01 00:29:00
19
Georgia & New Hampshire Ave NW
16th & Harvard St NW
Casual
2
W00235
2012-01-01 00:10:00
2012-01-01 00:29:00
19
Georgia & New Hampshire Ave NW
16th & Harvard St NW
Registered
3
W00864
2012-01-01 00:15:00
2012-01-01 00:23:00
8
14th & V St NW
Park Rd & Holmead Pl NW
Registered
4
W00995
2012-01-01 00:15:00
2012-01-01 00:23:00
8
11th & Kenyon St NW
7th & T St NW
Registered
5
W00466
2012-01-01 00:17:00
2012-01-01 00:23:00
6
Court House Metro / 15th & N Uhle St
Lynn & 19th St North
Registered
6
W00525
2012-01-01 00:18:00
2012-01-01 00:47:00
29
37th & O St NW / Georgetown University
9th & Upshur St NW
Registered
7
W00340
2012-01-01 00:22:00
2012-01-01 00:27:00
5
14th & V St NW
15th & P St NW
Registered
8
W00466
2012-01-01 00:24:00
2012-01-01 00:33:00
9
Lynn & 19th St North
25th St & Pennsylvania Ave NW
Registered
9
W00963
2012-01-01 00:25:00
2012-01-01 00:40:00
15
14th & V St NW
L'Enfant Plaza / 7th & C St SW
Registered
10
W01398
2012-01-01 00:29:00
2012-01-01 00:48:00
19
Tenleytown / Wisconsin Ave & Albemarle St NW
Massachusetts Ave & Dupont Circle NW
Registered
11
W00042
2012-01-01 00:30:00
2012-01-01 00:38:00
8
New York Ave & 15th St NW
21st & I St NW
Registered
12
W00570
2012-01-01 00:32:00
2012-01-01 00:50:00
18
Metro Center / 12th & G St NW
Massachusetts Ave & Dupont Circle NW
Registered
13
W01463
2012-01-01 00:32:00
2012-01-01 00:50:00
18
Lamont & Mt Pleasant NW
14th & Rhode Island Ave NW
Registered
14
W00535
2012-01-01 00:33:00
2012-01-01 00:50:00
17
Lamont & Mt Pleasant NW
14th & Rhode Island Ave NW
Registered
15
W00494
2012-01-01 00:33:00
2012-01-01 00:50:00
17
Metro Center / 12th & G St NW
Massachusetts Ave & Dupont Circle NW
Registered
16
W00466
2012-01-01 00:33:00
2012-01-01 00:41:00
8
25th St & Pennsylvania Ave NW
New York Ave & 15th St NW
Registered
17
W00663
2012-01-01 00:33:00
2012-01-01 00:39:00
6
7th & T St NW
Convention Center / 7th & M St NW
Registered
18
W01052
2012-01-01 00:34:00
2012-01-01 00:42:00
8
14th & Rhode Island Ave NW
14th & V St NW
Registered
19
W00174
2012-01-01 00:36:00
2012-01-01 00:52:00
16
17th & Corcoran St NW
17th & Corcoran St NW
Registered
20
W01298
2012-01-01 00:37:00
2012-01-01 00:41:00
4
17th & Corcoran St NW
Massachusetts Ave & Dupont Circle NW
Registered
21
W01333
2012-01-01 00:39:00
2012-01-01 00:44:00
5
NaN
8th & H St NW
Registered
22
W00697
2012-01-01 00:39:00
2012-01-01 00:42:00
3
Potomac & Pennsylvania Ave SE
Potomac & Pennsylvania Ave SE
Registered
23
W00260
2012-01-01 00:41:00
2012-01-01 00:49:00
8
4th & E St SW
5th & F St NW
Registered
24
W00658
2012-01-01 00:45:00
2012-01-01 00:48:00
3
18th & M St NW
19th St & Pennsylvania Ave NW
Registered
25
W00996
2012-01-01 00:45:00
2012-01-01 00:54:00
9
15th & P St NW
NaN
Registered
26
W00790
2012-01-01 00:46:00
2012-01-01 00:53:00
7
15th & P St NW
NaN
Registered
27
W01213
2012-01-01 00:48:00
2012-01-01 01:04:00
16
Massachusetts Ave & Dupont Circle NW
5th St & Massachusetts Ave NW
Registered
28
W00981
2012-01-01 00:49:00
2012-01-01 00:55:00
6
Park Rd & Holmead Pl NW
Columbia Rd & Belmont St NW
Registered
29
W01270
2012-01-01 00:49:00
2012-01-01 00:59:00
10
17th & Corcoran St NW
16th & Harvard St NW
Registered
...
...
...
...
...
...
...
...
2412494
W01430
2012-12-31 09:09:00
2012-12-31 09:20:00
11
15th St & Massachusetts Ave SE
North Capitol St & F St NW
Subscriber
2412495
W01486
2012-12-31 09:09:00
2012-12-31 09:28:00
19
Lincoln Park / 13th & East Capitol St NE
17th & Rhode Island Ave NW
Subscriber
2412496
W00703
2012-12-31 09:08:00
2012-12-31 09:16:00
8
Braddock Rd Metro
Market Square / King St & Royal St
Subscriber
2412497
W00186
2012-12-31 09:08:00
2012-12-31 09:19:00
11
16th & Harvard St NW
24th & N St NW
Subscriber
2412498
W00190
2012-12-31 09:08:00
2012-12-31 09:19:00
11
Columbia Rd & Georgia Ave NW
Calvert St & Woodley Pl NW
Subscriber
2412499
W20898
2012-12-31 09:08:00
2012-12-31 09:14:00
6
7th & R St NW / Shaw Library
5th & K St NW
Subscriber
2412500
W20903
2012-12-31 09:08:00
2012-12-31 09:18:00
10
17th & Rhode Island Ave NW
10th & E St NW
Subscriber
2412501
W00189
2012-12-31 09:08:00
2012-12-31 09:18:00
10
Adams Mill & Columbia Rd NW
24th & N St NW
Subscriber
2412502
W20946
2012-12-31 09:07:00
2012-12-31 09:17:00
10
7th & F St NW / National Portrait Gallery
21st & I St NW
Subscriber
2412503
W01438
2012-12-31 09:07:00
2012-12-31 09:12:00
5
11th & H St NE
Columbus Circle / Union Station
Subscriber
2412504
W00110
2012-12-31 09:07:00
2012-12-31 09:20:00
13
14th & V St NW
1st & M St NE
Subscriber
2412505
W00122
2012-12-31 09:06:00
2012-12-31 09:08:00
2
N Quincy St & Wilson Blvd
Central Library / N Quincy St & 10th St N
Subscriber
2412506
W01397
2012-12-31 09:06:00
2012-12-31 09:13:00
7
Lincoln Park / 13th & East Capitol St NE
Columbus Circle / Union Station
Subscriber
2412507
W00078
2012-12-31 09:05:00
2012-12-31 09:27:00
22
10th & Monroe St NE
Eastern Market / 7th & North Carolina Ave SE
Subscriber
2412508
W00586
2012-12-31 09:05:00
2012-12-31 09:10:00
5
20th St & Florida Ave NW
20th & L St NW
Subscriber
2412509
W01417
2012-12-31 09:05:00
2012-12-31 09:23:00
18
17th & Corcoran St NW
Smithsonian / Jefferson Dr & 12th St SW
Subscriber
2412510
W00282
2012-12-31 09:05:00
2012-12-31 09:15:00
10
5th & K St NW
13th & H St NE
Subscriber
2412511
W00574
2012-12-31 09:04:00
2012-12-31 09:15:00
11
11th & H St NE
Maryland & Independence Ave SW
Subscriber
2412512
W00986
2012-12-31 09:04:00
2012-12-31 09:18:00
14
14th & R St NW
18th St & Pennsylvania Ave NW
Subscriber
2412513
W20079
2012-12-31 09:02:00
2012-12-31 09:10:00
8
Lincoln Park / 13th & East Capitol St NE
Columbus Circle / Union Station
Subscriber
2412514
W01142
2012-12-31 09:02:00
2012-12-31 09:06:00
4
Thomas Circle
17th & K St NW / Farragut Square
Subscriber
2412515
W20888
2012-12-31 09:02:00
2012-12-31 09:06:00
4
27th & Crystal Dr
Crystal City Metro / 18th & Bell St
Subscriber
2412516
W00025
2012-12-31 09:02:00
2012-12-31 09:11:00
9
14th & Rhode Island Ave NW
20th & E St NW
Subscriber
2412517
W01059
2012-12-31 09:01:00
2012-12-31 09:07:00
6
15th & P St NW
17th & K St NW / Farragut Square
Subscriber
2412518
W20913
2012-12-31 09:01:00
2012-12-31 09:12:00
11
13th & D St NE
7th & F St NW / National Portrait Gallery
Subscriber
2412519
W00885
2012-12-31 09:01:00
2012-12-31 09:09:00
8
New Hampshire Ave & T St NW
22nd & I St NW / Foggy Bottom
Subscriber
2412520
W00384
2012-12-31 09:01:00
2012-12-31 09:18:00
17
1st & M St NE
US Dept of State / Virginia Ave & 21st St NW
Subscriber
2412521
W20102
2012-12-31 09:00:00
2012-12-31 09:12:00
12
4th & East Capitol St NE
Smithsonian / Jefferson Dr & 12th St SW
Subscriber
2412522
W00746
2012-12-31 09:00:00
2012-12-31 09:11:00
11
21st & M St NW
13th St & New York Ave NW
Subscriber
2412523
W20982
2012-12-31 09:00:00
2012-12-31 09:09:00
9
16th & Harvard St NW
17th & K St NW
Subscriber
2412524 rows × 7 columns
In [6]:
weather.describe()
Out[6]:
weekday
season_code
is_holiday
is_work_day
weather_code
temp
subjective_temp
humidity
windspeed
no_casual_riders
no_reg_riders
total_riders
count
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
366.000000
mean
2.986339
2.494536
0.030055
0.683060
1.368852
0.504081
0.481852
0.612166
0.189572
1018.483607
4581.450820
5599.934426
std
2.006108
1.112185
0.170971
0.465921
0.516057
0.176112
0.156756
0.134206
0.078194
758.989897
1424.331846
1788.667868
min
0.000000
1.000000
0.000000
0.000000
1.000000
0.107500
0.101658
0.254167
0.046650
2.000000
20.000000
22.000000
25%
1.000000
2.000000
0.000000
0.000000
1.000000
0.347708
0.350685
0.508125
0.133721
429.750000
3730.500000
4369.000000
50%
3.000000
2.500000
0.000000
1.000000
1.000000
0.514167
0.497779
0.611875
0.174750
904.500000
4776.500000
5927.000000
75%
5.000000
3.000000
0.000000
1.000000
2.000000
0.653958
0.607646
0.711146
0.231196
1262.000000
5663.000000
7011.250000
max
6.000000
4.000000
1.000000
1.000000
3.000000
0.861667
0.804913
0.925000
0.441563
3410.000000
6946.000000
8714.000000
In [7]:
weather
Out[7]:
date
weekday
season_code
season_desc
is_holiday
is_work_day
weather_code
weather_desc
temp
subjective_temp
humidity
windspeed
no_casual_riders
no_reg_riders
total_riders
0
2012-01-01
0
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.692500
0.192167
686
1608
2294
1
2012-01-02
1
1
Spring
1
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.273043
0.252304
0.381304
0.329665
244
1707
1951
2
2012-01-03
2
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.150000
0.126275
0.441250
0.365671
89
2147
2236
3
2012-01-04
3
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.107500
0.119337
0.414583
0.184700
95
2273
2368
4
2012-01-05
4
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.265833
0.278412
0.524167
0.129987
140
3132
3272
5
2012-01-06
5
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.334167
0.340267
0.542083
0.167908
307
3791
4098
6
2012-01-07
6
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.393333
0.390779
0.531667
0.174758
1070
3451
4521
7
2012-01-08
0
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.337500
0.340258
0.465000
0.191542
599
2826
3425
8
2012-01-09
1
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.224167
0.247479
0.701667
0.098900
106
2270
2376
9
2012-01-10
2
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.308696
0.318826
0.646522
0.187552
173
3425
3598
10
2012-01-11
3
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.274167
0.282821
0.847500
0.131221
92
2085
2177
11
2012-01-12
4
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.382500
0.381938
0.802917
0.180967
269
3828
4097
12
2012-01-13
5
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.274167
0.249362
0.507500
0.378108
174
3040
3214
13
2012-01-14
6
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.180000
0.183087
0.457500
0.187183
333
2160
2493
14
2012-01-15
0
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.166667
0.161625
0.419167
0.251258
284
2027
2311
15
2012-01-16
1
1
Spring
1
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.190000
0.190663
0.522500
0.231358
217
2081
2298
16
2012-01-17
2
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.373043
0.364278
0.716087
0.349130
127
2808
2935
17
2012-01-18
3
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.303333
0.275254
0.443333
0.415429
109
3267
3376
18
2012-01-19
4
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.190000
0.190038
0.497500
0.220158
130
3162
3292
19
2012-01-20
5
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.217500
0.220958
0.450000
0.202750
115
3048
3163
20
2012-01-21
6
1
Spring
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.173333
0.174875
0.831250
0.222642
67
1234
1301
21
2012-01-22
0
1
Spring
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.162500
0.162250
0.796250
0.199638
196
1781
1977
22
2012-01-23
1
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.218333
0.243058
0.911250
0.110708
145
2287
2432
23
2012-01-24
2
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.342500
0.349108
0.835833
0.123767
439
3900
4339
24
2012-01-25
3
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.294167
0.294821
0.643750
0.161071
467
3803
4270
25
2012-01-26
4
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.341667
0.356050
0.769583
0.073396
244
3831
4075
26
2012-01-27
5
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.425000
0.415383
0.741250
0.342667
269
3187
3456
27
2012-01-28
6
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.315833
0.326379
0.543333
0.210829
775
3248
4023
28
2012-01-29
0
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.282500
0.272721
0.311250
0.240050
558
2685
3243
29
2012-01-30
1
1
Spring
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.269167
0.262625
0.400833
0.215792
126
3498
3624
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
336
2012-12-02
0
4
Winter
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.347500
0.359208
0.823333
0.124379
892
3757
4649
337
2012-12-03
1
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.452500
0.455796
0.767500
0.082721
555
5679
6234
338
2012-12-04
2
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.475833
0.469054
0.733750
0.174129
551
6055
6606
339
2012-12-05
3
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.438333
0.428012
0.485000
0.324021
331
5398
5729
340
2012-12-06
4
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.255833
0.258204
0.508750
0.174754
340
5035
5375
341
2012-12-07
5
4
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.320833
0.321958
0.764167
0.130600
349
4659
5008
342
2012-12-08
6
4
Winter
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.381667
0.389508
0.911250
0.101379
1153
4429
5582
343
2012-12-09
0
4
Winter
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.384167
0.390146
0.905417
0.157975
441
2787
3228
344
2012-12-10
1
4
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.435833
0.435575
0.925000
0.190308
329
4841
5170
345
2012-12-11
2
4
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.353333
0.338363
0.596667
0.296037
282
5219
5501
346
2012-12-12
3
4
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.297500
0.297338
0.538333
0.162937
310
5009
5319
347
2012-12-13
4
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.295833
0.294188
0.485833
0.174129
425
5107
5532
348
2012-12-14
5
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.281667
0.294192
0.642917
0.131229
429
5182
5611
349
2012-12-15
6
4
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.324167
0.338383
0.650417
0.106350
767
4280
5047
350
2012-12-16
0
4
Winter
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.362500
0.369938
0.838750
0.100742
538
3248
3786
351
2012-12-17
1
4
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.393333
0.401500
0.907083
0.098258
212
4373
4585
352
2012-12-18
2
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.410833
0.409708
0.666250
0.221404
433
5124
5557
353
2012-12-19
3
4
Winter
0
1
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.332500
0.342162
0.625417
0.184092
333
4934
5267
354
2012-12-20
4
4
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.330000
0.335217
0.667917
0.132463
314
3814
4128
355
2012-12-21
5
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.326667
0.301767
0.556667
0.374383
221
3402
3623
356
2012-12-22
6
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.265833
0.236113
0.441250
0.407346
205
1544
1749
357
2012-12-23
0
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.245833
0.259471
0.515417
0.133083
408
1379
1787
358
2012-12-24
1
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.231304
0.258900
0.791304
0.077230
174
746
920
359
2012-12-25
2
1
Spring
1
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.291304
0.294465
0.734783
0.168726
440
573
1013
360
2012-12-26
3
1
Spring
0
1
3
Light Snow, Light Rain + Thunderstorm + Scatte...
0.243333
0.220333
0.823333
0.316546
9
432
441
361
2012-12-27
4
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.254167
0.226642
0.652917
0.350133
247
1867
2114
362
2012-12-28
5
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.253333
0.255046
0.590000
0.155471
644
2451
3095
363
2012-12-29
6
1
Spring
0
0
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.253333
0.242400
0.752917
0.124383
159
1182
1341
364
2012-12-30
0
1
Spring
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.255833
0.231700
0.483333
0.350754
364
1432
1796
365
2012-12-31
1
1
Spring
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.577500
0.154846
439
2290
2729
366 rows × 15 columns
Wow, those season_desc values look wrong! We'll come back to that in a minute.
Now let's look at some of the columns
In [8]:
weather.columns
Out[8]:
Index([u'date', u'weekday', u'season_code', u'season_desc', u'is_holiday', u'is_work_day', u'weather_code', u'weather_desc', u'temp', u'subjective_temp', u'humidity', u'windspeed', u'no_casual_riders', u'no_reg_riders', u'total_riders'], dtype='object')
In [9]:
weather['is_holiday'].value_counts()
Out[9]:
0 355
1 11
dtype: int64
In [10]:
weather['temp'].describe()
Out[10]:
count 366.000000
mean 0.504081
std 0.176112
min 0.107500
25% 0.347708
50% 0.514167
75% 0.653958
max 0.861667
Name: temp, dtype: float64
Sanity checking the weather data
In [11]:
weather.groupby(['season_code', 'season_desc'])['date'].agg([min, max])
Out[11]:
min
max
season_code
season_desc
1
Spring
2012-01-01
2012-12-31
2
Summer
2012-03-21
2012-06-20
3
Fall
2012-06-21
2012-09-22
4
Winter
2012-09-23
2012-12-20
The season codes look OK (1 = Winter, 2 = Spring ...), but the season descriptions look wrong. Let's fix them:
In [12]:
weather.season_desc = weather.season_desc.map({'Spring' : 'Winter', 'Winter' : 'Fall', 'Fall' : 'Summer', 'Summer' : 'Spring' })
In [13]:
weather.season_desc
Out[13]:
0 Winter
1 Winter
2 Winter
3 Winter
4 Winter
5 Winter
6 Winter
7 Winter
8 Winter
9 Winter
10 Winter
11 Winter
12 Winter
13 Winter
14 Winter
...
351 Fall
352 Fall
353 Fall
354 Fall
355 Winter
356 Winter
357 Winter
358 Winter
359 Winter
360 Winter
361 Winter
362 Winter
363 Winter
364 Winter
365 Winter
Name: season_desc, Length: 366, dtype: object
That looks right, but we would rather the date be our index:
In [14]:
weather.index = pd.DatetimeIndex(weather['date'])
How many unique bikes are there?
In [15]:
usage.columns
Out[15]:
Index([u'bike_id', u'time_start', u'time_end', u'duration_mins', u'station_start', u'station_end', u'cust_type'], dtype='object')
In [16]:
usage['bike_id'].describe()
Out[16]:
count 2412524
unique 1746
top W01414
freq 2422
Name: bike_id, dtype: object
In [17]:
usage['bike_id'].nunique()
Out[17]:
1746
How many unique stations are there?
In [18]:
stations.shape
Out[18]:
(347, 136)
In [19]:
len(stations)
Out[19]:
347
How many of those actually appear in our usage data?
In [20]:
usage['station_start'].nunique()
Out[20]:
185
In [21]:
usage['station_end'].nunique()
Out[21]:
185
In [22]:
usage['station_start'].unique()
Out[22]:
array(['7th & R St NW / Shaw Library', 'Georgia & New Hampshire Ave NW',
'14th & V St NW', '11th & Kenyon St NW',
'Court House Metro / 15th & N Uhle St',
'37th & O St NW / Georgetown University', 'Lynn & 19th St North',
'Tenleytown / Wisconsin Ave & Albemarle St NW',
'New York Ave & 15th St NW', 'Metro Center / 12th & G St NW',
'Lamont & Mt Pleasant NW', '25th St & Pennsylvania Ave NW',
'7th & T St NW', '14th & Rhode Island Ave NW',
'17th & Corcoran St NW', nan, 'Potomac & Pennsylvania Ave SE',
'4th & E St SW', '18th & M St NW', '15th & P St NW',
'Massachusetts Ave & Dupont Circle NW', 'Park Rd & Holmead Pl NW',
'19th St & Pennsylvania Ave NW', 'Adams Mill & Columbia Rd NW',
'Eastern Market Metro / Pennsylvania Ave & 7th St SE',
"L'Enfant Plaza / 7th & C St SW", '20th & Crystal Dr',
'10th & U St NW', '5th & K St NW', 'California St & Florida Ave NW',
'Connecticut Ave & Newark St NW / Cleveland Park', '1st & M St NE',
'16th & Harvard St NW', 'Columbia Rd & Belmont St NW',
'20th St & Florida Ave NW', '17th & K St NW / Farragut Square',
'3000 Connecticut Ave NW / National Zoo',
'Crystal City Metro / 18th & Bell St',
'36th & Calvert St NW / Glover Park',
'Convention Center / 7th & M St NW', '14th & Harvard St NW',
'14th & R St NW', '5th St & Massachusetts Ave NW', '13th & H St NE',
'Columbus Circle / Union Station', 'Van Ness Metro / UDC',
'13th & D St NE', 'M St & New Jersey Ave SE', 'Thomas Circle',
'21st & I St NW', '4th & East Capitol St NE',
'17th & Rhode Island Ave NW', 'Calvert St & Woodley Pl NW',
'Clarendon Blvd & N Fillmore St', '8th & H St NW',
'14th St & Spring Rd NW', 'North Capitol St & F St NW',
'6th & H St NE', 'Eastern Market / 7th & North Carolina Ave SE',
'14th & G St NW', '34th St & Wisconsin Ave NW',
'1st & Rhode Island Ave NW', '3rd & G St SE',
'Minnesota Ave Metro/DOES', '14th & D St SE', '19th & L St NW',
'Lincoln Park / 13th & East Capitol St NE', '17th & K St NW',
'3rd & H St NE', '4th & M St SW', '3rd & D St SE', '20th & E St NW',
'Eckington Pl & Q St NE',
'Clarendon Metro / Wilson Blvd & N Highland St',
'USDA / 12th & Independence Ave SW', '21st & M St NW',
'12th & Army Navy Dr', '15th St & Massachusetts Ave SE',
'Georgetown Harbor / 30th St NW', '9th & Upshur St NW',
'Florida Ave & R St NW', 'C & O Canal & Wisconsin Ave NW',
'Harvard St & Adams Mill Rd NW', '19th St & Constitution Ave NW',
'14th & D St NW / Ronald Reagan Building', 'S Glebe & Potomac Ave',
'Bladensburg Rd & Benning Rd NE',
'Rosslyn Metro / Wilson Blvd & Ft Myer Dr',
'13th St & New York Ave NW', '15th & Crystal Dr',
'23rd & Crystal Dr', '5th & F St NW', '19th & East Capitol St SE',
'M St & Pennsylvania Ave NW', 'Columbia Rd & Georgia Ave NW',
'7th & F St NW / National Portrait Gallery',
'14th St Heights / 14th & Crittenden St NW', '10th & Monroe St NE',
'19th & E Street NW', '1st & N St SE',
'6th & Water St SW / SW Waterfront',
'US Dept of State / Virginia Ave & 21st St NW', '27th & Crystal Dr',
'Pentagon City Metro / 12th & S Hayes St',
'10th St & Constitution Ave NW', 'Good Hope & Naylor Rd SE',
'11th & H St NE', 'Kennedy Center', '23rd & Eads St',
'12th & Newton St NE', 'Wilson Blvd & Franklin Rd',
'4th & D St NW / Judiciary Square', 'S Joyce & Army Navy Dr',
'Georgia Ave and Fairmont St NW', '20th & Bell St',
'N Rhodes & 16th St N', '8th & Eye St SE / Barracks Row',
'Ward Circle / American University',
'Aurora Hills Community Ctr/18th & Hayes St',
'18th St & Pennsylvania Ave NW', 'Pennsylvania & Minnesota Ave SE',
'Wilson Blvd & N Edgewood St',
'John McCormack Dr & Michigan Ave NE',
'Branch & Pennsylvania Ave SE', 'Anacostia Metro',
'Clarendon Blvd & Pierce St', 'Good Hope Rd & MLK Ave SE',
'Anacostia Library',
'Benning Rd & East Capitol St NE / Benning Rd Metro',
'Benning Branch Library',
'Anacostia Ave & Benning Rd NE / River Terrace',
'Randle Circle & Minnesota Ave SE', 'Congress Heights Metro',
'Fairfax Village', 'Maryland & Independence Ave SW',
'1st & K St SE', '39th & Calvert St NW / Stoddert',
'20th & O St NW / Dupont South',
'1st & Washington Hospital Center NW',
'Central Library / N Quincy St & 10th St N',
'Virginia Square Metro / N Monroe St & 9th St N',
'D St & Maryland Ave NE', 'Washington Blvd & 10th St N',
'Ohio Dr & West Basin Dr SW / MLK & FDR Memorials',
'Jefferson Dr & 14th St SW', 'New Hampshire Ave & T St NW',
'Idaho Ave & Newark St NW [on 2nd District patio]',
'Smithsonian / Jefferson Dr & 12th St SW', 'Key Blvd & N Quinn St',
'N Veitch & Key Blvd', 'N Veitch & 20th St N',
'N Adams St & Lee Hwy', '15th & N Scott St',
'Wilson Blvd & N Oakland St', 'Fairfax Dr & Wilson Blvd',
'N Randolph St & Fairfax Dr', 'N Quincy St & Glebe Rd',
'Ballston Metro / N Stuart & 9th St N',
'George Mason Dr & Wilson Blvd', 'Washington Blvd & 7th St N',
'Jefferson Memorial', 'Good Hope Rd & 14th St SE',
'Glebe Rd & 11th St N', 'N Quincy St & Wilson Blvd',
'20th & L St NW', '12th & L St NW', '21st St & Pennsylvania Ave NW',
'19th & K St NW', '15th & K St NW', '24th & N St NW',
'Gallaudet / 8th St & Florida Ave NE', '10th & E St NW',
'22nd & I St NW / Foggy Bottom', 'Utah St & 11th St N',
'Market Square / King St & Royal St', 'Prince St & Union St',
'Braddock Rd Metro', 'King St Metro', 'Commerce St & Fayette St',
'King St & Patrick St', 'Saint Asaph St & Pendleton St',
'Henry St & Pendleton St', 'Barton St & 10th St N',
'Arlington Blvd & N Queen St', 'Lincoln Memorial',
'Potomac Ave & 35th St S'], dtype=object)
Let's look for correlations in the weather:
In [23]:
weather[['temp', 'subjective_temp', 'humidity', 'windspeed', 'total_riders']].corr()
Out[23]:
temp
subjective_temp
humidity
windspeed
total_riders
temp
1.000000
0.986165
0.118262
-0.203691
0.713793
subjective_temp
0.986165
1.000000
0.134662
-0.233086
0.720314
humidity
0.118262
0.134662
1.000000
-0.291031
-0.088861
windspeed
-0.203691
-0.233086
-0.291031
1.000000
-0.279008
total_riders
0.713793
0.720314
-0.088861
-0.279008
1.000000
When temperature is higher, there are more riders, and when windspeed is higher there are fewer riders. Maybe these trends are different at different times of the year?
In [24]:
weather[weather.season_desc=='Winter'][['temp', 'subjective_temp', 'humidity', 'windspeed', 'total_riders']].corr()
Out[24]:
temp
subjective_temp
humidity
windspeed
total_riders
temp
1.000000
0.991726
0.160954
-0.090210
0.811410
subjective_temp
0.991726
1.000000
0.207901
-0.186061
0.812550
humidity
0.160954
0.207901
1.000000
-0.425621
-0.083438
windspeed
-0.090210
-0.186061
-0.425621
1.000000
-0.177168
total_riders
0.811410
0.812550
-0.083438
-0.177168
1.000000
In [25]:
weather[weather.season_desc=='Summer'][['temp', 'subjective_temp', 'humidity', 'windspeed', 'total_riders']].corr()
Out[25]:
temp
subjective_temp
humidity
windspeed
total_riders
temp
1.000000
0.789556
-0.284143
-0.055013
-0.325743
subjective_temp
0.789556
1.000000
-0.179344
-0.107119
-0.282228
humidity
-0.284143
-0.179344
1.000000
-0.017163
-0.307289
windspeed
-0.055013
-0.107119
-0.017163
1.000000
-0.165871
total_riders
-0.325743
-0.282228
-0.307289
-0.165871
1.000000
So in winter higher temperatures mean more riders, but in summer higher temperatures mean fewer riders. This makes a good bit of sense.
We'll measure a station's success by its average number of daily rentals. We can take a couple of different approaches.
The first is to use groupby. We'll start by adding a date column:
In [26]:
usage['date'] = usage.time_start.dt.date
In [27]:
usage.groupby('date')
Out[27]:
<pandas.core.groupby.DataFrameGroupBy object at 0x7f1948678810>
In [28]:
station_counts = usage.groupby(['station_start']).size() / 366
station_counts.sort()
station_counts
Out[28]:
station_start
Potomac Ave & 35th St S 0.360656
Randle Circle & Minnesota Ave SE 0.360656
Branch & Pennsylvania Ave SE 0.491803
Fairfax Village 0.535519
Henry St & Pendleton St 0.658470
Good Hope Rd & 14th St SE 0.666667
Minnesota Ave Metro/DOES 0.713115
Good Hope & Naylor Rd SE 0.737705
Benning Branch Library 0.756831
Congress Heights Metro 0.773224
Commerce St & Fayette St 1.054645
Anacostia Library 1.092896
Pennsylvania & Minnesota Ave SE 1.306011
Arlington Blvd & N Queen St 1.387978
Benning Rd & East Capitol St NE / Benning Rd Metro 1.562842
...
8th & H St NW 85.404372
7th & F St NW / National Portrait Gallery 86.256831
14th & Rhode Island Ave NW 86.781421
North Capitol St & F St NW 86.937158
20th St & Florida Ave NW 87.297814
21st & I St NW 90.751366
16th & Harvard St NW 95.270492
Eastern Market Metro / Pennsylvania Ave & 7th St SE 108.587432
Thomas Circle 109.565574
Adams Mill & Columbia Rd NW 109.786885
14th & V St NW 109.950820
17th & Corcoran St NW 118.980874
15th & P St NW 135.016393
Columbus Circle / Union Station 150.672131
Massachusetts Ave & Dupont Circle NW 190.846995
Length: 185, dtype: float64
We can also use a pivot table:
In [29]:
pivot = pd.pivot_table(usage, index='date', columns='station_start', values='bike_id', aggfunc=len, fill_value=0)
pivot
Out[29]:
station_start
10th & E St NW
10th & Monroe St NE
10th & U St NW
10th St & Constitution Ave NW
11th & H St NE
11th & Kenyon St NW
12th & Army Navy Dr
12th & L St NW
12th & Newton St NE
13th & D St NE
...
USDA / 12th & Independence Ave SW
Utah St & 11th St N
Van Ness Metro / UDC
Virginia Square Metro / N Monroe St & 9th St N
Ward Circle / American University
Washington Blvd & 10th St N
Washington Blvd & 7th St N
Wilson Blvd & Franklin Rd
Wilson Blvd & N Edgewood St
Wilson Blvd & N Oakland St
date
2012-01-01
0
10
58
54
20
58
12
0
2
46
...
74
0
14
0
4
0
0
6
0
0
2012-01-02
0
4
62
20
22
52
14
0
2
38
...
30
0
16
0
16
0
0
8
8
0
2012-01-03
0
12
60
26
30
50
12
0
2
48
...
56
0
18
0
10
0
0
0
4
0
2012-01-04
0
10
58
16
28
58
4
0
0
60
...
54
0
10
0
4
0
0
0
2
0
2012-01-05
0
16
74
40
30
62
10
0
4
62
...
72
0
30
0
8
0
0
4
4
0
2012-01-06
0
18
132
58
32
122
10
0
2
76
...
86
0
28
0
22
0
0
8
10
0
2012-01-07
0
34
140
88
64
122
20
0
14
96
...
74
0
32
0
14
0
0
12
16
0
2012-01-08
0
14
132
54
36
106
8
0
14
72
...
54
0
34
0
6
0
0
0
10
0
2012-01-09
0
8
66
24
28
72
4
0
6
64
...
40
0
10
0
4
0
0
2
0
0
2012-01-10
0
10
110
40
38
112
8
0
4
56
...
94
0
20
0
8
0
0
4
8
0
2012-01-11
0
12
62
22
24
60
6
0
2
72
...
28
0
10
0
8
0
0
4
2
0
2012-01-12
0
14
114
56
36
88
6
0
10
52
...
96
0
46
0
10
0
0
8
4
0
2012-01-13
0
14
70
38
36
96
12
0
2
50
...
66
0
16
0
6
0
0
4
4
0
2012-01-14
0
14
60
20
34
72
12
0
0
42
...
26
0
26
0
2
0
0
2
8
0
2012-01-15
0
14
92
48
40
90
2
0
4
42
...
32
0
36
0
4
0
0
0
0
0
2012-01-16
0
12
66
36
22
52
14
0
0
46
...
36
0
18
0
12
0
0
2
2
0
2012-01-17
0
6
66
30
14
50
16
0
4
40
...
74
0
36
0
32
0
0
2
0
0
2012-01-18
0
14
88
36
32
82
10
0
4
80
...
50
0
24
0
26
0
0
4
4
0
2012-01-19
0
14
64
44
36
76
8
0
4
68
...
66
0
34
0
26
0
0
2
0
0
2012-01-20
0
12
78
30
38
82
14
0
2
78
...
70
0
28
0
18
0
0
4
2
0
2012-01-21
0
2
52
10
20
40
0
0
0
26
...
10
0
16
0
2
0
0
2
0
0
2012-01-22
0
6
64
20
20
58
6
0
0
30
...
6
0
10
0
10
0
0
0
0
0
2012-01-23
0
12
36
22
16
58
10
0
0
46
...
44
0
16
0
18
0
0
0
0
0
2012-01-24
0
14
82
60
34
92
8
0
6
90
...
100
0
36
0
20
0
0
2
4
0
2012-01-25
0
14
106
58
36
56
14
0
2
78
...
144
0
44
0
36
0
0
4
4
0
2012-01-26
0
24
86
42
36
76
16
0
4
74
...
88
0
46
0
28
0
0
2
2
0
2012-01-27
0
6
92
56
24
78
16
0
0
46
...
62
0
26
0
12
0
0
10
8
0
2012-01-28
0
24
94
72
70
116
16
0
2
64
...
134
0
50
0
10
0
0
6
4
0
2012-01-29
0
8
104
52
58
90
22
0
2
44
...
34
0
30
0
14
0
0
6
8
0
2012-01-30
0
22
74
32
38
66
14
0
6
88
...
72
0
38
0
40
0
0
4
6
0
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
2012-12-02
17
3
57
35
25
55
2
31
1
28
...
6
1
14
7
4
4
3
5
2
8
2012-12-03
40
9
47
24
28
50
6
56
2
47
...
39
5
19
17
27
4
4
11
12
5
2012-12-04
52
10
49
31
29
42
6
70
3
48
...
43
7
28
20
28
4
5
14
14
8
2012-12-05
39
7
51
28
29
38
8
56
1
50
...
34
2
18
17
24
1
2
7
4
1
2012-12-06
28
5
37
32
28
35
5
62
1
37
...
40
5
18
10
14
1
3
10
4
4
2012-12-07
21
7
43
27
22
39
1
36
1
37
...
24
3
11
10
12
2
1
9
5
9
2012-12-08
45
6
74
49
35
66
9
49
1
39
...
16
9
18
12
11
2
2
9
8
8
2012-12-09
18
3
52
12
26
33
2
38
0
23
...
2
7
13
4
2
1
0
6
9
3
2012-12-10
27
10
53
36
24
38
3
47
3
42
...
38
4
17
10
21
4
4
6
5
3
2012-12-11
32
7
40
31
30
46
5
60
3
46
...
31
1
26
19
14
2
4
9
6
4
2012-12-12
18
11
51
40
24
41
6
56
1
46
...
33
3
20
21
9
1
2
6
4
7
2012-12-13
25
9
62
36
20
44
4
58
1
49
...
40
5
11
16
12
1
3
6
9
4
2012-12-14
41
7
50
31
27
59
3
51
1
48
...
34
7
11
11
11
2
3
6
6
8
2012-12-15
31
3
52
21
31
61
12
31
1
37
...
19
3
22
15
8
5
1
11
11
9
2012-12-16
16
6
55
19
24
59
7
28
3
30
...
8
2
14
7
7
1
2
10
6
8
2012-12-17
21
7
41
27
22
41
4
46
3
39
...
31
3
17
9
10
6
4
3
3
2
2012-12-18
31
8
54
39
30
42
6
55
3
45
...
36
2
14
14
6
1
3
12
7
3
2012-12-19
28
7
50
23
29
46
5
57
1
41
...
22
2
16
13
8
5
5
7
3
2
2012-12-20
19
9
34
29
20
35
5
33
0
37
...
29
3
13
11
1
1
3
9
1
2
2012-12-21
16
7
48
21
33
30
4
29
0
36
...
11
3
5
9
3
1
2
5
3
3
2012-12-22
14
2
26
6
13
16
4
14
1
7
...
2
0
2
6
0
1
0
3
2
2
2012-12-23
11
0
22
5
8
14
4
12
1
11
...
1
0
7
3
11
2
0
0
5
1
2012-12-24
6
2
7
8
3
5
2
11
0
8
...
2
0
0
1
0
1
0
0
0
2
2012-12-25
6
3
3
7
3
6
4
9
0
12
...
0
3
2
0
1
0
0
1
0
1
2012-12-26
1
0
2
1
1
1
0
1
0
4
...
1
0
1
2
1
0
0
2
0
1
2012-12-27
21
1
19
13
12
14
1
15
0
10
...
5
1
2
4
0
1
1
4
1
2
2012-12-28
19
1
33
18
27
14
4
19
0
15
...
20
1
5
7
1
3
1
1
4
3
2012-12-29
10
0
25
6
15
12
0
8
0
8
...
6
1
7
3
1
0
1
2
1
0
2012-12-30
6
3
26
9
18
20
1
13
1
14
...
4
0
2
2
1
2
0
2
2
3
2012-12-31
19
4
26
14
17
25
5
24
0
31
...
4
1
9
1
2
2
0
5
1
2
366 rows × 185 columns
In [30]:
avg_daily_trips = pivot.mean()
avg_daily_trips.sort()
avg_daily_trips.index.name = 'station'
avg_daily_trips
Out[30]:
station
Potomac Ave & 35th St S 0.360656
Randle Circle & Minnesota Ave SE 0.360656
Branch & Pennsylvania Ave SE 0.491803
Fairfax Village 0.535519
Henry St & Pendleton St 0.658470
Good Hope Rd & 14th St SE 0.666667
Minnesota Ave Metro/DOES 0.713115
Good Hope & Naylor Rd SE 0.737705
Benning Branch Library 0.756831
Congress Heights Metro 0.773224
Commerce St & Fayette St 1.054645
Anacostia Library 1.092896
Pennsylvania & Minnesota Ave SE 1.306011
Arlington Blvd & N Queen St 1.387978
Benning Rd & East Capitol St NE / Benning Rd Metro 1.562842
...
8th & H St NW 85.404372
7th & F St NW / National Portrait Gallery 86.256831
14th & Rhode Island Ave NW 86.781421
North Capitol St & F St NW 86.937158
20th St & Florida Ave NW 87.297814
21st & I St NW 90.751366
16th & Harvard St NW 95.270492
Eastern Market Metro / Pennsylvania Ave & 7th St SE 108.587432
Thomas Circle 109.565574
Adams Mill & Columbia Rd NW 109.786885
14th & V St NW 109.950820
17th & Corcoran St NW 118.980874
15th & P St NW 135.016393
Columbus Circle / Union Station 150.672131
Massachusetts Ave & Dupont Circle NW 190.846995
Length: 185, dtype: float64
There are invariably other ways to do this
We'll want to look at the avg daily trips by geographic location, which is in the stations data frame. Let's pull it out into its own:
In [31]:
station_geos = stations[['lat','long']]
station_geos.index = stations['station']
station_geos
Out[31]:
lat
long
station
20th & Bell St
38.856100
-77.051200
18th & Eads St.
38.857250
-77.053320
20th & Crystal Dr
38.856400
-77.049200
15th & Crystal Dr
38.860170
-77.049593
Aurora Hills Community Ctr/18th & Hayes St
38.857866
-77.059490
Pentagon City Metro / 12th & S Hayes St
38.862303
-77.059936
S Joyce & Army Navy Dr
38.863700
-77.063300
Crystal City Metro / 18th & Bell St
38.857300
-77.051100
12th & Army Navy Dr
38.862900
-77.052800
27th & Crystal Dr
38.848441
-77.051516
S Glebe & Potomac Ave
38.842600
-77.050200
23rd & Crystal Dr
38.853300
-77.049800
26th & S Clark St
38.850688
-77.051520
19th St & Pennsylvania Ave NW
38.900300
-77.042900
14th & V St NW
38.917600
-77.032100
11th & Kenyon St NW
38.929464
-77.027822
16th & Harvard St NW
38.926088
-77.036536
Adams Mill & Columbia Rd NW
38.922925
-77.042581
14th & Harvard St NW
38.926800
-77.032200
Calvert & Biltmore St NW
38.923203
-77.047637
Lamont & Mt Pleasant NW
38.931900
-77.038800
4th & M St SW
38.876700
-77.017800
15th & P St NW
38.909850
-77.034438
14th & R St NW
38.912682
-77.031681
14th & Rhode Island Ave NW
38.908600
-77.032300
20th & E St NW
38.896300
-77.045000
21st & I St NW
38.900800
-77.047000
Georgia & New Hampshire Ave NW
38.936043
-77.024649
14th St & Spring Rd NW
38.937500
-77.032800
John McCormack Dr & Michigan Ave NE
38.934600
-76.995500
...
...
...
Rockville Metro West
39.084379
-77.146866
Bethesda Metro
38.984691
-77.094537
Iwo Jima Memorial/N Meade & 14th St N
38.889920
-77.071301
34th & Water St NW
38.903582
-77.067786
Duke St & John Carlyle St
38.804378
-77.060866
Lee Hwy & N Cleveland St
38.894941
-77.091690
Arlington Blvd & S George Mason Dr/NFATC
38.869442
-77.104503
MLK Library/9th & G St NW
38.898404
-77.024281
Lee Hwy & N Scott St
38.897612
-77.080851
New Hampshire Ave & 24th St NW
38.901755
-77.051084
Eisenhower Ave & Mill Race Ln
38.801111
-77.068952
Potomac Greens Dr & Slaters Ln
38.821750
-77.047494
Ballenger Ave & Dulaney St
38.802677
-77.063562
Mount Vernon Ave & E Nelson Ave
38.820064
-77.057619
Mount Vernon Ave & E Del Ray Ave
38.825950
-77.058541
Monroe Ave & Leslie Ave
38.820932
-77.053096
Mount Vernon Ave & Kennedy St
38.833077
-77.059821
Court House Metro / 15th & N Uhle St
38.890612
-77.084801
Washington Adventist U / Flower Ave & Division St
38.986743
-77.000035
6th & S Ball St
38.864702
-77.048672
McKinley St & Connecticut Ave NW
38.964970
-77.075946
15th & L St NW
38.903810
-77.034931
17th & G St NW
38.898410
-77.039624
Spring St & Second Ave
38.997653
-77.034499
18th & R St NW
38.912648
-77.041834
S Joyce & 16th St S
38.859254
-77.063275
Union Market/6th St & Neal Pl NE
38.908008
-76.996985
N Nelson St & Lee Hwy
38.895929
-77.105246
21st St N & N Pierce St
38.898984
-77.078317
Lee Hwy & N Kirkwood Rd
38.895377
-77.097130
347 rows × 2 columns
And then we need to make trips into a data frame
In [32]:
trips = DataFrame({ 'avg_daily_trips' : avg_daily_trips})
trips
Out[32]:
avg_daily_trips
station
Potomac Ave & 35th St S
0.360656
Randle Circle & Minnesota Ave SE
0.360656
Branch & Pennsylvania Ave SE
0.491803
Fairfax Village
0.535519
Henry St & Pendleton St
0.658470
Good Hope Rd & 14th St SE
0.666667
Minnesota Ave Metro/DOES
0.713115
Good Hope & Naylor Rd SE
0.737705
Benning Branch Library
0.756831
Congress Heights Metro
0.773224
Commerce St & Fayette St
1.054645
Anacostia Library
1.092896
Pennsylvania & Minnesota Ave SE
1.306011
Arlington Blvd & N Queen St
1.387978
Benning Rd & East Capitol St NE / Benning Rd Metro
1.562842
Barton St & 10th St N
1.579235
King St & Patrick St
1.762295
Prince St & Union St
1.792350
Utah St & 11th St N
2.032787
Saint Asaph St & Pendleton St
2.049180
Anacostia Ave & Benning Rd NE / River Terrace
2.079235
Good Hope Rd & MLK Ave SE
2.128415
Washington Blvd & 7th St N
2.183060
Market Square / King St & Royal St
2.273224
Anacostia Metro
2.535519
Braddock Rd Metro
2.887978
Washington Blvd & 10th St N
3.065574
Glebe Rd & 11th St N
3.300546
12th & Newton St NE
3.554645
King St Metro
3.956284
...
...
1st & M St NE
67.756831
14th & R St NW
68.147541
25th St & Pennsylvania Ave NW
68.833333
Convention Center / 7th & M St NW
69.120219
New Hampshire Ave & T St NW
70.775956
14th & Harvard St NW
71.054645
10th & U St NW
71.243169
21st & M St NW
76.396175
Jefferson Dr & 14th St SW
76.595628
Park Rd & Holmead Pl NW
77.516393
Calvert St & Woodley Pl NW
78.032787
17th & Rhode Island Ave NW
78.297814
5th & K St NW
78.494536
Lincoln Park / 13th & East Capitol St NE
80.587432
Metro Center / 12th & G St NW
84.224044
8th & H St NW
85.404372
7th & F St NW / National Portrait Gallery
86.256831
14th & Rhode Island Ave NW
86.781421
North Capitol St & F St NW
86.937158
20th St & Florida Ave NW
87.297814
21st & I St NW
90.751366
16th & Harvard St NW
95.270492
Eastern Market Metro / Pennsylvania Ave & 7th St SE
108.587432
Thomas Circle
109.565574
Adams Mill & Columbia Rd NW
109.786885
14th & V St NW
109.950820
17th & Corcoran St NW
118.980874
15th & P St NW
135.016393
Columbus Circle / Union Station
150.672131
Massachusetts Ave & Dupont Circle NW
190.846995
185 rows × 1 columns
In [33]:
trips_by_geo = station_geos.join(trips, how='inner')
trips_by_geo
Out[33]:
lat
long
avg_daily_trips
station
Potomac Ave & 35th St S
38.844015
-77.050537
0.360656
Randle Circle & Minnesota Ave SE
38.878000
-76.960700
0.360656
Branch & Pennsylvania Ave SE
38.869200
-76.959900
0.491803
Fairfax Village
38.865590
-76.952103
0.535519
Henry St & Pendleton St
38.811456
-77.050276
0.658470
Good Hope Rd & 14th St SE
38.866611
-76.985238
0.666667
Minnesota Ave Metro/DOES
38.897063
-76.947446
0.713115
Good Hope & Naylor Rd SE
38.860100
-76.967200
0.737705
Benning Branch Library
38.894000
-76.947974
0.756831
Congress Heights Metro
38.844711
-76.987823
0.773224
Commerce St & Fayette St
38.805648
-77.052930
1.054645
Anacostia Library
38.865784
-76.978400
1.092896
Pennsylvania & Minnesota Ave SE
38.873057
-76.971015
1.306011
Arlington Blvd & N Queen St
38.889365
-77.077294
1.387978
Benning Rd & East Capitol St NE / Benning Rd Metro
38.889935
-76.937230
1.562842
Barton St & 10th St N
38.884961
-77.087770
1.579235
King St & Patrick St
38.805317
-77.049883
1.762295
Prince St & Union St
38.803124
-77.040363
1.792350
Utah St & 11th St N
38.883669
-77.113905
2.032787
Saint Asaph St & Pendleton St
38.810743
-77.044664
2.049180
Anacostia Ave & Benning Rd NE / River Terrace
38.896544
-76.960120
2.079235
Good Hope Rd & MLK Ave SE
38.867373
-76.988039
2.128415
Washington Blvd & 7th St N
38.880834
-77.091129
2.183060
Market Square / King St & Royal St
38.804718
-77.043363
2.273224
Anacostia Metro
38.862669
-76.994637
2.535519
Braddock Rd Metro
38.814577
-77.052808
2.887978
Washington Blvd & 10th St N
38.884734
-77.093485
3.065574
Glebe Rd & 11th St N
38.883921
-77.116817
3.300546
12th & Newton St NE
38.933668
-76.991016
3.554645
King St Metro
38.805767
-77.060720
3.956284
...
...
...
...
1st & M St NE
38.905700
-77.005600
67.756831
14th & R St NW
38.912682
-77.031681
68.147541
25th St & Pennsylvania Ave NW
38.903827
-77.053485
68.833333
Convention Center / 7th & M St NW
38.905720
-77.022264
69.120219
New Hampshire Ave & T St NW
38.915540
-77.038180
70.775956
14th & Harvard St NW
38.926800
-77.032200
71.054645
10th & U St NW
38.917200
-77.025900
71.243169
21st & M St NW
38.905340
-77.046774
76.396175
Jefferson Dr & 14th St SW
38.888553
-77.032429
76.595628
Park Rd & Holmead Pl NW
38.930800
-77.031500
77.516393
Calvert St & Woodley Pl NW
38.923583
-77.050046
78.032787
17th & Rhode Island Ave NW
38.906602
-77.038785
78.297814
5th & K St NW
38.903040
-77.019027
78.494536
Lincoln Park / 13th & East Capitol St NE
38.890461
-76.988355
80.587432
Metro Center / 12th & G St NW
38.898364
-77.027869
84.224044
8th & H St NW
38.899700
-77.023086
85.404372
7th & F St NW / National Portrait Gallery
38.897324
-77.022322
86.256831
14th & Rhode Island Ave NW
38.908600
-77.032300
86.781421
North Capitol St & F St NW
38.897446
-77.009888
86.937158
20th St & Florida Ave NW
38.915400
-77.044600
87.297814
21st & I St NW
38.900800
-77.047000
90.751366
16th & Harvard St NW
38.926088
-77.036536
95.270492
Eastern Market Metro / Pennsylvania Ave & 7th St SE
38.884000
-76.995397
108.587432
Thomas Circle
38.905900
-77.032500
109.565574
Adams Mill & Columbia Rd NW
38.922925
-77.042581
109.786885
14th & V St NW
38.917600
-77.032100
109.950820
17th & Corcoran St NW
38.912100
-77.038700
118.980874
15th & P St NW
38.909850
-77.034438
135.016393
Columbus Circle / Union Station
38.896960
-77.004930
150.672131
Massachusetts Ave & Dupont Circle NW
38.910100
-77.044400
190.846995
185 rows × 3 columns
Before we merge, we'd like to aggregate the usage data to the daily level:
In [51]:
daily_usage = usage.groupby(['date', 'station_start', 'cust_type'], as_index=False)['duration_mins'].agg(['mean', len])
daily_usage.columns = ['avg_trip_duration', 'num_trips']
daily_usage
Out[51]:
avg_trip_duration
num_trips
date
station_start
cust_type
2012-01-01
10th & Monroe St NE
Registered
16.400000
10
10th & U St NW
Casual
16.250000
8
Registered
10.000000
50
10th St & Constitution Ave NW
Casual
20.294118
34
Registered
14.200000
20
11th & H St NE
Casual
10.000000
4
Registered
11.000000
16
11th & Kenyon St NW
Casual
11.000000
4
Registered
10.000000
54
12th & Army Navy Dr
Casual
67.750000
8
Registered
5.500000
4
12th & Newton St NE
Registered
108.000000
2
13th & D St NE
Casual
11.250000
8
Registered
11.736842
38
13th & H St NE
Casual
34.400000
10
Registered
15.000000
40
13th St & New York Ave NW
Casual
8.500000
4
Registered
12.000000
6
14th & D St NW / Ronald Reagan Building
Casual
60.666667
18
Registered
14.500000
8
14th & D St SE
Casual
59.000000
4
Registered
5.785714
28
14th & G St NW
Casual
189.428571
14
Registered
8.000000
2
14th & Harvard St NW
Casual
29.000000
4
Registered
119.342105
76
14th & R St NW
Casual
11.500000
4
Registered
6.370370
54
14th & Rhode Island Ave NW
Casual
13.714286
14
Registered
8.682927
82
...
...
...
...
...
2012-12-31
Park Rd & Holmead Pl NW
Subscriber
9.404255
47
Pentagon City Metro / 12th & S Hayes St
Casual
105.500000
2
Subscriber
2.833333
6
Potomac & Pennsylvania Ave SE
Subscriber
9.777778
18
Potomac Ave & 35th St S
Casual
8.000000
1
Subscriber
6.500000
2
Prince St & Union St
Subscriber
11.000000
1
Rosslyn Metro / Wilson Blvd & Ft Myer Dr
Subscriber
21.000000
1
S Glebe & Potomac Ave
Subscriber
15.000000
2
S Joyce & Army Navy Dr
Subscriber
11.400000
5
Saint Asaph St & Pendleton St
Casual
15.000000
1
Subscriber
10.750000
4
Smithsonian / Jefferson Dr & 12th St SW
Casual
66.250000
24
Subscriber
13.800000
5
Tenleytown / Wisconsin Ave & Albemarle St NW
Subscriber
23.600000
5
Thomas Circle
Casual
25.000000
3
Subscriber
8.291667
24
US Dept of State / Virginia Ave & 21st St NW
Casual
15.000000
1
Subscriber
18.142857
14
USDA / 12th & Independence Ave SW
Casual
332.000000
2
Subscriber
16.000000
2
Utah St & 11th St N
Subscriber
4.000000
1
Van Ness Metro / UDC
Casual
21.000000
1
Subscriber
23.250000
8
Virginia Square Metro / N Monroe St & 9th St N
Subscriber
12.000000
1
Ward Circle / American University
Subscriber
7.500000
2
Washington Blvd & 10th St N
Subscriber
7.500000
2
Wilson Blvd & Franklin Rd
Subscriber
5.400000
5
Wilson Blvd & N Edgewood St
Subscriber
1.000000
1
Wilson Blvd & N Oakland St
Subscriber
9.000000
2
98634 rows × 2 columns
In [54]:
daily_usage = daily_usage.reset_index()
In [55]:
daily_usage.columns
Out[55]:
Index([u'date', u'station_start', u'cust_type', u'avg_trip_duration', u'num_trips'], dtype='object')
In [56]:
daily_usage.index
Out[56]:
Int64Index([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, ...], dtype='int64')
In [57]:
stations.columns
Out[57]:
Index([u'id', u'station', u'terminal_name', u'lat', u'long', u'no_bikes', u'no_empty_docks', u'fast_food', u'parking', u'restaurant', u'convenience', u'post_office', u'bicycle_parking', u'drinking_water', u'recycling', u'waste_basket', u'waste_disposal', u'cafe', u'currency_exchange', u'fountain', u'ice_cream', u'optician', u'pharmacy', u'tanning_salon', u'car_sharing', u'alcohol', u'bank', u'bar', u'club', u'embassy', u'food_court', u'government', u'internal_kindergarten', u'kindergarten', u'place_of_worship', u'post_box', u'pub', u'vending_machine', u'fuel', u'grave_yard', u'public_building', u'school', u'fire_station', u'nightclub', u'atm', u'hospital', u'doctors', u'theatre', u'university', u'clock', u'parking_entrance', u'police', u'cultural_center', u'stripclub', u'marketplace', u'dry_cleaner', u'bicycle_repair_station', u'office', u'arts_centre', u'library', u'studio', u'strip_club', u'tourist', u'veterinary', u'community_centre', u'compressed_air', u'tutor', u'clinic', u'dentist', u'bench', u'cinema', u'college', u'parking_exit', u'bar.restaurant', u'car_rental', u'coworking', u'shelter', u'bureau_de_change', u'food_cart', u'school..historic.', u'border_control', u'check_cashing', u'nail_salon', u'storage', u'tax', u'catering', u'dojo', u'tax_service', u'bus_station', u'hospital..historic.', u'toilets', u'marker', u'social_facility', u'telephone', u'taxi', u'building', u'gym', u'emergency_phone', u'courthouse', u'fitness_center', ...], dtype='object')
In [61]:
weather.columns
Out[61]:
Index([u'date', u'weekday', u'season_code', u'season_desc', u'is_holiday', u'is_work_day', u'weather_code', u'weather_desc', u'temp', u'subjective_temp', u'humidity', u'windspeed', u'no_casual_riders', u'no_reg_riders', u'total_riders'], dtype='object')
In [63]:
weather_rentals = daily_usage.merge(weather, left_on='date', right_on='date')
weather_rentals
Out[63]:
date
station_start
cust_type
avg_trip_duration
num_trips
weekday
season_code
season_desc
is_holiday
is_work_day
weather_code
weather_desc
temp
subjective_temp
humidity
windspeed
no_casual_riders
no_reg_riders
total_riders
In [81]:
weather['date'] = weather['date'].dt.date
In [83]:
usage_weather = daily_usage.merge(weather)
usage_weather
Out[83]:
date
station_start
cust_type
avg_trip_duration
num_trips
weekday
season_code
season_desc
is_holiday
is_work_day
weather_code
weather_desc
temp
subjective_temp
humidity
windspeed
no_casual_riders
no_reg_riders
total_riders
0
2012-01-01
10th & Monroe St NE
Registered
16.400000
10
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
1
2012-01-01
10th & U St NW
Casual
16.250000
8
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
2
2012-01-01
10th & U St NW
Registered
10.000000
50
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
3
2012-01-01
10th St & Constitution Ave NW
Casual
20.294118
34
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
4
2012-01-01
10th St & Constitution Ave NW
Registered
14.200000
20
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
5
2012-01-01
11th & H St NE
Casual
10.000000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
6
2012-01-01
11th & H St NE
Registered
11.000000
16
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
7
2012-01-01
11th & Kenyon St NW
Casual
11.000000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
8
2012-01-01
11th & Kenyon St NW
Registered
10.000000
54
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
9
2012-01-01
12th & Army Navy Dr
Casual
67.750000
8
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
10
2012-01-01
12th & Army Navy Dr
Registered
5.500000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
11
2012-01-01
12th & Newton St NE
Registered
108.000000
2
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
12
2012-01-01
13th & D St NE
Casual
11.250000
8
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
13
2012-01-01
13th & D St NE
Registered
11.736842
38
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
14
2012-01-01
13th & H St NE
Casual
34.400000
10
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
15
2012-01-01
13th & H St NE
Registered
15.000000
40
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
16
2012-01-01
13th St & New York Ave NW
Casual
8.500000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
17
2012-01-01
13th St & New York Ave NW
Registered
12.000000
6
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
18
2012-01-01
14th & D St NW / Ronald Reagan Building
Casual
60.666667
18
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
19
2012-01-01
14th & D St NW / Ronald Reagan Building
Registered
14.500000
8
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
20
2012-01-01
14th & D St SE
Casual
59.000000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
21
2012-01-01
14th & D St SE
Registered
5.785714
28
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
22
2012-01-01
14th & G St NW
Casual
189.428571
14
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
23
2012-01-01
14th & G St NW
Registered
8.000000
2
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
24
2012-01-01
14th & Harvard St NW
Casual
29.000000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
25
2012-01-01
14th & Harvard St NW
Registered
119.342105
76
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
26
2012-01-01
14th & R St NW
Casual
11.500000
4
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
27
2012-01-01
14th & R St NW
Registered
6.370370
54
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
28
2012-01-01
14th & Rhode Island Ave NW
Casual
13.714286
14
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
29
2012-01-01
14th & Rhode Island Ave NW
Registered
8.682927
82
0
1
Winter
0
0
1
Clear, Few clouds, Partly cloudy, Partly cloudy
0.370000
0.375621
0.6925
0.192167
686
1608
2294
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
98604
2012-12-31
Park Rd & Holmead Pl NW
Subscriber
9.404255
47
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98605
2012-12-31
Pentagon City Metro / 12th & S Hayes St
Casual
105.500000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98606
2012-12-31
Pentagon City Metro / 12th & S Hayes St
Subscriber
2.833333
6
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98607
2012-12-31
Potomac & Pennsylvania Ave SE
Subscriber
9.777778
18
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98608
2012-12-31
Potomac Ave & 35th St S
Casual
8.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98609
2012-12-31
Potomac Ave & 35th St S
Subscriber
6.500000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98610
2012-12-31
Prince St & Union St
Subscriber
11.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98611
2012-12-31
Rosslyn Metro / Wilson Blvd & Ft Myer Dr
Subscriber
21.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98612
2012-12-31
S Glebe & Potomac Ave
Subscriber
15.000000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98613
2012-12-31
S Joyce & Army Navy Dr
Subscriber
11.400000
5
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98614
2012-12-31
Saint Asaph St & Pendleton St
Casual
15.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98615
2012-12-31
Saint Asaph St & Pendleton St
Subscriber
10.750000
4
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98616
2012-12-31
Smithsonian / Jefferson Dr & 12th St SW
Casual
66.250000
24
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98617
2012-12-31
Smithsonian / Jefferson Dr & 12th St SW
Subscriber
13.800000
5
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98618
2012-12-31
Tenleytown / Wisconsin Ave & Albemarle St NW
Subscriber
23.600000
5
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98619
2012-12-31
Thomas Circle
Casual
25.000000
3
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98620
2012-12-31
Thomas Circle
Subscriber
8.291667
24
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98621
2012-12-31
US Dept of State / Virginia Ave & 21st St NW
Casual
15.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98622
2012-12-31
US Dept of State / Virginia Ave & 21st St NW
Subscriber
18.142857
14
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98623
2012-12-31
USDA / 12th & Independence Ave SW
Casual
332.000000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98624
2012-12-31
USDA / 12th & Independence Ave SW
Subscriber
16.000000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98625
2012-12-31
Utah St & 11th St N
Subscriber
4.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98626
2012-12-31
Van Ness Metro / UDC
Casual
21.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98627
2012-12-31
Van Ness Metro / UDC
Subscriber
23.250000
8
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98628
2012-12-31
Virginia Square Metro / N Monroe St & 9th St N
Subscriber
12.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98629
2012-12-31
Ward Circle / American University
Subscriber
7.500000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98630
2012-12-31
Washington Blvd & 10th St N
Subscriber
7.500000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98631
2012-12-31
Wilson Blvd & Franklin Rd
Subscriber
5.400000
5
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98632
2012-12-31
Wilson Blvd & N Edgewood St
Subscriber
1.000000
1
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98633
2012-12-31
Wilson Blvd & N Oakland St
Subscriber
9.000000
2
1
1
Winter
0
1
2
Mist + Cloudy, Mist + Broken clouds, Mist + Fe...
0.215833
0.223487
0.5775
0.154846
439
2290
2729
98634 rows × 19 columns
In [84]:
uws = usage_weather.merge(stations, left_on='station_start', right_on='station')
In [85]:
uws
Out[85]:
date
station_start
cust_type
avg_trip_duration
num_trips
weekday
season_code
season_desc
is_holiday
is_work_day
...
museum
sculpture
hostel
picnic_site
tour_guide
attraction
landmark
motel
guest_house
gallery
0
2012-01-01
10th & Monroe St NE
Registered
16.400000
10
0
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
1
2012-01-02
10th & Monroe St NE
Registered
20.500000
4
1
1
Winter
1
0
...
0
0
0
0
0
0
0
0
0
0
2
2012-01-03
10th & Monroe St NE
Registered
17.166667
12
2
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
3
2012-01-04
10th & Monroe St NE
Registered
33.200000
10
3
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
4
2012-01-05
10th & Monroe St NE
Registered
15.625000
16
4
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
5
2012-01-06
10th & Monroe St NE
Registered
19.111111
18
5
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
6
2012-01-07
10th & Monroe St NE
Casual
31.833333
12
6
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
7
2012-01-07
10th & Monroe St NE
Registered
18.454545
22
6
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
8
2012-01-08
10th & Monroe St NE
Registered
20.142857
14
0
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
9
2012-01-09
10th & Monroe St NE
Registered
16.000000
8
1
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
10
2012-01-10
10th & Monroe St NE
Registered
19.400000
10
2
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
11
2012-01-11
10th & Monroe St NE
Registered
17.333333
12
3
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
12
2012-01-12
10th & Monroe St NE
Casual
20.000000
2
4
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
13
2012-01-12
10th & Monroe St NE
Registered
12.166667
12
4
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
14
2012-01-13
10th & Monroe St NE
Registered
16.714286
14
5
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
15
2012-01-14
10th & Monroe St NE
Registered
16.428571
14
6
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
16
2012-01-15
10th & Monroe St NE
Casual
17.500000
4
0
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
17
2012-01-15
10th & Monroe St NE
Registered
10.000000
10
0
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
18
2012-01-16
10th & Monroe St NE
Registered
17.500000
12
1
1
Winter
1
0
...
0
0
0
0
0
0
0
0
0
0
19
2012-01-17
10th & Monroe St NE
Registered
19.000000
6
2
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
20
2012-01-18
10th & Monroe St NE
Registered
25.428571
14
3
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
21
2012-01-19
10th & Monroe St NE
Registered
27.142857
14
4
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
22
2012-01-20
10th & Monroe St NE
Registered
17.333333
12
5
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
23
2012-01-21
10th & Monroe St NE
Registered
14.000000
2
6
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
24
2012-01-22
10th & Monroe St NE
Registered
17.333333
6
0
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
25
2012-01-23
10th & Monroe St NE
Registered
17.500000
12
1
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
26
2012-01-24
10th & Monroe St NE
Registered
19.571429
14
2
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
27
2012-01-25
10th & Monroe St NE
Registered
15.571429
14
3
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
28
2012-01-26
10th & Monroe St NE
Registered
21.666667
24
4
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
29
2012-01-27
10th & Monroe St NE
Registered
15.333333
6
5
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
98604
2012-11-27
Potomac Ave & 35th St S
Subscriber
4.000000
2
2
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98605
2012-11-29
Potomac Ave & 35th St S
Subscriber
4.750000
4
4
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98606
2012-11-30
Potomac Ave & 35th St S
Casual
5.500000
2
5
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98607
2012-11-30
Potomac Ave & 35th St S
Subscriber
3.500000
2
5
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98608
2012-12-01
Potomac Ave & 35th St S
Subscriber
8.000000
3
6
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98609
2012-12-02
Potomac Ave & 35th St S
Subscriber
14.000000
1
0
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98610
2012-12-03
Potomac Ave & 35th St S
Subscriber
4.500000
2
1
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98611
2012-12-04
Potomac Ave & 35th St S
Subscriber
7.500000
4
2
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98612
2012-12-05
Potomac Ave & 35th St S
Subscriber
5.500000
2
3
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98613
2012-12-06
Potomac Ave & 35th St S
Subscriber
6.000000
2
4
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98614
2012-12-08
Potomac Ave & 35th St S
Casual
10.500000
2
6
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98615
2012-12-08
Potomac Ave & 35th St S
Subscriber
5.500000
2
6
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98616
2012-12-09
Potomac Ave & 35th St S
Subscriber
3.000000
2
0
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98617
2012-12-10
Potomac Ave & 35th St S
Subscriber
5.000000
1
1
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98618
2012-12-11
Potomac Ave & 35th St S
Subscriber
6.000000
1
2
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98619
2012-12-12
Potomac Ave & 35th St S
Subscriber
8.000000
1
3
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98620
2012-12-13
Potomac Ave & 35th St S
Subscriber
2.000000
1
4
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98621
2012-12-15
Potomac Ave & 35th St S
Subscriber
12.000000
1
6
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98622
2012-12-16
Potomac Ave & 35th St S
Subscriber
8.000000
1
0
4
Fall
0
0
...
0
0
0
0
0
0
0
0
0
0
98623
2012-12-18
Potomac Ave & 35th St S
Casual
39.000000
1
2
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98624
2012-12-18
Potomac Ave & 35th St S
Subscriber
5.000000
2
2
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98625
2012-12-20
Potomac Ave & 35th St S
Casual
140.000000
3
4
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98626
2012-12-20
Potomac Ave & 35th St S
Subscriber
6.000000
1
4
4
Fall
0
1
...
0
0
0
0
0
0
0
0
0
0
98627
2012-12-21
Potomac Ave & 35th St S
Subscriber
7.000000
4
5
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
98628
2012-12-23
Potomac Ave & 35th St S
Casual
8.000000
2
0
1
Winter
0
0
...
0
0
0
0
0
0
0
0
0
0
98629
2012-12-24
Potomac Ave & 35th St S
Subscriber
3.000000
1
1
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
98630
2012-12-25
Potomac Ave & 35th St S
Casual
8.000000
1
2
1
Winter
1
0
...
0
0
0
0
0
0
0
0
0
0
98631
2012-12-25
Potomac Ave & 35th St S
Subscriber
12.000000
1
2
1
Winter
1
0
...
0
0
0
0
0
0
0
0
0
0
98632
2012-12-31
Potomac Ave & 35th St S
Casual
8.000000
1
1
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
98633
2012-12-31
Potomac Ave & 35th St S
Subscriber
6.500000
2
1
1
Winter
0
1
...
0
0
0
0
0
0
0
0
0
0
98634 rows × 155 columns
In [87]:
sorted(uws.columns)
Out[87]:
['alcohol',
'arts_centre',
'artwork',
'atm',
'attraction',
'avg_trip_duration',
'bank',
'bar',
'bar.restaurant',
'bench',
'bicycle_parking',
'bicycle_repair_station',
'border_control',
'building',
'bureau_de_change',
'bus_station',
'bus_stop',
'cafe',
'car_rental',
'car_sharing',
'car_wash',
'catering',
'charging_station',
'check_cashing',
'cinema',
'clinic',
'clock',
'club',
'college',
'community_centre',
'compressed_air',
'convenience',
'courthouse',
'coworking',
'crossing',
'cultural_center',
'currency_exchange',
'cust_type',
'date',
'dentist',
'doctors',
'dojo',
'drinking_water',
'dry_cleaner',
'elevator',
'embassy',
'emergency_phone',
'ev_charging',
'fast_food',
'fire_station',
'fitness_center',
'food_cart',
'food_court',
'footway',
'fountain',
'fuel',
'gallery',
'government',
'grave_yard',
'guest_house',
'gym',
'hospital',
'hospital..historic.',
'hostel',
'hotel',
'humidity',
'ice_cream',
'id',
'information',
'internal_kindergarten',
'is_holiday',
'is_work_day',
'kindergarten',
'landmark',
'lat',
'library',
'long',
'marker',
'marketplace',
'mini_roundabout',
'motel',
'motorway_junction',
'museum',
'nail_salon',
'nightclub',
'no_bikes',
'no_casual_riders',
'no_empty_docks',
'no_reg_riders',
'num_trips',
'nursing_home',
'office',
'optician',
'parking',
'parking_entrance',
'parking_exit',
'pharmacy',
'photography',
'picnic_site',
'picnic_table',
'place_of_worship',
'police',
'post_box',
'post_office',
'pub',
'public_building',
'recycling',
'recycling.waste_basket',
'restaurant',
'school',
'school..historic.',
'sculpture',
'season_code',
'season_desc',
'service',
'shelter',
'sign',
'social_facility',
'speed_camera',
'station',
'station_start',
'stop',
'storage',
'street_lamp',
'strip_club',
'stripclub',
'studio',
'subjective_temp',
'tanning_salon',
'tax',
'tax_service',
'taxi',
'telephone',
'temp',
'terminal_name',
'theatre',
'toilets',
'total_riders',
'tour_guide',
'tourist',
'townhall',
'traffic_signals',
'traffic_signals.bus_stop',
'turning_circle',
'turning_loop',
'tutor',
'university',
'vending_machine',
'veterinary',
'waste_basket',
'waste_disposal',
'weather_code',
'weather_desc',
'weekday',
'windspeed']
In [88]:
uws['crossing']
Out[88]:
0 1
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 1
...
98619 0
98620 0
98621 0
98622 0
98623 0
98624 0
98625 0
98626 0
98627 0
98628 0
98629 0
98630 0
98631 0
98632 0
98633 0
Name: crossing, Length: 98634, dtype: int64
In [ ]:
Content source: joelgrus/codefellows-data-science-week
Similar notebooks: