Many questions about Philly311 API and website

[maneesha sane]

A few weeks ago, I started thinking about things I could do exploring Philly 311 data so I started exploring the API.  As I spent time exploring the data, I have a LOT of questions.  

• If I go to Open Data Philly I can see data through 2020; 2021 data is not listed here.  I can access 2021 data through the city's page (noting that the city's page is not accessible from the Open Data Philly page).  How does Open Data Philly keep its data up to date with source data? 
• Outside of an API request, is there a way to see data YTD for the current calendar year? 
• How does the data deal with timezones? For example, a request is datetime stamped as '2022-05-13T21:30:21Z'.  Does this mean that it came in at 17:30:21 local time?
• How can I find out the definitions of all the fields included? If I click on any field in the data described here I can see the data type (string, int, float, etc.) and get a mini SQL lesson but do not get any data definitions.  For example, does "expected_datetime" mean the date the user will get a response? Or the date the issue is expected to be resolved? 
• Is there a way to see when the issue was closed? Or do we assume that it's updated_datetime if status == closed? 
• Some fields seem to be have fixed valid options, such as "status" being one of 'Issue Resolved', 'Question Answered', 'Other', 'Information Provided'," (among others, and with variations in historical data).  There are no definitions of what each of those statuses mean.  What's the difference, for example,  between "Question Answered" and "Information provided"?
• There is a lot more information publicly available in the user interface for open requests than there is in the API.  Is there a reason that data is not available from the API? 
• Given that there is data available in the user interface that is not available in the API, and that I see no TOS in the web interface,  is web scraping the best option to get this data?
• The map in the user interface  has a key for color markers for submitted, received, in progress, completed.  But only submitted issues show on the map, and I can't find a way to get to the others.  Is there a way to see others on the web interface? here is a drop down that lets me add in the closed issues to the view, but I can't see the others at all, and I can't toggle between submitted and closed.  
• Also about the user interface - if I first go to this site it defaults to map view, and shows about 30 markers on the map.  If I then switch to list view, and start scrolling down,  I can see a LOT more submissions, and then when I switch back to map view, I see many more markers on the map too.  
• I started looking at the monthly reports.  It does not seem the city releases them on a predictable schedule.  For 2022, missing March.  For 2021, 12 months available.  For 2021, missing August, September, October.  Previous years seem complete.  

[Robert Cheetham]

Hi Maneesha. These are all great questions. I don't know the answers to any of them except for a couple of them:

• If I go to Open Data Philly I can see data through 2020; 2021 data is not listed here.  I can access 2021 data through the city's page (noting that the city's page is not accessible from the Open Data Philly page).  How does Open Data Philly keep its data up to date with source data? 

The City's Open Data team maintains the City's catalog entries in OpenDataPhilly.

• How can I find out the definitions of all the fields included? If I click on any field in the data described here I can see the data type (string, int, float, etc.) and get a mini SQL lesson but do not get any data definitions.  For example, does "expected_datetime" mean the date the user will get a response? Or the date the issue is expected to be resolved? 
  

There is more metadata available under the Metadata data item in the OpenDataPhilly entry - this points to the following page: 

https://metadata.phila.gov/#home/datasetdetails/5543864d20583086178c4e98/representationdetails/5762e19fa237544b2ecfe722/

Best,

Robert

------------------

Robert Cheetham

Azavea

--
You received this message because you are subscribed to the Google Groups "Open Data Philly" group.
To unsubscribe from this group and stop receiving emails from it, send an email to opendataphill...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/CALBzp3Xv2yZWi_eBx2rVzULVQ36tRKBd3ijZmL-ZRt9rJzSw7Q%40mail.gmail.com.

[maneesha sane]

Thanks Robert.  

You pointed to the metadata with data definitions - but that seems to be incomplete/inaccurate.

For example, the data includes the field "updated_datetime" but this field isn't listed here.  Or the "service_code" is listed as numeric but the data itself is a string.  Are "service_code" and "service_name" correlated 1:1? 

I know I had a lot of questions - I wasn't expecting any one person to answer them all.  If anyone has any information to help answer the other questions it would be much appreciated!

- Maneesha 

To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/CAGEj39n7BXh6c9E-4bdRQZntqNXsierVYC6%3D1T%2B7bBfCcevsGA%40mail.gmail.com.

[Aaron Couch]

The city does provide up-to-date data through this dashboard: https://data.phila.gov/visualizations/311-requests/ That data is available also through the API: https://phl.carto.com/api/v2/sql?q=SELECT%20*%20FROM%20public_cases_fc%20ORDER%20BY%20requested_datetime%20desc%20%20LIMIT%2010

