【3.1.2.1】Pandas--excel的读写

DataFrame创建

>>> df_out = pd.DataFrame([('string1', 1),
...                        ('string2', 2),
...                        ('string3', 3)],
...                       columns=['Name', 'Value'])
>>> df_out
      Name  Value
0  string1      1
1  string2      2
2  string3      3
>>> df_out.to_excel('tmp.xlsx')

一、读取excel 表格

读取文件

>>> pd.read_excel('tmp.xlsx')
      Name  Value
0  string1      1
1  string2      2
2  string3      3

或者：

>>> pd.read_excel(open('tmp.xlsx','rb'))
      Name  Value
0  string1      1
1  string2      2
2  string3      3

index和header可以指定是否读取

>>> pd.read_excel('tmp.xlsx', index_col=None, header=None)
     0        1      2
0  NaN     Name  Value
1  0.0  string1      1
2  1.0  string2      2
3  2.0  string3      3

列的数据类型可以被指定

>>> pd.read_excel('tmp.xlsx', dtype={'Name':str, 'Value':float})
      Name  Value
0  string1    1.0
1  string2    2.0
2  string3    3.0

某些数据替换为NA

>>> pd.read_excel('tmp.xlsx',
...               na_values=['string1', 'string2'])
      Name  Value
0      NaN      1
1      NaN      2
2  string3      3

过滤掉注释的行

>>> df = pd.DataFrame({'a': ['1', '#2'], 'b': ['2', '3']})
>>> df.to_excel('tmp.xlsx', index=False)
>>> pd.read_excel('tmp.xlsx')
    a  b
0   1  2
1  #2  3

>>> pd.read_excel('tmp.xlsx', comment='#')
   a  b
0  1  2

数据源2：

sheet1:
ID  NUM-1   NUM-2   NUM-3
36901   142 168 661
36902   78  521 602
36903   144 600 521
36904   95  457 468
36905   69  596 695

sheet2：
ID  NUM-1   NUM-2   NUM-3
36906   190 527 691
36907   101 403 470

(1)函数原型

basestation ="F://pythonBook_PyPDAM/data/test.xls"
data = pd.read_excel(basestation)
print data	

输出：是一个dataframe

      ID  NUM-1  NUM-2  NUM-3
0  36901    142    168    661
1  36902     78    521    602
2  36903    144    600    521
3  36904     95    457    468
4  36905     69    596    695

(2) sheetname参数：返回多表使用sheetname=[0,1],若sheetname=None是返回全表 注意：int/string 返回的是dataframe，而none和list返回的是dict of dataframe

data_1 = pd.read_excel(basestation,sheetname=[0,1])
print data_1
print type(data_1)

输出：dict of dataframe

OrderedDict([(0,       ID  NUM-1  NUM-2  NUM-3
0  36901    142    168    661
1  36902     78    521    602
2  36903    144    600    521
3  36904     95    457    468
4  36905     69    596    695), 
(1,       ID  NUM-1  NUM-2  NUM-3
0  36906    190    527    691
1  36907    101    403    470)])

这里要注意的，我用sheet_name不报错，但是永远用的是第一个sheet，搞的结果里面引入了bugs。可能是包更新的问题。

（3）header参数:指定列名行，默认0，即取第一行，数据为列名行以下的数据 若数据不含列名，则设定 header = None ，注意这里还有列名的一行。

data = pd.read_excel(basestation,header=None)
print data
输出：
       0      1      2      3
0     ID  NUM-1  NUM-2  NUM-3
1  36901    142    168    661
2  36902     78    521    602
3  36903    144    600    521
4  36904     95    457    468
5  36905     69    596    695

data = pd.read_excel(basestation,header=[3])
print data
输出：
   36903  144    600    521  
0  36904     95    457    468
1  36905     69    596    695

（4） skiprows 参数：省略指定行数的数据

data = pd.read_excel(basestation,skiprows = [1])
print data
输出:
      ID  NUM-1  NUM-2  NUM-3
0  36902     78    521    602
1  36903    144    600    521
2  36904     95    457    468
3  36905     69    596    695

(5)skip_footer参数：省略从尾部数的int行的数据

data = pd.read_excel(basestation, skip_footer=3)
print data
输出：
      ID  NUM-1  NUM-2  NUM-3
0  36901    142    168    661
1  36902     78    521    602

（6）index_col参数：指定列为索引列，也可以使用u”strings”

data = pd.read_excel(basestation, index_col="NUM-3")
print data
输出：
          ID  NUM-1  NUM-2
NUM-3                     
661    36901    142    168
602    36902     78    521
521    36903    144    600
468    36904     95    457
695    36905     69    596

（7）names参数： 指定列的名字。

data = pd.read_excel(basestation,names=["a","b","c","e"])
print data
       a    b    c    e
