9

When preparing data for analysis, I often encountered issues such as outliers, data entries that are logically inconsistent (e.g. age=150/age=-2), duplicates (that are not exactly equal) etc. When integrating data sets from different sources, there might not be a unique identifier oder data entries follow different standards (e.g. US vs. United States), which makes matching hard.

What are the issues you experience most commonly when working with data?

  • 2
    This question is very broad, probably too broad for a question and answer format. Can you be more specific? – jknappen Aug 16 '16 at 10:14
5

There can be numerous instances where data needs to be cleansed and standardised. To name a few -:

1) Mandatory fields having Null values - This is the greatest source of data loss

Remedy - Going back to the business, Replacing them with the mean.

2)Standardising columns- Like USA, US, America, United States. Remedy- Using a lookup to change them to one standard value.

3)Date- This is one major issue where a file might have multiple date formats. Remedy-This can be solved again by using a lookup.

4)Duplicates - This can be easily solved either in excel or in the database. You can even have a stored procedure for this.

5)Outliers- This is a separate thing all together, an outlier is a datapoint which comes from different distribution.I would suggest using the 3-standard deviation rules to mark potential outliers and then using a grub's test to check if they are outliers.

You can find cleansed and uncleansed datasets here https://www.datazar.com/

I hope this helped you.If you need any further details, feel free to email me at pramit@datazar.com

5

In addition to Pramit's answer:

  • Entities are stored differently: system A stores full name, whereas B stores first name and last name.
  • Character set issues: data was exported from system A to B and B cannot interpret characters from A correctly (or the conversion process had errors). This is common when exporting to CSV format.
  • Field values in table X point to an index field in table Y, but that record no longer exists (lookup fails)
  • Not properly distinguishing NULL and 0 values (or NULL values and empty strings)
  • Varying CASING For values that Should HAVE consistent CASing
  • End dates before start dates
  • Date ranges that are not sequential (have gaps)
  • Invalid dates
  • Missing values
  • Invalid usage of field types, i.e. storing numbers or dates in text fields, strings in a string field have their length prepended (yes, I've seen that)
  • Values that are valid in one system but not in another, e.g in a DB/2 database you can store time 24:00 - try that in Oracle or SQL Server.
  • Different decimal separators; using thousand separators
  • Different languages used for texts

Then there's a whole separate class of the issues coming from databases that are not properly normalized. Some that come to mind:

  • Value duplicated in table X looked up from table Y no longer matches with the current value in Y
  • Field value Z calculated from fields X and Y does not equal the actual calculation when you re-do it
  • Putting 2;12;34;5 in a string field instead of storing those numbers as integers in a detail table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.