資料前處理與SQL資料函式庫查詢應用

– 6 分鐘閱讀

資料分析的基礎在於資料的品質與可用性。商業環境中，龐雜的原始資料需要經過系統化的前處理，才能轉化為有價值的商業洞見。本文將介紹如何運用 Python 的 Pandas 函式庫與 SQL 語法進行資料前處理，包含資料讀取、格式轉換、欄位新增與計算等步驟，並示範如何使用 SQL 進行資料函式庫查詢，提取關鍵資訊。此外，文章也涵蓋資料子集選取、隨機抽樣與分組等技巧，並結合 R 語言進行分析與驗證，確保資料的隨機性與分析結果的可靠性，進一步提升資料分析的效率與精準度，有效支援商業決策。

資料前處理與SQL查詢

在進行資料分析之前，首先需要對資料進行前處理，以確保資料的品質和正確性。在這個例子中，我們使用Python的pandas函式庫來讀取和處理資料。

import pandas as pd
import numpy as np
from pandasql import sqldf

# 讀取資料
c5_mydata_1 = pd.read_csv('Transactions.csv', header=0)

# 檢查資料的形狀和資訊
print(np.shape(c5_mydata_1))
print(c5_mydata_1.info())
print(c5_mydata_1.describe())
print(c5_mydata_1.head())

# 將trans_dt欄位轉換為日期格式
c5_mydata_1['trans_dt'] = pd.to_datetime(c5_mydata_1['trans_dt'])

# 檢查資料的資訊
print(c5_mydata_1.info())

# 將nbr_items欄位轉換為浮點數
c5_mydata_1['nbr_items'] = c5_mydata_1['nbr_items'].astype(float)

# 建立新的欄位sls
c5_mydata_1['sls'] = c5_mydata_1['nbr_items'] * c5_mydata_1['amount']

# 檢查資料的資訊
print(c5_mydata_1.info())

# 使用SQL語法查詢資料
query_1 = """
    SELECT 
        customer_id, 
        SUM(sls) AS tot_sls
    FROM 
        c5_mydata_1
    GROUP BY 
        customer_id
"""

# 執行SQL查詢
c5_mydata_2 = sqldf(query_1)

# 檢查查詢結果
print(c5_mydata_2.head())

看圖說話：

  flowchart TD
    A[讀取資料] --> B[前處理]
    B --> C[轉換日期格式]
    C --> D[轉換資料型別]
    D --> E[建立新的欄位]
    E --> F[使用SQL語法查詢]
    F --> G[執行SQL查詢]
    G --> H[檢查查詢結果]

看圖說話：

上述流程圖展示了資料前處理和SQL查詢的步驟。首先，讀取資料並進行前處理，包括轉換日期格式和資料型別。接下來，建立新的欄位sls，然後使用SQL語法查詢資料。最後，執行SQL查詢並檢查查詢結果。

資料子集選取與抽樣

在進行資料分析時，選取資料子集和抽樣是非常重要的步驟。這些步驟可以幫助我們建立測試和控制組，以便評估行銷活動的效果，或者開發訓練和驗證集以衡量預測模型的準確性。此外，從大型資料集中抽樣可以幫助提高處理速度。

資料子集選取

首先，我們需要建立一個表格來儲存客戶列表。假設我們有一個名為 Customer_List.csv 的檔案，包含客戶 ID 和檔案建立日期。以下是建立表格和載入資料的 SQL 程式碼：

CREATE TABLE retail_db.test_control_groups
(`Customer_ID` VARCHAR(45) NULL,
`file_date` DATE NULL);

LOAD DATA LOCAL INFILE 'C:/Desktop/ANALYSIS/Customer_List.csv'
INTO TABLE retail_db.test_control_groups
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(Customer_ID, file_date);

驗證客戶 ID 獨一性

為了確保客戶 ID 的獨一性，我們需要進行驗證。以下是計算總客戶 ID 數量和獨一性客戶 ID 數量的 SQL 程式碼：

