データ分析師試験問題集：SQL、Python、線形回帰アルゴリズムによる予測

データ分析師 - 試験問題

ファイル名にはご自身の名前を付けて提出してください

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error, r2_score
from datetime import datetime
from pyecharts import options as opts
from pyecharts.charts import Line
from pyecharts.commons.utils import JsCode
from pandasql import sqldf

Pandasデータ前処理

df_event = pd.DataFrame(data=
             [
                 ['TikTok','ダブルイレブン','2022-11-09','2022-11-11'],
                 ['Weibo','ダブルイレブン','2022-11-10','2022-11-11'],
                 ['TikTok','クリスマス祭り','2022-12-24','2022-12-26'],
                 ['Weibo','クリスマス祭り','2022-12-23','2022-12-25']
             ],columns=['プラットフォーム','イベント名','開始日','終了日'])
df_event
プラットフォーム イベント名 開始日 終了日
0 TikTok ダブルイレブン 2022-11-09 2022-11-11
1 Weibo ダブルイレブン 2022-11-10 2022-11-11
2 TikTok クリスマス祭り 2022-12-24 2022-12-26
3 Weibo クリスマス祭り 2022-12-23 2022-12-25 
df_heat = pd.DataFrame(data=
             [
                 ['2022-11-09','TikTok',2000],
                 ['2022-11-10','TikTok',4184],
                 ['2022-11-11','TikTok',25816],
                 ['2022-11-12','TikTok',1000],
                 ['2022-11-09','Weibo',4000],
                 ['2022-11-10','Weibo',16781],
                 ['2022-11-11','Weibo',13219],
                 ['2022-11-12','Weibo',6000],
                 ['2022-11-12','Weibo',6000],
                 ['2022-11-12','Weibo',6000],
                 ['2022-11-12','Weibo',6000],
                 ['2022-12-23','TikTok',4000],
                 ['2022-12-24','TikTok',8618],
                 ['2022-12-25','TikTok',11382],
                 ['2022-12-26','TikTok',6000],
                 ['2022-12-23','Weibo',2000],
                 ['2022-12-24','Weibo',5129],
                 ['2022-12-25','Weibo',14871],
                 ['2022-12-26','Weibo',1000]
             ],columns=['日付','プラットフォーム','人気度'])
df_heat
日付 プラットフォーム 人気度
0 2022-11-09 TikTok 2000
1 2022-11-10 TikTok 4184
2 2022-11-11 TikTok 25816
3 2022-11-12 TikTok 1000
4 2022-11-09 Weibo 4000
5 2022-11-10 Weibo 16781
6 2022-11-11 Weibo 13219
7 2022-11-12 Weibo 6000
8 2022-11-12 Weibo 6000
9 2022-11-12 Weibo 6000
10 2022-11-12 Weibo 6000
11 2022-12-23 TikTok 4000
12 2022-12-24 TikTok 8618
13 2022-12-25 TikTok 11382
14 2022-12-26 TikTok 6000
15 2022-12-23 Weibo 2000
16 2022-12-24 Weibo 5129
17 2022-12-25 Weibo 14871
18 2022-12-26 Weibo 1000

データの重複削除

df_heatから重複を削除し、【日付】と【プラットフォーム】の組み合わせを一意に保ち、元のdf_heatを置き換えます

df_heat = df_heat.drop_duplicates(subset=['日付', 'プラットフォーム'])
df_heat
日付 プラットフォーム 人気度
0 2022-11-09 TikTok 2000
1 2022-11-10 TikTok 4184
2 2022-11-11 TikTok 25816
3 2022-11-12 TikTok 1000
4 2022-11-09 Weibo 4000
5 2022-11-10 Weibo 16781
6 2022-11-11 Weibo 13219
10 2022-11-12 Weibo 6000
11 2022-12-23 TikTok 4000
12 2022-12-24 TikTok 8618
13 2022-12-25 TikTok 11382
14 2022-12-26 TikTok 6000
15 2022-12-23 Weibo 2000
16 2022-12-24 Weibo 5129
17 2022-12-25 Weibo 14871
18 2022-12-26 Weibo 1000

データ型変換

