-
处理前样式
处理前样式 -
目标样式
目标样式
本篇Pandas数据处理实例，并非非常简洁，但串起了多个Pandas知识点，适合入门练习用
实际用到方法：连接数据库、取数，拆分文本，转置，逆透视，多表匹配，表的横向连接，分组聚合，分组应用自定义函数，导出数据到Excel表
-
任务背景：
- 数据为课程下选课学校和对应人数统计，
- 爬取文本数据样式；{"total":495,"field_count":5,"results":[{"doc_count":475,"key":532},......,{"doc_count":1,"key":9136}
- 目标样式：学校_3202(2187人次), 学校_2316(259人次), ......; 增加一列统计修行学校总数
-
任务分析：
- 需要对文本列拆分，替换掉非数字关键词
- 将key匹配替换成对应学校名
- 因为最终要用key来匹配，需要生成key值组成的列
- 要重新统计修行学校总数，需要分组求和
- 要生成目标样式，需要分组后做文本连接
- 导入必要库
import pandas as pd
import numpy as np
import pymysql
- 从数据库加载数据
eng = pymysql.connect(host='localhost', user='root', password='root', db='学银数据', charset='utf8')
df = pd.read_sql('SELECT * FROM `选课院校_人次`',eng)
df.head()
- 导出数据会强制在 10000 条以下, 查看选修超 10000 人的记录
# 查看人数超过10000的行
df.query('~文本.str.contains("10000")')
df.head()
image.png
- 删除超 10000 人以上的记录
# 删除人数超过10000人的行
df.drop(df[df['文本'].str.contains('10000')].index, inplace=True)
df.head()
- 重建索引 注意 reset_index() 和 reindex() 的区别
- reindex() 必须指定索引序列, 且可同时设置索引和列名:df.reindex(index=[0,2,5], columns=['A', 'C', 'B'])
# df.reset_index(drop=True,inplace=True)
- 也可以用 index() 指定
# df.index=np.arange(df.shape[0])
# df.head()
- 删除首尾空格, 删除换行符
注意有 df.replace 和 str.replace() 的区别
# 删除首尾空格
df['文本'] = df['文本'].str.strip()
# 删除换行符, replace可以直接用在DataFrame上一次替换, 要加参数,regex=True
df['文本'] = df['文本'].replace('/n','')
- 拆出一个期次ID，作为索引
df['期次IDs'] = df['期次IDs'].str.split(',',expand=True, n=1)[0]
df.set_index('期次IDs', inplace=True)
df.head()
- 观察文本结构，用 “},{” 拆分文本
df = df.join(df['文本'].str.split('},{', expand=True))
df.head()
- 文本列已拆分，无用，删除
df.drop(columns='文本', inplace=True)
df.head()
- 通过拆分将 [0] 列里的 "doc_count":,"key": 部分拿出来
df_1 = df[0].str.split('{', expand=True)[2]
df_1.name = '0'
df_1.head()
- 重新组装：删除[0]列，插入整理后的 df_1
df.drop(columns=0, inplace=True)
df = df.join(df_1)
df.head()
- 删除最后 一列的多余文本符号
df[498] = df[498].str.replace('}','')
- 转置整个表
df = df.T
df.head()
- 对整个表逆透视，注意结果是个 Series
df = df.stack()
df.head(10)
- 注意：结果是一个有两级索引的 Series
- 只能用 print(df.index) 查看索引，直接用 df.index 会报错
print(df.index)
- 利用 reset_index() 特性，将 Series 变成了 DataFrame：如果不设置 drop=True 的话，原 index 会成为生成的 DataFrame 的一列
- 将 ‘期次IDs’设置为索引，同时删除多余列 ‘level_0’
df = df.reset_index().set_index('期次IDs').drop(columns='level_0')
df.head()
- 拆分文本，添加列名，替换删除无关字符
df_1 = df[0].str.split(',',expand=True)
df_1.columns=['选修人次','学校名称']
df['选修人次'] = df_1['选修人次'].str.replace('"doc_count":', '')
df['学校名称'] = df_1['学校名称'].str.replace('"key":', '')
df['选修人次'] = df['选修人次'].astype('int64')
df.head()
- 从数据库导入 key_name 关系表
df_key_name = pd.read_sql('SELECT * FROM fid_name', eng)
df_key_name.head()
- 注意 merge 分为 pd.DataFrame.merge 和 pd.merge
- 使用 merge 会丢失索引, 这里先重置索引
df.reset_index(inplace=True)
df.head()
df = pd.merge(df, df_key_name, left_on='学校名称', right_on='fid')
df.head()
- 按 ‘期次IDs’ 和 ‘school_name’ 分组，对选修人次求和
df_2 = df.groupby(['期次IDs','school_name']).agg({'选修人次':'sum'})
df_2.head()
- 多字段分组会产生 MultiIndex
- 重设索引，注意保持默认参数 drop=False
df_2.reset_index(inplace=True)
df_2.head()
- 排序一下，保证分组连接文本后按选修人次多少排列
df_2.sort_values(['期次IDs', '选修人次'], ascending=[True, False], inplace=True)
df.head()
- 添加连接后的文本列，用 “+” 直接连接文本列
- 注意选修人次为整数格式，要用 map 函数将其变成文本
df_2['选课学校名单'] = df_2['school_name'] + '(' + df_2['选修人次'].map(str) + '人次)'
df_2.head()
- 计算每门课的选课学校数
df_3 = df_2.groupby('期次IDs').agg({'school_name':'count'})
df_3.rename(columns={'school_name':'选课学校总数'}, inplace=True)
df_3.head()
- 分组连接文本
df_4 = df_2.groupby('期次IDs')['选课学校名单'].apply(lambda x:x.str.cat(sep=','))
df_4.head()
- 连接生成结果表
df_target = df_3.join(df_4)
df_target.head()
df_target.to_excel('D:\LiCN\Desk\可加入课程\长春工程学院数据支持\选课信息_长工院_1.xlsx')
