Capstone: Data Type Translator

นี่คือโปรเจกต์จบของ Data Types Path ทั้งหมด — คุณจะเอาทุกอย่างที่เรียนมาใช้จริง ตั้งแต่ binary ไปจนถึง type contracts

เวลาที่ใช้: ~120 นาที

Input: Messy CSV

ใช้ข้อมูลนี้เป็น input — copy แล้ว save เป็น messy_students.csv:

student_id,name,phone,price,enrolled_on,active,note
1234,Somchai Jaidee,0812345678,"2,500.00",15/01/2025,yes,
001235,Somsri Deejai,+66812345679,1800.50,2025-01-16,TRUE,นักเรียนทุน
1236,Somying Sabaidi,,0,Jan 17 2025,1,
001237,Somkid Jaijing,081-234-5680,"3,200.00",2025-01-18,Y,note ปกติ
001238,Sompong Jaiyen,"","1,500.00",19/01/2025,yes,""
39,Somrak Deemak,+66898765432,"999.99",2025-01-20,TRUE,
001240,Somsak Jairai,0876543210,"4,100.00",21 Jan 2025,true,VIP
1241,Somwang Sawasdee,,"750.00",2025-01-22,FALSE,ยกเลิก
001242,Somporn Jaidee,081-999-8888,"2,200.50",23/01/2025,Yes,
001243,Somjit Jaijaroen,+66834567890,0,2025-01-24,no,ยังไม่จ่าย

ปัญหาที่ต้องแก้

Caution

ก่อนเริ่ม clean ให้วิเคราะห์ปัญหาทั้งหมดก่อน — อ่าน CSV ทุกแถวแล้วจดว่าเจอปัญหาอะไรบ้าง

#	ปัญหา	Column ที่เกี่ยว	ความรู้จาก Module
1	Leading zeros หาย	student_id	Module 0 (binary), Module 2 (text)
2	Date formats ปนกัน	enrolled_on	Module 3 (date/time)
3	Empty vs null vs zero	phone, note, price	Module 1 (blank/empty/null)
4	Text ที่ดูเหมือนตัวเลข	price	Module 0.5 (numbers)
5	Boolean ไม่ consistent	active	Module 1 (core categories)
6	Phone format ไม่ consistent	phone	Module 2 (text)

---

Deliverable 1: Type Contract Memo

เขียน type contract ก่อนเริ่ม clean — ใช้เป็นแผนที่สำหรับทุก deliverable:

ดูตัวอย่าง Type Contract

Column	Type	Required	Constraints / หมายเหตุ
student_id	VARCHAR(6)	Yes	ตัวเลข 6 หลัก, เติม leading zeros, ห้ามเป็น integer
name	VARCHAR(100)	Yes
phone	VARCHAR(20)	No	digits + '+' เท่านั้น, normalize เป็น +66XXXXXXXXX
price	NUMERIC(10,2)	Yes	>= 0, ลบ comma ก่อน convert
enrolled_on	DATE	Yes	ISO 8601 YYYY-MM-DD เท่านั้น
active	BOOLEAN	Yes	normalize yes/y/1/true → TRUE, default TRUE
note	TEXT	No	empty string "" → NULL

กฎ Null:

• phone = NULL หมายถึง “ไม่มีเบอร์” (ไม่ใช่ empty string)
• note = NULL หมายถึง “ไม่มีหมายเหตุ” (empty string "" ต้องแปลงเป็น NULL)
• price = 0 หมายถึง “ฟรี” ไม่ใช่ null

---

Deliverable 2: Cleaned Google Sheet

1. Import CSV — File > Import > Upload > เลือก “Do not auto-detect” สำหรับ column types

2. Fix student_id — ใช้สูตรเพื่อเติม leading zeros

3. Fix price — ลบ comma, ตรวจสอบว่าเป็นตัวเลข

4. Fix dates — normalize ทุก format ให้เป็น YYYY-MM-DD

5. Fix active — normalize ให้เป็น TRUE / FALSE

6. Fix phone — normalize format, แปลง empty string เป็นช่องว่าง

7. ตั้ง Data Validation — สำหรับทุก column

ดูตัวอย่าง Sheets formulas

// Fix student_id (สมมติ raw อยู่ column A, cleaned อยู่ column H)
=TEXT(VALUE(A2), "000000")

// Fix price (ลบ comma)
=VALUE(SUBSTITUTE(D2, ",", ""))

// Fix dates (ถ้ารู้ว่า format ปนกัน ต้องจัดการทีละ format)
// สำหรับ DD/MM/YYYY:
=DATE(RIGHT(E2,4), MID(E2,4,2), LEFT(E2,2))
// สำหรับ ISO YYYY-MM-DD — ไม่ต้องแปลง ใช้ DATEVALUE
=DATEVALUE(E2)

// Fix active — normalize
=IF(OR(LOWER(F2)="yes", LOWER(F2)="y", F2="1", LOWER(F2)="true"), TRUE, FALSE)

