In [1]:

#CrossCompute
target_folder = '/tmp'
minimum_vehicle_count=10000

In [2]:

import pandas as pd

In [3]:

t = pd.read_csv("https://data.cityofnewyork.us/api/views/p424-amsu/rows.csv?accessType=DOWNLOAD")

In [4]:

t.head()

Out[4]:

	ID	Segment ID	Roadway Name	From	To	Direction	Date	12:00-1:00 AM	1:00-2:00AM	2:00-3:00AM	...	2:00-3:00PM	3:00-4:00PM	4:00-5:00PM	5:00-6:00PM	6:00-7:00PM	7:00-8:00PM	8:00-9:00PM	9:00-10:00PM	10:00-11:00PM	11:00-12:00AM
0	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/02/2013	106	74	45.0	...	371	398	324	394	379	329	249	197	187	169
1	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/03/2013	109	74	55.0	...	308	291	313	253	242	217	210	144	125	79
2	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/04/2013	36	28	11.0	...	426	425	419	469	425	358	224	185	132	74
3	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/05/2013	42	28	16.0	...	393	479	441	476	446	424	305	219	171	76
4	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/06/2013	35	38	12.0	...	383	384	391	369	401	338	236	186	123	76

<p>5 rows × 31 columns</p>

In [5]:

t.columns

Out[5]:

Index(['ID', 'Segment ID', 'Roadway Name', 'From', 'To', 'Direction', 'Date',
       '12:00-1:00 AM', '1:00-2:00AM', '2:00-3:00AM', '3:00-4:00AM',
       '4:00-5:00AM', '5:00-6:00AM', '6:00-7:00AM', '7:00-8:00AM',
       '8:00-9:00AM', '9:00-10:00AM', '10:00-11:00AM', '11:00-12:00PM',
       '12:00-1:00PM', '1:00-2:00PM', '2:00-3:00PM', '3:00-4:00PM',
       '4:00-5:00PM', '5:00-6:00PM', '6:00-7:00PM', '7:00-8:00PM',
       '8:00-9:00PM', '9:00-10:00PM', '10:00-11:00PM', '11:00-12:00AM'],
      dtype='object')

In [6]:

time_columns = [x for x in t.columns if x.endswith('M')]

In [7]:

time_columns

Out[7]:

['12:00-1:00 AM',
 '1:00-2:00AM',
 '2:00-3:00AM',
 '3:00-4:00AM',
 '4:00-5:00AM',
 '5:00-6:00AM',
 '6:00-7:00AM',
 '7:00-8:00AM',
 '8:00-9:00AM',
 '9:00-10:00AM',
 '10:00-11:00AM',
 '11:00-12:00PM',
 '12:00-1:00PM',
 '1:00-2:00PM',
 '2:00-3:00PM',
 '3:00-4:00PM',
 '4:00-5:00PM',
 '5:00-6:00PM',
 '6:00-7:00PM',
 '7:00-8:00PM',
 '8:00-9:00PM',
 '9:00-10:00PM',
 '10:00-11:00PM',
 '11:00-12:00AM']

In [8]:

t.sum

Out[8]:

<bound method DataFrame.sum of        ID  Segment ID              Roadway Name              From  \
0       1        2153              HUGUENOT AVE        WOODROW RD   
1       1        2153              HUGUENOT AVE        WOODROW RD   
2       1        2153              HUGUENOT AVE        WOODROW RD   
3       1        2153              HUGUENOT AVE        WOODROW RD   
4       1        2153              HUGUENOT AVE        WOODROW RD   
5       1        2153              HUGUENOT AVE        WOODROW RD   
6       1        2153              HUGUENOT AVE        WOODROW RD   
7       1        2153              HUGUENOT AVE        WOODROW RD   
8       1        2153              HUGUENOT AVE        WOODROW RD   
9       1        2153              HUGUENOT AVE        WOODROW RD   
10      1        2153              HUGUENOT AVE        WOODROW RD   
11      1        2153              HUGUENOT AVE        WOODROW RD   
12      1        2153              HUGUENOT AVE        WOODROW RD   
13      1        2153              HUGUENOT AVE        WOODROW RD   
14      1        2153              HUGUENOT AVE        WOODROW RD   
15      1        2153              HUGUENOT AVE        WOODROW RD   
16      1        2153              HUGUENOT AVE        WOODROW RD   
17      1        2153              HUGUENOT AVE        WOODROW RD   
18      1        2153              HUGUENOT AVE        WOODROW RD   
19      1        2153              HUGUENOT AVE        WOODROW RD   
20      1        2153              HUGUENOT AVE        WOODROW RD   
21      1        2153              HUGUENOT AVE        WOODROW RD   
22      1        2153              HUGUENOT AVE        WOODROW RD   
23      1        2153              HUGUENOT AVE        WOODROW RD   
24      1        2153              HUGUENOT AVE        WOODROW RD   
25      1        2153              HUGUENOT AVE        WOODROW RD   
26      2        6644        RICHMOND HILL ROAD     EDINBORO ROAD   
27      2        6644        RICHMOND HILL ROAD     EDINBORO ROAD   
28      2        6644        RICHMOND HILL ROAD     EDINBORO ROAD   
29      2        6644        RICHMOND HILL ROAD     EDINBORO ROAD   
...   ...         ...                       ...               ...   
5915  375      179545   THROGS NECK EXPRESSWAY    HARDING AVENUE    
5916  375      179545   THROGS NECK EXPRESSWAY    HARDING AVENUE    
5917  375      179545   THROGS NECK EXPRESSWAY    HARDING AVENUE    
5918  375      179545   THROGS NECK EXPRESSWAY    HARDING AVENUE    
5919  375      179545   THROGS NECK EXPRESSWAY    HARDING AVENUE    
5920  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5921  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5922  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5923  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5924  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5925  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5926  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5927  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5928  376      179547  THROGS NECK EXPWY SER RD   HARDING AVENUE    
5929  377      179545                  E 177 ST   HARDING AVENUE    
5930  377      179545                  E 177 ST   HARDING AVENUE    
5931  377      179545                  E 177 ST   HARDING AVENUE    
5932  377      179545                  E 177 ST   HARDING AVENUE    
5933  377      179545                  E 177 ST   HARDING AVENUE    
5934  377      179545                  E 177 ST   HARDING AVENUE    
5935  377      179545                  E 177 ST   HARDING AVENUE    
5936  377      179545                  E 177 ST   HARDING AVENUE    
5937  377      179545                  E 177 ST   HARDING AVENUE    
5938  377      179545                  E 177 ST   HARDING AVENUE    
5939  377      179545                  E 177 ST   HARDING AVENUE    
5940  377      179545                  E 177 ST   HARDING AVENUE    
5941  377      179545                  E 177 ST   HARDING AVENUE    
5942  377      179545                  E 177 ST   HARDING AVENUE    
5943  377      179545                  E 177 ST   HARDING AVENUE    
5944  377      179545                  E 177 ST   HARDING AVENUE    

                       To Direction        Date  12:00-1:00 AM  1:00-2:00AM  \
