CRT 2017 #33 · Communication to the City Council · Jun 12 2017

State of Cambridge's Open Data Program A presentation for the June 1 NLTP Committee Meeting

Background on Open Data • Data as a public good • In service of improving city services: • Transparency and Engagement • Efficiency and Effectiveness • Innovation and Collaboration • Accessible and flexible: • Machine readable • Free and without constraints on use • Centralized location • Must balance openness with Open Data: privacy Big Impact

Civic Innovation & Data Analytics User Outreach and Engagement Data Governance and Privacy Data Supply and Automation

Civic Innovation & Data Analytics User Outreach and Engagement Data Governance and Privacy Data Supply and Automation

We've had a steady increase in the number of datasets posted to the portal CUMULATIVE UPLOADS 250 200 [phone removed]-01 2015-11 2014-12 2015-03 2015-05 2015-07 2015-10 2015-12 2016-07 2016-08 2016-09 2016-10 2016-12 2017-02 2017-03 2016-06 2015-06 2016-05 2015-08 2015-09 2017-04 2015-04 2016-03 2015-02 2016-02 2017-01 2016-04 2014-11 2016-01 2016-11 2014-01 2013-09 2014-10 2013-11 2014-09 2014-04 2014-05 2014-08 2013-12 2014-07 2014-03 2014-06 2013-10 2014-02

We are also steadily increasing the number of datasets that are automatically updated ur |=paste (FTPsite, yesterday, Sensor , sep="") City of Cambridge destination="yesterday.CSV" download.file(url, destination) ### Read in and format CSV file Cyclists Today 3T1 data=read. csv("yesterday. csv", header=FALSE) data-data[-C(1,2,3),1:5] colnames (data)=c ("Date" , "Total", "westb . "Time" "eotal", "westh datasdate=as.Date(dataśDate,"%d. %m.%Y") datasday=weekdays (data$Date) dataSDateTime=as.POSIXct (paste(datasDate, data , "Date" , "Time" data=data[,c("DateTime" , "Day" velists this Year

Civic Innovation & Data Analytics User Outreach and Engagement Data Governance and Privacy Data Supply and Automation

With help from Bloomberg What Works Cities, we created a robust review process for new datasets Program manager and/or coordinator Minor mitigatesrisks Data coordinator Program manager sensitivity QA's dataset uploads dataset concerns Dataset shared Risks Dataset with City Manager mitigated identified for Ог... for sensitivity upload review No sensitivity Data coordinator Program manager concerns QA's dataset uploads dataset Dataset Significant sensitivity concerns Overwhelming sensitivity Made concerns Public Dataset shared Dataset shared Dataset OK'd with Data Review with Law Dept. for Dataset Discarded Board for Dataset OK'd sensitivity review senstivity review Overwhelming sensitivity concerns Upload Stage Sensitivity Review Risk Mitigation QA Stage Publication

We put together a great team for the ODRB, which had its first meeting in June 2016 ASTOVES DONATA Oambridgema.gov Home City Departments City Manager Permits & Applications Home > City Calendar > Open Data Review Board Meeting Open Data Review Board Meeting 5:30 PM - 8:00 PM Thursday, October 6, 2016 Location: City Hall 795 Massachusetts Ave. 2nd Floor Ackermann Room Cambridge Title: Open Data Review Board Meeting Time: Thursday, October 6, 2016, from 5:30PM to 8PM Place: Ackerman Room ODRB Agenda (PDF)

Civic Innovation & Data Analytics User Outreach and Engagement Data Governance and Privacy Data Supply and Automation

Cambridge's open data portal averages approximately 5000 visitors each month Jan 1, 2017 - Jun 1, 2017 Site Analytics Browser Page Views + 26,236 3,318,535

City of Cambridge is one of the most active civic partners at Code For Boston CODE for BOSTON Thank you!'

ITD's summer 2016 open data research fellow identified several ways to improve data-driven civic innovation AUGUST 2016 1. Cities must build capacity among staff to use data analysis tools and AMPLIFYING CIVIC INNOVATION 2. Subject matter experts within city Community Engagement Strategies for Open Data Collaborations government must take the lead on A joint repart by Jennifer Angarita, Ash Center Summer Fellow in Innovation, and the City of Cambridge problem identification. 3. Cities must find ways to showcase successful data projects in order incentivize future work. Bonus: Cambridge is gaining a reputation as a leader in this space Page 1 -Report by Jennifer Angarita

