The City of Philadelphia today released a dataset considered critical to government transparency, with nearly a quarter-million lines of payments data from almost 60 city offices, departments, boards and commission.

This initial release of payments data covers the City’s fiscal year 2017 (FY2017), representing nearly $4.2 billion in payments during that period. The Open Data Census lists it as one of the most important datasets for cities to share with the public.

The release, deemed a priority by Mayor Kenney, resulted from a partnership among the Finance Department, the Law Department’s Right to Know (RTK) Division, and the Office of Innovation and Technology’s (OIT) open data team. Over the course of the past year, they worked with almost every department within City government to review their data and understand the types of payments they make.

While the City previously released other financial datasets – such as the  City budget data and employee salaries and overtime – this new dataset, because of its size and the confidential information contained within it, presented a unique challenge and opportunity.

 

At a glance: City payments data

  • Explore the transaction-level data with this interactive visualization or use the aggregate data visualization.
  • Download the FY2017 Payments dataset on OpenDataPhilly.org:
    • Detailed City payments (metadata) – this dataset shows each payment along with details such as the check date, vendor name, and department making the payment. Rows that included legal or security-sensitive information were removed from this dataset.
    • Aggregate City payments (metadata) – this dataset shows the totals by category of payments made. The totals include the payments that were removed from the detailed dataset.
  • Read the metadata to find descriptions of each field and learn important details about this data, such as:
    • Sensitive data has been removed from the detailed dataset.
    • This data cannot be compared with other financial or accounting reports released by the City.
    • This dataset does not include salary and benefits data or payments the City makes to fund the operations of the First Judicial District.  You can currently search for financial data from the First Judicial District, or file a request for those records. The City has already released employee salaries as open data.
    • Vendors should use the vendor payment website, not this dataset, to track payments. The website indicates what payments were made and are in progress (including tax holds). The website includes a ‘Department Contact’ tab that vendors can use to find the right point of contact to discuss their invoice(s).

A citywide effort

The team studied the payments data to design an approach to maximize the information available to the public while ensuring review and removal, as appropriate, of legal and security-sensitive information. While utilizing this approach required a significant investment of effort and time, it will result in faster review and release of this data in the future.

 

Sensitive data and automation

So how did this cross-departmental team manage to make 238,894 lines of data comprising $4.2 billion in payments from almost 60 City offices, departments, boards, and commissions ready for public release? The team began the project by sifting through the FY2017 data with the Law department to understand what types of sensitive information were included and how to easily identify this information in future years’ datasets. That information includes payments to individuals for social services (including, but not limited to, payments to foster care parents), payments which would harm public safety activities if made public, payments which would harm the City’s legal interests if released, and payments otherwise made confidential by law.

Once OIT’s open data team had a grasp of the type of information that was sensitive and why, they worked with departments throughout the City to review their data and delve deeper into the details of the types of payments they make to identify where such sensitive information was located in the dataset. They then created a set of rules that can be automatically applied to payments data in the future.

With these rules, the Office of Innovation and Technology (OIT) CityGeo team will automate the process as much as possible, which will reduce the review required in future years and improve the efficiency of the release process, while still ensuring the protection of sensitive data and access to updated payments information by the public.

 

Maximizing context available to the public

Another goal of the project is to maximize the context available to the public. To accomplish this goal, the team sought to integrate data from other financial datasets that had never before been linked to City payment data, and to highlight information that provides more context for the public.

For example, many of the payments in the dataset are payments associated with City contracts. Payments data and contract data are not housed in the same database and therefore linking this information was not a straightforward task. The open data team was able to connect contract numbers with relevant payments and then pull in contract descriptions and other additional information.

The team also added context through the inclusion of document reference number prefixes in the dataset. These numbers serve to track payments in the City’s financial accounting system, but also provide useful information about payments themselves. For example, “PCXX” indicates petty cash payments whereas “RPXX” are reimbursable expense vouchers for City employees. The City has included definitions for the fourteen different types of document reference number prefixes in the metadata so that the public the benefit from this research.

 

An iterative process

As a City government committed to transparency, we have been working and are excited to share out this first release of payments data. However, we still have additional technical work to do to get the full picture of payments publicly available.

Rather than delaying release until completely finalized, we are taking an iterative approach to achieving greater accuracy and completion of this data given the complexity of this dataset (many tables, joins and verification required) and process (coordinating several technical resources and collaboration among multiple city departments).

Since we have finished review of the bulk of payments information, we decided to release what we have ready while we continue to build on the foundation we have developed. Later this year, we will release an updated payments dataset that integrates employee benefits.

 

Related data

In addition to this newly released payments data, the City shares other financial information, including:

Please note: the payments dataset cannot be accurately compared directly with the other financial and accounting reports released by the City. The date of the payments made in the dataset may not always match up with the fiscal year that the services were rendered, which is one of the reasons why an accurate comparison between various financial datasets cannot be made.

 

Keep in Touch!

Let us know how you use the data or what other information you would find helpful. Contact data@phila.gov to share your story or comments.

There are lots of ways to get involved with open data. For more City datasets go to OpenDataPhilly.org. Visit this resources page for video tutorials and links to tools to help analyze data. Follow @PHLInnovation on Twitter to get alerts on future data releases, and join us on the public open data google forum.