数据分析师笔试试卷二：SQL、Python、线性回归算法预测

提交时请在文件名称前加上你的名字。

rubyw

816人浏览 · 2024-07-10 00:24:18

rubyw · 2024-07-10 00:24:18 发布

数据分析师-笔试题目

提交时请在文件名称前加上你的名字

import pandas as pd

pandas-数据预处理

df_1 = pd.DataFrame(data=
             [
                 ['抖音','双十一','2022-11-09','2022-11-11'],
                 ['微博','双十一','2022-11-10','2022-11-11'],
                 ['抖音','圣诞节狂欢','2022-12-24','2022-12-26'],
                 ['微博','圣诞节狂欢','2022-12-23','2022-12-25']
             ],columns=['平台','活动名称','活动开始日','活动结束日'])
df_1

	平台	活动名称	活动开始日	活动结束日
0	抖音	双十一	2022-11-09	2022-11-11
1	微博	双十一	2022-11-10	2022-11-11
2	抖音	圣诞节狂欢	2022-12-24	2022-12-26
3	微博	圣诞节狂欢	2022-12-23	2022-12-25

df_2 = pd.DataFrame(data=
             [
                 ['2022-11-09','抖音','2000'],
                 ['2022-11-10','抖音','4184'],
                 ['2022-11-11','抖音','25816'],
                 ['2022-11-12','抖音','1000'],
                 ['2022-11-09','微博','4000'],
                 ['2022-11-10','微博','16781'],
                 ['2022-11-11','微博','13219'],
                 ['2022-11-12','微博','6000'],
                 ['2022-11-12','微博','6000'],
                 ['2022-11-12','微博','6000'],
                 ['2022-11-12','微博','6000'],
                 ['2022-12-23','抖音','4000'],
                 ['2022-12-24','抖音','8618'],
                 ['2022-12-25','抖音','11382'],
                 ['2022-12-26','抖音','6000'],
                 ['2022-12-23','微博','2000'],
                 ['2022-12-24','微博','5129'],
                 ['2022-12-25','微博','14871'],
                 ['2022-12-26','微博','1000']
             ],columns=['日期','平台','热度'])
df_2

	日期	平台	热度
0	2022-11-09	抖音	2000
1	2022-11-10	抖音	4184
2	2022-11-11	抖音	25816
3	2022-11-12	抖音	1000
4	2022-11-09	微博	4000
5	2022-11-10	微博	16781
6	2022-11-11	微博	13219
7	2022-11-12	微博	6000
8	2022-11-12	微博	6000
9	2022-11-12	微博	6000
10	2022-11-12	微博	6000
11	2022-12-23	抖音	4000
12	2022-12-24	抖音	8618
13	2022-12-25	抖音	11382
14	2022-12-26	抖音	6000
15	2022-12-23	微博	2000
16	2022-12-24	微博	5129
17	2022-12-25	微博	14871
18	2022-12-26	微博	1000

数据去重

对df_2进行去重，保证【日期】与【平台】是唯一的，并替换原有df_2，

df_2 = df_2.drop_duplicates(subset=['日期', '平台'])
df_2

	日期	平台	热度
0	2022-11-09	抖音	2000
1	2022-11-10	抖音	4184
2	2022-11-11	抖音	25816
3	2022-11-12	抖音	1000
4	2022-11-09	微博	4000
5	2022-11-10	微博	16781
6	2022-11-11	微博	13219
10	2022-11-12	微博	6000
11	2022-12-23	抖音	4000
12	2022-12-24	抖音	8618
13	2022-12-25	抖音	11382
14	2022-12-26	抖音	6000
15	2022-12-23	微博	2000
16	2022-12-24	微博	5129
17	2022-12-25	微博	14871
18	2022-12-26	微博	1000

格式转换

查看df_1与df_2的数据类型，并将数字与日期转为正确格式，替换原有df_1 df_2，最后再次查看数据类型

df_1.dtypes

平台       object
活动名称     object
活动开始日    object
活动结束日    object
dtype: object

df_2.dtypes

日期    object
平台    object
热度    object
dtype: object

df_1['活动开始日'] = pd.to_datetime(df_1['活动开始日'])
df_1['活动结束日'] = pd.to_datetime(df_1['活动结束日'])
df_1.dtypes