// Fix phone — empty string → blank
=IF(C2="", , C2)

// Data Validation สำหรับ student_id:
// Custom formula: =AND(LEN(H2)=6, ISNUMBER(VALUE(H2)))

ผลลัพธ์ที่ถูกต้อง:

student_id	name	phone	price	enrolled_on	active	note
001234	Somchai Jaidee	0812345678	2500.00	2025-01-15	TRUE
001235	Somsri Deejai	+66812345679	1800.50	2025-01-16	TRUE	นักเรียนทุน
001236	Somying Sabaidi		0.00	2025-01-17	TRUE
001237	Somkid Jaijing	0812345680	3200.00	2025-01-18	TRUE	note ปกติ
001238	Sompong Jaiyen		1500.00	2025-01-19	TRUE
000039	Somrak Deemak	+66898765432	999.99	2025-01-20	TRUE
001240	Somsak Jairai	0876543210	4100.00	2025-01-21	TRUE	VIP
001241	Somwang Sawasdee		750.00	2025-01-22	FALSE	ยกเลิก
001242	Somporn Jaidee	0819998888	2200.50	2025-01-23	TRUE
001243	Somjit Jaijaroen	+66834567890	0.00	2025-01-24	FALSE	ยังไม่จ่าย

---

Deliverable 3: Cleaned pandas Notebook

ดูตัวอย่าง Python notebook

import pandas as pd
import numpy as np

# ===== STEP 1: อ่าน CSV (ทุก column เป็น string ก่อน) =====
df = pd.read_csv('messy_students.csv', dtype='string')
print("Raw data:")
print(df)
print(f"\nShape: {df.shape}")

# ===== STEP 2: Clean student_id — เติม leading zeros =====
df['student_id'] = df['student_id'].str.strip().str.zfill(6)
print("\nFixed student_id:")
print(df['student_id'])

# ===== STEP 3: Clean price — ลบ comma, แปลงเป็น float =====
df['price'] = (
    df['price']
    .str.replace(',', '', regex=False)
    .str.replace('"', '', regex=False)
    .astype(float)
)
print("\nFixed price:")
print(df['price'])

# ===== STEP 4: Clean enrolled_on — normalize dates =====
df['enrolled_on'] = pd.to_datetime(df['enrolled_on'], format='mixed', dayfirst=True)
print("\nFixed enrolled_on:")
print(df['enrolled_on'])

# ===== STEP 5: Clean active — normalize boolean =====
bool_map = {
    'true': True, 'yes': True, '1': True, 'y': True,
    'false': False, 'no': False, '0': False, 'n': False,
}
df['active'] = df['active'].str.strip().str.lower().map(bool_map)
print("\nFixed active:")
print(df['active'])

# ===== STEP 6: Clean phone — normalize empty → NaN =====
df['phone'] = df['phone'].replace({'': pd.NA, '""': pd.NA})
# ลบ dash ออก
df['phone'] = df['phone'].str.replace('-', '', regex=False)
print("\nFixed phone:")
print(df['phone'])

# ===== STEP 7: Clean note — empty string → NaN =====
df['note'] = df['note'].replace({'': pd.NA, '""': pd.NA})
print("\nFixed note:")
print(df['note'])

# ===== STEP 8: Run Import Checklist =====
print("\n" + "="*50)
print("IMPORT CHECKLIST")
print("="*50)

# Check 1: Row count
print(f"\n1. Row count: {len(df)} rows")

# Check 2: Nulls per column
print("\n2. Nulls per column:")
print(df.isnull().sum())

# Check 3: Leading zeros
bad_ids = df[df['student_id'].str.len() != 6]
print(f"\n3. IDs not 6 chars: {len(bad_ids)}")

# Check 4: Dates parsed
print(f"\n4. enrolled_on dtype: {df['enrolled_on'].dtype}")

# Check 5: Total price
print(f"\n5. Total price: {df['price'].sum():.2f}")

# ===== STEP 9: Export cleaned CSV =====
df.to_csv('cleaned_students.csv', index=False)
print("\nExported: cleaned_students.csv")

# ===== Final Result =====
print("\n" + "="*50)
print("CLEANED DATA")
print("="*50)
print(df)
print(df.dtypes)

---

Deliverable 4: SQL Schema + Load Script

ดูตัวอย่าง SQL

-- ===== SCHEMA (ตรงกับ Type Contract) =====
CREATE TABLE students (
    student_id   VARCHAR(6)     NOT NULL,
    name         VARCHAR(100)   NOT NULL,
    phone        VARCHAR(20),
    price        NUMERIC(10,2)  NOT NULL CHECK (price >= 0),
    enrolled_on  DATE           NOT NULL,
    active       BOOLEAN        NOT NULL DEFAULT TRUE,
    note         TEXT
);

