08.01-Xls.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!--BOOK_INFORMATION-->\n",
    "<img align=\"left\" style=\"padding-right:10px;\" src=\"figures/PHydro-cover-small.png\">\n",
    "*This is the Jupyter notebook version of the [Python in Hydrology](http://www.greenteapress.com/pythonhydro/pythonhydro.html) by Sat Kumar Tomer.*\n",
    "*Source code is available at [code.google.com](https://code.google.com/archive/p/python-in-hydrology/source).*\n",
    "\n",
    "*The book is available under the [GNU Free Documentation License](http://www.gnu.org/copyleft/fdl.html). If you have comments, corrections or suggestions, please send email to satkumartomer@gmail.com.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<!--NAVIGATION-->\n",
    "< [8. Input-Output](08.00-Input-Output.ipynb) | [Contents](Index.ipynb) | [Text-File](08.02-Text-File.ipynb) >"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.1 xls\n",
    "\n",
    "`data.xls`文件包含了来自AMSR-E平台估算的土壤水分数据。你可以打开这个xls文件，并查看其内容。在这个文件中，我们有两个工作表(sheet)的数据，分别对应卫星方向的上升和下降。每个工作表包含各种网格点的时间序列数据。缺失的数据被赋予999.9的数。在本节，我们将一直读取一个站点的数据，修改丢失的数据，并写入其他的xls文件。我们将使用`xlrd`库从xls文件中读取数据，`xlws`写入数据到xls文件。`xlrd`并不读取`xlsx`数据文件，你应该在读取前，将`xlsx`类型的文件转换为`xls`。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xlrd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们通过传递xls文件的名称到`xlrd.open_workbook`来创建工作簿对象。我们使用`sheet_by_name`来指定需要读取数据的工作表。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "book = xlrd.open_workbook('datas/data.xls')\n",
    "sheet = book.sheet_by_name('Ascending')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在工作表中，可通过分别使用`nrows`和`ncols`属性来检查表中的列数和行数。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1100"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.nrows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.ncols"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们工作表的开头两行是表、经度和维度的标题，因此时间序列数据的长度少两个。首先，我们创建一个空的数组来存储数据，然后我们使用`cell_valule`来读取数据单元。我们将读取经度为12.4958和维度为75.7484，位于第四列(索引从零开始)的数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "sm = np.empty(sheet.nrows-2)\n",
    "year = np.empty(sheet.nrows-2, int)\n",
    "month = np.empty(sheet.nrows-2,int)\n",
    "day = np.empty(sheet.nrows-2,int)\n",
    "for i in range(sm.shape[0]):\n",
    "    sm[i] = sheet.cell_value(i+2,27)\n",
    "    year[i] = sheet.cell_value(i+2,0)\n",
    "    month[i] = sheet.cell_value(i+2,1)\n",
    "    day[i] = sheet.cell_value(i+2,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们可以检查某些变量的数据，例如，sm。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  12.6,  999.9,   13.2, ...,   12.1,   12.7,   12.8])"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我们可以将所有丢失的数据定义为`nan`。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 12.6,   nan,  13.2, ...,  12.1,  12.7,  12.8])"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sm[sm==999.9]=np.nan\n",
    "sm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "现在土壤湿度数据用`nan`代替999.9表示缺失值。我们将使用`xlws`库将该土壤湿度数据写入xls文件。首先我们打开工作簿，然后我们用`add_sheet`添加一个名称表。在此之后，我们开始按单元格写入条目。最终，我们使用`book.save`保存工作表。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "import xlwt\n",
    "book = xlwt.Workbook()\n",
    "sheet = book.add_sheet('Ascending')\n",
    "sheet.write(0,0,'Year')\n",
    "sheet.write(0,1,'Month')\n",
    "sheet.write(0,2,'Day')\n",
    "sheet.write(0,3,'Latitude')\n",
    "sheet.write(1,3,'Longitude')\n",
    "\n",
    "for i in range(len(sm)):\n",
    "    sheet.write(i+2,4,sm[i])\n",
    "    sheet.write(i+2,0,int(year[i]))\n",
    "    sheet.write(i+2,1,int(month[i]))\n",
    "    sheet.write(i+2,2,int(day[i]))\n",
    "\n",
    "book.save('datas/data1.xls')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我已经编写了一个库`ambhas.xls`,该库可以提供相当简单的方法来读取和写入xls数据。数据可以按照以下方式读取。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ambhas.xls import xlsread\n",
    "fname ='datas/data.xls'\n",
    "foo = xlsread(fname)\n",
    "data = foo.get_cells('a3:a5','Ascending')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "按照以下方式将数据写入`xls`文件，写入的数据应该是一个numpy数组。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from ambhas.xls import xlsread\n",
    "fname ='datas/data.xls'\n",
    "foo = xlswrite(data,'a3','Ascending')\n",
    "foo.save(fname)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于该库依赖于`xlrd`，它也无法读取`xlsx`数据文件，你应该在读取前将`xlsx`格式的文件转换为`xls`。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}