0  36901  142  168  661
1  36902   78  521  602
2  36903  144  600  521
3  36904   95  457  468
4  36905   69  596  695

读取多个sheet

方法1（推荐用这个方法）：

xl = pd.ExcelFile('foo.xls')
xl.sheet_names  # see all sheet names
xl.parse(sheet_name)  # read a specific sheet to DataFrame

方法2：

import xlrd
import pandas as pd
from pandas import DataFrame
 
DATA_DIR = 'E:/' 
excel_name = '%s2017.xls' % DATA_DIR
wb = xlrd.open_workbook(excel_name)
# print(wb)
 
# 获取workbook中所有的表格
sheets = wb.sheet_names()
# print(sheets)
 
# 循环遍历所有sheet
df_28 = DataFrame()
for i in range(len(sheets)):


    # skiprows=2 忽略前两行
    df = pd.read_excel(excel_name, sheet_name=i, skiprows=2, index=False, encoding='utf8')
    df_28 = df_28.append(df)
# 去除缺省值
df_28 = df_28.dropna()
df_28 = df_28.reset_index(drop=True)
print(len(df_28))

读取指定的列usecols

data = pd.read_excel(datafile,sheet_name='analysis',usecols=range(1,10))

二、写excel

>>> df.to_excel('tmp.xlsx', index=False)

更多参数说明见：

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html

数据源：

    ID  NUM-1   NUM-2   NUM-3
0   36901   142 168 661
1   36902   78  521 602
2   36903   144 600 521
3   36904   95  457 468
4   36905   69  596 695
5   36906   165 453 

加载数据：
basestation ="F://python/data/test.xls"
basestation_end ="F://python/data/test_end.xls"
data = pd.read_excel(basestation)

参数excel_writer，输出路径。

data.to_excel(basestation_end)
输出：
    ID  NUM-1   NUM-2   NUM-3
0   36901   142 168 661
1   36902   78  521 602
2   36903   144 600 521
3   36904   95  457 468
4   36905   69  596 695
5   36906   165 453 

sheet_name，将数据存储在excel的那个sheet页面。

data.to_excel(basestation_end,sheet_name="sheet2")

na_rep，缺失值填充

data.to_excel(basestation_end,na_rep="NULL")
输出：
    ID  NUM-1   NUM-2   NUM-3
0   36901   142 168 661
1   36902   78  521 602
2   36903   144 600 521
3   36904   95  457 468
4   36905   69  596 695
5   36906   165 453 NULL

colums参数： sequence, optional，Columns to write 选择输出的的列。

data.to_excel(basestation_end,columns=["ID"])
输出
    ID
0   36901
1   36902
2   36903
3   36904
4   36905
5   36906	

header 参数： boolean or list of string，默认为True,可以用list命名列的名字。header = False 则不输出题头。

data.to_excel(basestation_end,header=["a","b","c","d"])
输出：
    a   b   c   d
0   36901   142 168 661
1   36902   78  521 602
2   36903   144 600 521
3   36904   95  457 468
4   36905   69  596 695
5   36906   165 453 


data.to_excel(basestation_end,header=False,columns=["ID"])
header = False 则不输出题头
输出：
0   36901
1   36902
2   36903
3   36904
4   36905
5   36906

index : boolean, default True Write row names (index)

默认为True，显示index，当index=False 则不显示行索引（名字）。 index_label : string or sequence, default None 设置索引列的列名。

data.to_excel(basestation_end,index=False)
输出：
ID  NUM-1   NUM-2   NUM-3
36901   142 168 661
36902   78  521 602
36903   144 600 521
36904   95  457 468
36905   69  596 695
36906   165 453 

data.to_excel(basestation_end,index_label=["f"])
输出：
f   ID  NUM-1   NUM-2   NUM-3
0   36901   142 168 661
1   36902   78  521 602
2   36903   144 600 521
3   36904   95  457 468
4   36905   69  596 695
5   36906   165 453 

多sheet的数据的保存，不覆盖已有的sheet

import pandas as pd
writer = pd.ExcelWriter('foo.xlsx')
df.to_excel(writer, 'Data 0')
df.to_excel(writer, 'Data 1')
writer.save()

三、报错

报错1

'ascii' codec can't decode byte 0xef in position 0: ordinal not in range(128)

解决办法：

seq_info = pd.read_table(seq_info_fp,encoding='utf-8').rename(columns={'#seq_name':"#seq",'status':u"测试质量"})

报错2

python3 中报错：

xlrd.biffh.XLRDError: Excel xlsx file； not supported

原因是最近xlrd更新到了2.0.1版本，只支持.xls文件

解决办法：

pip uninstall xlrd
pip install xlrd==1.2.0

参考资料

• https://blog.csdn.net/tongxinzhazha/article/details/78796952
• https://stackoverflow.com/questions/17977540/pandas-looking-up-the-list-of-sheets-in-an-excel-file