基于 Python 的實現：居民用電量數據分析與可視化

本文將介紹如何利用 Python 技術棧（包括 pymysql、pandas、matplotlib 等庫）對居民用電量數據進行分析和可視化，以幫助我們更好地理解用電行為模式。

數據準備

在MySQL數據庫中創建數據，，數據庫表結構如下：

date：記錄日期
resident：居民標識
consumption：當日用電量（千瓦時）

-- 創建表語句..
CREATE TABLE `daily_electricity` (`id` int NOT NULL AUTO_INCREMENT,`date` date NOT NULL,`resident` varchar(50) NOT NULL,`consumption` int NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `unique_record` (`date`,`resident`)
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- 插入數據語句..INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (1, '2025-02-27', '居民1', 26);
-- 更多數據記錄...

讀取數據

通過 pymysql 庫連接數據庫并獲取數據：

import pymysqldef fetch_data_from_db():"""從數據庫中讀取用電量數據"""connection = pymysql.connect(**DB_CONFIG)data = []try:with connection.cursor() as cursor:cursor.execute("SELECT date, resident, consumption FROM daily_electricity ORDER BY date, resident")data = cursor.fetchall()finally:connection.close()return data

數據處理

將獲取的數據轉換為 pandas DataFrame 格式，便于后續分析：

import pandas as pddef convert_to_dataframe(data):"""將數據轉換為 DataFrame 格式"""df = pd.DataFrame(data, columns=['date', 'resident', 'consumption'])df['date'] = pd.to_datetime(df['date'])return df

數據可視化

1. 折線圖：居民用電量趨勢

通過折線圖可以直觀觀察每個居民在不同日期的用電量變化趨勢：

def plot_trends(df):plt.figure(figsize=(12, 6))for resident, group in df.groupby('resident'):plt.plot(group['date'], group['consumption'], marker='o', linestyle='-', label=resident)plt.title('居民每日用電量趨勢')plt.xlabel('日期')plt.ylabel('用電量 (千瓦時)')plt.legend()plt.xticks(rotation=45)plt.grid(True)plt.tight_layout()plt.show()

在這里插入圖片描述

2. 柱狀圖：最近 7 天平均用電量對比

展示最近一周內各居民的平均用電量對比：

def plot_bar_chart(df):recent_df = df[df['date'] >= df['date'].max() - pd.Timedelta(days=7)]avg_consumption = recent_df.groupby('resident')['consumption'].mean().reset_index()plt.figure(figsize=(10, 5))plt.bar(avg_consumption['resident'], avg_consumption['consumption'], color='orange')plt.title('最近 7 天各居民平均用電量對比')plt.xlabel('居民')plt.ylabel('平均用電量 (千瓦時)')plt.xticks(rotation=0)plt.grid(True, axis='y')plt.tight_layout()plt.show()

在這里插入圖片描述

3. 餅圖：總用電量分布

展示各居民在統計周期內的總用電量占比：

def plot_pie_chart(df):total_consumption = df.groupby('resident')['consumption'].sum()plt.figure(figsize=(8, 8))plt.pie(total_consumption, labels=total_consumption.index, autopct='%1.1f%%', startangle=90, shadow=True, explode=[0.05] * len(total_consumption))plt.title('各居民總用電量占比')plt.axis('equal')plt.tight_layout()plt.show()

在這里插入圖片描述

4. 熱力圖：每周用電量分布

通過熱力圖觀察不同星期幾和不同周的用電量分布模式：

def plot_heatmap(df):df['weekday'] = df['date'].dt.day_name()df['week_number'] = df['date'].dt.isocalendar().weekweekly_data = df.groupby(['week_number', 'weekday'])['consumption'].mean().unstack()weekday_order = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']weekly_data = weekly_data.reindex(columns=weekday_order)plt.figure(figsize=(12, 8))plt.imshow(weekly_data, cmap='YlOrRd')plt.colorbar(label='平均用電量 (千瓦時)')plt.title('每周用電量熱力圖')plt.xticks(np.arange(len(weekday_order)), weekday_order, rotation=45)plt.yticks(np.arange(len(weekly_data.index)), weekly_data.index)plt.xlabel('星期')plt.ylabel('周數')for i in range(len(weekly_data.index)):for j in range(len(weekday_order)):if not pd.isna(weekly_data.iloc[i, j]):plt.text(j, i, f"{weekly_data.iloc[i, j]:.1f}", ha="center", va="center", color="black" if weekly_data.iloc[i, j] < 30 else "white")plt.tight_layout()plt.show()

在這里插入圖片描述

5. 箱線圖：用電量分布情況

展示各居民用電量的分布特征（中位數、四分位數、異常值等）：

def plot_boxplot(df):plt.figure(figsize=(10, 6))plt.boxplot([df[df['resident'] == resident]['consumption'] for resident in df['resident'].unique()], labels=df['resident'].unique())plt.title('各居民用電量分布箱線圖')plt.xlabel('居民')plt.ylabel('用電量 (千瓦時)')plt.grid(True, linestyle='--')plt.tight_layout()plt.show()

在這里插入圖片描述

主程序與執行流程

完整的代碼執行流程如下：

if __name__ == "__main__":# 從數據庫讀取數據fetched_data = fetch_data_from_db()df = convert_to_dataframe(fetched_data)# 繪制各種圖表plot_trends(df)plot_bar_chart(df)plot_pie_chart(df)plot_heatmap(df)plot_boxplot(df)

通過多種圖表形式直觀展示用電模式和趨勢。這種分析方法有助于：

識別用電高峰和低谷時段
發現異常用電行為
制定個性化的節能策略
優化社區能源分配

共享源代碼：

1、數據庫數據：

INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (1, '2025-02-27', '居民1', 26);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (2, '2025-02-27', '居民2', 35);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (3, '2025-02-27', '居民3', 28);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (4, '2025-02-27', '居民4', 33);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (5, '2025-02-27', '居民5', 25);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (6, '2025-02-28', '居民1', 29);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (7, '2025-02-28', '居民2', 35);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (8, '2025-02-28', '居民3', 33);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (9, '2025-02-28', '居民4', 37);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (10, '2025-02-28', '居民5', 32);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (11, '2025-03-01', '居民1', 23);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (12, '2025-03-01', '居民2', 24);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (13, '2025-03-01', '居民3', 17);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (14, '2025-03-01', '居民4', 20);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (15, '2025-03-01', '居民5', 19);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (16, '2025-03-02', '居民1', 37);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (17, '2025-03-02', '居民2', 32);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (18, '2025-03-02', '居民3', 31);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (19, '2025-03-02', '居民4', 40);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (20, '2025-03-02', '居民5', 33);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (21, '2025-03-03', '居民1', 30);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (22, '2025-03-03', '居民2', 31);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (23, '2025-03-03', '居民3', 29);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (24, '2025-03-03', '居民4', 25);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (25, '2025-03-03', '居民5', 21);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (26, '2025-03-04', '居民1', 20);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (27, '2025-03-04', '居民2', 25);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (28, '2025-03-04', '居民3', 18);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (29, '2025-03-04', '居民4', 20);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (30, '2025-03-04', '居民5', 25);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (31, '2025-03-05', '居民1', 17);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (32, '2025-03-05', '居民2', 20);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (33, '2025-03-05', '居民3', 17);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (34, '2025-03-05', '居民4', 20);
INSERT INTO `jm_ysl`.`daily_electricity` (`id`, `date`, `resident`, `consumption`) VALUES (35, '2025-03-05', '居民5', 16);
INSERT INTO `jm_ysl`.`daily_electricity` (