在某些情况下需要对Excel中的数据做横向汇总,此时使用Pandas的将体现出很强的优势,请看下面的数据:
表格中有5个子类别:类别1----类别5,每一行中至少有1个类别的值是1,表示当前行有多个类别类别属性
两个汇总列:汇总类别1,汇总类别2
汇总要求
1. 类别1列中等于1的值,要替换成字符串 A类;
类别2列中等于1的值,要替换成字符串 B类;
类别3列中等于1的值,要替换成字符串 C类;
类别4列中等于1的值,要替换成字符串 D类;
类别5列中等于1的值,要替换成字符串 E类;
2. 最靠近左边的类别字段汇总到类别汇总1,其他的类别都汇总到类别汇总2
例如:
1. 某一行的类别1、类别2字段被选中其他类别是空,汇总之后的结果是类别汇总1=A类,类别汇总2=B类
2. 某一行的类别1、类别2、类别3 字段被选中其他类别是空,汇总之后的结果是类别汇总1=A类,类别汇总2=B类,C类
3. 某一行的类别4字段有数据,其他类别是空,汇总之后的结果是类别汇总1=D类
汇总后的结果:
通过汇总结果来看,类别1到类别5的数据是横向跌落到左边两个个汇总字段中。
用jupyter 来实现这个需求,
第1步: 载入这个exce文件 类别数据汇总.xlsx
import pandas as pd
file = r'E:\wangyq\learn\python\数据分析\pandas_02\类别数据汇总.xlsx'
data= pd.read_excel(file,sheet_name='S1',index_col='名称')
data
第2步:替换数据
def fill_class():
a=list('ABCDE')
for index,k in enumerate(a,start=1):
fieldName = '类别{0}'.format(index)
data[fieldName]= data[fieldName].apply(lambda x: '{0}类'.format(k) if x>0 else '' )
fill_class()
data
for循环中的内容展后其实是这样:
data['类别1']= data['类别1'].apply(lambda x: 'A类' if x>0 else '' )
data['类别2']= data['类别2'].apply(lambda x: 'B类' if x>0 else '' )
data['类别3']= data['类别3'].apply(lambda x: 'C类' if x>0 else '' )
data['类别4']= data['类别4'].apply(lambda x: 'D类' if x>0 else '' )
data['类别5']= data['类别5'].apply(lambda x: 'E类' if x>0 else '' )
执行结果是这样:
第3步:开始汇总
这里的汇总分成两步,先计算出类别汇总1字段值
def setClass1(row):
field = ['类别2','类别3','类别4','类别5']
value = ''
# 如果类别1字段不是空,直接返回类别1的字段值
if len(row['类别1'])>0:
value = row['类别1']
else:
# 否则从类别2开始向后拼接各个列字段值,并且只取最靠近左边的值
for f in field:
if len(row[f])>0:
value = row[f]
# 得到值之后立即退出循环返回结果
break
return value
data['类别汇总1'] = data.apply(lambda row: setClass1(row),axis=1)
data
这里用到了DataFrame的apply函数,可以作用于DataFrame的每个值,但是接受的参数不是各个值本身,而是DataFrame里各行(或列),返回一个新的行(列),axis=0表示操作列,axis=1表示操作行。
函数定义:
DataFrame.apply(func,axis=0)
该函数的参数是一个普通函数或者一个匿名函数,这里的用法是用lambda 调用setClass1,指定axis=1,表示是X轴方面的计算,同时给正常的函数传递一个row,这里的row其实是整行数据,在setClass1里面可以访问整行数据的不同列。
在jupyter里面可以单独执行上面的这段代码,返回结果如下:
可以看到类别汇总1的数据已经计算完成符合上面的要求
计算类别汇总2的值:
其实算法和上面的比较类似,但是为了清晰,我还是独立定义了一个新的函数
def setClass2(row):
field = ['类别1','类别2','类别3','类别4','类别5']
values = []
for f in field:
if len(row[f])>0:
values.append(row[f])
if len(values)>=2:
return ','.join(values[1:])
elif len(values)<=1:
return ''
data['类别汇总2'] = data.apply(lambda row: setClass2(row),axis=1)
data
总体算法是:
从类别1开始拼接各个类别的值,判断如果各列值不是空则添加到list,如果list值>=2,则跳过第一个元素(因为左边第一个元素会被作为类别汇总1的值)后面的数据转换成用逗号分隔的字符串返回。如果整个行的类别合并之后只有1个元素或者小于1个元素,那么返回空字符串。
执行结果如下:
最后一步把结果输出到excel文件
dest=r'E:\pandas_02\类别汇总.xls'
data.to_excel(dest,index=None)
打开这个文件:
可以看到已经汇总完成。其实可以有更优雅的处理方案,欢迎各位亲勇于探索,交流讨论