Latest update dates are 5/31/22 :)

Looks like the City Data portal just doesn't explain it well. The most recent year is 2020 https://www.opendataphilly.org/dataset/311-service-and-information-requests/resource/a6b2bee7-ef2f-45e9-8b84-1dd88ac1c171 but you can get the same data for 2021 by updating the API request: https://phl.carto.com/api/v2/sql?filename=public_cases_fc&format=csv&skipfields=cartodb_id,the_geom,the_geom_webmercator&q=SELECT%20*%20FROM%20public_cases_fc%20WHERE%20requested_datetime%20%3E=%20%272021-01-01%27%20AND%20requested_datetime%20%3C%20%272022-01-01%27 or 2022 year to date: https://phl.carto.com/api/v2/sql?filename=public_cases_fc&format=csv&skipfields=cartodb_id,the_geom,the_geom_webmercator&q=SELECT%20*%20FROM%20public_cases_fc%20WHERE%20requested_datetime%20%3E=%20%272022-01-01%27%20AND%20requested_datetime%20%3C%20%272022-05-31%27

best,

-Aaron

[maneesha sane]

Hi Aaron,

I am aware of the link to visualizations you shared. I asked about a way to get YTD data for 2022 like I can see single year data for complete years other than the API.  

This was all prompted when I was curious about the lifespan of a Philly311 service request -- how much time elapses between the date a service request is opened and the date closed.  Given that there are still open requests from years ago about things like abandoned vehicles, I realize now that data is probably meaningless.  

I still remain interested in answers to questions I posed though!

- Maneesha

To view this discussion on the web visit https://groups.google.com/d/msgid/opendataphilly/6a94c3ed-7039-4c35-af4f-8fc2cf92fc08n%40googlegroups.com.

[Phil Cochetti]

Maneesha, 

Interesting thing about P311 requests "lifespan"... The times recorded on the P311 requests are only how long it takes for P311 to route them to the appropriate city department. Often times a new queue system is then entered where the time to action is different. For example, I put in a request to Philly311 last May to get a L&I inspection. The P311 requests were "closed" nearly immediately by Philly 311 because they had "completed" their task of forwarding it to L&I. 

With L&I however, the inspector would not arrive for another three months and to date many of the issues raised are still unaddressed. In the L&I system the new item opened was a "case investigation" and subsequently "violations" of the city code. These have their own datasets, however the time from Philly 311 submission to arrival of an inspector is not publicly available that I'm aware unless you know the relevant IDs to match them or they included publicly the address involved and there are few enough to match consistently. Other departments have other similar queues for say potholes at the Streets Department etc. 

This is one of the major issues I have with Phily 311. There appears to be little "real" accountability as this aspect of the system is obfuscated by the centrality that the city has placed on Philly 311 as the single point of contact. Once it's out of their hands, they have no insights into how long it will take or even if the department of interest is pursuing the case. I was told by L&I that when Philly 311 misroutes a case to the wrong section the case is just closed without forwarding it to the right section even within L&I. There are also fiefdoms and turf wars so if you ask for a pothole repair that is on a street that also has trolley tracks, SEPTA and the Streets Department will both deny its their responsibility because it's simultaneously too close to and also not close to the trolley tracks making it the other department's responsibility. 

It's kind of a big mess under the hood. Aspects of Philly 311 were completely down for about 5 months last year and not allowing a good number of users to sign in. No one admitted was a problem and they denied it even when I showed them how Twitter was full of complaints. I eventually figured out what the issue was after the vendor lied saying it was an error on Facebook's end. I sent them a guide about Facebook making changes to single sign-on authentication and pointed out there was notice months in advance. I never heard back from anyone, and two weeks later magically the errors suddenly disappeared. 

Point is, don't take anything at face value. Data dictionaries would be very nice and they have not been forthcoming. We just get told the "metadata" is enough when it doesn't in fact include information on many codes used in the datasets. 

Phil  

[kistin...@gmail.com]

Hi Maneesha,

Here's a few responses to some of your questions bulleted below:

• If I go to Open Data Philly I can see data through 2020; 2021 data is not listed here.  I can access 2021 data through the city's page (noting that the city's page is not accessible from the Open Data Philly page).  How does Open Data Philly keep its data up to date with source data? 

