前言

本次分析只是用猿辅导的案例来分享数据分析的思路和方法论。禁止将分析结果用于任何商业目的以及非法行为，若引起法律纠纷后果自负。同时声明数据来源与猿辅导官网关键指标数据未必真实。

概述

笔者将通过以下环节展示整个分析的过程。

涉及知识：

结构化思维，描述性分析，相关性分析，python爬虫，数据清洗 ， mysql ， 数据可视化

分析流程：

第一部分 ： 定义目标

数据分析的第一步也是最关键的一步，就是“如何将一个命题转化拆分为一个可用数据分析的问题”，

笔者这里分享一下笔者的分析思路。

1.1 思考命题

命题： 分析猿辅导的课程

问1：为什么要分析这个命题？可以解决什么问题？带来多大价值？

答：这个问题的答案可能来自老板，来自需求方，来自自己；不管来源如何，都要审视命题本身是不是伪命题。

从本案例来说，笔者的分析的目的在于猿辅导是K-12教育的“独角兽”公司，目前已经E轮。对其公司“输出内容”的分析可以帮助笔者更深入的挖掘“在线辅导”业务的用户群体 分布，主要营收分布，学科分布;今后还可以通过分析其他同行业的公司进行对比分析。进而深入笔者对教育行业的理解，提高高人影响力。

问2：因变量Y是什么，自变量X是什么？

答：本案例中开始只是个抽象的命题：对猿辅导的课程进行分析，因变量Y应该是猿辅导的”辅导课“,那么辅导课可以拆解哪些属性呢？(老师，学生，时间，价格，类型，学段，年级，学科.......)，这些元素是否可以被数据化呢？很明显是可以的。

问3：Y，X在哪？我可以从哪些渠道获得它们？

答：我们看一下猿辅导的官网，这些信息都是可以找到的，那么笔者可以判断，这个命题是一个数据可分析的问题

1.2 结构化拆分命题

这里要考验一个分析师数据分析思维的环节，同样的数据从不同维度拆分，同样的维度选择不同的嵌套关系分析结果的深度，颗粒度，层次和价值可能会大不相同。

笔者采用“金字塔原理”将可量化的问题结构化的产出。分为描述性分析和相关性分析。

1.2.1描述性分析

描述性分析本质上就是像画肖像一样，去描述你要表达的事物。这里的描述笔者采用的是“数据分布”概念，(关于数据分布和统计学概念可以参考笔者另一篇文章《深入浅出统计学》---木南的书，可以帮助初学者快速建立统计学的知识架构。)

问：哪些维度是最关键的指标？

答：这个问题考验的是一个分析师对某个领域或者行业的经验。笔者2年的教育行业的经验，选择以下四个指标作为分析对象：“课程”，“用户”，“师资”，“营收”。

1.2.2相关性分析

相关性分析是找出数据和数据之间的相关关系。 将总营收分解为课程价格和报名人数的乘积。

报名人数越多整体收入也就越多。那么报名人数受那些因素影响，每个因素影响的权重如何呢？这里需要进行相关性分析来回答这些问题。

同样也是根据经验，笔者认为以下四个因素可能会影响一个课程的报名人数，“年级”，“学科”，“单价”，“时长”。(其实还有重要因素“老师”，官网的数据过于有广告成分，会造成数据失真，笔者不做探讨)

第二部分 ： 数据获取

数据获取的第一个问题要确定获取什么作为数据？这里如果第一步做的很到位那就很简单了，答案就是第一部分脑图中最末端的分支项。(课程，学科，年级，老师，价格，报名学生数)。

确定了要获取哪些数据后，我们来观察一下目标网站的层级结构。

需要爬取的元素

爬虫层级结构

爬虫思路

分析url有3个变量，学段，年级，学科。一共有72总组合，也就是72个不同的url，每个url有若干个对应的课程。爬虫是进行72次url的爬取，然后将对应的学段，年级，学科字段分配到对应的课程上。课程信息爬取名称，时间，老师，报名人数，价格等这些字段，然后汇总成一个表格嵌套字典的结构备用。其中老师的信息单拿出一个列表，并且每个老师有对应的课程id，用于和课程信息表相关联。最后输出两个excel，一个是课程列表，一个是老师列表。