df_eventとdf_heatのデータ型を確認し、数値と日付を正しい形式に変換して、元のdf_eventとdf_heatを置き換え、再度データ型を確認します

df_event.dtypes

プラットフォーム      object
イベント名        object
開始日          object
終了日          object
dtype: object

df_heat.dtypes

日付    object
プラットフォーム    object
人気度    object
dtype: object

df_event['開始日'] = pd.to_datetime(df_event['開始日'])
df_event['終了日'] = pd.to_datetime(df_event['終了日'])
df_event.dtypes

プラットフォーム             object
イベント名               object
開始日         datetime64[ns]
終了日         datetime64[ns]
dtype: object

df_heat['日付'] = pd.to_datetime(df_heat['日付'])
df_heat['人気度'] = pd.to_numeric(df_heat['人気度'])
df_heat.dtypes

日付         datetime64[ns]
プラットフォーム        object
人気度           int64
dtype: object

条件付き列の追加

df_heatに【人気度レベル】列を追加します。人気度が10000以上の場合は「高人気度」、10000未満の場合は「低人気度」とマークし、元のdf_heatを置き換えます

df_heat['人気度レベル'] = df_heat['人気度'].apply(lambda x: '高人気度' if x >= 10000 else '低人気度')
df_heat
日付 プラットフォーム 人気度 人気度レベル
0 2022-11-09 TikTok 2000 低人気度
1 2022-11-10 TikTok 4184 低人気度
2 2022-11-11 TikTok 25816 高人気度
3 2022-11-12 TikTok 1000 低人気度
4 2022-11-09 Weibo 4000 低人気度
5 2022-11-10 Weibo 16781 高人気度
6 2022-11-11 Weibo 13219 高人気度
10 2022-11-12 Weibo 6000 低人気度
11 2022-12-23 TikTok 4000 低人気度
12 2022-12-24 TikTok 8618 低人気度
13 2022-12-25 TikTok 11382 高人気度
14 2022-12-26 TikTok 6000 低人気度
15 2022-12-23 Weibo 2000 低人気度
16 2022-12-24 Weibo 5129 低人気度
17 2022-12-25 Weibo 14871 高人気度
18 2022-12-26 Weibo 1000 低人気度

df_eventを使用して、df_heatに【イベント名】列を追加します。【日付】と【イベント名】が正しく対応している必要があり、イベントのない日は【イベント名】列に「イベントなし」と表示します。元のdf_heatを置き換えます

df_merged = pd.merge(df_heat, df_event[['プラットフォーム', 'イベント名', '開始日', '終了日']], 
                    left_on=['日付', 'プラットフォーム'], 
                    right_on=['開始日', 'プラットフォーム'], 
                    how='left')
df_merged['イベント名'].fillna('イベントなし', inplace=True)
df_heat = df_merged[['日付', 'プラットフォーム', '人気度', 'イベント名']]
df_heat
日付 プラットフォーム 人気度 人気度レベル イベント名
0 2022-11-09 TikTok 2000 低人気度 ダブルイレブン
1 2022-11-10 TikTok 4184 低人気度 ダブルイレブン
2 2022-11-11 TikTok 25816 高人気度 ダブルイレブン
3 2022-11-12 TikTok 1000 低人気度 イベントなし
4 2022-11-09 Weibo 4000 低人気度 イベントなし
5 2022-11-10 Weibo 16781 高人気度 ダブルイレブン
6 2022-11-11 Weibo 13219 高人気度 ダブルイレブン
10 2022-11-12 Weibo 6000 低人気度 イベントなし
11 2022-12-23 TikTok 4000 低人気度 イベントなし
12 2022-12-24 TikTok 8618 低人気度 クリスマス祭り
13 2022-12-25 TikTok 11382 高人気度 クリスマス祭り
14 2022-12-26 TikTok 6000 低人気度 クリスマス祭り
15 2022-12-23 Weibo 2000 低人気度 クリスマス祭り
16 2022-12-24 Weibo 5129 低人気度 クリスマス祭り
17 2022-12-25 Weibo 14871 高人気度 クリスマス祭り
18 2022-12-26 Weibo 1000 低人気度 イベントなし