This is a very large dataset and we had the choice to either just make it available by API or create custom links so people can download it by year. We chose to try to increase accessibility with custom links giving a yearly cut of the data, but this also means manual effort so we appreciate you alerting us to the missing SHP for 2021! We'll add that information shortly. If of interest and easy for you, please note that you can customize the date ranges yourself towards the end of the url (unfortunately I can't seem to highlight those in this platform but look for 2020-01-01 and 2021-01-01 below). Any dataset we host in Carto has this ability: https://phl.carto.com/api/v2/sql?filename=public_cases_fc&format=shp&skipfields=cartodb_id&q=SELECT%20*%20FROM%20public_cases_fc%20WHERE%20requested_datetime%20%3E=%20%272020-01-01%27%20AND%20requested_datetime%20%3C%20%272021-01-01%27

Obviously, not every end user will feel comfortable making those changes so that's why we've also made the yearly links available. Please note that the metadata page you mentioned *is* available on the OpenDataPhilly (ODP) page, just at the very bottom. Since we have so many custom links on this page, we thought it judicious to not repeat the metadata link numerous times. 

• Outside of an API request, is there a way to see data YTD for the current calendar year? 

Perhaps I'm misunderstanding but the 2022 links for CSV or SHP should provide you this - it is set to show results from 01-01-2022 to 01-01-2023. Let me know if you meant something else: https://phl.carto.com/api/v2/sql?filename=public_cases_fc&format=csv&skipfields=cartodb_id,the_geom,the_geom_webmercator&q=SELECT%20*%20FROM%20public_cases_fc%20WHERE%20requested_datetime%20%3E=%20%272022-01-01%27%20AND%20requested_datetime%20%3C%20%272023-01-01%27

• How can I find out the definitions of all the fields included? If I click on any field in the data described here I can see the data type (string, int, float, etc.) and get a mini SQL lesson but do not get any data definitions.  For example, does "expected_datetime" mean the date the user will get a response? Or the date the issue is expected to be resolved? 

You can see a description of fields on this metadata page. If you still have questions about the fields, then reach out to the listed dataset contact: james...@phila.gov. I will also forward on the questions separately and see if they build out the metadata more. 

The the user interface link you shared returns an error message for me. Can you re-share? Is this from a City of Philadelphia website?

For the following questions, please email james...@phila.gov, the dataset contact listed in the metadata: 

• I started looking at the monthly reports.  It does not seem the city releases them on a predictable schedule.  For 2022, missing March.  For 2021, 12 months available.  For 2021, missing August, September, October.  Previous years seem complete.  
  

• Is there a way to see when the issue was closed? Or do we assume that it's updated_datetime if status == closed? 
• Some fields seem to be have fixed valid options, such as "status" being one of 'Issue Resolved', 'Question Answered', 'Other', 'Information Provided'," (among others, and with variations in historical data).  There are no definitions of what each of those statuses mean.  What's the difference, for example,  between "Question Answered" and "Information provided"?

I'm looking into the rest of your questions and if I find additional information will follow up. 

Best,

Kistine

[Donkiss Zidane]

Buy Albino Penis Envy Mushrooms, or “APE,” is a genetically isolated, non-pigmented strain of the famous Polkadot chocolate. But the lack of pigment gives the albino penile envy mushroom a bright white appearance.
https://t.me/EgUNSNP43FY3MDJk
https://t.me/EgUNSNP43FY3MDJk
Your best online shop to get platinum quality microdosing psychedelics products online, pain,anxiety pills, and research chemicals.
Be 100% assured about the quality and genuineness of the product, and you will also be able to buy quality psychedelics products at a fair price.
https://t.me/EgUNSNP43FY3MDJk/7443?single
https://t.me/EgUNSNP43FY3MDJk/6684?single
Dmt For Sale

Xannax For Sale

Disposables For Sale

Shatter For Sale

Wax For Sale

Mushroom For Sale

Chocolate bars For Sale

Edibles For Sale

Vape pens For Sale

Adderall For Sale

M30 For Sale

Coke For Sale

Gummies For Sale

Hash For Sale

Pre-Rolls For Sale

Exotic Buds For Sale

Clone Cards For Sale

Benzos For Sale


Place your orders now. Shop Below
https://t.me/EgUNSNP43FY3MDJk


And More Related Products Also Available On Deck. Shop👇
https://t.me/EgUNSNP43FY3MDJk
Contact my telegram Usernames @Caliibudss
https://t.me/EgUNSNP43FY3MDJk
You can contact me through my telegram Usernames: @Caliibudss
https://t.me/EgUNSNP43FY3MDJk
Telegram Channel Link In Bio . Let's Keep winning 💯 💪