-- ===== LOAD DATA (จาก cleaned CSV) =====
INSERT INTO students VALUES ('001234', 'Somchai Jaidee',   '0812345678',     2500.00, '2025-01-15', TRUE,  NULL);
INSERT INTO students VALUES ('001235', 'Somsri Deejai',    '+66812345679',   1800.50, '2025-01-16', TRUE,  'นักเรียนทุน');
INSERT INTO students VALUES ('001236', 'Somying Sabaidi',  NULL,             0.00,    '2025-01-17', TRUE,  NULL);
INSERT INTO students VALUES ('001237', 'Somkid Jaijing',   '0812345680',     3200.00, '2025-01-18', TRUE,  'note ปกติ');
INSERT INTO students VALUES ('001238', 'Sompong Jaiyen',   NULL,             1500.00, '2025-01-19', TRUE,  NULL);
INSERT INTO students VALUES ('000039', 'Somrak Deemak',    '+66898765432',   999.99,  '2025-01-20', TRUE,  NULL);
INSERT INTO students VALUES ('001240', 'Somsak Jairai',    '0876543210',     4100.00, '2025-01-21', TRUE,  'VIP');
INSERT INTO students VALUES ('001241', 'Somwang Sawasdee', NULL,             750.00,  '2025-01-22', FALSE, 'ยกเลิก');
INSERT INTO students VALUES ('001242', 'Somporn Jaidee',   '0819998888',     2200.50, '2025-01-23', TRUE,  NULL);
INSERT INTO students VALUES ('001243', 'Somjit Jaijaroen', '+66834567890',   0.00,    '2025-01-24', FALSE, 'ยังไม่จ่าย');

-- ===== VERIFICATION QUERIES =====

-- Check 1: Row count
SELECT COUNT(*) AS row_count FROM students;
-- Expected: 10

-- Check 2: Nulls per column
SELECT
    COUNT(*) - COUNT(student_id) AS null_id,
    COUNT(*) - COUNT(name)       AS null_name,
    COUNT(*) - COUNT(phone)      AS null_phone,
    COUNT(*) - COUNT(price)      AS null_price,
    COUNT(*) - COUNT(enrolled_on) AS null_date,
    COUNT(*) - COUNT(active)     AS null_active,
    COUNT(*) - COUNT(note)       AS null_note
FROM students;
-- Expected: 0, 0, 3, 0, 0, 0, 4

-- Check 3: IDs — ทุก ID ต้อง 6 หลัก
SELECT student_id FROM students WHERE LENGTH(student_id) != 6;
-- Expected: 0 rows

-- Check 4: Date range check
SELECT MIN(enrolled_on), MAX(enrolled_on) FROM students;
-- Expected: 2025-01-15 to 2025-01-24

-- Check 5: Total price
SELECT SUM(price) AS total_price FROM students;
-- Expected: 17050.99

-- Bonus: cross-check ยอดตรงกับ pandas
SELECT
    SUM(price) AS total,
    COUNT(CASE WHEN active THEN 1 END) AS active_count,
    COUNT(CASE WHEN NOT active THEN 1 END) AS inactive_count
FROM students;

---

Self-Check: ข้ามระบบ

ก่อนส่ง ตรวจว่าทั้ง 3 ระบบให้ผลลัพธ์ตรงกัน:

ตรวจอะไร	Google Sheets	pandas	SQL
Row count	10	10	10
SUM(price)	17,050.99	17050.99	17050.99
IDs ที่ 6 หลัก	10/10	10/10	10/10
Active count	8	8	8
Null phones	3	3	3

Tip

ถ้าตัวเลขไม่ตรงกัน — ย้อนกลับไป Module 5.3 แล้วดูว่า data type ไหนทำให้ยอดต่างกัน อย่า debug logic ก่อนตรวจ type!

---

Rubric Checklist

ใช้ตารางนี้ตรวจงานของตัวเองก่อนส่ง:

เกณฑ์	คะแนน	ตรวจ
Row counts match ทั้ง 3 ระบบ	20%	Sheets = pandas = SQL = 10 rows?
IDs preserved (6 หลัก, leading zeros)	15%	001234 ไม่ใช่ 1234? ทุกระบบ?
Dates correct (ISO 8601)	20%	2025-01-15 ไม่ใช่ 15/01/2025? ทุกระบบ?
Numeric types correct (ยอดตรง)	20%	SUM ตรงกัน? ไม่มี comma ใน price?
Type contract complete	25%	ทุก column มี type + nullable + constraints?

---

เสร็จแล้ว?

Tip

ยินดีด้วย! คุณเพิ่งทำสิ่งที่ data professionals ทำทุกวัน — แปลง messy data ให้เป็นข้อมูลที่เชื่อถือได้ข้ามระบบ ความรู้เรื่อง data types ที่คุณเรียนมาตั้งแต่ Module 0 ถึง Module 5 ล้วนถูกใช้ใน project นี้

Mark Chapter as Complete Completed! Click to undo