平台               object
活动名称             object
活动开始日    datetime64[ns]
活动结束日    datetime64[ns]
dtype: object

df_2['日期'] = pd.to_datetime(df_2['日期'])
df_2['热度'] = pd.to_numeric(df_2['热度'])
df_2.dtypes

日期    datetime64[ns]
平台            object
热度             int64
dtype: object

数据增加条件列

为df_2增加【热度等级】列，热度大于等于10000，标记高热度，热度小于10000标记低热度，并替换原有df_2

df_2['热度等级'] = df_2['热度'].apply(lambda x: '高热度' if x >= 10000 else '低热度')
df_2

	日期	平台	热度	热度等级
0	2022-11-09	抖音	2000	低热度
1	2022-11-10	抖音	4184	低热度
2	2022-11-11	抖音	25816	高热度
3	2022-11-12	抖音	1000	低热度
4	2022-11-09	微博	4000	低热度
5	2022-11-10	微博	16781	高热度
6	2022-11-11	微博	13219	高热度
10	2022-11-12	微博	6000	低热度
11	2022-12-23	抖音	4000	低热度
12	2022-12-24	抖音	8618	低热度
13	2022-12-25	抖音	11382	高热度
14	2022-12-26	抖音	6000	低热度
15	2022-12-23	微博	2000	低热度
16	2022-12-24	微博	5129	低热度
17	2022-12-25	微博	14871	高热度
18	2022-12-26	微博	1000	低热度

利用为df_1，为df_2增加【活动名称】列，要求【日期】与【活动名称】正确对应，无活动的日期【活动名称】列显示“无活动日”，并替换原有df_2

df_merged = pd.merge(df_2, df_1[['平台', '活动名称', '活动开始日', '活动结束日']], left_on=['日期', '平台'], right_on=['活动开始日', '平台'], how='left')
df_merged['活动名称'].fillna('无活动日', inplace=True)
df_2 = df_merged[['日期', '平台', '热度', '活动名称']]
df_2

	日期	平台	热度	热度等级	活动名称
0	2022-11-09	抖音	2000	低热度	双十一
1	2022-11-10	抖音	4184	低热度	双十一
2	2022-11-11	抖音	25816	高热度	双十一
3	2022-11-12	抖音	1000	低热度	无活动日
4	2022-11-09	微博	4000	低热度	无活动日
5	2022-11-10	微博	16781	高热度	双十一
6	2022-11-11	微博	13219	高热度	双十一
10	2022-11-12	微博	6000	低热度	无活动日
11	2022-12-23	抖音	4000	低热度	无活动日
12	2022-12-24	抖音	8618	低热度	圣诞节狂欢
13	2022-12-25	抖音	11382	高热度	圣诞节狂欢
14	2022-12-26	抖音	6000	低热度	圣诞节狂欢
15	2022-12-23	微博	2000	低热度	圣诞节狂欢
16	2022-12-24	微博	5129	低热度	圣诞节狂欢
17	2022-12-25	微博	14871	高热度	圣诞节狂欢
18	2022-12-26	微博	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
		热度	热度	热度	热度
平台	活动名称
微博	双十一	30000	2	15000.000000	2518.714355
	圣诞节狂欢	22000	3	7333.333333	6712.672667
	无活动日	11000	3	3666.666667	2516.611478
抖音	双十一	32000	3	10666.666667	13165.074604
	圣诞节狂欢	26000	3	8666.666667	2691.330031
	无活动日	5000	2	2500.000000	2121.320344

pyecharts-数据可视化

绘制出微博2022-11-09至2022-11-12热度的折线图，并标记出双十一活动范围、热度最大值与均值线

from datetime import datetime
from pyecharts import options as opts
from pyecharts.charts import Line
from pyecharts.commons.utils import JsCode
import pandas as pd