爬虫代码

import requests

import json

import xlwt

import pandas as pd

listxueduan=['xiaoxue','chuzhong','gaozhong']

listgrade=['1','2','3','4','5','6','7','8','9','10','11','12']

list_c_1=['2','3','201']

list_c_2=['1','2','3','4','5','6','7','8','14']

list_c_3=['1','2','3','4','5','6','7','8','9']

lesson = []

teacher = []

def get_key_value(info_dict,k,j):

try:

info_dict[k] = j[k]

except KeyError as e:

for j_key in j:

if isinstance(j[j_key], dict):

get_key_value(info_dict,k,j[j_key])

list=[]

list11=[]

list12=[]

list13=[]

urllist=[]

for i in listxueduan:

if i=='xiaoxue':

for j1 in range(1,7):

#print(j1)

g=j1

for c1 in list_c_1:

c=c1

url = 'https://www.yuanfudao.com/tutor-student-lesson/api/homepage?_productId=374&platform=www&version=5.11.0&UDID=4d27a58f757db800e339317f1c245223&timestamp=1532658211041&startCursor=0&limit=18&grade='+str(g)+'&channelId='+str(c)+'&studyPhase='+str(i)+'&withNextGrade=false'

urllist.append(url)

list11.append(i)

list12.append(j1)

list13.append(c1)

elif i=='chuzhong':

for j2 in range(7,10):

#print(j2)

g=j2

for c2 in list_c_2:

c=c2

url = 'https://www.yuanfudao.com/tutor-student-lesson/api/homepage?_productId=374&platform=www&version=5.11.0&UDID=4d27a58f757db800e339317f1c245223&timestamp=1532658211041&startCursor=0&limit=18&grade='+str(g)+'&channelId='+str(c)+'&studyPhase='+str(i)+'&withNextGrade=false'

urllist.append(url)

list11.append(i)

list12.append(j2)

list13.append(c2)

else :

for j3 in range(10,13):

#print(j3)

g=j3

for c3 in list_c_3:

c=c3

url = 'https://www.yuanfudao.com/tutor-student-lesson/api/homepage?_productId=374&platform=www&version=5.11.0&UDID=4d27a58f757db800e339317f1c245223&timestamp=1532658211041&startCursor=0&limit=18&grade='+str(g)+'&channelId='+str(c)+'&studyPhase='+str(i)+'&withNextGrade=false'

urllist.append(url)

list11.append(i)

list12.append(j2)

list13.append(c3)

study_dict={

'studyPhase':list11,

'grade':list12,

'channelId':list13

}

def main():

for i in range(0,72):

url = urllist[i]

s1=study_dict['studyPhase'][i]

s2=study_dict['grade'][i]

s3=study_dict['channelId'][i]

r = requests.get(url)

json_r= r.json()['list']

for j in json_r:

info_dict={

'studyPhase':s1,

'grade':s2,

'channelId':s3,

'id': None,

'minPrice': None,

'maxPrice': None,

'name': None,

'soldCount': None,

'price':None,

'subName': None,

'teachers':None

}

for k in info_dict:

get_key_value(info_dict,k,j)

print(info_dict)

lesson.append(info_dict)

for t in j['teachers']:

teacher_dict={

'lessonid':info_dict['id'],

'id':t['id'],

'nickname':t['nickname'],

'avatar':t['avatar']

}

teacher.append(teacher_dict)

work=xlwt.Workbook() #创建excel

sheet1=work.add_sheet('sheet1',cell_overwrite_ok=True) #创建sheet

head=['studyPhase','grade','channelId','id','minPrice','maxPrice','name','soldCount','price','subName']

y=0

for item in head: #编辑表头

sheet1.write(0,y,item)

y+=1

x=1

#用data做例子，实际操作用网页爬出的数据

for item in lesson:

if isinstance(item,dict):

for head_item in head:

if head_item in item.keys():

y=head.index(head_item)

sheet1.write(x,y,item[head_item])

x+=1

work.save('猿辅导课程表new2.xls')#保存

work2=xlwt.Workbook() #创建excel