0            STAFFORD AVE        NB  02/02/2013            106           74   
1            STAFFORD AVE        NB  02/03/2013            109           74   
2            STAFFORD AVE        NB  02/04/2013             36           28   
3            STAFFORD AVE        NB  02/05/2013             42           28   
4            STAFFORD AVE        NB  02/06/2013             35           38   
5            STAFFORD AVE        NB  02/07/2013             33           26   
6            STAFFORD AVE        NB  02/08/2013             35           28   
7            STAFFORD AVE        NB  02/09/2013            110           73   
8            STAFFORD AVE        NB  02/10/2013            114           77   
9            STAFFORD AVE        NB  02/11/2013             38           30   
10           STAFFORD AVE        NB  02/12/2013             43           32   
11           STAFFORD AVE        NB  02/13/2013             38           41   
12           STAFFORD AVE        NB  02/14/2013             37           29   
13           STAFFORD AVE        SB  02/02/2013            132           88   
14           STAFFORD AVE        SB  02/03/2013            126           89   
15           STAFFORD AVE        SB  02/04/2013             59           30   
16           STAFFORD AVE        SB  02/05/2013             62           27   
17           STAFFORD AVE        SB  02/06/2013             67           29   
18           STAFFORD AVE        SB  02/07/2013             49           30   
19           STAFFORD AVE        SB  02/08/2013             76           36   
20           STAFFORD AVE        SB  02/09/2013            121           60   
21           STAFFORD AVE        SB  02/10/2013            126           98   
22           STAFFORD AVE        SB  02/11/2013             23           15   
23           STAFFORD AVE        SB  02/12/2013             33           17   
24           STAFFORD AVE        SB  02/13/2013              6            7   
25           STAFFORD AVE        SB  02/14/2013             13            2   
26       FOREST HILL ROAD        NB  09/29/2012             89           40   
27       FOREST HILL ROAD        NB  09/30/2012             99           66   
28       FOREST HILL ROAD        NB  10/01/2012             51           35   
29       FOREST HILL ROAD        NB  10/02/2012             30           20   
...                   ...       ...         ...            ...          ...   
5915   LONGSTREET AVENUE         WB  02/03/2013           1141          523   
5916   LONGSTREET AVENUE         WB  02/04/2013            812          393   
5917   LONGSTREET AVENUE         WB  02/05/2013            419          292   
5918   LONGSTREET AVENUE         WB  02/06/2013            408          250   
5919   LONGSTREET AVENUE         WB  02/07/2013            459          293   
5920   LONGSTREET AVENUE         WB  10/27/2012             34           16   
5921   LONGSTREET AVENUE         WB  10/28/2012             28           23   
5922   LONGSTREET AVENUE        WB   02/01/2013             25           15   
5923   LONGSTREET AVENUE        WB   02/02/2013             51           21   
5924   LONGSTREET AVENUE        WB   02/03/2013             78           22   
5925   LONGSTREET AVENUE        WB   02/04/2013            115           28   
5926   LONGSTREET AVENUE        WB   02/05/2013             18           14   
5927   LONGSTREET AVENUE        WB   02/06/2013             20           12   
5928   LONGSTREET AVENUE        WB   02/07/2013             12           15   
5929   LONGSTREET AVENUE         EB  10/27/2012             32           27   
5930   LONGSTREET AVENUE         EB  10/28/2012             38           29   
5931   LONGSTREET AVENUE        EB   02/01/2013             16           20   
5932   LONGSTREET AVENUE        EB   02/02/2013             31           24   
5933   LONGSTREET AVENUE        EB   02/03/2013             34           36   
5934   LONGSTREET AVENUE        EB   02/04/2013             23           17   
5935   LONGSTREET AVENUE        EB   02/05/2013             18           11   
5936   LONGSTREET AVENUE        EB   02/06/2013             27            9   
5937   LONGSTREET AVENUE        EB   02/07/2013             18           14   
5938   LONGSTREET AVENUE         WB  02/01/2013              8           14   
5939   LONGSTREET AVENUE         WB  02/02/2013             19           14   
5940   LONGSTREET AVENUE         WB  02/03/2013             11           17   
5941   LONGSTREET AVENUE         WB  02/04/2013             14           15   
5942   LONGSTREET AVENUE         WB  02/05/2013             13            9   
5943   LONGSTREET AVENUE         WB  02/06/2013             12            8   
5944   LONGSTREET AVENUE         WB  02/07/2013             20           11   

      2:00-3:00AM      ...        2:00-3:00PM  3:00-4:00PM  4:00-5:00PM  \
