CreateAPSIMMaizeObservedFile.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bring biomass data into dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "path = 'D:\\GitHubRepos\\ApsimX\\Tests\\Validation\\Maize\\Observations'\n",
    "Observed = pd.read_excel(path+'\\obsDataMaize_DB.xlsx',sheetname='HarvestData')\n",
    "ObservedTimeSeries = pd.read_excel(path+'\\obsDataMaize_DB.xlsx',sheetname='TimeSeriesData')\n",
    "NZObserved = pd.read_excel(path+'\\obsDataMaize_DB.xlsx',sheetname='observed')\n",
    "RainShelterN = pd.read_excel(path+'\\obsDataMaize_DB.xlsx',sheetname='RainShelterNitrogen')\n",
    "RainShelterSW = pd.read_excel(path+'\\SoilWater.xlsx',sheetname='SoilWater')\n",
    "RainShelterCob = pd.read_excel(path+'\\obsDataMaize_DB.xlsx',sheetname='RachisHusk_RainS')\n",
    "PhenologySimple = pd.read_excel(path+'\\obsDataMaize_DB.xlsx',sheetname='phenologySimple_RainS')\n",
    "RainShelterSen = pd.read_excel(path+'\\LeafSenesCount.xlsx',sheetname='EndSenes')\n",
    "RainShelterSenStart = pd.read_excel(path+'\\LeafSenesCount.xlsx',sheetname='StartSenes')\n",
    "RainShelterLeafSize = pd.read_excel(path+'\\IndLeafData_MaizeRainshelter.xlsx',sheetname='MaxLeafSize')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "AllDataTabs = [Observed, ObservedTimeSeries, NZObserved, RainShelterN, RainShelterSW,\n",
    "              RainShelterCob, PhenologySimple,RainShelterSen, RainShelterSenStart, RainShelterLeafSize]\n",
    "               "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make a column index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ColumnIndex = Observed.columns\n",
    "for Table in AllDataTabs:\n",
    "    ColumnIndex = ColumnIndex  | Table.columns  # the union of the merged data frame and the next in the list\n",
    "ColumnIndex = ColumnIndex.drop(['SimulationName','Clock.Today'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make row index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "RowIndexData = Observed.ix[:,Observed.columns.isin(['SimulationName','Clock.Today'])]\n",
    "for Table in AllDataTabs:\n",
    "    RowIndexData = pd.concat([RowIndexData,Table.ix[:,Table.columns.isin(['SimulationName','Clock.Today'])]],axis=0)\n",
    "RowIndex = RowIndexData.drop_duplicates()\n",
    "RowIndex = RowIndex.dropna()\n",
    "RowIndex.ix[:,'Clock.Today'] = pd.to_datetime(RowIndex.ix[:,'Clock.Today'])\n",
    "RowIndex.set_index(['SimulationName','Clock.Today'],inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make empty Data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ObsDataFrame = pd.DataFrame(index=RowIndex.index, columns=ColumnIndex)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set indexes in the smaller tables and deal to duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "def MakeMean(ser):\n",
    "    Series = pd.Series(index=ser.columns)\n",
    "    for Group in ser.columns:\n",
    "        data = ser[Group].dropna()\n",
    "        if len(data) > 0:\n",
    "            if isinstance(data.values[0], float):\n",
    "                Series[Group] = data.mean()\n",
    "            else:\n",
    "                if isinstance(data.values[0], np.int64):\n",
    "                    Series[Group] = np.round(data.mean(),0)\n",
    "                else:\n",
    "                    Series[Group] = Counter(data).most_common(1)[0][0]\n",
    "    return Series    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "CompressedData = []\n",
    "for Table in AllDataTabs:\n",
    "    Table.ix[:,'Clock.Today'] = pd.to_datetime(Table.ix[:,'Clock.Today'])\n",
    "    Table.set_index(['SimulationName','Clock.Today'],inplace=True)\n",
    "    Compressed = Table.groupby(level=['SimulationName','Clock.Today']).apply(MakeMean)\n",
    "    CompressedData.append(Compressed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for DataTable in range(len(CompressedData)):\n",
    "    ObsDataFrame.update(CompressedData[DataTable])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ObsDataFrame.where(ObsDataFrame!='*',other=np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ObsDataFrame.to_excel(path + '\\MaizeObservations.xlsx',merge_cells=False,sheet_name='Observed')"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}