# 筛选微博在2022-11-09至2022-11-12的数据
df_weibo = df_2[(df_2['平台'] == '微博') & (df_2['日期'] >= '2022-11-09') & (df_2['日期'] <= '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="微博热度折线图 (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_info = pd.read_excel(r'data.xlsx',sheet_name='文章信息表')
df_rule = pd.read_excel(r'data.xlsx',sheet_name='热度计算规则表')

from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())

# 查询示例
q = """SELECT *
       FROM df_info 
       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 
    平台,
    CASE WHEN 平台 IN (SELECT 平台 FROM 热度计算规则表) THEN 计算规则 ELSE '暂无规则' END AS 计算规则
FROM 
    文章信息表;

	平台	计算规则
0	平台A	阅读/15
1	平台B	(阅读-点赞)/3
2	平台C	预测模型
3	平台D	暂无规则

在文章信息表中筛选出最近一天平台A热度最高的数据

SELECT *
FROM 文章信息表
WHERE 平台 in ('平台A')
AND 日期 = (SELECT MAX(日期) FROM 文章信息表 WHERE 平台 in ('平台A'))
ORDER BY 热度 DESC
LIMIT 1;

	日期	平台	转发	评论	点赞	阅读	热度
0	2023-05-06 00:00:00.000000	平台A	11	163	1107	18468	1231.2

生成每日平台的总热度降序排名

WITH daily_sum AS (
    SELECT 
        日期,
        平台,
        SUM(热度) AS 总热度
    FROM 
        文章信息表
    GROUP BY 
        日期, 平台
)
SELECT 
    日期,
    平台,
    总热度,
    ROW_NUMBER() OVER (PARTITION BY 日期 ORDER BY 总热度 DESC) AS 排名
FROM 
    daily_sum;

	日期	平台	总热度	排名
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），可知至少存在一个重复的整数。
假设 nums 只有一个重复的整数，返回这个重复的数的两个位置。
你设计的解决方案必须不修改数组 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-square、Adjusted R-Square）

# 线性回归模型进行预测

import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error, r2_score
import numpy as np
import matplotlib.pyplot as plt

# 加载数据
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-squared: {r2_train:.4f}")
print(f"Adjusted R-squared: {adj_r2_train:.4f}")
print("---")
print("测试集评价指标:")
print(f"MSE: {mse_test:.2f}")
print(f"RMSE: {rmse_test:.2f}")
print(f"R-squared: {r2_test:.4f}")
print(f"Adjusted R-squared: {adj_r2_test:.4f}")

训练集评价指标:
MSE: 17467402650.05
RMSE: 132164.30
R-squared: 0.9115
Adjusted R-squared: 0.9109
---
测试集评价指标:
MSE: 8182461073.15
RMSE: 90456.96
R-squared: 0.7999
Adjusted R-squared: 0.7948

plt.rcParams['font.sans-serif'] = ['Arial Unicode MS']  # 设置字体，确保可以显示中文
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])

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)

永洪数据分析社区

永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。

更多推荐

【金融风控-贷款违约预测】数据挖掘学习：1.赛题理解

学习目标理解赛题数据和目标，清楚评分体系。完成相应报名，下载数据和结果提交打卡（可提交示例结果），熟悉比赛流程。了解赛题赛题概况比赛地址：https://tianchi.aliyun.com/competition/entrance/531830/introduction比赛要求参赛选手根据给定的数据集，建立模型，预测金融风险。赛题以预测金融风险为任务，数据集报名后可见并可下载，该数据来自某信贷平

永洪数据分析社区

智能分析最佳实践——指标逻辑树

所有业务都会面对“为什么涨、为什么降、原因是什么？”这种简单粗暴又不易定位的业务问题。为了找出数据发生异动的原因，业务人员会通过使用多维查询、dashboard等数据产品锁定问题，再辅助人工分析查找问题原因，这个过程通常需要一天时间。几乎每种业务角色的用户都在做相似的分析，但在业务方分析人员发生工作变动时，分析方法难以得到较好传承。因此我们需要一款自动给出分析结论的智能化数据产品来解决上面的问题，

永洪数据分析社区

数据挖掘实验一：分类技术——二分网络上的链路预测

实验一：分类技术——二分网络上的链路预测实验内容采用二分网络模型，对ml-1m文件夹中的“用户—电影”打分数据进行建模，考虑将用户信息、电影详细信息、以及打分分值作为该网络上的边、点的权重；根据网络结构特征给出节点相似性度量指标；基于相似性在二分网络上进行链路预测；画出ROC曲线来度量预测方法的准确性。分析及设计导入数据并初步分析处理数据：观察所给的文件类型为.dat格式，即纯文本格式，pytho