データピボット分析

各プラットフォームの各イベント（イベントなしを含む）について、人気度の合計、継続日数、平均人気度、人気度の標準偏差を求めます

summary = df_merged.groupby(['プラットフォーム', 'イベント名']).agg({
    '人気度': ['sum', 'count', 'mean', 'std'],
    '日付': lambda x: (max(x) - min(x)).days + 1  # 継続日数の計算
})
# インデックスをリセットして表示しやすくする
summary = summary.reset_index()
summary
sum count mean std
人気度 人気度 人気度 人気度
プラットフォーム イベント名
Weibo ダブルイレブン 30000 2 15000.000000 2518.714355
クリスマス祭り 22000 3 7333.333333 6712.672667
イベントなし 11000 3 3666.666667 2516.611478
TikTok ダブルイレブン 32000 3 10666.666667 13165.074604
クリスマス祭り 26000 3 8666.666667 2691.330031
イベントなし 5000 2 2500.000000 2121.320344

Pyechartsデータ可視化

Weiboの2022-11-09から2022-11-12までの人気度の折れ線グラフを作成し、ダブルイレブンイベントの範囲、人気度の最大値と平均値線をマークします

# Weiboの2022-11-09から2022-11-12のデータを抽出
df_weibo = df_heat[(df_heat['プラットフォーム'] == 'Weibo') & 
                  (df_heat['日付'] >= '2022-11-09') & 
                  (df_heat['日付'] <= '2022-11-12')]

# 日付と人気度データを抽出
date_list = df_weibo['日付'].dt.strftime('%Y-%m-%d').tolist()
heat_list = df_weibo['人気度'].tolist()

# 人気度の最大値と平均値を計算
max_heat = df_weibo['人気度'].max()
mean_heat = df_weibo['人気度'].mean()

# 折れ線グラフを描画
line = (
    Line()
    .add_xaxis(date_list)
    .add_yaxis("人気度", heat_list, markpoint_opts=opts.MarkPointOpts(
        data=[
            opts.MarkPointItem(type_="max", name="人気度最大値"),
            opts.MarkPointItem(type_="average", name="人気度平均値")
        ]
    ))
    .set_global_opts(
        title_opts=opts.TitleOpts(title="Weibo人気度折れ線グラフ (2022-11-09 至 2022-11-12)"),
        xaxis_opts=opts.AxisOpts(axislabel_opts={"rotate": 45}),
        yaxis_opts=opts.AxisOpts(name="人気度"),
        tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross"),
    )
    .render("weibo_heat_line_chart.html")
)

print("グラフファイル名: weibo_heat_line_chart.html")

SQL

df_content = pd.read_excel(r'data.xlsx',sheet_name='記事情報表')
df_rule = pd.read_excel(r'data.xlsx',sheet_name='人気度計算ルール表')

# SQLクエリを実行するための関数を定義
pysqldf = lambda q: sqldf(q, globals())

# クエリの例
q = """SELECT *
       FROM df_content 
       LIMIT 3
       ;"""
pysqldf(q)
日付 プラットフォーム リツイート コメント いいね 閲覧数 人気度
0 2023-05-01 00:00:00.000000 プラットフォームA 1 76 838 139879 9325.266667
1 2023-05-01 00:00:00.000000 プラットフォームA 2 1 3 2846 189.733333
2 2023-05-01 00:00:00.000000 プラットフォームA 1 1 15 2325 155.000000

記事情報表： この表は記事データであり、人気度は人気度計算ルール表に基づいて計算されたものです。計算誤りのある人気度が存在します 日付 datetime64[ns] プラットフォーム object リツイート int64 コメント int64 いいね int64 閲覧数 int64 人気度 float64

人気度計算ルール表： プラットフォーム object 計算ルール object

プラットフォームCラベルデータ： この表はプラットフォームCの実際の閲覧数を含むデータであり、異常値が存在します リツイート int64 コメント int64 いいね int64 閲覧数 int64

SQLキーワードを記述し、簡単に説明してください

