เปลี่ยนข้อมูล Long เป็น Wide Format ด้วย R Excel และ SQL

บทความแรกของปี 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 ได้ง่ายๆในสองขั้นตอน

1. คลิกที่ raw data ของเรา เสร็จแล้วไปที่ Insert > Pivot Table คลิก OK
2. ลากคอลัมน์ต่างใน 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 ตัวเต็มได้ที่นี่