---
title: 3 Ways to Clean Up Data So You Can Promote Public Dialog
description: 3 Ways to Clean Up Data So You Can Promote Public Dialog
image: https://blog.minitab.com/hubfs/B2BML%20-%20NEW%20FILE%20MANAGER%20STRUCTURE/Blog%20Images/Imported_Blog_Media/1280px_philadelphia_police___cruiser_on_ben_franklin_parkway_w1024.jpg
---
English
- [English](https://blog.minitab.com/en/?hsLang=en)
- [Français](https://blog.minitab.com/fr/?hsLang=en)
- [Deutsch](https://blog.minitab.com/de/?hsLang=en)
- [Português](https://blog.minitab.com/pt/?hsLang=en)
- [Español](https://blog.minitab.com/es/?hsLang=en)
- [日本語](https://blog.minitab.com/ja/?hsLang=en)
- [한국어](https://blog.minitab.com/ko/?hsLang=en)
- [中文（简体）](https://blog.minitab.com/zh/?hsLang=en)
- [ Partners](https://www.minitab.com/en-us/company/partners/)
- [ Contact](https://www.minitab.com/en-us/company/contact-us/)
- [ Talk to Minitab](https://www.minitab.com/en-us/talk-to-minitab/)
- [ My Account](https://licensing.minitab.com/account/manage)
[](https://www.minitab.com/en-us/)
- Products
Back
[Products](https://www.minitab.com/en-us/products/)
[All Products](https://www.minitab.com/en-us/products/)
- [Minitab Solution Center](https://www.minitab.com/en-us/products/minitab-solution-center/)
- [Minitab Statistical Software](https://www.minitab.com/en-us/products/minitab/)
- [Minitab Connect](https://www.minitab.com/en-us/products/connect/)
- [Minitab Model Ops](https://www.minitab.com/en-us/products/model-ops/)
- [Minitab Education Hub](https://www.minitab.com/en-us/products/education-hub/)
- [Minitab Engage](https://www.minitab.com/en-us/products/engage/)
- [Minitab Workspace](https://www.minitab.com/en-us/products/workspace/)
- [Real-Time SPC](https://www.minitab.com/en-us/products/real-time-spc/)
- [Prolink Data Collection & SPC](https://www.minitab.com/en-us/products/prolink)
- [Simul8 Discrete Event Simulation](https://info.minitab.com/en/new-to-simul8?hsLang=en)
- [SPM](https://www.minitab.com/en-us/products/spm/)
- Solutions
Back
[Solutions](https://www.minitab.com/en-us/solutions/)
[All Solutions](https://www.minitab.com/en-us/solutions/)
- [Analytics](https://www.minitab.com/en-us/solutions/analytics/)
- [Statistics & Predictive Analytics](https://www.minitab.com/en-us/solutions/analytics/statistical-analysis-predictive-analytics/)
- [Data Science & Machine Learning](https://www.minitab.com/en-us/solutions/analytics/data-science-machine-learning/)
- [Business Analytics & Intelligence](https://www.minitab.com/en-us/solutions/analytics/business-analytics-intelligence/)
- [Statistical Process Control](https://www.minitab.com/en-us/solutions/analytics/statistical-process-control/)
- [Quality Analytics](https://www.minitab.com/en-us/solutions/analytics/quality-analytics/)
- [Live Analytics](https://www.minitab.com/en-us/solutions/analytics/live-analytics/)
- [Reliability & Life Data Analysis](https://www.minitab.com/en-us/solutions/analytics/reliability/)
- [Discrete Event Simulation](https://info.minitab.com/en/new-to-simul8?hsLang=en)
- [Process Mining](https://info.minitab.com/en/new-to-simul8?hsLang=en)
- [Key Capabilities](https://www.minitab.com/en-us/solutions/key-capabilities/)
- [Continuous Improvement](https://www.minitab.com/en-us/solutions/key-capabilities/continuous-improvement/)
- [Data Integration & Data Prep](https://www.minitab.com/en-us/solutions/key-capabilities/data-integration-data-prep/)
- [Diagramming & Mind Mapping](https://www.minitab.com/en-us/solutions/key-capabilities/diagramming-mind-mapping/)
- [Digital Twins](https://info.minitab.com/en/new-to-simul8?hsLang=en)
- [Model Deployment & ML Ops](https://www.minitab.com/en-us/solutions/key-capabilities/model-deployment-ml-ops/)
- [Innovation & Project Management](https://www.minitab.com/en-us/solutions/key-capabilities/innovation-project-management/)
- [Process Excellence: Detect, Correct & Prevent](https://www.minitab.com/en-us/solutions/key-capabilities/process-excellence/)
- [Featured Industries](https://www.minitab.com/en-us/solutions/industry/)
- [Academic](https://www.minitab.com/en-us/solutions/industry/academic/)
- [Energy & Natural Resources](https://www.minitab.com/en-us/solutions/industry/energy-resources/)
- [Government & Public Sector](https://www.minitab.com/en-us/solutions/industry/government/)
- [Healthcare](https://www.minitab.com/en-us/solutions/industry/healthcare/)
- [Insurance](https://www.minitab.com/en-us/solutions/industry/insurance/)
- [Manufacturing & Industrial](https://www.minitab.com/en-us/solutions/industry/manufacturing-industrial/)
- [Services](https://www.minitab.com/en-us/solutions/industry/service-industries/)
- [Software & Technology](https://www.minitab.com/en-us/solutions/industry/software-technology/)
- [Construction](https://www.minitab.com/en-us/solutions/industry/construction/)
- [Featured Roles](https://www.minitab.com/en-us/solutions/role/)
- [Engineering](https://www.minitab.com/en-us/solutions/role/engineering/)
- [Business Analysis](https://www.minitab.com/en-us/solutions/role/business-analysts/)
- [Information Technology](https://www.minitab.com/en-us/solutions/role/information-technology/)
- [Supply Chain](https://www.minitab.com/en-us/solutions/role/supply-chain/)
- [Customer Service & Contact Center](https://www.minitab.com/en-us/solutions/role/customer-service-contact-center/)
- [Human Resources](https://www.minitab.com/en-us/solutions/role/human-resources/)
- [Marketing](https://www.minitab.com/en-us/solutions/role/marketing/)
- [Research and Development](https://www.minitab.com/en-us/solutions/role/research-and-development/)
- Resources & Services
Back
[Resources & Services](https://www.minitab.com/en-us/resources-services/)
[All Resources & Services](https://www.minitab.com/en-us/resources-services/)
- [Resources](https://www.minitab.com/en-us/resources-services/resources/)
- [Case Studies](https://www.minitab.com/en-us/resources-services/resources/case-studies/)
- [eBooks & White Papers](https://www.minitab.com/en-us/resources-services/resources/ebooks-and-white-papers/)
- [Blog](https://blog.minitab.com/blog/?hsLang=en)
- [Data Sets](https://www.minitab.com/en-us/resources-services/resources/data-set/)
- [Webinars & Events](https://www.minitab.com/en-us/resources-services/resources/events/)
- [Education Hub](https://www.minitab.com/en-us/resources-services/resources/education-hub/)
- [Services](https://www.minitab.com/en-us/resources-services/services/)
- [Training](https://www.minitab.com/en-us/resources-services/services/training/)
- [Deployment](https://www.minitab.com/en-us/resources-services/services/deployment/)
- [Consulting](https://www.minitab.com/en-us/resources-services/services/statistical-consulting/)
- [Self-Paced Learning](https://www.minitab.com/en-us/resources-services/services/self-paced-learning/)
- [Continuing Education](https://www.minitab.com/en-us/resources-services/services/continuing-education/)
- Support
Back
[Support](https://www.minitab.com/en-us/support/)
[Technical Support](https://www.minitab.com/en-us/support/)
- [Subscriptions & Activation](https://www.minitab.com/en-us/support/subscriptions-and-activation/)
- [Minitab Quick Start](https://www.minitab.com/en-us/support/quick-start/)
- [Training](https://www.minitab.com/en-us/resources-services/services/training/)
- [Installation Support](https://www.minitab.com/en-us/support/installation/)
- [Support Videos](https://www.minitab.com/en-us/support/videos/)
- [Support Documentation](https://www.minitab.com/en-us/support/documents/)
- [Software Updates](https://www.minitab.com/en-us/support/software-updates/)
- [Product Downloads](https://www.minitab.com/en-us/support/downloads/)
- [Support Policy](https://www.minitab.com/en-us/support/policy/)
- Company
Back
[Company](https://www.minitab.com/en-us/company/)
[Company](https://www.minitab.com/en-us/company/)
- [About Us](https://www.minitab.com/en-us/company/our-company/)
- [Leadership Team](https://www.minitab.com/en-us/company/leadership-team/)
- [Partners](https://www.minitab.com/en-us/company/partners/)
- [Careers](https://www.minitab.com/en-us/company/careers/)
- [Contact](https://www.minitab.com/en-us/company/contact-us/)
- [News](https://www.minitab.com/en-us/company/press-releases/)
[Try/Buy](https://www.minitab.com/en-us/try-buy/)
- [Statistics](https://blog.minitab.com/en/blog/tag/statistics)
- [Data Analysis](https://blog.minitab.com/en/blog/tag/data-analysis)
-
- [Quality Improvement](https://blog.minitab.com/en/blog/tag/quality-improvement)
- [Subscribe](https://info.minitab.com/blog/subscribe?hsLang=en)
## Notice
You are now leaving minitab.com.
Click Continue to proceed to:
Continue
Go Back
# 3 Ways to Clean Up Data So You Can Promote Public Dialog
[Cody Steele](https://blog.minitab.com/en/blog/author/cody-steele) | 6/24/2015
Topics: [Statistics in the News](https://blog.minitab.com/en/blog/tag/statistics-in-the-news)
- [Share](https://www.linkedin.com/shareArticle?mini=true&url=https://blog.minitab.com/en/blog/statistics-and-quality-improvement/3-ways-to-clean-up-data-so-you-can-promote-public-dialog)
- [Share](https://www.facebook.com/sharer.php?u=https://blog.minitab.com/en/blog/statistics-and-quality-improvement/3-ways-to-clean-up-data-so-you-can-promote-public-dialog)
*"By publishing the historical data, public dialogue that results from the data release can be more productive because you’ll be able to discuss changes over time."* — Denice Ross, 5/17/2015
Last month, President Obama launched the [Police Data Initiative](https://www.whitehouse.gov/blog/2015/05/18/launching-police-data-initiative). A key goal of the initiative was to make data about police departments more accessible to the public. Twenty-one communities decided to participate in the initial round, including Philadelphia.
Among Code for America's recommendations to help police departments get started was the suggestion to open historical records. On June 19, the data set "Philadelphia Police Advisory Commission Complaints" was made available via [opendataphilly.org](https://www.opendataphilly.org/dataset/activity/philadelphia-police-advisory-commission-complaints). The data set includes several variables about complaints made against police officers between 2009 and 2012, and gives us the chance to explore some steps you can take to clean up your data for analysis, using features in Minitab.
## Proper
One thing to look for is redundant categories and labels. If you download the data and take a look at the actions that resulted from the complaints, you’ll find these values in these frequencies.
**Tally for Discrete Variables: ACTION**
ACTION Count
Accept 26
ACCEPT 161
Audit 11
AUDIT 64
NAR 16
NoJurisdiction 2
NON-JURISDICTIONAL 1
Reject 23
REJECT 142
Rejected 1
WITHDRAWN 3
N= 450
\*= 5
It’s easy to see that the values “Accept” and “ACCEPT” should be the same. If you're using Minitab, it can change those values for you. (If you're not using Minitab, you can get a [free 30-day trial](http://it.minitab.com/products/minitab/free-trial.aspx).) Try this:
1. Choose **Calc > Calculator**.
2. In **Store result in variable**, enter *‘Action taken’*.
3. In **Expression** enter *Proper(ACTION)*. Click **OK**.
Now there’s a column with these values and frequencies:
**Tally for Discrete Variables: Action taken**
Action taken Count
Accept 187
Audit 75
Nar 16
Nojurisdiction 2
Non-jurisdictional 1
Reject 165
Rejected 1
Withdrawn 3
N= 450
\*= 5
Instead of having to make 62 corrections in the data, you have to make only 2. Prefer a different format? You could substitute LOWER or UPPER for PROPER to get all lowercase or all uppercase letters.
## Left
The Philadelphia data set includes a variable for the date and time of the incident, but none of the times are recorded. Including the unused values for time yields data like these:
To get the usable "date" portion of the data, you can use the calculator. Try this:
1. Choose **Calc > Calculator**.
2. In **Store result in variable**, enter '*Text date'*.
3. In **Expression**, enter *Left(DATE\_, 10)*. Click **OK**.
The column that results is still formatted as text. To do an analysis where you can sort by date, you can quickly [change the date format](https://blog.minitab.com/blog/statistics-and-quality-improvement/3-new-things-you-can-do-by-right-clicking-in-minitab-172?hsLang=en). Select a cell in the column, right-click, and select **Format Column**. When you pick **Date** from the list of types, Minitab recognizes the format for you.
## Code
If you dig a bit deeper into the data, you’ll notice an oddity that’s not readily apparent. The current web site for the police in Philadelphia lists 21 districts. In the data, 23 units are included. That's because the 23rd District has been incorporated into the 22nd District, and the 4th District incorporated into the 3rd. If we want to include complaints about officers from those districts in their new districts, you can recode the districts. Try this:
1. Choose **Data > Code > To Text**.
2. In **Code values in the following columns**, enter *Unit*.
3. In **Method**, select **Code Individual Values**.
4. For District 4, change the **Coded value** to *District 3*.
5. For District 23, change the **Coded value** to *District 22*.
6. Click **OK.**
**Code**
Summary
Number
Original Value Recoded Value of Rows
District 4 District 3 2
District 23 District 22 7
Source data column UNIT
Recoded data column Coded UNIT
Number of unchanged rows: 446
Minitab shows you a summary table so you can see how the values were recoded and you’re ready to go!
## Wrap up
Whether you have data about police complaints or [patient throughput times](http://www.minitab.com/Case-Studies/Via-Christi-Health/), you’re likely to need to do a little bit of work for your data to be ready to analyze. Fortunately, Minitab makes it easy to make common adjustments like getting the case of letters to match across entries. The faster your data is ready to analyze, the faster you can do the analysis to make better decisions.
*The photo of the police car is by [Zuzu](https://en.wikipedia.org/wiki/Philadelphia_Police_Department#/media/File:Philadelphia_Police_-_cruiser_on_Ben_Franklin_Parkway.jpeg) and is licensed under this [Creative Commons License](https://creativecommons.org/licenses/by-sa/3.0/).*
## You Might Also Like
[Better Together: Pairing Online and In-Person Learning is Key to Developing Critical Analytical Skills](https://blog.minitab.com/en/blog/better-together-pairing-online-and-in-person-learning-is-key-to-developing-critical-analytical-skills?hsLang=en)
[Data Analysis](https://blog.minitab.com/en/blog/tag/data-analysis) | 5 Minute Read
[Against the Odds: Honoring Women in Math and Statistics](https://blog.minitab.com/en/blog/honoring-women-in-math-and-statistics?hsLang=en)
[Statistics](https://blog.minitab.com/en/blog/tag/statistics) | 5 Minute Read
[Don't Stop 'Til You Get Enough: Demystifying CBD Oil Potency with Statistical Methodology](https://blog.minitab.com/en/blog/demystifying-cbd-oil-potency-with-statistical-methodology?hsLang=en)
[Food Manufacturing](https://blog.minitab.com/en/blog/tag/food-manufacturing) | 3 Minute Read
---
- [Careers](https://www.minitab.com/en-us/company/careers/)
- [Trust Center](https://www.minitab.com/en-us/trust-center/)
- [Suppliers](https://www.minitab.com/en-us/suppliers/)
- [Site Map](https://www.minitab.com/en-us/site-map/)
- [](https://www.linkedin.com/company/minitab/)
- [](https://www.facebook.com/Minitab/)
- [](https://www.youtube.com/user/MinitabInc)
- [](https://www.instagram.com/minitab/)
---
© 2025 Minitab, LLC. All Rights Reserved.
- [Terms of Use](https://www.minitab.com/en-us/legal/terms-of-use/)
- [Privacy Policy](https://www.minitab.com/en-us/legal/privacy-policy/)
- [Legal](https://www.minitab.com/en-us/legal/)
- Cookies Settings