-- SELECT: データベースからデータを取得します
-- FROM: データを取得するテーブルを指定します
-- WHERE: 取得するデータの条件を指定します
-- GROUP BY: データをグループ化します
-- HAVING: グループ化したデータの条件を指定します
-- ORDER BY: 結果を並び替えます
-- JOIN: 2つ以上のテーブルを結合します
-- INNER JOIN: 両方のテーブルに存在するデータのみを結合します
-- LEFT JOIN: 左側のテーブルのすべてのデータと、右側のテーブルに一致するデータを結合します
-- RIGHT JOIN: 右側のテーブルのすべてのデータと、左側のテーブルに一致するデータを結合します
-- FULL JOIN: 両方のテーブルのすべてのデータを結合します
-- UNION: 2つのSELECT文の結果を結合します
-- UNION ALL: UNIONと同じですが、重複する行も含みます
-- INSERT INTO: テーブルにデータを挿入します
-- UPDATE: テーブルのデータを更新します
-- DELETE FROM: テーブルからデータを削除します
-- CREATE TABLE: 新しいテーブルを作成します
-- ALTER TABLE: 既存のテーブルを変更します
-- DROP TABLE: テーブルを削除します
-- CREATE INDEX: インデックスを作成します
-- DROP INDEX: インデックスを削除します
-- PRIMARY KEY: 一意の識別子としてテーブルの主キーを設定します
-- FOREIGN KEY: 別のテーブルの主キーを参照する外部キーを設定します
-- CONSTRAINT: テーブルの制約を設定します
-- INDEX: テーブルのインデックスを作成します
-- UNIQUE: 列の値が一意であることを保証します
-- NOT NULL: 列にNULL値を許可しないことを指定します
-- DEFAULT: 列のデフォルト値を設定します
-- CHECK: 列の値が特定の条件を満たすことを保証します
-- VIEW: 仮想的なテーブルを作成します
-- SEQUENCE: 一意の数値を生成するシーケンスを作成します
-- TRIGGER: 特定のイベントが発生したときに実行されるトリガーを作成します
-- PROCEDURE: データベースで実行される手続きを作成します
-- FUNCTION: 値を返すデータベース関数を作成します

記事情報表中のすべてのプラットフォームの人気度計算ルールをクエリし、ルールがない場合は「ルールなし」と返します

SELECT 
    プラットフォーム,
    CASE WHEN プラットフォーム IN (SELECT プラットフォーム FROM df_rule) THEN 計算ルール ELSE 'ルールなし' END AS 計算ルール
FROM 
    df_content
GROUP BY 
    プラットフォーム;
プラットフォーム 計算ルール
0 プラットフォームA 閲覧数/15
1 プラットフォームB (閲覧数-いいね)/3
2 プラットフォームC 予測モデル
3 プラットフォームD ルールなし

記事情報表中から最新の日付のプラットフォームAで人気度が最も高いデータをフィルタリングします

SELECT *
FROM df_content
WHERE プラットフォーム = 'プラットフォームA'
AND 日付 = (SELECT MAX(日付) FROM df_content WHERE プラットフォーム = 'プラットフォームA')
ORDER BY 人気度 DESC
LIMIT 1;
日付 プラットフォーム リツイート コメント いいね 閲覧数 人気度
0 2023-05-06 00:00:00.000000 プラットフォームA 11 163 1107 18468 1231.2

毎日各プラットフォームの総人気度を降順でランキング表示します

WITH daily_total AS (
    SELECT 
        日付,
        プラットフォーム,
        SUM(人気度) AS 総人気度
    FROM 
        df_content
    GROUP BY 
        日付, プラットフォーム
)
SELECT 
    日付,
    プラットフォーム,
    総人気度,
    RANK() OVER (PARTITION BY 日付 ORDER BY 総人気度 DESC) AS ランキング
FROM 
    daily_total;
