五分钟学会四种宽数据转长数据方法 |
您所在的位置:网站首页 › 怎么将excel表格变宽 › 五分钟学会四种宽数据转长数据方法 |
在学Mysql中的时候,想必大家都曾遇到如下的题:将表1转为表2, 表1 CompanyNameSale2013Sale2014Sale2015Sale2016Apple苹果5000505050505050Google谷歌3500380038003800Facebook脸书2300290029002900表2 CompanyNameYearSalesApple苹果Sale20135000Google谷歌Sale20133500Facebook脸书Sale20132300Apple苹果Sale20145050Google谷歌Sale20143800Facebook脸书Sale20142900Apple苹果Sale20155050Google谷歌Sale20153800Facebook脸书Sale20152900Apple苹果Sale20165050Google谷歌Sale20153800Facebook脸书Sale20162900这实际上就是宽表转长表。今天,分享一下分别在MySQL、excel、R、python中实现宽表转长表的方法。 目录 一、Mysql二、Excel三、R四、python 一、Mysql实现的关键字:union all #准备数据 create table items(Company varchar(10), Name varchar(10),Sale2013 int, Sale2014 int,Sale2015 int, Sale2016 int); insert into items values("Apple","苹果",5000,5050,5050,5050), ("Google","谷歌",3500,3800,3800,3800), ("Facebook","脸书",2300,2900,2900,2900); select Company,Name, 'Sale2013' as 'Year',`Sale2013` as 'Sales' from items union all select Company,Name, 'Sale2014' as 'Year',`Sale2014` as 'Sales' from items union all select Company,Name, 'Sale2015' as 'Year',`Sale2015` as 'Sales' from items union all select Company,Name, 'Sale2016' as 'Year',`Sale2016` as 'Sales' from items;实现关键词:PowerQuery逆透视 (1)数据——从表格——表包含标题 实现关键词:gather #准备数据 df=data.frame(Company=c("Apple","Google","Facebook"), Name=c("苹果","谷歌","脸书"), Sale2013=c(5000,3500,2300), Sale2014=c(5050,3800,2900), Sale2015=c(5050,3800,2900), Sale2016=c(5050,3800,2900)) library(tidyr) gather(df,key="Year",value="Sales",Sale2013:Sale2016)key:转换后新的列名 value:转换后值的新列名 Sale2013:Sale2016 : 需要转列的字段 实现关键词:set_index+stack+reset_index、melt(有点类似R中的gather) 方法一:set_index+stack+reset_index stack可以将行索引转为列索引,但Company、Name这列是没有变化的,所以需要先将其设置为索引,具体实现方法如下: import pandas as pd import os os.chdir('C:/Users/dell/Desktop') data=pd.read_excel('data.xlsx',encoding='utf-8') df=data.set_index(['Company','Name']).stack().reset_index() df.columns=['Company','Name','Year','Sales']方法二:melt data.melt(id_vars=['Company','Name'],var_name='Year',value_name='Sales')
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |