บทความแรกของปี 2020 วันนี้แอดมารีวิวเทคนิคการ transform ข้อมูลจาก long format เป็น wide format ง่ายๆด้วยเครื่องมือสามตัวเรียงจากง่ายสุดไปยากสุด ทั้งในด้านประสิทธิภาพและเวลาในการ execute

ก่อนอื่นมาทำความรู้จักกับ long และ wide format กันก่อน ปกติเวลาเราทำงานกับข้อมูล (โดยเฉพาะ R และ Excel) เราจะนิยมข้อมูลแบบ long format มากกว่า เพราะเป็นรูปแบบที่เหมาะกับการทำงานวิเคราะห์ข้อมูล
- Long เหมาะสำหรับการทำ data analysis (raw data)
- Wide เหมาะสำหรับรายงาน สรุปผลนำเสนอข้อมูล
โอเค! คราวนี้มาดูเทคนิคการเปลี่ยน long → wide format (หรือเปลี่ยนจาก wide → long) ง่ายๆด้วยภาษา R, Excel Pivot Table และ SQL เรียงตามความเร็วและความง่ายในการ execute
ดาวน์โหลดตัวอย่างข้อมูล long_format.csv สำหรับ tutorial ได้ที่นี่
R
ส่วนตัวแอดคิดว่าวิธีที่เร็วและใช้งานง่ายที่สุดคือการใช้ฟังก์ชัน spread() และ gather() ของ R สำหรับ transform ข้อมูล long-wide และ wide-long ตามลำดับ เขียนโค้ดแค่ไลน์เดียวก็เสร็จแล้ว ทำไมง่าย 555+
Note – สองฟังก์ชันนี้อยู่ใน package tidyr (หรือจะโหลด tidyverse library เลยก็ได้)
## long to wide format
long_format %>% spread(year, jazz_revenue)
## wide to long format
wide_format %>% gather(year, jazz_revenue, `2009`:`2013`, -fullname)

Pivot Table
Pivot Table คือหนึ่งในเครื่องมือที่ทรงพลังมากๆ (Power Tools) ของ Excel ส่วนตัวแอดแนะนำเพื่อนๆที่อยากเป็น data analyst ให้ใช้ Pivot ให้คล่องๆ จะช่วยตอนทำงานจริงเยอะเลยเวลาต้องทำรายงาน + สรุปผลข้อมูล

เราสามารถสร้าง Pivot Table เปลี่ยนข้อมูล long format เป็น wide format ได้ง่ายๆในสองขั้นตอน
- คลิกที่ raw data ของเรา เสร็จแล้วไปที่ Insert > Pivot Table คลิก OK
- ลากคอลัมน์ต่างใน Pivot Table Fields ไปวางตามรูปด้านล่าง โดยที่ rows = fullname, columns = year และ values = jazz_revenue

Note – ก่อนใช้งาน Pivot Table เราต้องปรับหน้าตาข้อมูลของเราให้เป็น long format ก่อน 😛 Pivot Table works best with long format dataset (the same is true with R)
SQL
และวิธีสุดท้ายที่แอดจะเลือกใช้เพื่อเปลี่ยน long เป็น wide format คือ SQL (จะใช้เวลาที่มีแต่ SQL ให้ใช้เท่านั้น 555+) โดยเทคนิคที่เราใช้กันประจำคือ CASE WHEN ..
- เขียน SUM( CASE WHEN .. ) เพื่อหาผลรวม jazz_revenue แยกตามปี
- และจับกลุ่มค่าสถิติ (ผลรวม) ตามชื่อ fullname ด้วย GROUP BY clause
SELECT
fullname,
SUM(CASE WHEN year = '2009' THEN jazz_revenue END) '2009',
SUM(CASE WHEN year = '2010' THEN jazz_revenue END) '2010',
SUM(CASE WHEN year = '2011' THEN jazz_revenue END) '2011',
SUM(CASE WHEN year = '2012' THEN jazz_revenue END) '2012',
SUM(CASE WHEN year = '2013' THEN jazz_revenue END) '2013',
SUM(jazz_revenue) total_sales
FROM tmp
GROUP BY 1 ORDER BY total_sales DESC;
Note – CASE คือการเขียนเงื่อนไข เหมือนฟังก์ชัน IF/ IFS ของ Excel ส่วน query ด้านบน tmp คือ long_format table ที่เราโหลดเข้า database

Summary
ส่วนตัวแอดคิดว่า Data Analyst อย่างพวกเราควรเลือกใช้เครื่องมือให้เหมาะสมกับงานที่เราต้องการทำให้เสร็จ ถ้าต้องการเปลี่ยน long เป็น wide format (หรือ wide เป็น long) R คือตัวเลือกอันดับหนึ่งเลย รองมาคือ Pivot Table และ SQL ตามลำดับ เรียงตามประสิทธิภาพและเวลาในการ execute
Highlight – อย่ายึดติดกับเครื่องมือ แต่จงยึดติดกับไอเดีย (be obsessed with ideas)
Appendix
SQL query สำหรับสร้างข้อมูล long format ที่ใช้ในบทความนี้ มีสามคอลัมน์ fullname, year, jazz_revenue (รายได้เพลง jazz ของพนักงานแต่ละคน ตั้งแต่ปี 2009 – 2013)
SELECT
E.firstname || ' ' || E.lastname fullname,
STRFTIME('%Y',invoicedate) year,
SUM(total) jazz_revenue
FROM invoices A JOIN invoice_items B ON A.invoiceid = B.invoiceid
JOIN tracks C ON B.trackid = C.trackid
JOIN customers D ON A.customerid = D.customerid
JOIN employees E ON E.employeeid = D.supportrepid
JOIN genres F ON C.genreid = F.genreid
AND F.name = 'Jazz'
GROUP BY 1, 2;
ตัวอย่าง long_format.csv ที่แอดใช้ในบทความนี้ดึงมาจาก chinook.db (popular database ที่ใช้สอน SQLite) เพื่อนๆสามารถดาวน์โหลด chinook.db ตัวเต็มได้ที่นี่