日付 プラットフォーム 総人気度 ランキング
0 2023-05-01 00:00:00.000000 プラットフォームA 9670.000000 1
1 2023-05-01 00:00:00.000000 プラットフォームB 2446.666667 2
2 2023-05-01 00:00:00.000000 プラットフォームD 846.000000 3
3 2023-05-01 00:00:00.000000 プラットフォームC NaN 4
4 2023-05-02 00:00:00.000000 プラットフォームB 7488.000000 1
5 2023-05-02 00:00:00.000000 プラットフォームD 1500.000000 2
6 2023-05-02 00:00:00.000000 プラットフォームA 1053.666667 3
7 2023-05-02 00:00:00.000000 プラットフォームC NaN 4
8 2023-05-03 00:00:00.000000 プラットフォームB 27850.666667 1
9 2023-05-03 00:00:00.000000 プラットフォームD 1158.000000 2
10 2023-05-03 00:00:00.000000 プラットフォームA 816.200000 3
11 2023-05-03 00:00:00.000000 プラットフォームC NaN 4
12 2023-05-04 00:00:00.000000 プラットフォームB 9646.466667 1
13 2023-05-04 00:00:00.000000 プラットフォームD 948.000000 2
14 2023-05-04 00:00:00.000000 プラットフォームA 83.400000 3
15 2023-05-04 00:00:00.000000 プラットフォームC NaN 4
16 2023-05-05 00:00:00.000000 プラットフォームB 9376.333333 1
17 2023-05-05 00:00:00.000000 プラットフォームA 3568.733333 2
18 2023-05-05 00:00:00.000000 プラットフォームD 926.000000 3
19 2023-05-05 00:00:00.000000 プラットフォームC NaN 4
20 2023-05-06 00:00:00.000000 プラットフォームB 8231.000000 1
21 2023-05-06 00:00:00.000000 プラットフォームD 1439.000000 2
22 2023-05-06 00:00:00.000000 プラットフォームA 1404.066667 3
23 2023-05-06 00:00:00.000000 プラットフォームC NaN 4

アルゴリズム問題

n + 1個の整数を含む配列numsが与えられ、その数字はすべて[1, n]の範囲内（1とnを含む）です。少なくとも1つの重複整数が存在することがわかります。 numsに重複する整数が1つしかないと仮定し、その重複数の2つの位置を返してください。 設計されたソリューションは、配列numsを変更せず、定数レベルO(1)の追加スペースしか使用してはなりません。

例1： 入力：nums = [1,3,4,2,2] 出力：3,4

例2： 入力：nums = [3,1,3,4,2] 出力：0,2

機械学習

目標：df_trainに300件のデータがあり、リツイート、コメント、いいねを特徴量として使用して、閲覧数を予測する回帰モデルを訓練します 要件：訓練セットとテストセットの比率は8:2、データクリーニング処理プロセスは正常かつ合理的、モデルは制限なし、最終的に予測閲覧数と実際の閲覧数の比較散布図を出力し、それぞれ訓練セットとテストセットの回帰モデルの評価指標（MSE、RMSE、R二乗、調整済みR二乗）を出力します

# 線形回帰モデルを使用して予測

# データの読み込み
df_train = pd.read_excel(r'data.xlsx', sheet_name='プラットフォームCラベルデータ')

# 欠損値の割合を確認
null_counts = df_train.isnull().mean()
print("各列の欠損値割合:")
print(null_counts)

各列の欠損値割合:
リツイート    0.013333
コメント    0.018333
いいね    0.023333
閲覧数    0.046667
dtype: float64

# 欠損値を埋める（欠損値の割合が少ないので平均値で埋める）
df_train.fillna(df_train.mean(), inplace=True)

# 欠損値が正しく埋められたか確認
null_counts = df_train.isnull().mean()
print("各列の欠損値割合:")
print(null_counts)

各列の欠損値割合:
リツイート    0.0
コメント    0.0
いいね    0.0
閲覧数    0.0
dtype: float64

# 特徴量と目的変数を抽出
X = df_train[['リツイート', 'コメント', 'いいね']]
y = df_train['閲覧数']

# 訓練セットとテストセットに分割
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# 線形回帰モデルを初期化
model = LinearRegression()

# 訓練セットでモデルを訓練
model.fit(X_train, y_train)

# 訓練セットとテストセットで予測
y_train_pred = model.predict(X_train)
y_test_pred = model.predict(X_test)