0            45.0      ...                371          398          324   
1            55.0      ...                308          291          313   
2            11.0      ...                426          425          419   
3            16.0      ...                393          479          441   
4            12.0      ...                383          384          391   
5            14.0      ...                151          409          422   
6            18.0      ...                430          518          461   
7            46.0      ...                379          406          330   
8            58.0      ...                315          297          320   
9            15.0      ...                434          433          438   
10           20.0      ...                401          488          450   
11           16.0      ...                391          392          399   
12           18.0      ...                154          417          405   
13           63.0      ...                383          406          341   
14           72.0      ...                326          308          314   
15           13.0      ...                377          425          431   
16           11.0      ...                398          488          454   
17           16.0      ...                377          428          438   
18            8.0      ...                353          432          352   
19           23.0      ...                368          364          379   
20           62.0      ...                377          469          359   
21           77.0      ...                309          319          306   
22           11.0      ...                330          423          354   
23            3.0      ...                267          205          244   
24            1.0      ...                239          252          278   
25            3.0      ...                269          248          270   
26           33.0      ...                606          501          539   
27           41.0      ...                499          429          353   
28           24.0      ...                444          482          440   
29           14.0      ...                437          491          452   
...           ...      ...                ...          ...          ...   
5915        263.0      ...               2565         2611         2977   
5916        233.0      ...               2320         2369         2579   
5917        245.0      ...               2313         2560         2709   
5918        236.0      ...               2435         2494         2713   
5919        284.0      ...               2764         2847         3090   
5920         15.0      ...                177          278          324   
5921         19.0      ...                261          230          295   
5922          4.0      ...                 87           81          111   
5923         29.0      ...                 79           73           74   
5924         19.0      ...                 50           69           75   
5925          7.0      ...                 85           84          106   
5926          2.0      ...                 71           71           98   
5927          6.0      ...                 79           77           85   
5928         11.0      ...                 93           60          102   
5929         19.0      ...                169          244          373   
5930         26.0      ...                279          286          274   
5931         10.0      ...                106          111          127   
5932         20.0      ...                118           99           94   
5933         32.0      ...                 80          104          138   
5934          7.0      ...                104           89          113   
5935          9.0      ...                100          108          119   
5936          8.0      ...                107          114          127   
5937          8.0      ...                105          107          120   
5938          7.0      ...                119           91          112   
5939         11.0      ...                 81           86          101   
5940         22.0      ...                 69           84           70   
5941          7.0      ...                 85           93           81   
5942          5.0      ...                 93          103           83   
5943          1.0      ...                 97          107           78   
5944          6.0      ...                 82          104           84   

      5:00-6:00PM  6:00-7:00PM  7:00-8:00PM  8:00-9:00PM  9:00-10:00PM  \
0             394          379          329          249           197   
1             253          242          217          210           144   
2             469          425          358          224           185   
3             476          446          424          305           219   
4             369          401          338          236           186   
5             364          356          339          275           225   
6             485          531          393          351           276   
7             402          387          336          254           201   
8             257          246          221          214           147   
9             478          433          365          228           189   
10            486          454          433          312           223   
11            377          409          345          240           190   
12            374          394          354          247           200   
13            342          336          272          228           199   
14            278          255          254          210           138   
15            409          390          358          251           194   
16            453          434          387          301           212   
17            391          426          306          253           176   
18            412          367          369          283           185   
19            365          418          387          264           211   
20            330          340          311          258           186   
21            284          275          208          171           139   
22            362          355          272          175           149   
23            172          221          134          114            54   
24            275          220          135          107            67   
25            247          230          171          130            92   
26            503          492          358          221           164   
27            301          231          190          139           110   
28            416          436          291          203           137   
29            416          418          330          212           140   
...           ...          ...          ...          ...           ...   
5915         3056         2403         1570         1330          1577   
5916         2576         2647         2001         1502          1286   
5917         2662         2455         1828         1431          1254   
5918         2765         2783         2058         1621          1388   
5919         2871         2896         2648         2195          1802   
5920          350          244          296          250           292   
5921          246          187           82           99            61   
5922           82           71           84           40            51   
5923           73           94           75           44            51   
5924          105           78           61           36            52   
5925           95           83           57           47            47   
5926           91           70           64           45            42   
5927           90           73           72           55            55   
5928          127          115           82           40            73   
5929          345          314          284          253           290   
5930          224          173           64           79            59   
5931          116          124           97           85            70   
5932          105           88           77           75            65   
5933          134           90           55           42            40   
5934          123           98           82           73            54   
5935          133          128           91           73            56   
5936          136          126          103           89            76   
5937          140          120          110           70            84   
5938           90           97           91           72            47   
5939           98           84           69           69            50   
5940          105           65           47           45            44   
5941           73           71           64           45            24   
5942           96           88           68           73            30   
5943           87          109           64           50            41   
5944          105          100           68           61            48   

      10:00-11:00PM  11:00-12:00AM  
