0.加载数据
read_csv
read_excel
read_sql
1.数据抽取
布尔索引
query
drop
2.数据清洗
缺失值：isnull，isna， notnull, notna, dropna, fillna
重复值：duplicated, drop_duplicates, numique
异常值：replace, drop
检测异常值：Z-score
IQR
DBScan
Isolation Forest
预处理: apply, transform, applymap
str: extract(regxp), contains
dt: year, quarter, month, weekday…
to_datetime
cut(data, bins), qcut(data, [0, 0.1, 0.25, 0.5, 0.75, 0.9, 1])
3.数据透视
分组：groupby —> agg —>聚合函数
透视表：pivot_table —> index, columns, values, aggfunc
交叉表（了解）:crosstab
排序: sort_values(by, ascending), sort_index(level)
取头部: nlargest(n, columns), nsmallest
4.可视化
绘图：plot
kind —> line, scatter, pie, bar, barh, hist, box,…
figsize
5.业务洞察
二、数据抽取和数据清洗 例一、读取kobe_data.csv文件import numpy as np import pandas as pd import matplotlib.pyplot as plt
df1 = pd.read_csv(r'C:UserswbyDesktopdatacsvkobe_data.csv', index_col='shot_id') df1
# 获取Dataframe的相关信息 df1.info()
# 设置不限制最大显示的列数
pd.set_option('max_columns', None)
# 显示前5行,df1.head(n)默认n=5,可省略 df1.head()
# 显示后5行,df1.tail(n)默认n=5，可省略 df1.tail()
# 科比使用最多的投篮动作 temp = df1.action_type + '-' + df1.combined_shot_type temp.value_counts().index[0]
# 科比交手次数最多的球队
match_index = df1.game_id.drop_duplicates().index
temp = df1.opponent[match_index]
temp.value_counts().index[0]
或者
df1.drop_duplicates('game_id').opponent.value_counts().index[0]
# 科比职业生涯得分（不包含罚篮） df1.shot_type.apply(lambda x: int(x[0]))[df1.shot_made_flag == 1].sum()例二、连接数据库
import pymysql conn = pymysql.connect(host='47.104.31.138',port=3306,user='guest',password='Guest.618',database='hrs',charset='utf8mb4') conn
emp_df = pd.read_sql('select * from tb_emp', conn, index_col='eno')
emp_df.info()
# 判断空值 emp_df.isnull() emp_df.isna()
# 判断非空值 emp_df.notnull() emp_df.notna()
# 计算每一个列空值的数量 emp_df.isnull().sum() # 删除空值（默认沿着0轴删除）(会把有空值的行全部删除掉) emp_df.dropna() # 用插值法填充空值 emp_df.comm.interpolate()
emp_df['mgr'] = emp_df.mgr.fillna(-1).astype(np.int64) emp_df['comm'] = emp_df.comm.interpolate() emp_df
emp_df.info()
dept_df = pd.read_sql('select * from tb_dept', conn, index='dno')
dept_df
dept_df.loc[50]={'dname':'销售部','dloc':'长沙'}
dept_df.loc[60]={'dname': '运维部', 'dloc':'绵阳'}
dept_df
# 是否重复，返回布尔值,默认keep='first'
dept_df.duplicated('dname')
# 最后出现的值返回False
dept_df.duplicated('dname', keep='last')
# 最先出现的值返回False
dept_df.duplicated('dname', keep='first')
# 重复值全部返回True
dept_df.duplicated('dname', keep=False)
例三、读取某视频网站运营数据(excel)
df2 = pd.read_excel(r'C:UserswbyDesktopdataexcel某视频网站运营数据.xlsx') df2.info()
df2.drop_duplicates('video_id').shape
# 去重
df2.drop_duplicates('video_id').shape
df2.drop_duplicates('title').shape
# 统计不重复的元素的个数
df2.nunique()
heights = np.ceil(np.random.normal(110, 5, 50)) heights
heights[-1] = 193 heights[0] = 80 heights
# 画箱线图，whis默认是1.5 plt.boxplot(heights, whis=3) plt.show()寻找异常值
四种方法：1.1.5QR；2.zscore判定法；3.DBScan聚类;4.孤立森林
正态分布
# 寻找异常值 # 1.5倍IQR def detect_outliers_iqr(data, whis=1.5): q1, q3 = np.quantile(data, [0.25, 0.75]) iqr = q3 - q1 lower ,upper = q1 - whis * iqr,q3 + whis * iqr return data[(data < lower) | (data >upper)] print(detect_outliers_iqr(heights))
# 3塞格玛法则（zscore判定法） def detect_outliers_zscore(data, threshold=3): avg_value = np.mean(data) std_value = np.std(data) z_score = np.abs((data-avg_value)/std_value) return data[z_score > threshold] print(detect_outliers_zscore(heights))
new_heights = pd.Series(heights).replace([80, 193], 110) new_heights.plot(kind='box')
new_heights = pd.Series(heights).replace([80, 193], 110) new_heights.plot(kind='box')
plt.boxplot(heights[1:-1]) plt.show()例三、读取bilibili.csv数据
bilibili_df = pd.read_csv(r'C:UserswbyDesktopdatacsvbilibili.csv', encoding='gbk') bilibili_df
# 处理网址
from urllib.parse import urljoin
def fix_url(url):
pos = url.rfind('?')
if pos >= 0:
url = url[:pos]
return urljoin('https:', url)
bilibili_df['url'] = bilibili_df.url.apply(fix_url)
bilibili_df
# 处理watchnum
def handle_watch_num(watchnum):
unit_dict = {'万':10000, '亿':100000000}
unit = unit_dict.get(watchnum[-1], 1)
if watchnum[-1] in unit_dict:
watchnum = watch[:-1]
return int(float(watchnum) * unit)
bilibili_df['watchnum'] = bilibili_df.watchnum.apply(handle_watch_num) bilibili_df
bilibili_df.info
# 处理日期,方便单独拿出年、月、日 bilibili_df['uptime'] = pd.to_datetime(bilibili_df.uptime) bilibili_df.info()
bilibili.df.uptime.dt.year例四 、读取lagou.csv文件
import numpy as np import pandas as pd import matplotlib.pyplot as plt plt.rcParams['font.sans-serif'] = 'STZhongsong' plt.rcParams['axes.unicode_minus'] = False
%config InlineBackend.figure_format = 'svg'
# 读取所需要的列的数据
lagou_df = pd.read_csv(r'C:UserswbyDesktopdatacsvlagou.csv', usecols=['city', 'companyFullName',
'companyLabelList', 'companySize', 'district',
'education', 'financeStage', 'industryField',
'positionName', 'salary','workYear'])
lagou_df
lagou_df.info()
lagou_df[lagou_df.district.isnull()]
# 0.筛选出数据分析的岗位
temp_df = lagou_df[lagou_df.positionName.str.contains('数据分析')]
temp_df
index_nums = lagou_df[~lagou_df.positionName.str.contains('数据分析')].index
lagou_df.drop(index=index_nums, inplace=True)
lagou_df.shape
# 1.将薪资处理成上下限的平均值 sals = lagou_df.salary.str.extract(r'(d+)[kK]?-(d+)[kK]?').applymap(int).mean(axis=1) lagou_df['salary'] = sals lagou_df
# 2.将工作年限要求处理成下限值或0 import re def min_work_year(content): matcher = re.search(r'd+', content) result = matcher.group if matcher else '0' return int(result) lagou_df['workYear'] = lagou_df.workYear.apply(min_work_year) lagou_df
lagou_df.salary.mean()
lagou_df.workYear.mean()
Dataframe函数的用法
temp_df = pd.Dataframe(data=np.random.randint(30, 101, (5,3))) temp_df.mean() temp_df.apply(np.mean) temp_df.apply([np.mean]) temp_df.apply([np.mean, np.std, np.max, np.min])
# 此处lambda函数的参数x是一个数据序列 temp_df.apply(lambda x:(x ** 0.5 * 10).astype(int))
# 此处lambda函数的参数是一个数据序列 # transform的参数是不带规约性质的参数 temp_df.transfrom(lambda x:(x ** 0.5 *10).astype(int))
# 此处lambda函数的参数是一个值 temp_df.applymap(lambda x: int(x ** 0.5 * 10)
# ValueError # temp_df.transform(np.mean)
总结：
数据序列三个添加函数方法: apply, map, transform
Dataframe三个添加函数方法：apply，transform，applymap
transform的参数不能带规约性质，否则报错，传入几个参数，就返回几个结果
例五、读取2020年销售数据sales_df = pd.read_excel(r'C:UserswbyDesktop2020年销售数据-2.xlsx') sales_df
sales_df.info()
sales_df['年'] = sales_df.销售日期.dt.year sales_df['季度'] = sales_df.销售日期.dt.quarter sales_df['月'] = sales_df.销售日期.dt.month sales_df['销售额'] = sales_df.售价 * sales_df.销售数量 sales_df
# 写入excel表格 sales_df.to_excel(r'C:UserswbyDesktop2020年销售数据-3.xlsx')
# 1.统计月度销售额
sales_df.groupby('月').销售额.sum()
sales_df.groupby('月').销售额.agg([np.sum, np.max, np.min])
# 2.统计品牌销售占比
ser = sales_df.groupby('品牌').销售额.sum()
ser.plot(kind='pie', autopct='%.1f%%', counterclock=False)
plt.show()
# 3.统计各地区的月度销售额 sales_df.groupby(['销售区域', '月']).销售额.sum()
# 画透视图 pd.pivot_table(sales_df, index=['销售区域'], columns=['月'],values=['销售额'], aggfunc=np.sum, fill_value=0, margins=True, margins_name='总计')
# 4.统计各渠道的品牌销量 sales_df.grouby(['销售渠道', '品牌']).销售数量.sum()
pd.pivot_table(sales_df, index='销售渠道', columns='品牌', values='销售数量', aggfunc=np.sum)
# 5.统计不同销售区间的月度销量占比 # 数据分箱 bins = [0, 100, 300, 500, 800, 1200, 10000] cate = pd.cut(sales_df.售价, bins, right=False) cate
sales_df['价格区间'] = cate sales_df
temp_df = pd.pivot_table(sales_df, index='价格区间', columns='月', values='销售数量', aggfunc=np.sum, margins=True, margins_name='总计') temp_df.iloc temp_df
ser = temp_df.iloc[:-1, :-1].sum()
temp_df = np.round(temp_df.divide(ser) * 100, 2) temp_df
temp_df.applymap(lambda x:f'{x}%'')
三、Dataframe的其他方法
df1 = pd.Dataframe({
'col1':[[1, 2, 3], [4, 5, 6]],
'col2':[[0] * 3,[1] * 5]
})
df1
df1.col1.explode()
df1.explode('col1', ignore_index=True)
df1.col2.explode()
df1.explode('col1').explode('col2')
ser1 = pd.Series(np.random.randint(10, 100, 10)) ser1
ser1.rolling(3).mean()Index对象 1.范围索引
# 范围索引 sales_data = np.random.randint(400, 1000, 12) month_index = pd.RangeIndex(1, 13, name='月份') ser = pd.Series(data=sales_data, index=month_index) ser
ser.index2.多级索引
# 多级索引 ids = np.arange(1001, 1006) sms = ['期中', '期末'] index = pd.MultiIndex.from_product((ids, sms), names=['学号', '学期']) courses = ['语文', '数学', '英语'] scores = np.random.randint(60, 101, (10, 3)) df = pd.Dataframe(data=scores, columns=courses, index=index) df
df.reset_index(level=1)取数据的方法
# 将数据分为10个周期，取出10个周期的数据
pd.date_range('2021-1-1', '2021-6-1', periods=10)
# 一周为一个周期取出数据
temp = pd.date_range('2021-1-1', '2021-6-1', freq='W')
temp
temp - pd.DateOffset(days=2) temp + pd.DateOffset(days=2)
import pandas_datareader as pdr
baidu_df = pdr.get_data_stooq('BIDU', start='2021-11-1', end='2021-12-8')
baidu_df.sort_index(inplace=True)
baidu_df
baidu_df.Close.rolling(5).mean()
baidu_df.Close.plot(kind='line') plt.yticks(np.arange(100, 181, 10)) plt.show()
# 往后移三天，第一天的数据到第四天，以此类推 baidu_df.shift(3, fill_value=0)
# 每隔10天取数据，如果没有数据，用前面的数据填充后面的数据
baidu_df.asfreq('10D', method='ffill')
# 取每个月数据平均值
baidu_df.resample('1M').mean()
# 取每10天数据的总和
baidu_df.resample('10D').Volume.sum()
四、用pyecharts绘图
例一、
from pyecharts.charts import Bar
from pyecharts import options as opts
# 内置主题类型可查看 pyecharts.globals.ThemeType
from pyecharts.globals import ThemeType
bar = Bar()
bar.add_xaxis(["衬衫", "羊毛衫", "雪纺衫", "裤子", "高跟鞋", "袜子"])
bar.add_yaxis("商家A", [5, 20, 36, 10, 75, 90], color='blue')
bar.add_yaxis("商家B", [15, 6, 45, 20, 35, 66], color='red')
bar.set_global_opts(title_opts=opts.TitleOpts(title="销售统计图", subtitle="服装销售统计图", pos_left='12%'))
bar.render_notebook()
例二、读取股票数据绘制K线图
df1 = pd.read_excel(r'C:UserswbyDesktopdataexcel阿里巴巴2020年股票数据.xlsx', usecols=['Date','Open', 'Close', 'Low', 'High'], index_col='Date')
def format_date(curr_date):
return curr_date.strftime('%y/%m/%d')
cols = df1.columns[[2, 0, 3, 1]]
df1 = df1[cols]
x, y = df1.index.tolist(), np.round(df1.values, 2).tolist()
x = list(map(format_date, x))
from pyecharts import options as opts
from pyecharts.charts import Kline
c = Kline()
c.add_xaxis(x)
c.add_yaxis(
"kline",
y,
markline_opts=opts.MarkLineOpts(
data=[opts.MarkLineItem(type_="max", value_dim="close")]
),
)
c.set_global_opts(
xaxis_opts=opts.AxisOpts(is_scale=True),
yaxis_opts=opts.AxisOpts(
is_scale=True,
splitarea_opts=opts.SplitAreaOpts(
is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
),
),
title_opts=opts.TitleOpts(title="阿里巴巴2020年股票数据"),
datazoom_opts = opts.DataZoomOpts(),
)
c.render_notebook()
五、用seaborn画散点图
import ssl ssl._create_default_https_context = ssl._create_unverified_context
import seaborn as sns df = pd.read_csv(r'C:UserswbyDesktoptips.csv')
df['sex'] = df.sex.apply(lambda x: 0 if x == 'Female' else 1) df['smoker'] = df.smoker.apply(lambda x: 0 if x == 'No' else 1)
sns.set_theme() sns.pairplot(df, y_vars="tip", x_vars=["total_bill", "size"])
.AxisOpts(
is_scale=True,
splitarea_opts=opts.SplitAreaOpts(
is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
),
),
title_opts=opts.TitleOpts(title=“阿里巴巴2020年股票数据”),
datazoom_opts = opts.DataZoomOpts(),
)
c.render_notebook()
## 五、用seaborn画散点图
import ssl
ssl._create_default_https_context = ssl._create_unverified_context
import seaborn as sns
df = pd.read_csv(r’C:UserswbyDesktoptips.csv’)
df[‘sex’] = df.sex.apply(lambda x: 0 if x == ‘Female’ else 1)
df[‘smoker’] = df.smoker.apply(lambda x: 0 if x == ‘No’ else 1)
sns.set_theme(style=“ticks”)
sns.pairplot(df, y_vars=“tip”, x_vars=[“total_bill”, “size”])