# 評価指標の計算
def calculate_metrics(y_true, y_pred, num_features):
    mse = mean_squared_error(y_true, y_pred)
    rmse = np.sqrt(mse)
    r2 = r2_score(y_true, y_pred)
    adj_r2 = 1 - (1 - r2) * ((len(y_true) - 1) / (len(y_true) - num_features - 1))
    return mse, rmse, r2, adj_r2

mse_train, rmse_train, r2_train, adj_r2_train = calculate_metrics(y_train, y_train_pred, X_train.shape[1])
mse_test, rmse_test, r2_test, adj_r2_test = calculate_metrics(y_test, y_test_pred, X_test.shape[1])

print("訓練セットの評価指標:")
print(f"MSE: {mse_train:.2f}")
print(f"RMSE: {rmse_train:.2f}")
print(f"R二乗: {r2_train:.4f}")
print(f"調整済みR二乗: {adj_r2_train:.4f}")
print("---")
print("テストセットの評価指標:")
print(f"MSE: {mse_test:.2f}")
print(f"RMSE: {rmse_test:.2f}")
print(f"R二乗: {r2_test:.4f}")
print(f"調整済みR二乗: {adj_r2_test:.4f}")

訓練セットの評価指標:
MSE: 17467402650.05
RMSE: 132164.30
R二乗: 0.9115
調整済みR二乗: 0.9109
---
テストセットの評価指標:
MSE: 8182461073.15
RMSE: 90456.96
R二乗: 0.7999
調整済みR二乗: 0.7948

# 予測結果の可視化
plt.rcParams['font.sans-serif'] = ['SimHei']  # 日本語フォントを設定
plt.rcParams.update({'font.size': 12})  # フォントサイズを更新
plt.rcParams.update({'figure.autolayout': True})  # レイアウトを自動調整

plt.figure(figsize=(10, 6))

# 訓練セット
plt.scatter(y_train, y_train_pred, color='blue', label='訓練セット')
# テストセット
plt.scatter(y_test, y_test_pred, color='orange', label='テストセット')

plt.title('予測値 vs 実際値')
plt.xlabel('実際の閲覧数')
plt.ylabel('予測閲覧数')
plt.legend()
plt.show()

# プラットフォームCのリツイート、コメント、いいねデータを抽出
df_articles = pd.read_excel(r'data.xlsx', sheet_name='記事情報表')
df_X_C = df_articles[df_articles['プラットフォーム'] == 'プラットフォームC'][['リツイート', 'コメント', 'いいね']]
print(df_X_C.head())

     リツイート  コメント    いいね
40   19  25  3474
41   18  92   414
42  210  19  3753
43  201   7  2086
44   84  33  1016

# 予測を実行
y_pred_C = model.predict(df_X_C)
y_pred_C

array([150419.44114891,  73813.26050879, 175368.53973794, 134022.42539186,
        96194.23690129,  74351.84542786, 300595.84222147,  83995.71348931,
       127682.76682427,  99185.74854635,  95565.24982545,  95049.821038  ,
        90571.79145297, 143330.88282334,  72418.61805704, 100992.87979393,
        81403.62573173,  82871.4949947 ,  76594.46109362,  76695.42144577])

# プラットフォームCの閲覧数を予測値で更新
df_articles.loc[df_articles['プラットフォーム'] == 'プラットフォームC', '閲覧数'] = y_pred_C
df_articles['閲覧数'] = df_articles['閲覧数'].round(0)
df_articles.loc[df_articles['プラットフォーム'] == 'プラットフォームC', '閲覧数']

40    150419.0
41     73813.0
42    175369.0
43    134022.0
44     96194.0
45     74352.0
46    300596.0
47     83996.0
48    127683.0
49     99186.0
50     95565.0
51     95050.0
52     90572.0
53    143331.0
54     72419.0
55    100993.0
56     81404.0
57     82871.0
58     76594.0
59     76695.0
Name: 閲覧数, dtype: float64

# 処理後のデータをExcelファイルに保存
excel_file = 'result.xlsx'
df_articles.to_excel(excel_file, index=False)

タグ: Pandas Python SQL 線形回帰 データ可視化

7月1日 16:54 投稿

ホットタグ

©2026 異端開発室