0               187            169  
1               125             79  
2               132             74  
3               171             76  
4               123             76  
5               169            104  
6               242            154  
7               191            174  
8               121             85  
9               136             75  
10              175             97  
11              126             79  
12              151             97  
13              190            155  
14              101             89  
15              132             79  
16              146            101  
17              127             89  
18              155            146  
19              178            167  
20              171            191  
21               97             48  
22               69             54  
23               27             21  
24               37             19  
25               56             26  
26              145            130  
27              104             66  
28               93             55  
29               94             56  
...             ...            ...  
5915           1238           1011  
5916           1052            677  
5917           1116            703  
5918           1116            683  
5919           1442           1248  
5920             74             69  
5921             44             31  
5922             56             34  
5923             58             72  
5924             44            106  
5925             47             22  
5926             56             15  
5927             61             24  
5928             62             46  
5929             80             54  
5930             59             34  
5931             84             42  
5932             61             62  
5933             35             63  
5934             52             23  
5935             51             41  
5936             60             25  
5937             69             30  
5938             53             37  
5939             41             47  
5940             29            111  
5941             21             25  
5942             21             31  
5943             58             17  
5944             34             29  

[5945 rows x 31 columns]>

In [9]:

t.sum()

Out[9]:

ID                                                         1155340
Segment ID                                               429623158
Roadway Name     HUGUENOT AVEHUGUENOT AVEHUGUENOT AVEHUGUENOT A...
From             WOODROW RDWOODROW RDWOODROW RDWOODROW RDWOODRO...
To               STAFFORD AVESTAFFORD AVESTAFFORD AVESTAFFORD A...
Direction        NBNBNBNBNBNBNBNBNBNBNBNBNBSBSBSBSBSBSBSBSBSBSB...
Date             02/02/201302/03/201302/04/201302/05/201302/06/...
12:00-1:00 AM                                              1593980
1:00-2:00AM                                                1186001
2:00-3:00AM                                                 943428
3:00-4:00AM                                                 823281
4:00-5:00AM                                                 864327
5:00-6:00AM                                            1.07755e+06
6:00-7:00AM                                                1776670
7:00-8:00AM                                            2.67327e+06
8:00-9:00AM                                            3.07229e+06
9:00-10:00AM                                               2946417
10:00-11:00AM                                              2896912
11:00-12:00PM                                              2976042
12:00-1:00PM                                               3084089
1:00-2:00PM                                                3170067
2:00-3:00PM                                                3376024
3:00-4:00PM                                                3557591
4:00-5:00PM                                                3563714
5:00-6:00PM                                                3568822
6:00-7:00PM                                                3440468
7:00-8:00PM                                                3102515
8:00-9:00PM                                                2731307
9:00-10:00PM                                               2399095
10:00-11:00PM                                              2152386
11:00-12:00AM                                              1893921
dtype: object

In [10]:

t.sum(axis = 1)

Out[10]:

0         7725.0
1         6422.0
2         8085.0
3         8396.0
4         7824.0
5         7515.0
6         8665.0
7         7841.0
8         6516.0
9         8211.0
10        8555.0
11        7929.0
12        7558.0
13        7495.0
14        6425.0
15        7789.0
16        8027.0
17        7639.0
18        7594.0
19        7567.0
20        7508.0
21        6552.0
22        7414.0
23        5493.0
24        5183.0
25        5080.0
26       13923.0
27       11977.0
28       12506.0
29       13033.0
          ...   
5915    216281.0
5916    228627.0
5917    227436.0
5918    229421.0
5919    235006.0
5920    183055.0
5921    182491.0
5922    181317.0
5923    181087.0
5924    181062.0
5925    181508.0
5926    181266.0
5927    181289.0
5928    181567.0
5929    183146.0
5930    182426.0
5931    181679.0
5932    181389.0
5933    181249.0
5934    181339.0
5935    181427.0
5936    181471.0
5937    181541.0
5938    181607.0
5939    181299.0
5940    181070.0
5941    181214.0
5942    181284.0
5943    181345.0
5944    181427.0
Length: 5945, dtype: float64

In [11]:

t[time_columns]

Out[11]:

	12:00-1:00 AM	1:00-2:00AM	2:00-3:00AM	3:00-4:00AM	4:00-5:00AM	5:00-6:00AM	6:00-7:00AM	7:00-8:00AM	8:00-9:00AM	9:00-10:00AM	...	2:00-3:00PM	3:00-4:00PM	4:00-5:00PM	5:00-6:00PM	6:00-7:00PM	7:00-8:00PM	8:00-9:00PM	9:00-10:00PM	10:00-11:00PM	11:00-12:00AM