sheet1=work2.add_sheet('sheet1',cell_overwrite_ok=True) #创建sheet

head2=['lessonid','id','nickname','avatar']

y=0

for item in head2: #编辑表头

sheet1.write(0,y,item)

y+=1

x=1

#用data做例子，实际操作用网页爬出的数据

for item in teacher:

if isinstance(item,dict):

for head_item in head2:

if head_item in item.keys():

y=head2.index(head_item)

sheet1.write(x,y,item[head_item])

x+=1

work2.save('猿辅导老师表new2.xls')#保存

if __name__ == '__main__':

main()

爬行结果

lesson表(清洗前)

一共225条课程数据

表头字段名：

学段，年级，课程类别id，课程ID ，最低售价，最高售价，课程名，报名数，售价，老师名，标签名

teacher表(清洗前)

一共465条老师数据(同一门课可能多名老师带，一名老师可能上多门课)

表头字段名：

课程ID ，老师ID，老师名，老师头像

第三部分 ： 数据清洗

3.1 一次数据清洗 ---python

case1 --- 将价格赋值

当price列数据为0的时候，minprice和maxprice是有价格数据的，因此当price为0时候，将maxprice价格赋值给price，代码如下：

mport pandas as pd

str1='C:\\Users\\wnapple\\Desktop\\猿辅导课程表new2.xls'

str2='C:\\Users\\wnapple\\Desktop\\猿辅导老师表new2.xls'

lessondf = pd.read_excel(str1)

teacherdf = pd.read_excel(str2)

#print(p1)

s3 =lessondf.shape

#print(lessondf)

'''

数据清洗---价格赋值

'''

for i in range(0,225):

p1 =lessondf.loc[i,'price']

print(p1)

if p1==0:

p1=lessondf.loc[i,'maxPrice']

lessondf['price'][i]=p1

lessondf['price'].fillna(0,inplace=True)

case2 --- 将subname列中日期和年级字段拆分

将subname标签中的日期信息和年级信息拆分，分别变成两列，代码如下：

'''

数据清洗---将日期和年级拆分(思路日期长度>年级，按长度判断)

'''

subname =lessondf.loc[:,'subName']

datelist=[]

gradelist=[]

for i in subname:

datestr=i.split(' ')

if len(datestr)==1:

datelist.append(datestr[0])

gradelist.append('暂无数据')

elif len(datestr[0])>len(datestr[2]):

datelist.append(datestr[0])

gradelist.append(datestr[2])

else:

datelist.append(datestr[2])

gradelist.append(datestr[0])

lessondf['date']=datelist

lessondf['Grade']=gradelist

#print(lessondf)

case3 --- 将日期列，继续拆分成开始日期和截止日期

'''

#数据清洗---将日期拆分(开始日期 和 结束日期 )

'''

date =lessondf.loc[:,'date']

from_datelist=[]

to_datelist=[]

for i in date:

datestr2=i.split('-')

#print(datestr2)

from_datelist.append(datestr2[0])

if len(datestr2)==1:

to_datelist.append('暂无数据')

else:

to_datelist.append(datestr2[1])

lessondf['from_date']=from_datelist

lessondf['to_date']=to_datelist

#print(lessondf)

case4 --- 数据清洗给channelid赋值

'''

#数据清洗---给channelid赋值

'''

for i in range(0,225):

date3 =lessondf.loc[i,'channelId']

if date3 ==1:

lessondf['channelId'][i]='语文'

elif date3 ==2:

lessondf['channelId'][i]='数学'

elif date3 ==3:

lessondf['channelId'][i]='英语'

elif date3 ==201:

lessondf['channelId'][i]='编程'

elif date3 ==4:

lessondf['channelId'][i]='物理'

elif date3 ==5:

lessondf['channelId'][i]='化学'

elif date3 ==6:

lessondf['channelId'][i]='生物'

elif date3 ==7:

lessondf['channelId'][i]='历史'

elif date3 ==8:

lessondf['channelId'][i]='地理'

elif date3 ==9:

lessondf['channelId'][i]='政治'

elif date3 ==14:

lessondf['channelId'][i]='道德与法制'

else :

pass