Data office hours and dashboard training Power Bl KEEP Dashboard in an Hour by Power BI Team, Microsoft CALM AND COME TO OFFICE $85.22M $2.22M HOURS wwwwwww

We launched the civic innovation challenge inventory in winter 2017 SUBSCRIBE statescoop NEWS • EVENTS TV/RADIO PEOPLE SUBSO Civic Innovation Challenge Inventory pairs problems with open data in Cambridge The city is showing civic hackers where the city needs help by compiling a list of data sets that can be used to build useful apps and tools. By Jason Shueh MAY 30, 2017 12:38 PM BIO Y

And it's already leading to a significant improvement in community engagement About Dashboard <› Source Code Cambridge Board of Zoning Appeals Requests 2015-2017 192 Variances 114 Special Permits 65 Variance / Special permits Year Status Type of request • 2015 • 2016 • 2017 • Special Permit • Variance • Variance/Special Permit • Approved • Denied • Pending Payment • Under Pre-Review • Under Review • Withdrawn channing Ka deacham St Row Baker S Mall S Island End Ried Donahy P CommonS! Spring Hin Sturtevany sy Somerville Washington St Mystic Ryect Pearl St Fresh Pond Massport Dalton Rd 8B30OT Bunker Hill Washington St Medford St. Lewis Ra Popla Main St Joy St Bunker Hill Sta Payson Ra Meetinghouse Hill concord Ave Linwood Si Oaklau Country Club 10K Charlestown Mount Cambridge Auburn Soldiers School Si Cemetery Field Mr. Aubumn St Harvard Boyisto, 5 Athletic Area Cambridge Domenik Flippello & Commerci Putnam St University Melendy Ave Solders a Rogers St? • Playground Fall o Sumner Tunnel: -Dashboard by Kent Johnson

We went live with our new website and showcase last week Search High Contrast 50° @ Quick Links a) Subscribe * Translate Q OPEN DATA ITY O CAMBRIDGE SERVICES I WANT TO DEPARTMENTS SHARE f 8+ Contact Open Data Portal News and Announcements Get Involved Other Resources › Request a Dataset › Green Building Article 22 Data › GIS Data Search all the datasets on the GIS department Request a dataset from the City of Cambridge Check out our new open dataset and data dashboard on green buildings in Cambridge. › Open Budget › Contact Us › New Development Log Data The open budget dataset Contact us about a question, idea, or event Take a look at our most recent data on major development › Open Data Ordinance in Cambridge

Civic Innovation & Data Analytics User Outreach and Engagement Data Governance and Privacy Data Supply and Automation

We're building data dashboards to aid in decision making and to make data more engaging for residents 100% Benefits Total Employees • Eligible Employee Demographics (Beta) • Not Eligible Last Updated: January 24, 2017 1571 •E gible Employment by Gender Employment by Ethnicity • Black •Hispanic Gender •Female • Asian • Not Specified • Male •White Ethnicity •American Indian Union 66% • Non-Union| 16% 3% 71% 33% 7% • Union 34% 4J72 100% •Non Unian @Union 20% 80% Ethnicity Median Salary Average Salary Min Salary Median Salary Average Salary Max Salary Gender Min Salary EEO-4-Cat Max Salary Female $66,128 $22,593 Asian $187,811 $31,200 $59,391 $72,370 $60,939 $226,000 • Administrative Support $27,105 Male $27,105 $60,939 $66,391 $292,500 White $68,426 $292,500 $60,939 • Official/Administrator Total $292,500 $32,336 Not Specified $22,593 $66,303 $60,939 $67,895 $64,166 $131.607 • Paraprofessional Black $22,593 $59,187 $58,675 $121,330 • Professional $58,463 Hispanic $58,675 $136,369 $36,245 • Protective Services Salary and Gender $46,033 American Indian $33,177 $49,786 $55,136 • Service Maintenance Total $66.303 $292,500 $22,593 $60.939 • Skilled Craft 20-40k 58% 42% • Technical 34% 66% 40-60k Salary Ranges by Ethnicity 27% 73% 60-80k •60-80k • 80-100k •Over 100k Salary Band • 20-40k • 40-60k 35% 65% 80-100k 100% Over 100k 41% 59% 1012 0% 20% 60% 80% 50% Ethnicity and Gender 0% 33% 67% American Indian White Black Hispanic Asian American Indian Not Specified 45% 55% Asian Benefits •Eligible 34% Black 66% 100% Hispanic 33% 67% Not Spocified 38% 62% 100% 100% 50% 100% 100% 100% 100% 67% 33% White 0% 60% 40% 100% 0% 20% 80% Asian Block White Not Specified American Indian Hispanic Median Years Worked by Gender Number of Hires by Year Benefits •Eligible 100 Female Male 50 M 1950 1960 1970 1990 2010