0	106	74	45.0	29.0	29	45.0	71	145.0	213.0	278	...	371	398	324	394	379	329	249	197	187	169
1	109	74	55.0	37.0	26	25.0	47	74.0	111.0	204	...	308	291	313	253	242	217	210	144	125	79
2	36	28	11.0	16.0	32	108.0	168	418.0	493.0	263	...	426	425	419	469	425	358	224	185	132	74
3	42	28	16.0	12.0	34	109.0	193	397.0	499.0	241	...	393	479	441	476	446	424	305	219	171	76
4	35	38	12.0	14.0	31	98.0	195	372.0	490.0	297	...	383	384	391	369	401	338	236	186	123	76
5	33	26	14.0	22.0	31	100.0	171	382.0	460.0	273	...	151	409	422	364	356	339	275	225	169	104
6	35	28	18.0	14.0	34	89.0	202	306.0	440.0	290	...	430	518	461	485	531	393	351	276	242	154
7	110	73	46.0	32.0	29	44.0	73	150.0	217.0	283	...	379	406	330	402	387	336	254	201	191	174
8	114	77	58.0	40.0	28	27.0	48	77.0	127.0	208	...	315	297	320	257	246	221	214	147	121	85
9	38	30	15.0	19.0	34	112.0	172	427.0	502.0	268	...	434	433	438	478	433	365	228	189	136	75
10	43	32	20.0	15.0	36	113.0	197	404.0	509.0	245	...	401	488	450	486	454	433	312	223	175	97
11	38	41	16.0	16.0	33	100.0	199	379.0	500.0	303	...	391	392	399	377	409	345	240	190	126	79
12	37	29	18.0	23.0	33	103.0	177	389.0	470.0	278	...	154	417	405	374	394	354	247	200	151	97
13	132	88	63.0	39.0	24	32.0	67	99.0	188.0	299	...	383	406	341	342	336	272	228	199	190	155
14	126	89	72.0	42.0	43	24.0	36	63.0	107.0	178	...	326	308	314	278	255	254	210	138	101	89
15	59	30	13.0	10.0	28	64.0	212	410.0	402.0	264	...	377	425	431	409	390	358	251	194	132	79
16	62	27	11.0	10.0	25	73.0	212	440.0	401.0	272	...	398	488	454	453	434	387	301	212	146	101
17	67	29	16.0	14.0	19	61.0	192	438.0	397.0	248	...	377	428	438	391	426	306	253	176	127	89
18	49	30	8.0	23.0	26	71.0	188	417.0	407.0	252	...	353	432	352	412	367	369	283	185	155	146
19	76	36	23.0	16.0	25	58.0	190	405.0	395.0	229	...	368	364	379	365	418	387	264	211	178	167
20	121	60	62.0	35.0	19	33.0	52	147.0	188.0	224	...	377	469	359	330	340	311	258	186	171	191
21	126	98	77.0	33.0	24	36.0	32	84.0	151.0	220	...	309	319	306	284	275	208	171	139	97	48
22	23	15	11.0	23.0	64	177.0	398	401.0	249.0	205	...	330	423	354	362	355	272	175	149	69	54
23	33	17	3.0	17.0	62	165.0	399	417.0	173.0	105	...	267	205	244	172	221	134	114	54	27	21
24	6	7	1.0	9.0	33	72.0	194	264.0	132.0	113	...	239	252	278	275	220	135	107	67	37	19
25	13	2	3.0	10.0	31	66.0	210	221.0	116.0	82	...	269	248	270	247	230	171	130	92	56	26
26	89	40	33.0	32.0	31	44.0	116	176.0	274.0	473	...	606	501	539	503	492	358	221	164	145	130
27	99	66	41.0	33.0	28	25.0	77	115.0	200.0	249	...	499	429	353	301	231	190	139	110	104	66
28	51	35	24.0	18.0	35	75.0	133	213.0	228.0	287	...	444	482	440	416	436	291	203	137	93	55
29	30	20	14.0	18.0	46	132.0	223	439.0	406.0	401	...	437	491	452	416	418	330	212	140	94	56
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
5915	1141	523	263.0	204.0	218	348.0	649	986.0	1235.0	1464	...	2565	2611	2977	3056	2403	1570	1330	1577	1238	1011
5916	812	393	233.0	271.0	470	1338.0	3817	5005.0	4230.0	3495	...	2320	2369	2579	2576	2647	2001	1502	1286	1052	677
5917	419	292	245.0	296.0	473	1274.0	3458	4585.0	4256.0	3610	...	2313	2560	2709	2662	2455	1828	1431	1254	1116	703
5918	408	250	236.0	286.0	525	1757.0	4133	4641.0	4164.0	3516	...	2435	2494	2713	2765	2783	2058	1621	1388	1116	683
5919	459	293	284.0	336.0	606	1868.0	3898	4870.0	4421.0	4062	...	2764	2847	3090	2871	2896	2648	2195	1802	1442	1248
5920	34	16	15.0	16.0	5	27.0	39	55.0	65.0	73	...	177	278	324	350	244	296	250	292	74	69
5921	28	23	19.0	16.0	18	34.0	52	63.0	56.0	97	...	261	230	295	246	187	82	99	61	44	31
5922	25	15	4.0	14.0	15	23.0	89	93.0	94.0	79	...	87	81	111	82	71	84	40	51	56	34
5923	51	21	29.0	3.0	15	11.0	21	32.0	41.0	33	...	79	73	74	73	94	75	44	51	58	72
5924	78	22	19.0	17.0	8	21.0	28	22.0	27.0	22	...	50	69	75	105	78	61	36	52	44	106
5925	115	28	7.0	2.0	19	38.0	159	108.0	99.0	54	...	85	84	106	95	83	57	47	47	47	22
5926	18	14	2.0	5.0	11	23.0	104	120.0	91.0	74	...	71	71	98	91	70	64	45	42	56	15
5927	20	12	6.0	4.0	11	16.0	112	100.0	99.0	72	...	79	77	85	90	73	72	55	55	61	24
5928	12	15	11.0	9.0	20	25.0	118	143.0	113.0	105	...	93	60	102	127	115	82	40	73	62	46
5929	32	27	19.0	20.0	7	25.0	34	57.0	56.0	76	...	169	244	373	345	314	284	253	290	80	54
5930	38	29	26.0	29.0	15	23.0	45	36.0	62.0	93	...	279	286	274	224	173	64	79	59	59	34
5931	16	20	10.0	16.0	9	15.0	73	63.0	86.0	93	...	106	111	127	116	124	97	85	70	84	42
5932	31	24	20.0	7.0	11	21.0	22	22.0	41.0	46	...	118	99	94	105	88	77	75	65	61	62
5933	34	36	32.0	16.0	8	14.0	27	12.0	32.0	36	...	80	104	138	134	90	55	42	40	35	63
5934	23	17	7.0	4.0	9	23.0	58	62.0	82.0	56	...	104	89	113	123	98	82	73	54	52	23
5935	18	11	9.0	3.0	9	14.0	74	66.0	74.0	68	...	100	108	119	133	128	91	73	56	51	41
5936	27	9	8.0	9.0	5	20.0	63	66.0	69.0	61	...	107	114	127	136	126	103	89	76	60	25
5937	18	14	8.0	7.0	3	21.0	65	62.0	79.0	81	...	105	107	120	140	120	110	70	84	69	30
5938	8	14	7.0	9.0	10	22.0	68	129.0	133.0	96	...	119	91	112	90	97	91	72	47	53	37
5939	19	14	11.0	6.0	6	16.0	22	43.0	44.0	76	...	81	86	101	98	84	69	69	50	41	47
5940	11	17	22.0	8.0	5	8.0	6	23.0	44.0	56	...	69	84	70	105	65	47	45	44	29	111
5941	14	15	7.0	4.0	9	25.0	48	120.0	123.0	76	...	85	93	81	73	71	64	45	24	21	25
5942	13	9	5.0	3.0	7	21.0	56	113.0	124.0	72	...	93	103	83	96	88	68	73	30	21	31
5943	12	8	1.0	8.0	4	20.0	58	130.0	115.0	79	...	97	107	78	87	109	64	50	41	58	17
5944	20	11	6.0	5.0	4	24.0	63	117.0	128.0	77	...	82	104	84	105	100	68	61	48	34	29

