pandas追加写入excel_[Excel]如果你爱Excel，请学好pandas

现在坐办公室的各种大小团体里，都会有一个“懂Excel的人”，可能那个人是团体里的一员，也可能是和这个团队关系比较好的一个热心人，但总之，你的身边会有这么一个人。如果你环顾四周也没发现，那可能你就是那个懂Excel的人。

从基本的打印添加表头，到筛选排序，再到vlookup各种花式，Excel确实是促进生产力的一大利器，不过这个工具为了保证易用性也做了很多的妥协，包括但不限于：

1. 没有强制用户使用数据表格式。

很多人应该都听过这样一个新闻：数据汪：日本老爷爷坚持17年用Excel作画，我可能用了假的Excel 当然老人家的确是很厉害，但是从另外一个角度来说，一个用来处理数据的软件，在软件的输入端竟然可以画画，难道不也说明了这个软件是多么的随和以及不专业。毕竟数据处理不是画画，最基本的原则还是要遵守的。

2. 将数据呈现和数据处理混在一起

数据的录入，分析与计算对数据表的格式是有需求的，所以看起来会不大美观。如果要美观，你需要的是数据分析报告。不过Excel里面不管是报告、原始数据还是图标都是一箩筐放在数据表里面。

然后就是让人深恶痛绝的合并单元格

3. 黑箱一时爽，回头火葬场

Excel会自动对数据进行格式转化。一个比较经典的例子就是“MARC1”基因这个例子。在Excel里面这个字符会自动被转换成3月1日。有人写了个小程序“解决”了这个问题，然后在Plos one上面发了个文章。

然后复制粘贴过程中的自动偏移，vlookup在大表格里对性能的考验……

但是，Excel绝对是非常强大的一个软件，之所以有时候会犯傻，那也只是大智若愚，毕竟为了照顾用户总是要做一些牺牲的。

---

“这个问题用Excel怎么解决呢？”用Pandas吧

比如吧，交叉表怎么转换回数据表呢？Excel有数据透视表的功能，可以将数据表转换成交叉表，但是没有反向转换的功能。VBA是一个选项，但是，拜托，既然你已经开始讲编程提上日程了，那难道不应该选择更加流行一点的语言么？

TIOBE index，2019年8月

你们心心念念的VB排在16位，VB .NET排在第六位，是不错啦，比起Swift，Perl，R这些来说的话。但是Python好歹也是排在第3呀，真的不考虑一下么？

pandas是一个数据处理的库，基于python语言。下面举几个例子展示一下pandas的能力：

读取和写入Excel文件

