Data cleansing (データクレンジング) is the work of finding and correcting errors and inconsistencies in collected data to raise data quality.
Type of flaw
Example
How to fix it
Inconsistent notation
"Unit 3" and "Unit three" both appear, with full-width and half-width digits mixed
Standardize on one form
Duplicate
One member is registered twice, once on joining and again on re-registering
Merge into a single entry
Missing value
The postal code field is left blank
Fill it in from other information, or leave it out of the totals
Error or outlier
The age field contains 250
Check the original record and correct it
Typical flaws include typing errors, duplicates where the same thing is registered twice, inconsistent notation where the same content is written in different ways, and missing values where a field is empty. If you aggregate or join data with these flaws still in it, one person may be counted as two, and the results can no longer be trusted. That is why cleansing is done before the data is used for analysis or storage.
AnalogyIt is like tidying your address book before sending holiday cards. If a friend appears twice, under an old and a new surname, you merge the entries, and you update the addresses of people who have moved before printing the labels.
How to rememberCleanse means to wash thoroughly, the same word as in a facial cleanser. Picture scrubbing the dirt off your data.
Exam points
ExamData cleansing (データクレンジング) corrects errors and inconsistencies in the values and records of the data. Organizing the structure of business data into entities and relationships is called data modeling (データモデリング), which serves a different purpose.
A data join (データの結合) matches separate tables on the values of a shared column (the key) and combines them into a single table.
Member and purchase tables
Pick the shared column
Pair rows with equal values
One combined table
Names and amounts now sit on the same row, so one table shows who spent how much.
4 / 4
A join uses a column with the same meaning in both tables, pairs rows whose values match, and places their columns side by side. This yields combinations of information that neither table can show on its own. What happens to rows with no match depends on the type of join; one type, for example, keeps only the rows that matched.
AnalogyIt is like taking your copies of delivery slips, looking up each customer number in the customer register, and writing the recipient's phone number onto each slip.
How to rememberJoin literally means to link up. Picture two tables holding hands through the column they share.
Exam points
ExamTables are linked through a column that carries the same meaning in both. Usually this is the primary key (主キー) that identifies rows in one table and the foreign key (外部キー) in the other table that refers to it.
Mix-upA data join (データの結合) brings together information held in separate tables, while data cleansing (データクレンジング) fixes errors and inconsistencies to improve quality, so their purposes differ. The standard order is to clean the data first and then join it.
Data Cleansing vs. Data Join
Aspect
Data Cleansing
Data Join
Key differencePurpose
Remove errors and inconsistencies to raise quality
Bring together information from separate tables
Result
Data whose values and records are correct and consistent
A single table with the columns placed side by side
Code design (コード設計) is the work of deciding the length, numbering scheme, and meaning of each digit of the codes (numbers or symbols) assigned to data items such as products and customers.
Scheme
How it works
Example
Sequential code
Numbers are given in order of registration
0001, 0002, 0003
Block code
Each range of numbers is assigned to a category
100-199 for drinks, 200-299 for snacks
Positional code
Each digit position carries a set meaning
First 2 digits for the region, next 2 for the store
Mnemonic code
Letters or abbreviations show what the item is
TV-32 for a 32-inch television
A code exists to tell items apart uniquely, and if its digits or number ranges carry meaning, it can also be used for classification and aggregation. Leave room in the number of digits so that you do not run out of numbers as items are added later. To catch input errors, a check digit may also be added, a verification digit calculated from the other digits by a fixed rule.
AnalogyIt is like a postal code. The leading digits indicate a broad region and later digits narrow it down, so the number alone tells you roughly where a delivery is headed.
How to rememberRead the name literally: a digit whose only job is to check the others.
Exam points
ExamA check digit (チェックディジット) is a digit calculated from the original digits by a set rule. Recalculating an entered code with the same rule reveals an input error whenever the result does not match.
Data cleansing deals with wrong values, duplicates, inconsistent notation, missing values, and similar flaws to raise data quality, and it comes before aggregation and analysis.
A data join pairs rows whose values match in a column with the same meaning in both tables, combining them into one table.
Code design sets the number of digits, the numbering scheme, and the meaning of each digit so that every item can be told apart and there are enough numbers for future additions.
With a check digit attached, recalculating an entered code shows a typing mistake whenever the result does not match.