We Bike Retrospective Results from a MultiYear EBike

We. Bike Retrospective: Results from a Multi-Year E-Bike Study Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Agenda • We. Bike study overview • Results of the data analysis • Lessons learned 2 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Part 1 Overview 3 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

We. Bike • Summer 2014 – Summer 2017 • 31 sensor-equipped electric bicycles (e-bikes) • Over 150 GB of sensor data GPS, acceleration, temperature, battery charge/discharge current, voltage… 4 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Collection • Autonomous data recording sampling rate: 1/min 1/sec 3 min 5 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Architecture 6 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Part 2 Results 7 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Contribution • Algorithms to identify trips and charging sessions • Analysis of trip and battery charging statistics • Analysis of survey responses 8 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Trip & charging identification • Threshold activated • Merge trips with short breaks in-between (<5 min) • Discard trips when too short (<3 min) Time 9 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Trip statistics 10 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Trips – temporal distribution 11 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Trips – temporal distribution 13 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Trips – duration p-value of 3. 5· 10− 8 15 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Trips – speed p-value of 2. 3· 10− 14 17 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Charging statistics 18 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Charging – temporal distribution p-value of 0. 01 20 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Charging – state of charge p-value of 0. 049 22 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Anticipated vs actual riding SUMMER 23 WINTER We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Sentiment analysis 24 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Sentiment analysis 25 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Conclusion • No evidence for range anxiety • Participants rated regular bikes higher than e-bikes • No correlation between estimated and actual behavior • Study did not change participants’ sentiments • E-bikes did not (completely) replace card trips 26 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Part 3 Lessons Learned 27 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Contribution t s i l k c e h c t c e j o r p T o I Lessons learned & best practices for: • Data Collection n a e t a e r C • Extract-transform-load (ETL) • Data management ecosystem selection • Data quality issues • Sensor data stream analysis 28 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Most Important • DO A PILOT FIRST • ENABLE EASY SOFTWARE FIELD UPDATES 29 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Collection • Use variable sampling rates • Event-driven frequency change • Implement cooldown period sampling rate: 1/min 1/sec 3 min 30 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Collection • Include metadata with data • Data origin, e. g. CSV column names • Format version • Data types for each sensor 31 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Extract Transform Load • Never delete raw data • Log error messages 32 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Extract Transform Load • Ensure exactly once processing No duplicates! 33 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems • Benchmark • 1 week of simulated data for 1000 e-bikes • 1 simulated record per second per e-bike • 30 measurements per record • ~0. 5 k. B per record (raw data) ØOver 600 million data rows ØTotal of about 120 GB (in database) 34 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems 1 1000 inserts per second 35 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems 36 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems 37 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems 38 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems 39 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems 40 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Database Systems • It’s not just time series! • It’s not just about performance! 41 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Quality • Collect redundant data GPS discharge current (2 x) 42 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Quality • Pay attention to sensor precision low precision sensor range interesting event 43 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Quality • Be careful with zero values L L NU 0 44 04 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav 5

Data Analysis • Be careful with temporal partitioning Wednesday Thursday Trip data analysis 45 data analysis We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Data Analysis • Pay attention to sample size trip occurrences 46 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

Conclusion • http: //blizzard. cs. uwaterloo. ca/iss 4 e/wp-content/uploads/2017/06/checklist. pdf 47 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav

For more details [1] C. Gorenflo, I. Rios, L. Golab, and S. Keshav, “Usage Patterns of Electric Bicycles: An Analysis of the We. Bike Project, ” 2017. [2] C. Gorenflo, L. Golab, and S. Keshav, “Managing Sensor Data Streams: Lessons Learned from the We. Bike Project, ” Proc. 29 th Int. Conf. Sci. Stat. Database Manag. - SSDBM ’ 17, vol. 11, pp. 1– 11, 2017. 48 We. Bike Retrospective — Christian Gorenflo, Lukasz Golab, Srinivasan Keshav