CMIP6_pivotTables.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_478361/1529845526.py:9: FutureWarning: Passing a negative integer is deprecated in version 1.0 and will not be supported in future version. Instead, use None to not limit the column width.\n",
      "  pd.set_option('display.max_colwidth', -1)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import gcsfs\n",
    "import sys\n",
    "import xarray as xr\n",
    "pd.set_option('display.max_rows', None)\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.width', None)\n",
    "pd.set_option('display.max_colwidth', -1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('https://cmip6.storage.googleapis.com/cmip6-zarr-consolidated-stores.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define a simple search on keywords\n",
    "def search_df(df, verbose= False, **search):\n",
    "    \"search by keywords - if list, then match exactly, otherwise match as substring\"\n",
    "    keys = ['activity_id','institution_id','source_id','experiment_id','member_id', 'table_id', 'variable_id', 'grid_label']\n",
    "    d = df\n",
    "    for skey in search.keys():\n",
    "        \n",
    "        if isinstance(search[skey], str):  # match a string as a substring\n",
    "            d = d[d[skey].str.contains(search[skey])]\n",
    "        else:\n",
    "            dk = []\n",
    "            for key in search[skey]:       # match a list of strings exactly\n",
    "                dk += [d[d[skey]==key]]\n",
    "            d = pd.concat(dk)\n",
    "            keys.remove(skey)\n",
    "    if verbose:\n",
    "        for key in keys:\n",
    "            print(key,' = ',list(d[key].unique()))      \n",
    "    return d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dfp = search_df(df, experiment_id=['historical','ssp585'], table_id=['Omon','Amon'], variable_id=[\"uas\",\"vas\",\"tos\"], grid_label=['gr'])\n",
    "dfp = search_df(df,\n",
    "                experiment_id=[\"historical\",\"ssp245\", \"ssp585\"],\n",
    "                table_id=[\"Amon\",\"Amon\", \"Amon\", \"Amon\", \"Omon\", \"SImon\", \"SImon\", \"SImon\", \"SImon\",\"Amon\",\"Omon\",\"Amon\",\"Amon\",\"Amon\"],\n",
    "                variable_id=[\"prw\",\"clt\", \"uas\", \"vas\", \"chl\", \"sithick\", \"siconc\", \"sisnthick\", \"sisnconc\", \"tas\",\"tos\",\"rsus\",\"rsds\",\"ps\"],\n",
    "                grid_label=['gn'])\n",
    "#dfp = search_df(df, experiment_id=['piControl','historical','ssp126','ssp245','ssp370'],table_id=['Omon'], variable_id=[\"thetao\"], grid_label=['gn'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "dm = dfp[[\"experiment_id\",\"source_id\",\"variable_id\",\"member_id\",\"grid_label\"]].groupby([\"member_id\",\"source_id\",\"experiment_id\",\"grid_label\"]).nunique()[[\"variable_id\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>grid_label</th>\n",
       "      <th colspan=\"3\" halign=\"left\">gn</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>experiment_id</th>\n",
       "      <th>historical</th>\n",
       "      <th>ssp245</th>\n",
       "      <th>ssp585</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>source_id</th>\n",
       "      <th>member_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">CanESM5</th>\n",
       "      <th>r10i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r1i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9i1p2f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">MPI-ESM1-2-HR</th>\n",
       "      <th>r1i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">MPI-ESM1-2-LR</th>\n",
       "      <th>r10i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r1i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r5i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r6i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r7i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r9i1p1f1</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">UKESM1-0-LL</th>\n",
       "      <th>r1i1p1f2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r2i1p1f2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r3i1p1f2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r4i1p1f2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>r8i1p1f2</th>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "grid_label                      gn              \n",
       "experiment_id           historical ssp245 ssp585\n",
       "source_id     member_id                         \n",
       "CanESM5       r10i1p2f1  14         14     14   \n",
       "              r1i1p2f1   14         14     14   \n",
       "              r2i1p2f1   14         14     14   \n",
       "              r3i1p2f1   14         14     14   \n",
       "              r4i1p2f1   14         14     14   \n",
       "              r5i1p2f1   14         14     14   \n",
       "              r6i1p2f1   14         14     14   \n",
       "              r7i1p2f1   14         14     14   \n",
       "              r8i1p2f1   14         14     14   \n",
       "              r9i1p2f1   14         14     14   \n",
       "MPI-ESM1-2-HR r1i1p1f1   14         14     14   \n",
       "              r2i1p1f1   14         14     14   \n",
       "MPI-ESM1-2-LR r10i1p1f1  14         14     14   \n",
       "              r1i1p1f1   14         14     14   \n",
       "              r2i1p1f1   14         14     14   \n",
       "              r3i1p1f1   14         14     14   \n",
       "              r4i1p1f1   14         14     14   \n",
       "              r5i1p1f1   14         14     14   \n",
       "              r6i1p1f1   14         14     14   \n",
       "              r7i1p1f1   14         14     14   \n",
       "              r8i1p1f1   14         14     14   \n",
       "              r9i1p1f1   14         14     14   \n",
       "UKESM1-0-LL   r1i1p1f2   14         14     14   \n",
       "              r2i1p1f2   14         14     14   \n",
       "              r3i1p1f2   14         14     14   \n",
       "              r4i1p1f2   14         14     14   \n",
       "              r8i1p1f2   14         14     14   "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = pd.DataFrame.pivot_table(dm, values=\"variable_id\", index=[\"source_id\",\"member_id\"],\n",
    "                                 columns=[\"grid_label\",\"experiment_id\"], aggfunc=np.sum, fill_value=0)\n",
    "table[(table.values.sum(axis=1)==14*3)]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}