<p>5945 rows × 24 columns</p>

In [12]:

t[time_columns].sum(axis=1)

Out[12]:

0        5571.0
1        4268.0
2        5931.0
3        6242.0
4        5670.0
5        5361.0
6        6511.0
7        5687.0
8        4362.0
9        6057.0
10       6401.0
11       5775.0
12       5404.0
13       5341.0
14       4271.0
15       5635.0
16       5873.0
17       5485.0
18       5440.0
19       5413.0
20       5354.0
21       4398.0
22       5260.0
23       3339.0
24       3029.0
25       2926.0
26       7277.0
27       5331.0
28       5860.0
29       6387.0
         ...   
5915    36361.0
5916    48707.0
5917    47516.0
5918    49501.0
5919    55086.0
5920     3132.0
5921     2568.0
5922     1394.0
5923     1164.0
5924     1139.0
5925     1585.0
5926     1343.0
5927     1366.0
5928     1644.0
5929     3224.0
5930     2504.0
5931     1757.0
5932     1467.0
5933     1327.0
5934     1417.0
5935     1505.0
5936     1549.0
5937     1619.0
5938     1685.0
5939     1377.0
5940     1148.0
5941     1292.0
5942     1362.0
5943     1423.0
5944     1505.0
Length: 5945, dtype: float64

In [13]:

t['sum_count'] = t[time_columns].sum(axis=1)

In [14]:

t.iloc[0]

Out[14]:

ID                          1
Segment ID               2153
Roadway Name     HUGUENOT AVE
From               WOODROW RD
To               STAFFORD AVE
Direction                  NB
Date               02/02/2013
12:00-1:00 AM             106
1:00-2:00AM                74
2:00-3:00AM                45
3:00-4:00AM                29
4:00-5:00AM                29
5:00-6:00AM                45
6:00-7:00AM                71
7:00-8:00AM               145
8:00-9:00AM               213
9:00-10:00AM              278
10:00-11:00AM             387
11:00-12:00PM             335
12:00-1:00PM              406
1:00-2:00PM               411
2:00-3:00PM               371
3:00-4:00PM               398
4:00-5:00PM               324
5:00-6:00PM               394
6:00-7:00PM               379
7:00-8:00PM               329
8:00-9:00PM               249
9:00-10:00PM              197
10:00-11:00PM             187
11:00-12:00AM             169
sum_count                5571
Name: 0, dtype: object

In [15]:

t.sort_values('sum_count')

Out[15]:

	ID	Segment ID	Roadway Name	From	To	Direction	Date	12:00-1:00 AM	1:00-2:00AM	2:00-3:00AM	...	3:00-4:00PM	4:00-5:00PM	5:00-6:00PM	6:00-7:00PM	7:00-8:00PM	8:00-9:00PM	9:00-10:00PM	10:00-11:00PM	11:00-12:00AM	sum_count