读取和写入csv就相当简单了，但是excel的话还是需要首先安装几个包：(xlrd,xlsxwriter）

读取：

import pandas as pd
xl_file = pd.ExcelFile('filepath.xlsx') #接受excel文件路径，建立Excel文件对象
df = xl_file.parse('Sheet_name') #parse接受工作表名称，将工作表转换成Dataframe

df现在就是一个dataframe了，你可以用df.columns看看表头有哪些列，你可以用df.loc['行标签','列标签']获取/设置特定位置的内容，用apply，或者是向量化运算处理数据

写入:

xl_writer = pd.ExcelWriter('out.xlsx',engine='xlsxwriter')
df.to_excel(xl_writer,'Sheet0')
xl_writer.save()

pandas.DataFrame.to_excel - pandas 0.25.1 documentation​pandas.pydata.org

所以说，pandas读取和写入Excel文件都是非常容易的。

交叉表变数据表

用下面的一组数据为例

df = pd.DataFrame([['jane',20,83,41],['tom',22,89,82],['mike',24,55,93]],columns=['name','age','grade_curr1','grade_curr2'])
df
Out[10]: 
   name  age  grade_curr1  grade_curr2
0  jane   20           83           41
1   tom   22           89           82
2  mike   24           55           93

可以看到这里有三行数据，包括了姓名，年龄，以及两个科目的成绩。第三列和第四列其实都是成绩，所以如果能放在一列会更方便后续的分析。

df_melted = df.melt(id_vars=['name','age'],value_vars=['grade_curr1','grade_curr2'])
df_melted
Out[13]: 
   name  age     variable  value
0  jane   20  grade_curr1     83
1   tom   22  grade_curr1     89
2  mike   24  grade_curr1     55
3  jane   20  grade_curr2     41
4   tom   22  grade_curr2     82
5  mike   24  grade_curr2     93

分组统计

可能看了上面的例子你没有觉得哪里更“方便”了。那让我们加一些细节。比如现在还是这三个人，分别接受两个项目的考试（curr1和curr2），每个考试可以最多参加3次，但是也有人只参加了1次。所以成绩登记出来是下面这样的：

如果现在要求空白数据不纳入分析（比如jane的科目1只参加了两次考试，所以平均分应该是74+80/2=77）。那……用Excel你准备怎么做呢？要滤掉空白项那你必须用筛选呀，用筛选就……肯定要形成数据表呀……

import pandas as pd
import os

os.chdir('/Users/zheng/Desktop/')
xl = pd.ExcelFile('工作簿1.xlsx')
df = xl.parse('工作表1')
print(df)

 name  age  cur1_trial1  ...  cur2_trial1  cur2_trial2  cur2_trial3
0  jane   20           74  ...          NaN         88.0           89
1   tom   23           65  ...         78.0         82.0           48
2  mike   21           90  ...          NaN          NaN           87

[3 rows x 8 columns]

转换成数据表

df_melt = df.melt(id_vars=['name','age'])
df_melt.head()
Out[6]: 
   name  age     variable  value
0  jane   20  cur1_trial1   74.0
1   tom   23  cur1_trial1   65.0
2  mike   21  cur1_trial1   90.0
3  jane   20  cur1_trial2    NaN
4   tom   23  cur1_trial2   66.0

好了，接下来可以先筛选掉那些空值

df_filtered = df_melt[pd.notna(df_melt['value'])]
df_filtered.head()
Out[8]: 
   name  age     variable  value
0  jane   20  cur1_trial1   74.0
1   tom   23  cur1_trial1   65.0
2  mike   21  cur1_trial1   90.0
4   tom   23  cur1_trial2   66.0
5  mike   21  cur1_trial2   91.0

之前id=3的那一行已经被过滤了。接下来我们要求平均数

那如果是在excel里面我们可以用到averageifs，当然了我不知道Excel对于求标准差，方差，样本估算总体方差，中值，1st quater，3rd quater，极差……等等等等这些东西有没有ifs函数，毕竟我不care。

况且上面的数据还有个坑，我把两列数据合并成一列了（cur1_trial1是两个东西：科目和试验次数) :)

不用担心，可以用.str.split

df_splitted = df_filtered['variable'].str.split('_',expand=True)
df_splitted
Out[17]: 
       0       1
0   cur1  trial1
1   cur1  trial1
2   cur1  trial1
4   cur1  trial2
5   cur1  trial2
6   cur1  trial3
7   cur1  trial3
10  cur2  trial1
12  cur2  trial2
13  cur2  trial2
15  cur2  trial3
16  cur2  trial3
17  cur2  trial3

配合改个名

df_splitted = df_splitted.rename({0:'curriculum',1:'trial'},axis=1)
df_splitted.head()
Out[23]: 
  curriculum   trial
0       cur1  trial1
1       cur1  trial1
2       cur1  trial1
4       cur1  trial2
5       cur1  trial2
 

ok，现在把df_filtered和df_splitted合并一下

df_comb = pd.concat([df_filtered,df_splitted],axis=1)
df_comb.head()
Out[25]: 
   name  age     variable  value curriculum   trial
0  jane   20  cur1_trial1   74.0       cur1  trial1
1   tom   23  cur1_trial1   65.0       cur1  trial1
2  mike   21  cur1_trial1   90.0       cur1  trial1
4   tom   23  cur1_trial2   66.0       cur1  trial2
5  mike   21  cur1_trial2   91.0       cur1  trial2

注意到两个axis=1，这是设置操作的方向。大体上来说，0就是默认的，1呢，就是换一个方向。

接下来回到求平均值的问题

df_comb.groupby(['name','curriculum','age']).mean()
Out[30]: 
                         value
name curriculum age           
jane cur1       20   77.000000
     cur2       20   88.500000
mike cur1       21   90.500000
     cur2       21   87.000000
tom  cur1       23   64.000000
     cur2       23   69.333333

所以呢，这个按姓名和课程的分组就搞定了，多容易。

类似的：

一行变多行：路人乙小明：[python]Shit, 我完全不明白为什么这么做会work，但是it worked！

万能的apply：路人乙小明：[pandas]emm……一旦你接受了这个设定：pandas的apply