数据分析过程中,有一个非常的重要的环节就是需要对原始数据进行清洗、重组结构等预处理,然后再进行数据分析、挖掘及可视化。
长宽表的转换在数据格式、结构重塑过程中经常用到,将长表转换成宽表(数据透视),或将宽表转换成长表(数据逆透视),本文介绍Python、R语言及Excel长宽表转换的几种方法。
ps:Excel实现需要环境:window + office2016+
目录
1、长宽数据简介
2、Python长宽数据转换
3、R语言长宽数据转换
4、Excel长宽数据转换
1、长宽数据简介
我们继续使用mysql窗口函数中数据进行操作。
宽数据:很多列较少行,即列多行少的表,一行中的数据量较大,行数少。
宽数据表格式如下:
长数据:很多行较少列,即行多列少,一行中的数据量较少,行数大
长数据表格式如下(其中相同的行已合并):
2、Python长宽数据转换
我们使用pandas包中melt与pivot_table来实现长宽转换,还有一些其他的函数比如stack、wide_to_long、unstack本文暂不做介绍。
2.1 宽转长
pandas.melt:宽数据转长数据
pandas.melt(frame: pandas.core.frame.DataFrame, id_vars=None, value_vars=None, var_name=None, value_name='value', col_level=None)
frame :需要处理的的DataFrame;
id_vars :不需要被转换的列名,在转换后作为标识符列(不是索引列)可以为tuple、list、or ndarray、optional Column(s)等
value_vars:被转换的列名,如果未指定,除 id_vars 之外的其他列都被转换,可以为tuple、list or ndarray、optional Column(s)等
var_name:被转换的列名组成的新列的 name,如果没指定或者为None,那么则默认为frame.columns.name 或者varivale
value_name:被转换的列下的数据组成的新列的 name
col_level:int或string,可选,如果列为MultiIndex, 它将使用此级别来融化
pandas.pivot_table:长数据表转宽数据表
import pandas as pdimport numpy as np wide_df=pd.DataFrame( { "year":[2000,2000,2000,2001,2001], "country":['Finland','India','USA','Finland','USA'], 'Calculator':[np.NAN,150,75,np.NAN,50], 'Computer':[1500,1200,1500,np.NAN,2700], 'Phone':[100,np.NAN,np.NAN,10,np.NAN], 'TV':[np.NAN,np.NAN,np.NAN,np.NAN,250] })wide_df wide_to_long_df=wide_df.melt( id_vars=["year","country"], var_name="product", value_name="profit").dropna(subset=['profit']).reindex().sort_values(by=['year','country','product'], ascending=True)# 删除profit为空的,排序输出结果 wide_to_long_df
2.2 长转宽
pandas.pivot_table
pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All', observed=False)
data :需要处理的的DataFrame;
values:要进行汇总结算的列名,类似于数据透视表中的"数值"
index:用于分组的列,类似行索引,相当于excel透视表中的"行标签"
columns:相当于excel透视表中的"列标签",更详细的展示内容
aggfunc:汇总聚合的计算方式,类似于在excel数据透视表中选定列了以后选择是求和还是取平均,如果缺省,默认求平均。可以接受列表,即对values作不同的聚合,也可以接受字典,即对不同的values作不同的操作,也可以将字典里的值改为列表形式的,即对某列作几种不同的操作。对于aggfunc,操作的是values后面的值,而不是columns后面的值
fill_value:缺失值填充
dropna: 默认True,不包括全是NaN的列
margins:是否对计算结果求行列汇总合计,默认为Flase,若为True则会添加分项的的合计,即行列的汇总
margins_name='All':margins结果的命名,默认为‘ALL'
long_df = wide_to_long_df.copy() long_to_wide_df = long_df.pivot_table( index=["year","country"], columns=["product"], aggfunc='sum', values=["profit"]) long_to_wide_df # 返回结果多层索引我们转为一层long_to_wide_df.columns =[i1 +'_'+ str(i2) for (i1,i2) in long_to_wide_df.columns.tolist()]long_to_wide_df = long_to_wide_df.reset_index() long_to_wide_df
多层索引
3、R语言长宽数据转换
R语言中有两组函数可以方便的实现长宽数据的转换:
reshape2包的melt()和dcast()函数;
tidyr包的gather()函数和spread()函数,这也是reshape2包的一个新接口。
reshape()、stack()、unstack()也能实现,本文中暂不介绍
3.1 宽转长
reshape2.melt():S3 通用函数,它会根据数据类型(数据框,数组或列表)选择 melt.data.frame, melt.array 或 melt.list 函数进行实际操作,这里我们主要看数据框情形下的处理。
melt(data,id.vars,measure.vars,variable.name = "variable",...,na.rm = FALSE,value.name = "value",factorsAsStrings = TRUE)
data:需要处理的的DataFrame;
id.vars:保留不参与转换的列,用于分组,缺省所有的其他变量则会被视为id.vars
measure.vars:被转换的列名,若留空,则会转换除id.vars之外的所有列,然后增加两个新列:variable和value,一个存储变量的名称一个存储变量值,当然measure.vars也可以一系列变量,表示观测变量。若两者均缺省,会把因子和字符串类型的变量作为id.vars,其余变量作为measure.vars计算;
variable.name:被处理的列名组成的新列列名,如果缺省则默认为variable
value.name:被转换的列下的数据组成的新列列名
na.rm:是否去除缺失值,默认是FALSE
tidyr.gather()
gather(data,key = "key",value = "value",...,na.rm = FALSE,convert = FALSE,factor_key = FALSE)
data:需要处理的的DataFrame;
key::被转换的列名组成的新列列名
value:被转换的列名下的数据组成的新列列名
...:被转换的列名,若留空,则会转换全部,可以用names、 x:z、-y选择需要被处理的列
year country Calculator Computer Phone TV wide_df wide_df # melt()函数实现wide_to_long_df wide_df %>% melt( id.vars=c('year','country'), variable.name='product', value.name='profit', na.rm=T ) %>% arrange(year, country, product)wide_to_long_df # gather()函数实现wide_to_long_df wide_df %>% gather( key='product', value='profit', 3:6, na.rm=T ) %>% arrange(year, country, product)wide_to_long_df
melt()输出结果
gather()输出结果
3.2 长转宽
reshape2.dcast()
dcast(data,formula,fun.aggregate = NULL,...,margins = NULL,subset = NULL,fill = NULL,drop = TRUE,value.var = guess_value(data))
data:需要处理的的DataFrame;
formula:用于指定需要保留和转换的列,用'~'连接,左边为标识变量,右边为测量变量,类似于melt函数中的id.vars参数和measure.vars参数;
fun.aggregate:用于指定聚合函数,对已聚合的数据执行聚合运算,如mean, sum, median,类似于在excel数据透视表中选定列了以后选择是求和还是取平均
margins:相当于透视表中的行总计和列总计,可以是TRUE也可以列名,默认为null
subset:选取满足一些特定值的数据,相当于Excel透视表的筛选;
fill:缺失值填充方式
drop:默认为TRUE,是否删除缺失的组合
value.var: 新的变量名下的取值,需要加双引号,如果缺省则使用guess_value,Strategy:
Is value or (all) column present? If so, use that
Otherwise, guess that last column is the value column
tidyr.spread()
spread(data, key, value, fill = NA, convert = FALSE, drop = TRUE, sep = NULL)
data:需要处理的的DataFrame;
key:被展开的列,相当于excel透视表中的"列标签",更细化地展示一些内容
value:要进行汇总结算的列名,类似于数据透视表中的"数值"
sep:默认为空,如果设置后,展开后列名为 ""
# dcast()函数实现long_to_wide_df wide_to_long_df %>% dcast( year+country~product, fun.aggregate=sum, value.var='profit' ) long_to_wide_df # spread()函数实现long_to_wide_df wide_to_long_df %>% spread( key='product', value='profit' ) long_to_wide_df
dcast()输出结果
spread()输出结果
4、Excel长宽数据转换
Excel中长宽转换我们使用透视表和逆透视功能来实现
4.1 宽转长
Power query中的逆透视表功能可以将宽表转换为长表,我们需要Excel版本2016+,windows系统,MAC版暂无此功能~
我们按图示序号进行操作:
点击数据区域任意单元格 》点击[数据]菜单 》点击[从表格/区域]
在弹出的Power Query编辑器,点击列名选择保留的列 》点击[转换]菜单 》点击[逆透视] 》点击[逆透视其他列]
点击[主页]菜单 》 点击[关闭并上载] 》点击[关闭并上载]
自动生成了一个结果工作表,我们只需修改列名就OK了
4.2 长转宽
长数据转宽数据我们使用透视表来实现:
选中数据区域 》点击[插入]菜单 》点击[数据透视表]
把year、country拖到行区域,把product拖到列区域,把profit拖到值区域
点击值区域profit,弹出对话框可以选择值计算方式,这里我们选择求和
点击透视表任意区域 》点击[设计]菜单 》点击[报表布局] 》点击[以表格形式显示] 》重复1、2、3点击[重复所有项目标签]
点击透视表任意区域 》点击[设计]菜单 》点击[分类汇总] 》点击[不显示分类汇总]
输出结果
本文总结了Python、R语言及Excel进长宽表转换的几种方法,当然还有一些其他的方法,以后再补充~