4240	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	02/02/2013	0	0	0.0	...	7	2	1	1	1	0	0	1	0	34.0
4241	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	02/03/2013	0	0	0.0	...	6	2	2	1	2	2	1	0	2	40.0
4244	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	02/06/2013	0	0	0.0	...	2	3	3	6	2	2	1	0	0	49.0
4242	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	02/04/2013	2	0	0.0	...	5	2	4	3	1	0	1	3	2	52.0
4243	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	02/05/2013	1	0	0.0	...	0	1	9	2	6	1	1	1	0	59.0
4245	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	02/07/2013	0	1	0.0	...	2	3	3	5	5	2	1	2	1	59.0
4644	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	10/28/2012	2	1	2.0	...	7	3	4	4	4	3	3	1	2	65.0
4247	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	WB	10/28/2012	1	2	2.0	...	5	10	5	8	4	0	4	3	0	82.0
4649	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/03/2013	2	2	4.0	...	8	3	4	11	2	0	1	1	1	83.0
4643	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	10/27/2012	0	0	0.0	...	9	12	2	2	2	2	5	12	5	85.0
4650	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/04/2013	2	0	0.0	...	3	9	5	2	6	7	4	9	2	88.0
4239	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	10/28/2012	2	2	3.0	...	4	5	4	4	4	4	3	3	3	89.0
4656	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	WB	01/30/2013	0	1	0.0	...	6	4	8	8	7	5	3	6	2	90.0
1792	108	32066	CORTLANDT ALLEY	WALKER STREET	WHITE STREET	SB	10/15/2012	3	2	0.0	...	10	3	0	2	7	8	0	2	1	91.0
4238	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	EB	10/27/2012	4	1	3.0	...	4	6	6	4	3	4	4	4	3	94.0
4648	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/02/2013	1	3	6.0	...	6	5	8	9	3	8	5	3	4	99.0
4651	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/05/2013	3	1	0.0	...	7	2	10	5	4	7	8	3	1	100.0
4246	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	WB	10/27/2012	0	1	0.0	...	7	10	4	3	6	7	4	4	1	104.0
4653	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/07/2013	2	0	1.0	...	7	6	15	9	2	8	6	9	1	113.0
4652	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/06/2013	3	4	0.0	...	6	8	7	14	5	7	2	2	1	114.0
4647	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	02/01/2013	0	0	1.0	...	5	7	13	11	6	12	4	5	0	116.0
4249	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	WB	02/03/2013	3	0	0.0	...	9	11	6	6	8	5	3	1	3	122.0
4655	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	WB	10/28/2012	3	4	1.0	...	3	11	1	5	9	11	2	9	1	125.0
4645	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	01/30/2013	2	1	0.0	...	5	7	13	9	7	9	10	7	2	130.0
1789	108	32066	CORTLANDT ALLEY	WALKER STREET	WHITE STREET	SB	10/13/2012	2	6	4.0	...	11	10	7	4	4	2	4	3	2	130.0
4248	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	WB	02/02/2013	1	0	0.0	...	13	12	3	13	6	7	1	1	0	133.0
1798	108	32066	CORTLANDT ALLEY	WALKER STREET	WHITE STREET	SB	10/21/2012	5	2	5.0	...	5	5	8	11	4	6	0	2	1	135.0
4252	280	101614	MARYLAND RD	DOUGLASTON PKWY	HANFORD ST	WB	02/06/2013	1	0	0.0	...	5	8	11	11	8	5	3	3	1	139.0
4646	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	EB	01/31/2013	7	2	3.0	...	5	6	12	8	8	8	5	5	2	141.0
4660	306	129486	115 ROAD	205 STREET	FRANCIS LEWIS BLVD	WB	02/03/2013	2	2	6.0	...	9	6	10	20	4	3	3	3	5	141.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
5856	369	154496	CROSS BRONX EXPRESSWAY	MORRIS AVENUE	WEEKS AVENUE	WB	02/05/2013	1662	1132	900.0	...	4527	4062	4286	4339	3929	3686	3019	2692	2121	77435.0
3309	226	65634	CROSS ISLAND PARKWAY	107 AVENUE	115 AVENUE	NB	02/04/2013	1052	527	355.0	...	5120	5378	5227	5076	4098	3186	2542	2334	1783	77754.0
3325	226	65634	CROSS ISLAND PARKWAY	107 AVENUE	115 AVENUE	SB	02/05/2013	1355	619	405.0	...	5572	5923	5849	5578	4709	3828	3201	2546	2105	77948.0
4917	319	154280	GOWANUS EXPRESSWAY	29 STREET	28 STREET	SB	02/07/2013	2262	1225	880.0	...	5268	5294	4871	4916	4403	4700	4144	3820	3005	78748.0
3312	226	65634	CROSS ISLAND PARKWAY	107 AVENUE	115 AVENUE	NB	02/07/2013	1065	577	387.0	...	5420	5418	5529	5156	4520	3548	3046	2762	1773	80118.0
3310	226	65634	CROSS ISLAND PARKWAY	107 AVENUE	115 AVENUE	NB	02/05/2013	983	507	332.0	...	5279	5151	5243	4941	4134	3200	2552	2352	1737	80174.0
5858	369	154496	CROSS BRONX EXPRESSWAY	MORRIS AVENUE	WEEKS AVENUE	WB	02/07/2013	1904	1225	964.0	...	4355	4490	2940	4651	4304	4021	3901	3413	2821	80285.0
5882	372	154454	CROSS BRONX EXPRESSWAY	MARMION AVENUE	PROSPECT AVENUE	EB	02/07/2013	1839	1240	998.0	...	5229	5172	4977	3172	3460	3252	2803	3037	2994	82463.0
3327	226	65634	CROSS ISLAND PARKWAY	107 AVENUE	115 AVENUE	SB	02/07/2013	1524	753	425.0	...	5518	5895	6169	5810	5249	4493	3896	3209	2364	83345.0
5878	372	154454	CROSS BRONX EXPRESSWAY	MARMION AVENUE	PROSPECT AVENUE	EB	02/03/2013	3432	2359	1774.0	...	5197	5098	5021	4925	4919	4728	4070	3586	2889	83445.0
5881	372	154454	CROSS BRONX EXPRESSWAY	MARMION AVENUE	PROSPECT AVENUE	EB	02/06/2013	2300	1252	969.0	...	4815	4987	5095	4815	4514	4004	3397	2965	2364	85083.0
5880	372	154454	CROSS BRONX EXPRESSWAY	MARMION AVENUE	PROSPECT AVENUE	EB	02/05/2013	2396	1222	1040.0	...	5007	5044	4967	5007	4589	3769	3327	3142	2337	85461.0
2299	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/02/2012	2249	1585	1139.0	...	4500	4121	4493	4895	4463	4269	3976	3816	3236	87258.0
2301	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/04/2012	2934	2013	1325.0	...	4345	3691	4179	4888	4349	4770	4522	4431	4031	87642.0
2300	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/03/2012	2467	1751	1167.0	...	4037	3789	4575	5045	4713	4393	3804	3887	3912	87950.0
2298	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/01/2012	2343	1392	1114.0	...	4713	4296	4730	5254	4578	4118	3549	3574	3246	88205.0
2303	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/06/2012	4167	4024	4174.0	...	4215	3915	4022	4333	4444	4733	4284	4312	4292	88429.0
5841	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/15/2012	2484	1712	1458.0	...	4756	4614	4408	4088	4312	4310	3920	3943	4279	90426.0
5843	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/17/2012	2753	1876	1512.0	...	4777	4506	4554	4388	4799	4220	4139	4283	4577	90932.0
5883	372	154454	CROSS BRONX EXPRESSWAY	MARMION AVENUE	PROSPECT AVENUE	EB	02/08/2013	2371	1537	1307.0	...	5241	5122	5129	5258	4927	4828	4154	4005	3653	92754.0
5845	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/19/2012	2896	2350	1856.0	...	4493	4473	4697	4354	4404	4715	4278	3984	4730	93149.0
2302	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/05/2012	3761	2563	1839.0	...	4219	4038	4419	5199	5164	5102	4986	4447	4815	93212.0
2297	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	09/30/2012	4463	4489	4818.0	...	4855	4603	4774	4968	5010	4565	4121	3624	2816	94114.0
5846	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/20/2012	4147	3494	3061.0	...	5311	4331	3291	2361	1685	1883	2814	3834	4604	96944.0
5840	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/14/2012	4200	3722	3304.0	...	4841	4613	4530	4399	4365	4426	4207	3942	3352	97815.0
5842	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/16/2012	3199	2396	1721.0	...	5238	5558	5032	4879	4903	4639	4640	4354	3621	97958.0
5844	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/18/2012	3603	2606	1819.0	...	5036	5907	5081	4509	5122	4836	4569	4159	3670	98013.0
5847	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/21/2012	4169	3641	3202.0	...	5221	4684	4660	4334	4368	4371	4037	3812	3452	98793.0
5838	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/12/2012	4002	2950	2086.0	...	5266	5296	5018	4817	4862	4569	4546	4468	3440	100101.0
5839	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/13/2012	3058	3385	2919.0	...	6016	5612	5500	5087	4679	4643	4258	4407	4614	110057.0