We're building data dashboards to aid in decision making and to make data more engaging for residents Green Building Dashboard: Facts & Trends (2010 - Present) Community Development Department - City of Cambridge, MA How To Use This Page The data presented on this page can be viewed through any filter(s) of your choice: To view all residential buildings, simply click on the "Residential" slice in the "Primary Project Use" graph. The graphs and charts will adjust to only show data pertaining to "Residential" buildings. You can also hover over a different data points in any graph for some additional information. To exit out of that filter, click a second time on that slice. To view the data through more than one filter, hold down "CTRL" and then "click" the filters of your choice. Note: When using more than one filter, the data can only be sorted one graph at a time. For example, you cannot choose as filters both a primary use (i.e. "Residential"') and a neighborhood, (i.e. "North Cambridge") since they are in two different graphs. For any suggestions, questions/comments, or feedback please contact Wendell Joseph at [email protected] or [phone removed]. Figure 2 // Buildings by Gross Floor Area (GFA) Figure 1 // Primary Project Use Government 1- a) less than 25,000 3 Lab/R&D 3 — Hotel 4 - 14 b) 25,000 - 50,000 College/Univer... 5 c) 50,000 - 100,000 18 10 d) 100,000 - 200,000 14 e) 200,000 - 300,000 f) greater than 300,000 16 Office/R&D 17

We're also creating a foundation for more complex data analytics projects Alewife Brook banks Little Pond BALL SQUARE SS STRI WEST WINTER HILL LLE Clay Pit Pond EAST SOMERVILLE CHARLESTOWN EAST WATERTOWN File Edit Format View Help NORTH ("Station_Name", "Hydrant_ID", "Station_latlon' testDF$Time-rep(1, nrow(testDF)) testDF=na.omit(testDF) GOLD #testDF-as.data.frame(cbind(rep(Stations$ID,1 §latlon,10), test$latlon)); colnames(testDF)=c( DOCK SQU #testDF$Start=as.character(testDF$Start) #testDF$End-as.character(testDFSEnd) harles River #testDF$Time=rep(1, nrow(testDF)) FINANC ALLSTON for (i in 1:nrow(testDF)) DISTRIC Sys. sleep(0.5) testDF$Time[i]=sum(route(from=testDF$Static STORROW: DRIVE ['1) seconds) CHINATOWN BACK BA BRIGHTON MinList_testDF=aggregate(testDFSTime,by=list( (MinList_testDF)-c("Hydrant_ID", "MinTime") testDF$Station_Name=as.character(testDF$Station_Name) testDF=left_join(x=testDF,y=MinList_testDF, by="Hydrant_ID") testDF$FastestStation-ifelse(testDF$Time==testDF$MinTime, testDF$Station_Name, "x") Assignments=tilter(testor, rasteststation!= ×» Assignments=unique(Assignments) Assignments-left join(Assignments,Hydrants, by-"Hydrant_ID") Assignments$FastestStation=as.factor(Assignments$FastestStation)

... and relatively simple projects that nevertheless help us deliver city services more efficiently and create a market for open data solarList=read.csv("Solar. csv") PropDatabase=read.csv("PropDatabase.csv") solarList$Zip=paste(''',Ø, solarList$Zip, sep = ''") PropDatabase Address=PropDatabase$Owner_Address PropDatabase$Owner_Address=NULL | solarListOut=left_join(solarList,PropDatabase,by="Address") write. csv(solarListOut,file="SolarListOut.csv") PropertyDatabase Solar _Database .xlsx .x/5x SolarMailingList X/5X

Plans for the coming year...

Plans for the coming year... • Continue building out Cambridge's data analysis and visualization toolkit and know-how R whil Shiny,

Plans for the coming year... • Drive increased engagement by better aligning open data activities with Cambridge's strategic initiatives. ENTERING VISION ZERO CAMBRIDGE On the Road to Equal Say ENVISION CAMBRIDGE pb cambridge THE PATH TO A City of Cambridge Every Minute Counts: NET ZERO Fighting the Opioid STEAM (Science, Technology, Engineering, Arts, and Math) Epidemic in Cambridge CAMBRIDGE Initiative

Plans for the coming year.. • Identify pilot opportunities to test new tools, techniques, and processes for analytics and internal data sharing related to IOT data.

Thank you!