Smart date inferencing
jqnatividad opened this issue · 0 comments
With qsv stats
we collect descriptive statistics when we infer each column's data type during the Analysis phase of a DP+ job.
For example, using the benchmark data from qsv based on a 1M row , 512 mb, 41 column sample of NYC's 311 data, the command:
$ qsv index .\NYC_311_SR_2010-2020-sample-1M.csv
$ qsv stats .\NYC_311_SR_2010-2020-sample-1M.csv > nyc311stats-simple.csv
yields the file below in 0.27 seconds:
field ,type ,sum ,min ,max ,min_length ,max_length ,mean ,stddev ,variance
Unique Key ,Integer ,32687965858032 ,11465364 ,48478173 ,8 ,8 ,32687965.858031966 ,9013895.335828971 ,81250309125279.27
Created Date ,String , ,01/01/2010 01:05:51 PM ,12/31/2019 12:58:50 PM ,22 ,22 , , ,
Closed Date ,String , ,01/01/1900 12:00:00 AM ,12/31/2019 12:59:00 PM ,0 ,22 , , ,
Agency ,String , ,3-1-1 ,TLC ,3 ,42 , , ,
Agency Name ,String , ,3-1-1 ,Valuation Policy ,3 ,82 , , ,
Complaint Type ,String , ,../../WEB-INF/web.xml;x= ,ZTESTINT ,3 ,41 , , ,
Descriptor ,String , ,1 Missed Collection ,unknown odor/taste in drinking water (QA6) ,0 ,80 , , ,
Location Type ,String , ,"1-, 2- and 3- Family Home" ,Wooded Area ,0 ,36 , , ,
Incident Zip ,String , ,* ,XXXXX ,0 ,10 , , ,
Incident Address ,String , ,* * ,west 155 street and edgecombe avenue ,0 ,55 , , ,
Street Name ,String , ,* ,wyckoff avenue ,0 ,55 , , ,
Cross Street 1 ,String , ,1 AVE ,mermaid ,0 ,32 , , ,
Cross Street 2 ,String , ,1 AVE ,surf ,0 ,35 , , ,
Intersection Street 1 ,String , ,1 AVE ,flatlands AVE ,0 ,35 , , ,
Intersection Street 2 ,String , ,1 AVE ,glenwood RD ,0 ,33 , , ,
Address Type ,String , ,ADDRESS ,PLACENAME ,0 ,12 , , ,
City ,String , ,* ,YORKTOWN HEIGHTS ,0 ,22 , , ,
Landmark ,String , ,1 AVENUE ,ZULETTE AVENUE ,0 ,32 , , ,
Facility Type ,String , ,DSNY Garage ,School District ,0 ,15 , , ,
Status ,String , ,Assigned ,Unspecified ,4 ,16 , , ,
Due Date ,String , ,01/01/2010 01:26:03 PM ,12/31/2018 12:59:20 PM ,0 ,22 , , ,
Resolution Description ,String , ,A DOB violation was issued for failing to comply with an existing Stop Work Order. ,"Your request was submitted to the Department of Homeless Services. The City?s outreach team will assess the homeless individual and offer appropriate assistance within 2 hours. If you asked to know the outcome of your request, you will get a call within 2 hours. No further status will be available through the NYC 311 App, 311, or 311 Online." ,0 ,934 , , ,
Resolution Action Updated Date ,String , ,01/01/2010 01:50:45 PM ,12/31/2019 12:58:00 PM ,0 ,22 , , ,
Community Board ,String , ,0 Unspecified ,Unspecified STATEN ISLAND ,8 ,25 , , ,
BBL ,Integer ,2082985217282449 ,0 ,5270000501 ,0 ,10 ,2751798943.2415347 ,1168122117.923852 ,1.3645092823829053e18
Borough ,String , ,BRONX ,Unspecified ,5 ,13 , , ,
X Coordinate (State Plane) ,Integer ,919555108413 ,913281 ,1067220 ,0 ,7 ,1005337.5451259619 ,22512.45281021959 ,506810531.5323639
Y Coordinate (State Plane) ,Integer ,188099299101 ,121152 ,271876 ,0 ,6 ,205646.49782053265 ,31723.198493763975 ,1006361322.674749
Open Data Channel Type ,String , ,MOBILE ,UNKNOWN ,5 ,7 , , ,
Park Facility Name ,String , ,"""Uncle"" Vito F. Maranzano Glendale Playground" ,Zimmerman Playground ,3 ,82 , , ,
Park Borough ,String , ,BRONX ,Unspecified ,5 ,13 , , ,
Vehicle Type ,String , ,Ambulette / Paratransit ,Green Taxi ,0 ,23 , , ,
Taxi Company Borough ,String , ,BRONX ,Staten Island ,0 ,13 , , ,
Taxi Pick Up Location ,String , ,1 5 AVENUE MANHATTAN ,YORK AVENUE AND EAST 70 STREET ,0 ,60 , , ,
Bridge Highway Name ,String , ,145th St. Br - Lenox Ave ,Willis Ave Br - 125th St/1st Ave ,0 ,42 , , ,
Bridge Highway Direction ,String , ,Bronx Bound ,Westbound/To Goethals Br ,0 ,33 , , ,
Road Ramp ,String , ,N/A ,Roadway ,0 ,7 , , ,
Bridge Highway Segment ,String , ,1-1-1265963747 ,Wythe Ave/Kent Ave (Exit 31) ,0 ,100 , , ,
Latitude ,Float ,30355391.760447357 ,40.1123853 ,40.9128688 ,0 ,18 ,40.72881808178842 ,0.0893143967633158 ,0.007977061469194996
Longitude ,Float ,-55100392.94986465 ,-77.5195844 ,-73.7005968 ,0 ,18 ,-73.92999235194249 ,0.16351835417538158 ,0.026738252152225528
Location ,String , ,"(40.1123853, -77.5195844)" ,"(40.9128688, -73.9024731)" ,0 ,40 , , ,
Adding the --everything
and --infer-dates
options...
$ qsv stats --everything --infer-dates .\NYC_311_SR_2010-2020-sample-1M.csv > nyc311stats.-everything-inferdates.csv
yields the file below in 103.89 seconds. More than 3 orders of magnitude slower!
field ,type ,sum ,min ,max ,min_length ,max_length ,mean ,stddev ,variance ,lower_fence ,q1 ,q2_median ,q3 ,iqr ,upper_fence ,skew ,mode ,cardinality ,nullcount
Unique Key ,Integer ,32687965858032 ,11465364 ,48478173 ,8 ,8 ,32687965.85803196 ,9013895.335828971 ,81250309125279.27 ,2803282.25 ,25245773.0 ,32853358.5 ,40207433.5 ,14961660.5 ,62649924.25 ,-0.055045893858106744 , ,1000000 ,0
Created Date ,DateTime , ,2010-01-01 05:00:00 UTC ,2020-12-23 06:25:51 UTC ,22 ,22 , , , , , , , , , , ,01/24/2013 12:00:00 AM ,841014 ,0
Closed Date ,DateTime , ,1900-01-01 05:00:00 UTC ,2100-01-01 05:00:00 UTC ,0 ,22 , , , , , , , , , , , ,688837 ,28619
Agency ,String , ,3-1-1 ,TLC ,3 ,42 , , , , , , , , , , ,NYPD ,28 ,0
Agency Name ,String , ,3-1-1 ,Valuation Policy ,3 ,82 , , , , , , , , , , ,New York City Police Department ,553 ,0
Complaint Type ,String , ,../../WEB-INF/web.xml;x= ,ZTESTINT ,3 ,41 , , , , , , , , , , ,Noise - Residential ,287 ,0
Descriptor ,String , ,1 Missed Collection ,unknown odor/taste in drinking water (QA6) ,0 ,80 , , , , , , , , , , ,Loud Music/Party ,1392 ,3001
Location Type ,String , ,"1-, 2- and 3- Family Home" ,Wooded Area ,0 ,36 , , , , , , , , , , ,RESIDENTIAL BUILDING ,162 ,239131
Incident Zip ,String , ,* ,XXXXX ,0 ,10 , , , , , , , , , , , ,535 ,54978
Incident Address ,String , ,* * ,west 155 street and edgecombe avenue ,0 ,55 , , , , , , , , , , , ,341996 ,174700
Street Name ,String , ,* ,wyckoff avenue ,0 ,55 , , , , , , , , , , , ,14837 ,174720
Cross Street 1 ,String , ,1 AVE ,mermaid ,0 ,32 , , , , , , , , , , , ,16238 ,320401
Cross Street 2 ,String , ,1 AVE ,surf ,0 ,35 , , , , , , , , , , , ,16486 ,323644
Intersection Street 1 ,String , ,1 AVE ,flatlands AVE ,0 ,35 , , , , , , , , , , , ,11237 ,767422
Intersection Street 2 ,String , ,1 AVE ,glenwood RD ,0 ,33 , , , , , , , , , , , ,11674 ,767709
Address Type ,String , ,ADDRESS ,PLACENAME ,0 ,12 , , , , , , , , , , ,ADDRESS ,6 ,125802
City ,String , ,* ,YORKTOWN HEIGHTS ,0 ,22 , , , , , , , , , , ,BROOKLYN ,382 ,61963
Landmark ,String , ,1 AVENUE ,ZULETTE AVENUE ,0 ,32 , , , , , , , , , , , ,5915 ,912779
Facility Type ,String , ,DSNY Garage ,School District ,0 ,15 , , , , , , , , , , ,N/A ,6 ,145478
Status ,String , ,Assigned ,Unspecified ,4 ,16 , , , , , , , , , , ,Closed ,10 ,0
Due Date ,DateTime , ,1900-01-02 05:00:00 UTC ,2021-06-17 20:34:13 UTC ,0 ,22 , , , , , , , , , , , ,345077 ,647794
Resolution Description ,String , ,A DOB violation was issued for failing to comply with an existing Stop Work Order. ,"Your request was submitted to the Department of Homeless Services. The City?s outreach team will assess the homeless individual and offer appropriate assistance within 2 hours. If you asked to know the outcome of your request, you will get a call within 2 hours. No further status will be available through the NYC 311 App, 311, or 311 Online." ,0 ,934 , , , , , , , , , , ,The Police Department responded to the complaint and with the information available observed no evidence of the violation at that time. ,1216 ,20480
Resolution Action Updated Date ,DateTime , ,2009-12-31 06:35:00 UTC ,2020-12-23 11:56:14 UTC ,0 ,22 , , , , , , , , , , , ,690314 ,15072
Community Board ,String , ,0 Unspecified ,Unspecified STATEN ISLAND ,8 ,25 , , , , , , , , , , ,0 Unspecified ,77 ,0
BBL ,Integer ,2082985217282449 ,0 ,5270000501 ,0 ,10 ,2751798943.241534 ,1168122117.9238517 ,1.364509282382905e18 ,-941195045.5 ,2028310001.0 ,3019480063.0 ,4007980032.0 ,1979670031.0 ,6977485078.5 ,-0.6874652461017321 , ,268383 ,243046
Borough ,String , ,BRONX ,Unspecified ,5 ,13 , , , , , , , , , , ,BROOKLYN ,6 ,0
X Coordinate (State Plane) ,Integer ,919555108413 ,913281 ,1067220 ,0 ,7 ,1005337.5451259615 ,22512.45281021959 ,506810531.5323639 ,956616.5 ,993572.0 ,1004546.0 ,1018209.0 ,24637.0 ,1055164.5 ,0.105480970816589 , ,102556 ,85327
Y Coordinate (State Plane) ,Integer ,188099299101 ,121152 ,271876 ,0 ,6 ,205646.49782053265 ,31723.19849376398 ,1006361322.6747493 ,103334.0 ,182411.0 ,202514.0 ,235129.0 ,52718.0 ,314206.0 ,0.29623410966726027 , ,116092 ,85327
Open Data Channel Type ,String , ,MOBILE ,UNKNOWN ,5 ,7 , , , , , , , , , , ,PHONE ,5 ,0
Park Facility Name ,String , ,"""Uncle"" Vito F. Maranzano Glendale Playground" ,Zimmerman Playground ,3 ,82 , , , , , , , , , , ,Unspecified ,1889 ,0
Park Borough ,String , ,BRONX ,Unspecified ,5 ,13 , , , , , , , , , , ,BROOKLYN ,6 ,0
Vehicle Type ,String , ,Ambulette / Paratransit ,Green Taxi ,0 ,23 , , , , , , , , , , , ,5 ,999652
Taxi Company Borough ,String , ,BRONX ,Staten Island ,0 ,13 , , , , , , , , , , , ,11 ,999156
Taxi Pick Up Location ,String , ,1 5 AVENUE MANHATTAN ,YORK AVENUE AND EAST 70 STREET ,0 ,60 , , , , , , , , , , , ,1903 ,992129
Bridge Highway Name ,String , ,145th St. Br - Lenox Ave ,Willis Ave Br - 125th St/1st Ave ,0 ,42 , , , , , , , , , , , ,68 ,997711
Bridge Highway Direction ,String , ,Bronx Bound ,Westbound/To Goethals Br ,0 ,33 , , , , , , , , , , , ,50 ,997691
Road Ramp ,String , ,N/A ,Roadway ,0 ,7 , , , , , , , , , , , ,4 ,997693
Bridge Highway Segment ,String , ,1-1-1265963747 ,Wythe Ave/Kent Ave (Exit 31) ,0 ,100 , , , , , , , , , , , ,937 ,997556
Latitude ,Float ,30355391.760447353 ,40.1123853 ,40.9128688 ,0 ,18 ,40.72881808178841 ,0.08931439676331582 ,0.007977061469195 ,40.46458052499999 ,40.6677055 ,40.7221652 ,40.80312215 ,0.13541665000000336 ,41.006247125 ,0.2234650413429593 , ,353694 ,254695
Longitude ,Float ,-55100392.949864656 ,-77.5195844 ,-73.7005968 ,0 ,18 ,-73.92999235194247 ,0.16351835417538158 ,0.02673825215222553 ,-74.11194174999999 ,-73.970536 ,-73.9279455 ,-73.8762655 ,0.09427049999999326 ,-73.73485975000001 ,-0.03755270078620233 , ,353996 ,254695
Location ,String , ,"(40.1123853, -77.5195844)" ,"(40.9128688, -73.9024731)" ,0 ,40 , , , , , , , , , , , ,375772 ,254695
while the command:
qsv stats --everything .\NYC_311_SR_2010-2020-sample-1M.csv > nyc311stats-everything.csv
yields the file below in only 3.60 seconds. The only difference being we didn't use the --infer-dates
option and date fields and their min/max values are treated as String
s.
field ,type ,sum ,min ,max ,min_length ,max_length ,mean ,stddev ,variance ,lower_fence ,q1 ,q2_median ,q3 ,iqr ,upper_fence ,skew ,mode ,cardinality ,nullcount
Unique Key ,Integer ,32687965858032 ,11465364 ,48478173 ,8 ,8 ,32687965.858031962 ,9013895.335828971 ,81250309125279.27 ,2803282.25 ,25245773.0 ,32853358.5 ,40207433.5 ,14961660.5 ,62649924.25 ,-0.0550458938581055 , ,1000000 ,0
Created Date ,String , ,01/01/2010 01:05:51 PM ,12/31/2019 12:58:50 PM ,22 ,22 , , , , , , , , , , ,01/24/2013 12:00:00 AM ,841014 ,0
Closed Date ,String , ,01/01/1900 12:00:00 AM ,12/31/2019 12:59:00 PM ,0 ,22 , , , , , , , , , , , ,688837 ,28619
Agency ,String , ,3-1-1 ,TLC ,3 ,42 , , , , , , , , , , ,NYPD ,28 ,0
Agency Name ,String , ,3-1-1 ,Valuation Policy ,3 ,82 , , , , , , , , , , ,New York City Police Department ,553 ,0
Complaint Type ,String , ,../../WEB-INF/web.xml;x= ,ZTESTINT ,3 ,41 , , , , , , , , , , ,Noise - Residential ,287 ,0
Descriptor ,String , ,1 Missed Collection ,unknown odor/taste in drinking water (QA6) ,0 ,80 , , , , , , , , , , ,Loud Music/Party ,1392 ,3001
Location Type ,String , ,"1-, 2- and 3- Family Home" ,Wooded Area ,0 ,36 , , , , , , , , , , ,RESIDENTIAL BUILDING ,162 ,239131
Incident Zip ,String , ,* ,XXXXX ,0 ,10 , , , , , , , , , , , ,535 ,54978
Incident Address ,String , ,* * ,west 155 street and edgecombe avenue ,0 ,55 , , , , , , , , , , , ,341996 ,174700
Street Name ,String , ,* ,wyckoff avenue ,0 ,55 , , , , , , , , , , , ,14837 ,174720
Cross Street 1 ,String , ,1 AVE ,mermaid ,0 ,32 , , , , , , , , , , , ,16238 ,320401
Cross Street 2 ,String , ,1 AVE ,surf ,0 ,35 , , , , , , , , , , , ,16486 ,323644
Intersection Street 1 ,String , ,1 AVE ,flatlands AVE ,0 ,35 , , , , , , , , , , , ,11237 ,767422
Intersection Street 2 ,String , ,1 AVE ,glenwood RD ,0 ,33 , , , , , , , , , , , ,11674 ,767709
Address Type ,String , ,ADDRESS ,PLACENAME ,0 ,12 , , , , , , , , , , ,ADDRESS ,6 ,125802
City ,String , ,* ,YORKTOWN HEIGHTS ,0 ,22 , , , , , , , , , , ,BROOKLYN ,382 ,61963
Landmark ,String , ,1 AVENUE ,ZULETTE AVENUE ,0 ,32 , , , , , , , , , , , ,5915 ,912779
Facility Type ,String , ,DSNY Garage ,School District ,0 ,15 , , , , , , , , , , ,N/A ,6 ,145478
Status ,String , ,Assigned ,Unspecified ,4 ,16 , , , , , , , , , , ,Closed ,10 ,0
Due Date ,String , ,01/01/2010 01:26:03 PM ,12/31/2018 12:59:20 PM ,0 ,22 , , , , , , , , , , , ,345077 ,647794
Resolution Description ,String , ,A DOB violation was issued for failing to comply with an existing Stop Work Order. ,"Your request was submitted to the Department of Homeless Services. The City?s outreach team will assess the homeless individual and offer appropriate assistance within 2 hours. If you asked to know the outcome of your request, you will get a call within 2 hours. No further status will be available through the NYC 311 App, 311, or 311 Online." ,0 ,934 , , , , , , , , , , ,The Police Department responded to the complaint and with the information available observed no evidence of the violation at that time. ,1216 ,20480
Resolution Action Updated Date ,String , ,01/01/2010 01:50:45 PM ,12/31/2019 12:58:00 PM ,0 ,22 , , , , , , , , , , , ,690314 ,15072
Community Board ,String , ,0 Unspecified ,Unspecified STATEN ISLAND ,8 ,25 , , , , , , , , , , ,0 Unspecified ,77 ,0
BBL ,Integer ,2082985217282449 ,0 ,5270000501 ,0 ,10 ,2751798943.2415357 ,1168122117.9238517 ,1.3645092823829048e18 ,-941195045.5 ,2028310001.0 ,3019480063.0 ,4007980032.0 ,1979670031.0 ,6977485078.5 ,-0.6874652461017284 , ,268383 ,243046
Borough ,String , ,BRONX ,Unspecified ,5 ,13 , , , , , , , , , , ,BROOKLYN ,6 ,0
X Coordinate (State Plane) ,Integer ,919555108413 ,913281 ,1067220 ,0 ,7 ,1005337.5451259618 ,22512.45281021959 ,506810531.5323639 ,956616.5 ,993572.0 ,1004546.0 ,1018209.0 ,24637.0 ,1055164.5 ,0.10548097081662003 , ,102556 ,85327
Y Coordinate (State Plane) ,Integer ,188099299101 ,121152 ,271876 ,0 ,6 ,205646.49782053265 ,31723.19849376398 ,1006361322.6747493 ,103334.0 ,182411.0 ,202514.0 ,235129.0 ,52718.0 ,314206.0 ,0.29623410966726027 , ,116092 ,85327
Open Data Channel Type ,String , ,MOBILE ,UNKNOWN ,5 ,7 , , , , , , , , , , ,PHONE ,5 ,0
Park Facility Name ,String , ,"""Uncle"" Vito F. Maranzano Glendale Playground" ,Zimmerman Playground ,3 ,82 , , , , , , , , , , ,Unspecified ,1889 ,0
Park Borough ,String , ,BRONX ,Unspecified ,5 ,13 , , , , , , , , , , ,BROOKLYN ,6 ,0
Vehicle Type ,String , ,Ambulette / Paratransit ,Green Taxi ,0 ,23 , , , , , , , , , , , ,5 ,999652
Taxi Company Borough ,String , ,BRONX ,Staten Island ,0 ,13 , , , , , , , , , , , ,11 ,999156
Taxi Pick Up Location ,String , ,1 5 AVENUE MANHATTAN ,YORK AVENUE AND EAST 70 STREET ,0 ,60 , , , , , , , , , , , ,1903 ,992129
Bridge Highway Name ,String , ,145th St. Br - Lenox Ave ,Willis Ave Br - 125th St/1st Ave ,0 ,42 , , , , , , , , , , , ,68 ,997711
Bridge Highway Direction ,String , ,Bronx Bound ,Westbound/To Goethals Br ,0 ,33 , , , , , , , , , , , ,50 ,997691
Road Ramp ,String , ,N/A ,Roadway ,0 ,7 , , , , , , , , , , , ,4 ,997693
Bridge Highway Segment ,String , ,1-1-1265963747 ,Wythe Ave/Kent Ave (Exit 31) ,0 ,100 , , , , , , , , , , , ,937 ,997556
Latitude ,Float ,30355391.760447357 ,40.1123853 ,40.9128688 ,0 ,18 ,40.72881808178842 ,0.0893143967633158 ,0.007977061469194998 ,40.46458052499999 ,40.6677055 ,40.7221652 ,40.80312215 ,0.13541665000000336 ,41.006247125 ,0.223465041343198 , ,353694 ,254695
Longitude ,Float ,-55100392.94986466 ,-77.5195844 ,-73.7005968 ,0 ,18 ,-73.92999235194246 ,0.16351835417538155 ,0.02673825215222552 ,-74.11194174999999 ,-73.970536 ,-73.9279455 ,-73.8762655 ,0.09427049999999326 ,-73.73485975000001 ,-0.03755270078594161 , ,353996 ,254695
Location ,String , ,"(40.1123853, -77.5195844)" ,"(40.9128688, -73.9024731)" ,0 ,40 , , , , , , , , , , , ,375772 ,254695
Clearly, --infer-dates
is a very expensive operation, and understandably so, since qsv's date parser engine has to parse and recognize 15 different date formats, with each format having several permutations.
Currently, DP+ uses the --infer-dates
option during its analysis phase, which is something I'd still like to keep as its very useful when it does infer a column is a date field.
Perhaps, we should only attempt to infer dates when a quick initial scan of the CSV headers suggest the presence of a date field (i.e. search for the presence of "date", "time", "timestamp", "datetime" anywhere in a column name)?