今天是上海疫情封控的第58天，看到好多沪漂离开上海，心中阵阵发酸，始终相信一句话，冬天到了，春天还会远吗？终有胜利的一天，再坚持坚持。
话不多说，上正题，今天分享一套面试试题，原试题考察的是excel函数数组用法，透视表和透视图，改造了一下，使用pandas,pyecharts解题，感兴趣的朋友也可以使用excel试一下，试题资料放在网盘里，文末附网盘链接，建议读者先下载试题资料，做完之后再看答案，希望能帮助到大家。
题目：
现有三张表 hco,hcp,sales表，字段如下：
hco表字段：
HCOId；HCOName；City；
其中，HCOId是唯一标识符；
hcp表字段：
HCOId；HCPId；HCPName；Status；RepresentativeName；
HCPId是唯一标识符，且表中存在重复数据；
sales表：
Period；Brand；HCOId；SalesValue；
第一题：显示hcp表中HCPId字段的中的重复值。
import pandas as pd
import numpy as np
import os
data_path=r'C:\Users\GDY\Desktop\Others\测试题.xlsx'
hcp=pd.read_excel(data_path,sheet_name='HCP')
hco=pd.read_excel(data_path,sheet_name='HCO')
sales=pd.read_excel(data_path,sheet_name='Sales')
hcp[hcp['HCPId'].duplicated()]第二题：在HCO表中增加字段“招募医生数”并统计每家医院的去重后的招募总数。HCP表Status=Normal表示已招募到的医生，表中有重复数据（HCPId是唯一标识），每个医生任意一条满足招募条件即是已招募。
#筛选hcp表中Status为Normal，删除重复值
hcp_count=hcp[(hcp['Status']=='Normal')][['HCOId','HCPId','HCPName','Status']].drop_duplicates()
#hcp表进行聚合
hcp_agg=hcp_count.groupby('HCOId').agg({'HCPId':'count'})
hcp_agg=hcp_agg.reset_index()
#hcp表拼接招募医生数
hcp_agg.rename(columns={'HCPId':'招募医生数'},inplace=True)
hco=pd.merge(hco,hcp_agg,how='left',on='HCOId')
#空值使用0填充
hco['招募医生数']=hco['招募医生数'].fillna(0)
#将数值转换为int
hco['招募医生数']=hco['招募医生数'].map(lambda x: int(x))
#降序排列
hco.sort_values('招募医生数',inplace=True,ascending=False)
hco.head(10)第三题：参照图制作汇总表，按招募人数统计医院数。
import numpy as np
#定义分组函数
def fun1(x):
if x==0:
a='0人'
elif 1<=x<=3:
a='1-3人'
else:
a='4人及以上'
return a
#hcp表添加招募人数分组列
hco['招募人数分组']=hco['招募医生数'].map(lambda x: fun1(x))
#hco表进行聚合
agg1=hco.groupby('招募人数分组').agg({'HCOId':'count'})
agg1=agg1.reset_index()
#修改列名
agg1.rename(columns={'HCOId':'医院数'},inplace=True)
#计算占比，格式化字符串
agg1['占比']=agg1['医院数'].map(lambda x: str(round(x/agg1['医院数'].sum()*100,2))+'%')
agg1第四题：在HCO表增加2个字段“Y20”和“Y19”，分别统计2020年1月至11月的销售总和以及2019年同期的销售总和。
品牌限定为Brand=G。显示千分位，隐藏小数位。
#导入销售数据
sales=pd.read_excel(data_path,sheet_name='Sales')
#筛选品牌为G
sales=sales[sales['Brand']=='G']
#筛选2019年1月至11月的销售总额
sales_19=sales[sales['Period']<=201911]
#对2019年1月至11月数据进行聚合
y19=sales_19.groupby('HCOId').agg({'SalesValue':'sum'})
y19=y19.reset_index()
y19.rename(columns={'SalesValue':'Y19'},inplace=True)
#筛选2020年1月至11月的销售总额
sales_20=sales[sales['Period']>=202001]
#对2020年1月至11月数据进行聚合
y20=sales_20.groupby('HCOId').agg({'SalesValue':'sum'})
y20=y20.reset_index()
y20.rename(columns={'SalesValue':'Y20'},inplace=True)
#hco表拼接Y19和Y20
hco=pd.merge(hco,y19,how='left',on='HCOId')
hco=pd.merge(hco,y20,how='left',on='HCOId')
#空值使用0填充
hco['Y19']=hco['Y19'].fillna(0)
hco['Y20']=hco['Y20'].fillna(0)
hco.head()第五题：在HCO表中增加1个字段“销量趋势”，根据“参照2”的定义标注每家医院品牌G的销量趋势。并在Sum表画出环形图。保持样式一致。
#定义分组函数
def fun2(y19,y20):
if y19<=0 and y20<=0:
result='无药'
elif y19>0 and y20<=0:
result='停药'
elif y19<=0 and y20>0:
result='新进药'
elif y20/y19-1>0.1:
result='增长'
elif y20/y19-1>=-0.1 and y20/y19-1<=0.1:
result='持平'
elif y20/y19-1<-0.1:
result='下降'
return result
#计算销量趋势
count_list=[]
for i in range(hco.shape[0]):
y19=hco.loc[i,'Y19']
y20=hco.loc[i,'Y20']
count_list.append(fun2(float(y19),float(y20)))
#加入销量趋势字段
hco['销量趋势']=count_list
hco.head()from pyecharts import options as opts
from pyecharts.charts import Pie
agg2=hco.groupby('销量趋势').agg({'销量趋势':'count'})
agg2['占比']=agg2['销量趋势'].map(lambda x: str(round(x/agg2['销量趋势'].sum()*100,2))+'%')
c = (
Pie()
.add(
"",
[list(z) for z in zip(agg2.index, agg2['销量趋势'])],
radius=["40%", "75%"],
)
.set_global_opts(
title_opts=opts.TitleOpts(title="销量趋势图"),
legend_opts=opts.LegendOpts(orient="vertical", pos_top="15%", pos_left="2%"),
)
.set_series_opts(label_opts=opts.LabelOpts(formatter='{b}: {d}%'))
#.render("pie_radius.html")
)
c.render_notebook()
第六题：按照“参照3”制作透视表，然后按照“参照4”制作图表。保持样式一致。GR=(Y20/Y19-1)*100%
1.制作图表
#2019年1月至11月分组
agg19=sales_19.groupby('Period').agg({'SalesValue':'sum'})
agg19=agg19.reset_index()
#2020年1月至11月分组
agg20=sales_20.groupby('Period').agg({'SalesValue':'sum'})
agg20=agg20.reset_index()
#生成参照表
agg_year=pd.DataFrame({'Y19':agg19['SalesValue'],'Y20':agg20['SalesValue'],'月':np.arange(1,12)})
agg_year['月']=agg_year['月'].map(lambda x: str(x)+'月')
#计算GR
agg_year['GR']=round(agg_year['Y20']/agg_year['Y19']-1,2)
#agg_year['GR100']=round((agg_year['Y20']/agg_year['Y19']-1)*100,2)
#格式化GR
agg_year['GR%']=agg_year['GR'].map(lambda x: str(round(x*100,2))+'%')
agg_year=agg_year[['月','Y19','Y20','GR','GR%']]
agg_year2.Pyecharts 画图
from pyecharts import options as opts
from pyecharts.charts import Bar, Line
from pyecharts.globals import ThemeType
m1=list(agg_year['月'])
v1=agg_year['Y19'].to_list()
v2=agg_year['Y20'].to_list()
v3=agg_year['GR'].to_list()
bar = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.WHITE))
.add_xaxis(agg_year['月'].to_list())
.add_yaxis("Y19", v1)
.add_yaxis("Y20", v2)
.extend_axis(
yaxis=opts.AxisOpts(
axislabel_opts=opts.LabelOpts(formatter="{value}"), interval=5
)
)
.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
.set_global_opts(
title_opts=opts.TitleOpts(title="2020年VS2019年"),
yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}")),
)
)
line =(
Line()
.add_xaxis(m1)
.add_yaxis("GR", v3, yaxis_index=1)
.set_series_opts(label_opts=opts.LabelOpts(formatter='{c}',position='right'))
.set_global_opts(yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value}")))
)
bar.overlap(line) #在柱状图上叠加折线图
# bar.render("overlap_bar_line.html")
bar.render_notebook()
第七题：在HCO表中增加1个字段“销量贡献”，统计2020年1至11月品牌G每家医院的销量占比，并显示为百分数。销量贡献=每家医院销量/销量总和（分子负数记为0，分母负数不计入总和）
#计算销量总和
sale_amounts=hco[hco['Y20']>0]['Y20'].sum()
#计算销量贡献
hco['销量贡献']=hco['Y19'].map(lambda x: 0 if x<0 else x/sale_amounts)
#格式化销量贡献
hco['销量贡献']=hco['销量贡献'].map(lambda x: str(round(x*100,2))+'%')
#销量贡献和招募医生数降序排列
hco.sort_values(['销量贡献','招募医生数'],inplace=True,ascending=False)
hco最终hco表如下：
总结：
本套测试题使用Pandas和Pyecharts解题，主要考察了pandas的基本操作，数据筛选；重复值处理；自定义函数数据分组聚合；数据拼接组合和格式化字符串；数据可视化Pyecharts的环形图，柱状图和折线图的组合图；
测试题资料已放在 我的网盘 提取码：test
最后，感谢您的阅读，不足之处请多指教。如果文章对您有所帮助，请点赞收藏一下哦，您的收藏和点赞是我创作的动力。
1209
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