SELECT COUNT(Customer_ID), COUNT(DISTINCT Customer_ID);

如果總客戶 ID 數量和獨一性客戶 ID 數量相同，則表示所有客戶 ID 都是唯一的。

資料抽樣

資料抽樣是從大型資料集中選取一個代表性的子集。以下是使用 SQL 進行資料抽樣的範例：

SELECT *
FROM retail_db.test_control_groups
WHERE RAND() < 0.1;

這個範例會從 test_control_groups 表格中隨機選取 10% 的資料。

看圖說話：
  flowchart TD
    A[資料載入] --> B[資料子集選取]
    B --> C[資料抽樣]
    C --> D[驗證客戶 ID 獨一性]
    D --> E[資料分析]

這個流程圖展示了資料分析的步驟，從資料載入開始，然後進行資料子集選取和抽樣，接著驗證客戶 ID 的獨一性，最後進行資料分析。

資料分組與隨機抽樣

在進行資料分析時，經常需要將資料分成不同的群組，以便進行比較和分析。這裡我們將介紹如何使用SQL和R來實作資料分組和隨機抽樣。

SQL實作

首先，我們需要建立兩個表：test_group和control_group。這兩個表將分別儲存隨機抽樣的客戶ID。

CREATE TABLE retail_db.test_group
SELECT Customer_ID
FROM retail_db.test_control_groups
ORDER BY RAND() LIMIT 30267;

然後，我們可以建立control_group表，包含不在test_group表中的客戶ID。

CREATE TABLE retail_db.control_group
SELECT Customer_ID
FROM retail_db.test_control_groups
WHERE Customer_ID NOT IN (SELECT Customer_ID FROM retail_db.test_group);

接下來，我們需要驗證這兩個表是否互斥，即沒有重覆的客戶ID。

SELECT COUNT(Customer_ID)
FROM retail_db.test_group;

SELECT COUNT(Customer_ID)
FROM retail_db.control_group;

SELECT t1.Customer_ID
FROM retail_db.test_group t1
INNER JOIN retail_db.control_group t2
ON t1.Customer_ID = t2.Customer_ID;

如果上述查詢沒有傳回任何結果，則證明這兩個表是互斥的。

R實作

在R中，我們可以使用read.csv函式來讀取客戶列表檔案。

setwd("C:\\Desktop\\ANALYSIS")
getwd()
c6_mydata_1 <- read.csv("Customer_List.csv", header = TRUE)
dim(c6_mydata_1)
summary(c6_mydata_1)

然後，我們可以使用sample函式來隨機抽樣客戶ID。

set.seed(123)  # 設定隨機種子
test_group <- sample(nrow(c6_mydata_1), 30267, replace = FALSE)
control_group <- setdiff(1:nrow(c6_mydata_1), test_group)

最後，我們可以驗證這兩個群組是否互斥。

length(intersect(test_group, control_group))  # 應該傳回0

如果上述程式碼沒有傳回任何錯誤，則證明這兩個群組是互斥的。

資料分組與隨機性驗證

在進行資料分析時，將資料分組為測試組和控制組是一種常見的做法。為了確保資料的隨機性，我們可以使用隨機分組的方法。

首先，我們需要計算樣本大小。假設我們想要將資料分成兩個相等的部分，我們可以使用 floor 函式來計算樣本大小。

smp_size <- floor(0.50 * nrow(c6_mydata_1))

接下來，我們可以使用 set.seed 函式來設定隨機種子，確保隨機分組的結果可以複製。

set.seed(123)
test_group <- sample(seq_len(nrow(c6_mydata_1)), size = smp_size)

然後，我們可以使用隨機分組的結果來建立測試組和控制組的資料集。

mydata_T <- c6_mydata_1[test_group, ]
mydata_C <- c6_mydata_1[-test_group, ]