#print(lessondf.loc[:,'channelId'])

case5 --- 删除多余的列 ，“minprice” ，“maxprice”，“subname”，date

lessondf1=lessondf.drop(columns=['minPrice', 'maxPrice','date','subName','Grade'])

print(lessondf1.shape)

lesson表(一次清洗后)

3.2 二次数据清洗 ---excel

case1 重复课程均值处理

有一类课程是给整个学段开发，总报名人数重复出现了3次，将此类课程按出现次数取均值分别赋值到每个年级。

case2 --- 进增课程时长列(if函数，处理跨年异常值)

课程长度=结束日期-开始日期

当 结束时间-开始时间＞0

时长= 结束时间-开始时间

当 结束时间-开始时间=0

时长=1

当 结束时间-开始时间＜0

时长=12月31日-开始时间+(结束时间月份数-1)×30+结束时间日

case3 --- 个别异常值处理

二次清洗后结果

第四部分 ： 数据提取

将teacher表和l清洗后的lesson表导入Navicat，方便用sql语句提取信息。

按照第一部分的脑图，将需要的信息拆解成问题的形式用SQL语句提取

(1) 一共有多少课程？ (255)

(2) 三个学段各多少课程？ (小学36 ， 初中50 ，高中 139)

SELECT studyPhase,count(*) from sheet2

GROUP BY studyPhase

(3)每个年级各多少课程？ (4 4 8 7 7 6 13 17 20 21 29 89 )

SELECT grade,count(*) from sheet2

GROUP BY grade

(4)每个年级下，各学科有多少课程？

SELECT grade,channelId,count(*) from sheet2

GROUP BY grade,channelId

(5)一共有多少学生报名？ (323877)

SELECT sum(soldCount) from sheet2 ;

(6)每个学段各多少学生报名？

SELECT studyPhase,sum(soldCount) from sheet2

GROUP BY studyPhase ;

(7)每个年级下各学科多少学生报名？

SELECT grade,channelId,sum(soldCount) from sheet2

GROUP BY grade,channelId ;

(8)课程总收入？

SELECT sum(soldCount*price) from sheet2 ;

(9)三个学段课程收入？

SELECT studyPhase,sum(soldCount*price) from sheet2

GROUP BY studyPhase ;

(10)每个年级下各学科多少卖了多少钱？

SELECT grade,channelId,sum(soldCount*price) from sheet2

GROUP BY grade,channelId ;

(11)一共有多少名辅导老师？

SELECT count(DISTINCT nickname) from teacher ;

(12)每个年级下对应的学科各有多少老师？

SELECT grade,channelId,count(DISTINCT t.id ) from sheet2 as s , teacher as t

where s.id=t.lessonid

GROUP BY grade , channelId ;

(13)老师代课数量排名？

SELECT nickname,id,count(lessonid)from teacher as t

GROUP BY id

ORDER BY count(lessonid) desc

(14)老师代课学生数排名

SELECT nickname,t.id, count(DISTINCT lessonid) ,SUM(DISTINCT s.soldCount) from teacher as t ,sheet2 as s

where s.id= t.lessonid

GROUP BY t.id

ORDER BY SUM(DISTINCT s.soldCount) desc;

(15)老师营收排名？

SELECT nickname,t.id, count(DISTINCT lessonid) ,SUM(DISTINCT s.soldCount*price) as s from teacher as t ,sheet2 as s

where s.id= t.lessonid

GROUP BY t.id

ORDER BY s desc;

将查询结果导出，从以下4个维度制作图标进行描述性分析

第五部分 ： 数据数据可视化

数据提取部分已经获得了我们需要的数据，数据可视化的过程就是选择恰当的图标来表达要描述的内容。

笔者这次分析主要是展示“分布”，“构成”，“联系”

因此选择： 柱状图，累计柱状图，散点图 ，为主要的说明方式。

第六部分 ： 撰写报告

报告分为以下几个模块展开，笔者输出报告后会另写一篇文章分享报告内容

(1)背景与目的

(2)描述性分析

(3)相关性分析

(4)结论

本文主要是分享一个数据分析的全过程，最终的报告结果笔者会在另一篇文章中分享。