<p>5945 rows × 32 columns</p>

In [16]:

t.drop(time_columns,axis=1, inplace=True)

In [17]:

t[:5]

Out[17]:

	ID	Segment ID	Roadway Name	From	To	Direction	Date	sum_count
0	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/02/2013	5571.0
1	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/03/2013	4268.0
2	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/04/2013	5931.0
3	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/05/2013	6242.0
4	1	2153	HUGUENOT AVE	WOODROW RD	STAFFORD AVE	NB	02/06/2013	5670.0

In [18]:

t.sort_values('sum_count', ascending=False)[:10]

Out[18]:

	ID	Segment ID	Roadway Name	From	To	Direction	Date	sum_count
5839	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/13/2012	110057.0
5838	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/12/2012	100101.0
5847	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/21/2012	98793.0
5844	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/18/2012	98013.0
5842	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/16/2012	97958.0
5840	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/14/2012	97815.0
5846	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/20/2012	96944.0
2297	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	09/30/2012	94114.0
2302	160	34659	2 AVENUE	EAST 27 STREET	EAST 28 STREET	SB	10/05/2012	93212.0
5845	368	192292	2 AVENUE	EAST 59 STREET	EAST 60 STREET	SB	10/19/2012	93149.0

In [19]:

table = t.sort_values('sum_count', ascending=False)

In [20]:

from os.path import join
target_path = join(target_folder, 'sorted-counts.csv')
table.to_csv(target_path, index=False)
print('count_table_path = %s'%target_path)

count_table_path = /tmp/sorted-counts.csv

In [ ]:

In [21]:

filtered_table = t[t['sum_count'] >= minimum_vehicle_count]

In [22]:

table2 = filtered_table.sort_values('sum_count', ascending=False)

In [24]:

from os.path import join
target_path = join(target_folder, 'sorted-counts2.csv')
table2.to_csv(target_path, index=False)
print('table2_table_path = %s'%target_path)

table2_table_path = /tmp/sorted-counts2.csv

In [ ]:

Pay Notebook Creator: Ning Wei	0
Set Container: Numerical CPU with TINY Memory for 10 Minutes	0
Total	0

income and FHV trips per region 20190213-2048