為了驗證資料的隨機性，我們可以使用 t.test 函式來比較測試組和控制組的變數是否有統計學上的差異。或者，我們可以使用交叉表來檢視變數的分佈是否相似。

# 載入必要的套件
library(sqldf)
library(dplyr)

# 讀取人口統計資料
mydata_demographics <- read.csv("Demographics.csv", header = TRUE)

# 合併測試組和人口統計資料
mydata_demo_T <- sqldf("
  select a1.customer_id, b1.Gender, b1.City, b1.BluCollarOcc
  from mydata_T A1
  LEFT JOIN mydata_demographics B1 ON a1.customer_id = b1.customer_id
")

# 合併控制組和人口統計資料
mydata_demo_C <- sqldf("
  select a1.customer_id, b1.Gender, b1.City, b1.BluCollarOcc
  from mydata_C A1
  LEFT JOIN mydata_demographics B1 ON a1.customer_id = b1.customer_id
")

現在，我們可以使用 dplyr 套件來分析測試組和控制組的變數是否有統計學上的差異。

# 分析測試組和控制組的變數
mydata_demo_T %>% 
  group_by(Gender, City, BluCollarOcc) %>% 
  summarise(count = n())

mydata_demo_C %>% 
  group_by(Gender, City, BluCollarOcc) %>% 
  summarise(count = n())

透過這些步驟，我們可以確保資料的隨機性和分組的有效性。

這篇文章涵蓋了資料前處理、SQL查詢、資料子集選取、抽樣、分組和隨機性驗證等多個方面，內容豐富，但結構略顯鬆散。以下是一個以玄貓風格撰寫的結論，著重於高階管理者視角，並整合文章的核心觀點：

從內在修養到外在表現的全面檢視顯示，資料分析能力已成為現代管理者的核心競爭力。本文深入剖析了從資料前處理到分組驗證的完整流程，涵蓋Python和SQL等多種工具的應用，展現了資料驅動決策的實踐路徑。觀察高績效長官者的共同特質，他們不僅精通資料分析方法，更能將資料洞察轉化為商業價值。挑戰與瓶頸深析指出，許多管理者受限於資料思維的缺乏，難以將資料分析融入日常決策。突破此限制的關鍵在於培養系統性資料思維，而非僅僅掌握工具操作。未來3-5年，資料分析將不再是專業人士的專利，而是所有管理者的必備技能。對於重視長期發展的高階經理人，系統學習資料分析方法並建立資料驅動的決策模式，將是釋放長官潛力的關鍵所在。玄貓認為，此發展路徑已展現足夠效益，適合關注長期成長的管理者採用。

以下是一些針對文章特定內容的補充說明，可以根據需要新增到結論中：

  • 關於資料品質: 可以強調資料前處理的重要性，例如「資料的品質決定了分析結果的可靠性，如同穩固的根基才能支撐參天大樹。前處理階段的嚴謹程度直接影響後續分析的有效性，不可輕忽。」
  • 關於工具選擇: 可以比較Python和SQL的優劣，並針對不同場景提供建議，例如「Python的靈活性更適合複雜的資料處理和模型構建，而SQL則更擅長於資料函式庫的互動和查詢。高階管理者應根據實際需求選擇合適的工具，或整合兩者的優勢，最大化資料價值。」
  • 關於隨機性驗證: 可以強調驗證的重要性，例如「如同精密的儀器需要校準，資料分組的隨機性也需要嚴格驗證，才能確保分析結果的準確性和可信度。」
  • 關於高階管理者的角色: 可以強調高階管理者應著重於資料洞察的解讀和應用，例如「高階管理者無需成為資料分析的技術專家，但必須具備解讀資料、洞察趨勢、並據此做出明智決策的能力。」

總之，結論的撰寫要緊扣文章核心，並從高階管理者的視角出發，提供有深度、有價值的洞察和建議。

玄貓

玄貓

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。