LESSON 2
Data Cleaning
Machine-readability
Dirty data: definition and types
Key-value relations
Cleansing process
Dirty data: definition and types
Key-value relations
Cleansing process
Data Cleaning
Data Cleaning & Processing is the second step of your work with data.
After we found a dataset, we need to preprocess it, so it would be easier for us to analyze it — for instance, to apply formulas.
The purpose of this step is the structurization and
the unification of formats and contents.
After we found a dataset, we need to preprocess it, so it would be easier for us to analyze it — for instance, to apply formulas.
The purpose of this step is the structurization and
the unification of formats and contents.
Data types:
- int — integer — whole numbers
- float / real — decimal numbers
- str — string — text
- date — date
- time — time
- Boolean — True/False; ИСТИНА / ЛОЖЬ — logical type, yes or no
- Null / None — no data or an action without any result
- in python, None impacts the calculations as an autonomous data type and not as just a blank space
Machine-Readability
"Machine-readable" means "can be easily processed
by a computer". It is an internationally acclaimed format of data organization and presentation.
The definition of the American Open, Public, Electronic, and Necessary Government Data Act (OPEN Government Data Act):
"Data in a format that can be easily processed by a computer without human intervention while ensuring no semantic meaning is lost"
by a computer". It is an internationally acclaimed format of data organization and presentation.
The definition of the American Open, Public, Electronic, and Necessary Government Data Act (OPEN Government Data Act):
"Data in a format that can be easily processed by a computer without human intervention while ensuring no semantic meaning is lost"
Definition by the Russian Ministry of Finance from the Recommendations on Open Data Publication:
"Machine-readable data are data presented in formats suitable for automatic or automated use"
Read more:
GOLDEN RULE
ONE SHEET — ONE TABLE
ONE ROW — ONE ENTITY
ONE COLUMN — ONE CHARACTERISTIC
ONE COLUMN — ONE SCALE AND MEASURE
ONE ROW — ONE ENTITY
ONE COLUMN — ONE CHARACTERISTIC
ONE COLUMN — ONE SCALE AND MEASURE
Dirty Data
Dirty data is inaccurate, incomplete or inconsistent data that ruins the intergrity and the structurization of your datasets.
Data quality criteria:
Data quality criteria:
- Validity — no logical or formatting errors
- Accuracy — detailed and trustworthy
- Completeness — minimum blank cells
- Consistency — no contradicting values
- Uniformity — structured and machine-readable
Ответ: вагадане — золотодобыча, рыбная ловля, энергетика.
In other sources the same principles may be called differently, but the essence remains the same.
There are other criteria, you can learn more about them in the following articles:
- Data Cleansing on Wikipedia
- Dirty Data Samples — Get Your Hands Dirty Cleaning Data on Foresight BI
- Dirty Data — What is it and how to prevent it? on UX Planet Blog (VPN)
- Data Quality on Business Analytics website (in Russian)
Types of Dirty Data
- Broken encoding.
- Many different tables on one sheet.
- The golden rule "One row — one entity, one column — one characteristic" is not respected, for instance the value "24 kg" should be divided into two columns: "24" for "weight" and "kg" for "measure unit"
- Duplicates.
- Blank cells — for instance, due to formatting mistakes.
- Fake blank cells.
- Inconsistent formatting of data — for instance "Full name + full surname" and "Full surname and the initials".
- Yulia Vered
- Klimova E. B.
- +7-926-170-40-93
- 8-(926)-170-4093
8. Data in the column is not connected to its title, for instance the column is titled "Countries" and contains numbers.
9. Different data types in one column, for instance:
- 25.03.1821
- Greece
- TRUE
11. Impossible data — for instance, 13th month.
12. One column — different languages (if it is not wanted):
- England
- Lietuva
- Spain
- Deutschland
14. Different spellings for the same entity — for instance, "Kia Rio" and "КИА РИО" in the same column.
15. Some cells that must contain obligatory data are blank, the data is missing.
Human-readable and machine-readable
Broken encoding and impossible data
Tutorials
- Define the encoding.
- List of countries by number of Internet users
- Who invented the automobile.
- Plugins (Web extensions) for Scraping (autocollecting of data):
6. Estimate numbers from a chart image manually
7. OCR — Optical Character Recognition — extract text from images.
Zeroes in numbers
- K, 千 — 1000 — 1 000 — 3 zeroes
- M — 1000000 — 1 000 000 — 6 zeroes
- B — 1000000000 — 1 000 000 000— 9 zeroes
- 万 — 10000 — 1 00 00 — 4 zeroes
- 亿 — 100000000 — 1 00 00 00 00 — 8 zeroes
Как очистить эту таблицу?
How to delete duplicates and blank cells in Excel
How to delete duplicates and blank cells
in Google Sheets
in Google Sheets
Split by non-breaking space in Google Sheets
in Russian
How to collect data of a YouTube channel
in Russian
Incorrect time formatting in Google Sheets
in Russian
Different date formats in Excel
in Russian
Convert American dates to Russian ones
in Russian
How to extract data from a chart image
in Russian
Cleaning Functions
1.=IMPORTHTML("link";"table";number_of_needed_table) — works only in Google Sheets. In Excel you need to go to the toolbar, select "Data" → "Import from Web" or something similar.
2. =SPLIT(text;"delimiter"; FALSE) The argument "FALSE" is needed when you want to split the text by the whole word
"Split" works only in Google Sheets. In Excel you should find in the menu "Data" → "Text to columns".
Case 1:
3. If you want to join text pieces from several cells in one text:
=CONCATENATE("text_1";"delimiter";"text_2").
Space is used as a delimiter most often.
4. =VLOOKUP(key_needed; range_for_searching; number_of_column_with_needed_values; FALSE). "FALSE" here means 100 % match. See "Key-Value Relations" for more info.
5. Change the case of the text:
2. =SPLIT(text;"delimiter"; FALSE) The argument "FALSE" is needed when you want to split the text by the whole word
"Split" works only in Google Sheets. In Excel you should find in the menu "Data" → "Text to columns".
Case 1:
- Formula =SPLIT("Imagine me and you";"and")
- Result "Im" "gi" "e me" "d you"
- Formula =SPLIT("Imagine me and you";"and"; ЛОЖЬ)
- Result "Imagine me" "you
3. If you want to join text pieces from several cells in one text:
=CONCATENATE("text_1";"delimiter";"text_2").
Space is used as a delimiter most often.
4. =VLOOKUP(key_needed; range_for_searching; number_of_column_with_needed_values; FALSE). "FALSE" here means 100 % match. See "Key-Value Relations" for more info.
5. Change the case of the text:
- =PROPER("text") — Example: "Happy Together"
- =LOWER("text") — Example: "happy together"
- =UPPER("text") — Example: "HAPPY TOGETHER"
6. Select ... characters from the ... side
- =LEFT("text";number_of_characters) — Sample: =LEFT("Happy together";4) returns "Happ"
- =LEFT("text";number_of_characters) — Sample: =RIGHT("Happy together";4) returns "ther".
7. Time
These formulas return an integer. If the result contains 1900 as the year, change the formatting of the cell from date to number.
- =YEAR(date)
- =MONTH(date)
- =DAY(date)
- =WEEKDAY(date;2) — Sunday is the first day of the week by default, that's why we need to put 2 as the second value in the function.
- =HOUR(time)
- =MINUTE(time)
- =SECOND(time)
- =TIME(hours; minutes; seconds) — to format individual numbers as time
8. Check the formats and return TRUE or FALSE
- =ISBLANK() — is this cell blank. Returns FALSE if the cell is fake blank and used to have "" in it or contained a result of a formula application.
- =ISTEXT() — does this cell contain text values.
- =ISNUMBER() — does this cell contain numbers as values.
9. Tell me how many characters a cell has
- =LEN("text") returns the amount of characters in a text string. Can be used to clean the dates.
Text functions and formulas
Key-Value Relations
Sometimes we have several rows for the same object. For instance,
We have:
- Your name, Your surname, Your pet, Your pet's name
- Elena, Richter, dog, Bobik
- Elena, Richter, dog, Garold
- Elena, Richter, cat, Sophia
- Elena, Korobkova, pigeon, Garold
- Elena, Korobkova, dog, Bobik
We have:
- 5 Elenas
- 3 Richter, 2 Korobkova
- 3 dogs, 1 cat, 1 pigeon
- 2 Bobiks, 2 Garolds, 1 Sophia
So even though this is a dataset with different pets, the values repeat themselves. If we want to refer to a particular row, we should add a unique identifier to every row — "id".
ID's are usually numeric — either ordinal numbers, or some calculated combination.
The ids as the keys help us to create relations between datasets — so called "relational databases" that are interconnected.
Home Assignment
Deadline — 5 February, Thursday, 18:00
Grading criteria
Grading criteria
This task is individual. One person = one dataset.
Clean up one of those datasets you found in the first homework assignment.
If all of your datasets are already clean, you can scrape (= autocollect using Chrome extensions):
The chosen dataset should meet these criteria in its original form:
1. Create a new subfolder for your group project in the folder for the home assignments. Name it with your family names, like "Cutie_Beautie"
2. Upload there your chosen dataset — in the original format that you have found it, without any changes. Name it with the prefix "YourFamilyName_Dirty_NameOfDataset.xlsx".
3. Clean your dataset and upload it in the same folder. Name it with the prefix "YourFamilyName_Clean_NameOfDataset.xlsx".
🌻 You do not have to include all the data from the original dataset. You might include only the parameters that you actually need.
4. Create a text document with the description of everything you have done to the original file during the cleaning. Example:
Problem №1: names and years were included in the same column, like [Peter Parker (1920–1980)]
Violation of the principle: every column should contain only one machine-readable characteristic (see "Types of Dirty Data" above)
Cleaning algorithm:
___
If in the first homework assignment you found an infographic, a widget or a comprehensive text description instead of a dataset, you can create a machine-readable dataset from that object as part of this task. Write "Human-readable, but not machine-readable format" as one of the problems. In other words, if your data is not in tabular format yet, you need to convert it to tabular machine-readable format yourself.
If you are extracting the table from another file, e.g. from a pdf or from the web, also write this in the descriptive document.
You can add screenshots if you want to. You may have several problems for every dataset.
Every single step should be recorded, it is incredibly important. Describe your actions so that a stranger, following your steps, could get the same results.
Clean up one of those datasets you found in the first homework assignment.
If all of your datasets are already clean, you can scrape (= autocollect using Chrome extensions):
- a page of a particular item on a web marketplace, as we did during the lesson
- statistics of a YouTube-channel as shown in the tutorial above.
The chosen dataset should meet these criteria in its original form:
- Minumum 15 rows (without column titles).
- Minimum 5 columns.
1. Create a new subfolder for your group project in the folder for the home assignments. Name it with your family names, like "Cutie_Beautie"
2. Upload there your chosen dataset — in the original format that you have found it, without any changes. Name it with the prefix "YourFamilyName_Dirty_NameOfDataset.xlsx".
3. Clean your dataset and upload it in the same folder. Name it with the prefix "YourFamilyName_Clean_NameOfDataset.xlsx".
🌻 You do not have to include all the data from the original dataset. You might include only the parameters that you actually need.
4. Create a text document with the description of everything you have done to the original file during the cleaning. Example:
Problem №1: names and years were included in the same column, like [Peter Parker (1920–1980)]
Violation of the principle: every column should contain only one machine-readable characteristic (see "Types of Dirty Data" above)
Cleaning algorithm:
- Added three new columns to the right
- Text to columns, the delimiter is "("
- Text to columns, the delimiter is "-"
- Text to columns, the delimiter is space
- Find and replace, ")" to nothing
- Column names changed to "Name", "Surname", "Born", "Died"
___
If in the first homework assignment you found an infographic, a widget or a comprehensive text description instead of a dataset, you can create a machine-readable dataset from that object as part of this task. Write "Human-readable, but not machine-readable format" as one of the problems. In other words, if your data is not in tabular format yet, you need to convert it to tabular machine-readable format yourself.
If you are extracting the table from another file, e.g. from a pdf or from the web, also write this in the descriptive document.
You can add screenshots if you want to. You may have several problems for every dataset.
Every single step should be recorded, it is incredibly important. Describe your actions so that a stranger, following your steps, could get the same results.
