datapackage_tables_2024prop.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sys\n",
    "sys.path.append('/home/greg/GitHub/')\n",
    "import pyEDIutils.search as edi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get table from EDI\n",
    "\n",
    "This lists all data packages at EDI under the knb-lter-jrn scope."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://pasta.lternet.edu/package/search/eml?defType=edismax&q=%2A&fq=scope%3Aknb-lter-jrn&fl=packageid%2Ctitle%2Cpubdate%2Ckeyword%2Cauthor%2Cbegindate%2Cenddate%2Cdoi&sort=packageid%2Casc&rows=500\n"
     ]
    },
    {
     "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>packageid</th>\n",
       "      <th>title</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>keywords</th>\n",
       "      <th>authors</th>\n",
       "      <th>begindate</th>\n",
       "      <th>enddate</th>\n",
       "      <th>doi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>knb-lter-jrn.100.3</td>\n",
       "      <td>High resolution shrub cover raster maps of the...</td>\n",
       "      <td>2020</td>\n",
       "      <td>canopy cover;land surface properties;plant cov...</td>\n",
       "      <td>Ji, Wenjie;Hanan, Niall P.</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>2011-12-31</td>\n",
       "      <td>doi:10.6073/pasta/313fec8669bc7b4d8debf7393dd2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>knb-lter-jrn.101.1</td>\n",
       "      <td>Thresholds and alternative states in neotropic...</td>\n",
       "      <td>2021</td>\n",
       "      <td>Disturbance;Vegetation Dynamics;plant communit...</td>\n",
       "      <td>Peinetti, H. Raúl;Bestelmeyer, Brandon;Chirino...</td>\n",
       "      <td>2005-09-01</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>doi:10.6073/pasta/e1978467bd09fc4f6e0cd8509b06...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>knb-lter-jrn.102.1</td>\n",
       "      <td>Carbon exchange responses of rehydrated and in...</td>\n",
       "      <td>2023</td>\n",
       "      <td>populations;algae;bacterial abundance;carbon a...</td>\n",
       "      <td>Hoellrich, Mikaela;Pietrasiak, Nicole</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>doi:10.6073/pasta/c6ffd88dc80df1ed1ec32ccdc477...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>knb-lter-jrn.103.1</td>\n",
       "      <td>Gridded 1-hectare estimates of shrub community...</td>\n",
       "      <td>2023</td>\n",
       "      <td>JRN LTER;Jornada Basin;LiDAR;aerial imagery;pl...</td>\n",
       "      <td>Roberts, Trevor H;Hanan, Niall P</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>doi:10.6073/pasta/49bec3ac2917e494c5de4b19130e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>knb-lter-jrn.200021001.5</td>\n",
       "      <td>Data to explore circular manureshed management...</td>\n",
       "      <td>2023</td>\n",
       "      <td>crop-livestock integration;manureshed;LTAR;ani...</td>\n",
       "      <td>Spiegal, Sheri;Vendramini, João M.B.;Bittman, ...</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>doi:10.6073/pasta/6025924b89e313f25bddf1912c8a...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  packageid  \\\n",
       "0        knb-lter-jrn.100.3   \n",
       "1        knb-lter-jrn.101.1   \n",
       "2        knb-lter-jrn.102.1   \n",
       "3        knb-lter-jrn.103.1   \n",
       "4  knb-lter-jrn.200021001.5   \n",
       "\n",
       "                                               title pubdate  \\\n",
       "0  High resolution shrub cover raster maps of the...    2020   \n",
       "1  Thresholds and alternative states in neotropic...    2021   \n",
       "2  Carbon exchange responses of rehydrated and in...    2023   \n",
       "3  Gridded 1-hectare estimates of shrub community...    2023   \n",
       "4  Data to explore circular manureshed management...    2023   \n",
       "\n",
       "                                            keywords  \\\n",
       "0  canopy cover;land surface properties;plant cov...   \n",
       "1  Disturbance;Vegetation Dynamics;plant communit...   \n",
       "2  populations;algae;bacterial abundance;carbon a...   \n",
       "3  JRN LTER;Jornada Basin;LiDAR;aerial imagery;pl...   \n",
       "4  crop-livestock integration;manureshed;LTAR;ani...   \n",
       "\n",
       "                                             authors   begindate     enddate  \\\n",
       "0                         Ji, Wenjie;Hanan, Niall P.  2011-01-01  2011-12-31   \n",
       "1  Peinetti, H. Raúl;Bestelmeyer, Brandon;Chirino...  2005-09-01  2018-03-31   \n",
       "2              Hoellrich, Mikaela;Pietrasiak, Nicole  2020-01-01  2022-12-31   \n",
       "3                   Roberts, Trevor H;Hanan, Niall P  2011-01-01  2019-12-31   \n",
       "4  Spiegal, Sheri;Vendramini, João M.B.;Bittman, ...  2010-01-01  2019-12-31   \n",
       "\n",
       "                                                 doi  \n",
       "0  doi:10.6073/pasta/313fec8669bc7b4d8debf7393dd2...  \n",
       "1  doi:10.6073/pasta/e1978467bd09fc4f6e0cd8509b06...  \n",
       "2  doi:10.6073/pasta/c6ffd88dc80df1ed1ec32ccdc477...  \n",
       "3  doi:10.6073/pasta/49bec3ac2917e494c5de4b19130e...  \n",
       "4  doi:10.6073/pasta/6025924b89e313f25bddf1912c8a...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = edi.search_pasta(fields=['packageid','title','pubdate','keyword','author',\n",
    "                                   'begindate','enddate','doi'])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>packageid</th>\n",
       "      <th>title</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>keywords</th>\n",
       "      <th>authors</th>\n",
       "      <th>begindate</th>\n",
       "      <th>enddate</th>\n",
       "      <th>doi</th>\n",
       "      <th>pkgid</th>\n",
       "      <th>seriesid</th>\n",
       "      <th>studyid</th>\n",
       "      <th>studyid2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>knb-lter-jrn.100.3</td>\n",
       "      <td>High resolution shrub cover raster maps of the...</td>\n",
       "      <td>2020</td>\n",
       "      <td>canopy cover;land surface properties;plant cov...</td>\n",
       "      <td>Ji, Wenjie;Hanan, Niall P.</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>2011-12-31</td>\n",
       "      <td>doi:10.6073/pasta/313fec8669bc7b4d8debf7393dd2...</td>\n",
       "      <td>100</td>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>knb-lter-jrn.101.1</td>\n",
       "      <td>Thresholds and alternative states in neotropic...</td>\n",
       "      <td>2021</td>\n",
       "      <td>Disturbance;Vegetation Dynamics;plant communit...</td>\n",
       "      <td>Peinetti, H. Raúl;Bestelmeyer, Brandon;Chirino...</td>\n",
       "      <td>2005-09-01</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>doi:10.6073/pasta/e1978467bd09fc4f6e0cd8509b06...</td>\n",
       "      <td>101</td>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>knb-lter-jrn.102.1</td>\n",
       "      <td>Carbon exchange responses of rehydrated and in...</td>\n",
       "      <td>2023</td>\n",
       "      <td>populations;algae;bacterial abundance;carbon a...</td>\n",
       "      <td>Hoellrich, Mikaela;Pietrasiak, Nicole</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>doi:10.6073/pasta/c6ffd88dc80df1ed1ec32ccdc477...</td>\n",
       "      <td>102</td>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>knb-lter-jrn.103.1</td>\n",
       "      <td>Gridded 1-hectare estimates of shrub community...</td>\n",
       "      <td>2023</td>\n",
       "      <td>JRN LTER;Jornada Basin;LiDAR;aerial imagery;pl...</td>\n",
       "      <td>Roberts, Trevor H;Hanan, Niall P</td>\n",
       "      <td>2011-01-01</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>doi:10.6073/pasta/49bec3ac2917e494c5de4b19130e...</td>\n",
       "      <td>103</td>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>knb-lter-jrn.200021001.5</td>\n",
       "      <td>Data to explore circular manureshed management...</td>\n",
       "      <td>2023</td>\n",
       "      <td>crop-livestock integration;manureshed;LTAR;ani...</td>\n",
       "      <td>Spiegal, Sheri;Vendramini, João M.B.;Bittman, ...</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>2019-12-31</td>\n",
       "      <td>doi:10.6073/pasta/6025924b89e313f25bddf1912c8a...</td>\n",
       "      <td>200021001</td>\n",
       "      <td>jer</td>\n",
       "      <td>021</td>\n",
       "      <td>jer021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  packageid  \\\n",
       "0        knb-lter-jrn.100.3   \n",
       "1        knb-lter-jrn.101.1   \n",
       "2        knb-lter-jrn.102.1   \n",
       "3        knb-lter-jrn.103.1   \n",
       "4  knb-lter-jrn.200021001.5   \n",
       "\n",
       "                                               title pubdate  \\\n",
       "0  High resolution shrub cover raster maps of the...    2020   \n",
       "1  Thresholds and alternative states in neotropic...    2021   \n",
       "2  Carbon exchange responses of rehydrated and in...    2023   \n",
       "3  Gridded 1-hectare estimates of shrub community...    2023   \n",
       "4  Data to explore circular manureshed management...    2023   \n",
       "\n",
       "                                            keywords  \\\n",
       "0  canopy cover;land surface properties;plant cov...   \n",
       "1  Disturbance;Vegetation Dynamics;plant communit...   \n",
       "2  populations;algae;bacterial abundance;carbon a...   \n",
       "3  JRN LTER;Jornada Basin;LiDAR;aerial imagery;pl...   \n",
       "4  crop-livestock integration;manureshed;LTAR;ani...   \n",
       "\n",
       "                                             authors   begindate     enddate  \\\n",
       "0                         Ji, Wenjie;Hanan, Niall P.  2011-01-01  2011-12-31   \n",
       "1  Peinetti, H. Raúl;Bestelmeyer, Brandon;Chirino...  2005-09-01  2018-03-31   \n",
       "2              Hoellrich, Mikaela;Pietrasiak, Nicole  2020-01-01  2022-12-31   \n",
       "3                   Roberts, Trevor H;Hanan, Niall P  2011-01-01  2019-12-31   \n",
       "4  Spiegal, Sheri;Vendramini, João M.B.;Bittman, ...  2010-01-01  2019-12-31   \n",
       "\n",
       "                                                 doi      pkgid seriesid  \\\n",
       "0  doi:10.6073/pasta/313fec8669bc7b4d8debf7393dd2...        100    other   \n",
       "1  doi:10.6073/pasta/e1978467bd09fc4f6e0cd8509b06...        101    other   \n",
       "2  doi:10.6073/pasta/c6ffd88dc80df1ed1ec32ccdc477...        102    other   \n",
       "3  doi:10.6073/pasta/49bec3ac2917e494c5de4b19130e...        103    other   \n",
       "4  doi:10.6073/pasta/6025924b89e313f25bddf1912c8a...  200021001      jer   \n",
       "\n",
       "  studyid studyid2  \n",
       "0       1   other1  \n",
       "1       1   other1  \n",
       "2       1   other1  \n",
       "3       1   other1  \n",
       "4     021   jer021  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract package ID\n",
    "pkgid = df.packageid.str.split('.', expand=True).loc[:,1]\n",
    "df['pkgid'] = pkgid.astype(str)\n",
    "# Series ids (prj, jer, other)\n",
    "df['seriesid'] = 'other'\n",
    "df.loc[pkgid.str[-9:-6]=='200', 'seriesid'] = 'jer'\n",
    "df.loc[pkgid.str[-9:-6]=='210', 'seriesid'] = 'prj'\n",
    "# Study IDs\n",
    "df['studyid'] = pkgid.astype(str).str[0:-2]\n",
    "df.loc[pkgid.astype(int) > 200000000, 'studyid'] = pkgid.loc[pkgid.astype(int) > 200000000].str[-6:-3]\n",
    "df['studyid2'] = df['seriesid'] + df['studyid']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read local status of packages\n",
    "\n",
    "This table is where we assign core, signature, local keywords, and other categorizations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "388\n",
      "211\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 388 entries, 0 to 387\n",
      "Data columns (total 7 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   seriesid  388 non-null    object\n",
      " 1   studyid   388 non-null    object\n",
      " 2   studyid2  388 non-null    object\n",
      " 3   pkgid     388 non-null    object\n",
      " 4   pubdate   388 non-null    object\n",
      " 5   title     388 non-null    object\n",
      " 6   authors   388 non-null    object\n",
      "dtypes: object(7)\n",
      "memory usage: 21.3+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 211 entries, 0 to 210\n",
      "Data columns (total 10 columns):\n",
      " #   Column             Non-Null Count  Dtype  \n",
      "---  ------             --------------  -----  \n",
      " 0   studyid            211 non-null    object \n",
      " 1   pkgid              211 non-null    int64  \n",
      " 2   pubdate            211 non-null    int64  \n",
      " 3   title              211 non-null    object \n",
      " 4   authors            211 non-null    object \n",
      " 5   old_core           61 non-null     object \n",
      " 6   core_sig_2020prop  168 non-null    object \n",
      " 7   top10_2020prop     18 non-null     float64\n",
      " 8   top10doi_2020prop  18 non-null     object \n",
      " 9   corearea           211 non-null    object \n",
      "dtypes: float64(1), int64(2), object(7)\n",
      "memory usage: 16.6+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "localstatus = df.loc[:, ['seriesid', 'studyid', 'studyid2', 'pkgid', 'pubdate', 'title', 'authors']]\n",
    "dtype_dic= { 'studyid': str }\n",
    "#localstatus = pd.read_csv('edi_package_localstatus.csv', converters={'studyid': lambda x: str(x)})\n",
    "old_localstatus = pd.read_csv('edi_package_localstatus_jrn7.csv', dtype = dtype_dic)\n",
    "print(len(localstatus))\n",
    "print(len(old_localstatus))\n",
    "print(localstatus.info())\n",
    "print(old_localstatus.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Make changes if needed and resave\n",
    "\n",
    "Add keywords, alter core/signature, etc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['1' '021' '022' '029' '030' '034' '043' '045' '046' '047' '048' '2' '001'\n",
      " '002' '007' '008' '009' '010' '011' '012' '013' '015' '086' '119' '120'\n",
      " '121' '122' '126' '127' '128' '228' '259' '262' '278' '287' '288' '301'\n",
      " '308' '313' '327' '328' '338' '349' '351' '365' '368' '369' '370' '379'\n",
      " '380' '385' '386' '389' '392' '394' '395' '398' '407' '408' '409' '412'\n",
      " '413' '420' '425' '437' '444' '461' '465' '469' '472' '493' '500' '520'\n",
      " '527' '539' '543' '548' '574' '586']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(localstatus.studyid.unique())\n",
    "sum(localstatus.studyid.isna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Assign core areas by study id\n",
    "localstatus['corearea'] = 'None'\n",
    "lulc = localstatus.studyid2.isin(['other1','prj119','prj120','prj392','prj413','prj472', 'prj543',\n",
    "    'jer021','jer022','jer029','jer030','jer034','jer043','jer045','jer046','jer047','jer048'])\n",
    "localstatus.loc[lulc, 'corearea'] = 'Land Use and Land Cover Change'\n",
    "\n",
    "inorganic = localstatus.studyid2.isin(['prj001','prj012','prj013','prj015','prj128',\n",
    "    'prj228','prj288','prj338','prj365','prj368','prj386','prj394','prj395', 'prj398','prj586'])\n",
    "localstatus.loc[inorganic, 'corearea'] = 'Inorganic matter cycling'\n",
    "\n",
    "organic = localstatus.studyid2.isin(['prj301','prj327','prj389'])\n",
    "localstatus.loc[organic, 'corearea'] = 'Organic matter cycling'\n",
    "\n",
    "pops = localstatus.studyid2.isin(['prj007','prj008', 'prj009', 'prj010','prj351','prj370',\n",
    "    'prj369','prj385','prj412','prj444','prj469','prj493','prj500','prj520'])\n",
    "localstatus.loc[pops, 'corearea'] = 'Populations'\n",
    "\n",
    "disturbance = localstatus.studyid2.isin(['prj002','prj122','prj126','prj127', 'prj259',\n",
    "    'prj278','prj308','prj313','prj328','prj379','prj380','prj407','prj425','prj437','prj461',\n",
    "    'prj465','prj527','prj539','prj548','other2'])\n",
    "localstatus.loc[disturbance, 'corearea'] = 'Disturbance'\n",
    "\n",
    "prod = localstatus.studyid2.isin(['prj011','prj287','prj408','prj409','prj574'])\n",
    "localstatus.loc[prod, 'corearea'] = 'Primary production'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign core areas by pkgid (studies 015, 086, 121, 262)\n",
    "#lulc = localstatus.pkgid.isna()\n",
    "#localstatus.loc[lulc, 'corearea'] = 'Land Use and Land Cover Change'\n",
    "\n",
    "inorganic = localstatus.pkgid.isin(['210015002','210015003', '210420002'])\n",
    "localstatus.loc[inorganic, 'corearea'] = 'Inorganic matter cycling'\n",
    "\n",
    "organic = localstatus.pkgid.isin(['210086004', '210420001'])\n",
    "localstatus.loc[organic, 'corearea'] = 'Organic matter cycling'\n",
    "\n",
    "pops = localstatus.pkgid.isin(['210086001','210086002','210086003','210086005','210086006','210086008','210086009',\n",
    "'210121001','210121003', '210121004', '210121006','210262001','210262008','210262010'])\n",
    "localstatus.loc[pops, 'corearea'] = 'Populations'\n",
    "\n",
    "disturbance = localstatus.pkgid.isin(['210086007','210121005','210121008','210349002'])\n",
    "localstatus.loc[disturbance, 'corearea'] = 'Disturbance'\n",
    "\n",
    "prod = localstatus.pkgid.isin(['210015001','210262004','210262005','210349001', '210349003'])\n",
    "localstatus.loc[prod, 'corearea'] = 'Primary production'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign core/non-core\n",
    "localstatus['status'] = ''\n",
    "study_core = localstatus.studyid2.isin(['prj002','prj011','prj012','prj013','prj126','prj228','prj262','prj287','prj288',\n",
    "    'prj338','prj349','prj351','prj412','prj413','prj425','prj520','prj444','prj461','prj465','prj574','prj437','prj548',\n",
    "    'jer043','jer047','jer048'])\n",
    "localstatus.loc[study_core, 'status'] = 'core'\n",
    "pkg_core = localstatus.pkgid.isin(['210086001','210086002','210086006','210086009', '210121005','101','103','201','202'])\n",
    "localstatus.loc[pkg_core, 'status'] = 'core'\n",
    "\n",
    "localstatus['top10'] = ''\n",
    "top10ds = localstatus.pkgid.isin(['210351001','210351004','210379001','210574001','210574002', '210126001'\n",
    "'210539001','210259001','210288001','210308005','210308004','210262005','210262004','210262008','210338006',\n",
    "'210338002','100'])\n",
    "localstatus.loc[top10ds, 'top10'] = 'Y'\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seriesid</th>\n",
       "      <th>studyid</th>\n",
       "      <th>studyid2</th>\n",
       "      <th>pkgid</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>corearea</th>\n",
       "      <th>status</th>\n",
       "      <th>top10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "      <td>100</td>\n",
       "      <td>2020</td>\n",
       "      <td>High resolution shrub cover raster maps of the...</td>\n",
       "      <td>Ji, Wenjie;Hanan, Niall P.</td>\n",
       "      <td>Land Use and Land Cover Change</td>\n",
       "      <td></td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "      <td>101</td>\n",
       "      <td>2021</td>\n",
       "      <td>Thresholds and alternative states in neotropic...</td>\n",
       "      <td>Peinetti, H. Raúl;Bestelmeyer, Brandon;Chirino...</td>\n",
       "      <td>Land Use and Land Cover Change</td>\n",
       "      <td>core</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "      <td>102</td>\n",
       "      <td>2023</td>\n",
       "      <td>Carbon exchange responses of rehydrated and in...</td>\n",
       "      <td>Hoellrich, Mikaela;Pietrasiak, Nicole</td>\n",
       "      <td>Land Use and Land Cover Change</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>other</td>\n",
       "      <td>1</td>\n",
       "      <td>other1</td>\n",
       "      <td>103</td>\n",
       "      <td>2023</td>\n",
       "      <td>Gridded 1-hectare estimates of shrub community...</td>\n",
       "      <td>Roberts, Trevor H;Hanan, Niall P</td>\n",
       "      <td>Land Use and Land Cover Change</td>\n",
       "      <td>core</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>jer</td>\n",
       "      <td>021</td>\n",
       "      <td>jer021</td>\n",
       "      <td>200021001</td>\n",
       "      <td>2023</td>\n",
       "      <td>Data to explore circular manureshed management...</td>\n",
       "      <td>Spiegal, Sheri;Vendramini, João M.B.;Bittman, ...</td>\n",
       "      <td>Land Use and Land Cover Change</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  seriesid studyid studyid2      pkgid pubdate  \\\n",
       "0    other       1   other1        100    2020   \n",
       "1    other       1   other1        101    2021   \n",
       "2    other       1   other1        102    2023   \n",
       "3    other       1   other1        103    2023   \n",
       "4      jer     021   jer021  200021001    2023   \n",
       "\n",
       "                                               title  \\\n",
       "0  High resolution shrub cover raster maps of the...   \n",
       "1  Thresholds and alternative states in neotropic...   \n",
       "2  Carbon exchange responses of rehydrated and in...   \n",
       "3  Gridded 1-hectare estimates of shrub community...   \n",
       "4  Data to explore circular manureshed management...   \n",
       "\n",
       "                                             authors  \\\n",
       "0                         Ji, Wenjie;Hanan, Niall P.   \n",
       "1  Peinetti, H. Raúl;Bestelmeyer, Brandon;Chirino...   \n",
       "2              Hoellrich, Mikaela;Pietrasiak, Nicole   \n",
       "3                   Roberts, Trevor H;Hanan, Niall P   \n",
       "4  Spiegal, Sheri;Vendramini, João M.B.;Bittman, ...   \n",
       "\n",
       "                         corearea status top10  \n",
       "0  Land Use and Land Cover Change            Y  \n",
       "1  Land Use and Land Cover Change   core        \n",
       "2  Land Use and Land Cover Change               \n",
       "3  Land Use and Land Cover Change   core        \n",
       "4  Land Use and Land Cover Change               "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "localstatus.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seriesid</th>\n",
       "      <th>studyid</th>\n",
       "      <th>studyid2</th>\n",
       "      <th>pkgid</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>corearea</th>\n",
       "      <th>status</th>\n",
       "      <th>top10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [seriesid, studyid, studyid2, pkgid, pubdate, title, authors, corearea, status, top10]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "localstatus.loc[localstatus.corearea=='None',:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "localstatus_out = localstatus.copy()\n",
    "localstatus_out.to_csv('edi_package_localstatus_jrn8.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make Table1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "388\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_374715/2683925567.py:23: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.corearea_sort.loc[t1.corearea=='Pp'] = 1\n",
      "/tmp/ipykernel_374715/2683925567.py:24: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.corearea_sort.loc[t1.corearea=='Om'] = 2\n",
      "/tmp/ipykernel_374715/2683925567.py:25: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.corearea_sort.loc[t1.corearea=='D'] = 3\n",
      "/tmp/ipykernel_374715/2683925567.py:26: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.corearea_sort.loc[t1.corearea=='Im'] = 4\n",
      "/tmp/ipykernel_374715/2683925567.py:27: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.corearea_sort.loc[t1.corearea=='Po'] = 5\n",
      "/tmp/ipykernel_374715/2683925567.py:28: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.corearea_sort.loc[t1.corearea=='Lu'] = 6\n"
     ]
    },
    {
     "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 th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>packageid</th>\n",
       "      <th>title</th>\n",
       "      <th>pubdate</th>\n",
       "      <th>keywords</th>\n",
       "      <th>authors</th>\n",
       "      <th>begindate</th>\n",
       "      <th>enddate</th>\n",
       "      <th>doi</th>\n",
       "      <th>pkgid</th>\n",
       "      <th>seriesid</th>\n",
       "      <th>studyid</th>\n",
       "      <th>studyid2</th>\n",
       "      <th>status</th>\n",
       "      <th>corearea</th>\n",
       "      <th>top10</th>\n",
       "      <th>drange</th>\n",
       "      <th>corearea_sort</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pkgid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>210011001</th>\n",
       "      <td>knb-lter-jrn.210011001</td>\n",
       "      <td>Seasonal aboveground plant biomass estimates a...</td>\n",
       "      <td>2023</td>\n",
       "      <td>populations;primary production;aboveground pro...</td>\n",
       "      <td>Peters, Debra C; Huenneke, Laura F</td>\n",
       "      <td>1989-05-01</td>\n",
       "      <td>2022-10-31</td>\n",
       "      <td>doi:10.6073/pasta/5507ade25993c2123e1886a545b5...</td>\n",
       "      <td>210011001</td>\n",
       "      <td>prj</td>\n",
       "      <td>011</td>\n",
       "      <td>prj011</td>\n",
       "      <td>core</td>\n",
       "      <td>Pp</td>\n",
       "      <td></td>\n",
       "      <td>1989-05-01 to 2022-10-31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210011002</th>\n",
       "      <td>knb-lter-jrn.210011002</td>\n",
       "      <td>Seasonal non-destructive vegetation measuremen...</td>\n",
       "      <td>2023</td>\n",
       "      <td>populations;primary production;aboveground pro...</td>\n",
       "      <td>Peters, Debra C; Huenneke, Laura F</td>\n",
       "      <td>1989-05-01</td>\n",
       "      <td>2022-10-31</td>\n",
       "      <td>doi:10.6073/pasta/4fb9b7b5ec3d0692b280bd74e8b0...</td>\n",
       "      <td>210011002</td>\n",
       "      <td>prj</td>\n",
       "      <td>011</td>\n",
       "      <td>prj011</td>\n",
       "      <td>core</td>\n",
       "      <td>Pp</td>\n",
       "      <td></td>\n",
       "      <td>1989-05-01 to 2022-10-31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210011003</th>\n",
       "      <td>knb-lter-jrn.210011003</td>\n",
       "      <td>Annual mean estimates of aboveground net prima...</td>\n",
       "      <td>2023</td>\n",
       "      <td>populations;primary production;aboveground pro...</td>\n",
       "      <td>Peters, Debra C; Huenneke, Laura F</td>\n",
       "      <td>1990-01-01</td>\n",
       "      <td>2022-12-31</td>\n",
       "      <td>doi:10.6073/pasta/70d2a2b922bd629b72cf7271bb77...</td>\n",
       "      <td>210011003</td>\n",
       "      <td>prj</td>\n",
       "      <td>011</td>\n",
       "      <td>prj011</td>\n",
       "      <td>core</td>\n",
       "      <td>Pp</td>\n",
       "      <td></td>\n",
       "      <td>1990-01-01 to 2022-12-31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210011004</th>\n",
       "      <td>knb-lter-jrn.210011004</td>\n",
       "      <td>Seasonal reference harvest measurements of veg...</td>\n",
       "      <td>2023</td>\n",
       "      <td>populations;primary production;aboveground pro...</td>\n",
       "      <td>Peters, Debra C; Huenneke, Laura F</td>\n",
       "      <td>1989-04-25</td>\n",
       "      <td>2023-05-15</td>\n",
       "      <td>doi:10.6073/pasta/f80e80f3501d000bf81c426362cb...</td>\n",
       "      <td>210011004</td>\n",
       "      <td>prj</td>\n",
       "      <td>011</td>\n",
       "      <td>prj011</td>\n",
       "      <td>core</td>\n",
       "      <td>Pp</td>\n",
       "      <td></td>\n",
       "      <td>1989-04-25 to 2023-05-15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210011005</th>\n",
       "      <td>knb-lter-jrn.210011005</td>\n",
       "      <td>Annual ground-based photographs taken at 15 ne...</td>\n",
       "      <td>2019</td>\n",
       "      <td>net primary production;aboveground production;...</td>\n",
       "      <td>Anderson, John</td>\n",
       "      <td>2003-10-14</td>\n",
       "      <td>2018-09-18</td>\n",
       "      <td>doi:10.6073/pasta/69ca7ac7254a2b236d3f44b21fa8...</td>\n",
       "      <td>210011005</td>\n",
       "      <td>prj</td>\n",
       "      <td>011</td>\n",
       "      <td>prj011</td>\n",
       "      <td>core</td>\n",
       "      <td>Pp</td>\n",
       "      <td></td>\n",
       "      <td>2003-10-14 to 2018-09-18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        packageid  \\\n",
       "pkgid                               \n",
       "210011001  knb-lter-jrn.210011001   \n",
       "210011002  knb-lter-jrn.210011002   \n",
       "210011003  knb-lter-jrn.210011003   \n",
       "210011004  knb-lter-jrn.210011004   \n",
       "210011005  knb-lter-jrn.210011005   \n",
       "\n",
       "                                                       title pubdate  \\\n",
       "pkgid                                                                  \n",
       "210011001  Seasonal aboveground plant biomass estimates a...    2023   \n",
       "210011002  Seasonal non-destructive vegetation measuremen...    2023   \n",
       "210011003  Annual mean estimates of aboveground net prima...    2023   \n",
       "210011004  Seasonal reference harvest measurements of veg...    2023   \n",
       "210011005  Annual ground-based photographs taken at 15 ne...    2019   \n",
       "\n",
       "                                                    keywords  \\\n",
       "pkgid                                                          \n",
       "210011001  populations;primary production;aboveground pro...   \n",
       "210011002  populations;primary production;aboveground pro...   \n",
       "210011003  populations;primary production;aboveground pro...   \n",
       "210011004  populations;primary production;aboveground pro...   \n",
       "210011005  net primary production;aboveground production;...   \n",
       "\n",
       "                                      authors   begindate     enddate  \\\n",
       "pkgid                                                                   \n",
       "210011001  Peters, Debra C; Huenneke, Laura F  1989-05-01  2022-10-31   \n",
       "210011002  Peters, Debra C; Huenneke, Laura F  1989-05-01  2022-10-31   \n",
       "210011003  Peters, Debra C; Huenneke, Laura F  1990-01-01  2022-12-31   \n",
       "210011004  Peters, Debra C; Huenneke, Laura F  1989-04-25  2023-05-15   \n",
       "210011005                      Anderson, John  2003-10-14  2018-09-18   \n",
       "\n",
       "                                                         doi      pkgid  \\\n",
       "pkgid                                                                     \n",
       "210011001  doi:10.6073/pasta/5507ade25993c2123e1886a545b5...  210011001   \n",
       "210011002  doi:10.6073/pasta/4fb9b7b5ec3d0692b280bd74e8b0...  210011002   \n",
       "210011003  doi:10.6073/pasta/70d2a2b922bd629b72cf7271bb77...  210011003   \n",
       "210011004  doi:10.6073/pasta/f80e80f3501d000bf81c426362cb...  210011004   \n",
       "210011005  doi:10.6073/pasta/69ca7ac7254a2b236d3f44b21fa8...  210011005   \n",
       "\n",
       "          seriesid studyid studyid2 status corearea top10  \\\n",
       "pkgid                                                       \n",
       "210011001      prj     011   prj011   core       Pp         \n",
       "210011002      prj     011   prj011   core       Pp         \n",
       "210011003      prj     011   prj011   core       Pp         \n",
       "210011004      prj     011   prj011   core       Pp         \n",
       "210011005      prj     011   prj011   core       Pp         \n",
       "\n",
       "                             drange  corearea_sort  \n",
       "pkgid                                               \n",
       "210011001  1989-05-01 to 2022-10-31              1  \n",
       "210011002  1989-05-01 to 2022-10-31              1  \n",
       "210011003  1990-01-01 to 2022-12-31              1  \n",
       "210011004  1989-04-25 to 2023-05-15              1  \n",
       "210011005  2003-10-14 to 2018-09-18              1  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge in core designations and rename them to \"signature\"\n",
    "t1 = pd.merge(df, localstatus.loc[:,['pkgid','status','corearea',\n",
    "                                     'top10']], on='pkgid')\n",
    "#t1.core_sig_2020prop.replace('core', 'signature',inplace=True)\n",
    "print(len(t1))\n",
    "\n",
    "# Replace or add whitespace for a couple columns\n",
    "t1.title = t1.title.str.replace('\\n +', ' ')\n",
    "t1.authors = t1.authors.str.replace(';', '; ')\n",
    "# Trim version # from package id\n",
    "t1.packageid = t1.packageid.map(lambda x: x.rsplit('.',1)[0])\n",
    "# Add a date range\n",
    "t1['drange'] = t1.begindate + ' to ' + t1.enddate\n",
    "# Replace core areas with abbreviations\n",
    "t1.corearea.replace('Disturbance', 'D', inplace=True)\n",
    "t1.corearea.replace('Land Use and Land Cover Change', 'Lu', inplace=True)\n",
    "t1.corearea.replace('Primary production', 'Pp', inplace=True)\n",
    "t1.corearea.replace('Populations', 'Po', inplace=True)\n",
    "t1.corearea.replace('Inorganic matter cycling', 'Im', inplace=True)\n",
    "t1.corearea.replace('Organic matter cycling', 'Om', inplace=True)\n",
    "# Add sorting order column for corearea\n",
    "t1['corearea_sort'] = 0\n",
    "t1.corearea_sort.loc[t1.corearea=='Pp'] = 1\n",
    "t1.corearea_sort.loc[t1.corearea=='Om'] = 2\n",
    "t1.corearea_sort.loc[t1.corearea=='D'] = 3\n",
    "t1.corearea_sort.loc[t1.corearea=='Im'] = 4\n",
    "t1.corearea_sort.loc[t1.corearea=='Po'] = 5\n",
    "t1.corearea_sort.loc[t1.corearea=='Lu'] = 6\n",
    "# Sort values\n",
    "t1 = t1.sort_values(['status', 'corearea_sort', 'pkgid'],\n",
    "                   ascending=[False, True, True])\n",
    "\n",
    "# Fill in NA values with empty string\n",
    "t1['status'].fillna('', inplace=True)\n",
    "t1['top10'].fillna('', inplace=True)\n",
    "\n",
    "t1.index = t1.loc[:,'pkgid']\n",
    "t1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Special cases (on Deb's orders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_217704/1044816083.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.authors.loc[t1.pkgid.isin([210011001, 210011002, 210011003, 210011004, 210287001])] = 'Peters, Debra; Huenneke, Laura'\n",
      "/tmp/ipykernel_217704/1044816083.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.authors.loc[t1.pkgid.isin([210002001])] = 'Peters, Debra; Anderson, John; Huenneke, Laura'\n",
      "/tmp/ipykernel_217704/1044816083.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.authors.loc[t1.pkgid.isin([210121005])] = 'Peters, Debra; Anderson, John; Rango, Al; Huenneke, Laura'\n",
      "/tmp/ipykernel_217704/1044816083.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.authors.loc[t1.pkgid.isin([210308005])] = 'Peters, Debra; Herrick, Jeff; Okin, Gregory S'\n",
      "/tmp/ipykernel_217704/1044816083.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.authors.loc[t1.pkgid.isin([210351003])] = 'Bestelmeyer, Brandon; Yao, Jin'\n",
      "/tmp/ipykernel_217704/1044816083.py:8: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  t1.title.loc[t1.pkgid.isin(multiyr)] = t1.title.loc[t1.pkgid.isin(multiyr)] + '*'\n"
     ]
    },
    {
     "ename": "KeyError",
     "evalue": "'[210011001, 210013002] not found in axis'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb Cell 19\u001b[0m line \u001b[0;36m1\n\u001b[1;32m      <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=7'>8</a>\u001b[0m t1\u001b[39m.\u001b[39mtitle\u001b[39m.\u001b[39mloc[t1\u001b[39m.\u001b[39mpkgid\u001b[39m.\u001b[39misin(multiyr)] \u001b[39m=\u001b[39m t1\u001b[39m.\u001b[39mtitle\u001b[39m.\u001b[39mloc[t1\u001b[39m.\u001b[39mpkgid\u001b[39m.\u001b[39misin(multiyr)] \u001b[39m+\u001b[39m \u001b[39m'\u001b[39m\u001b[39m*\u001b[39m\u001b[39m'\u001b[39m\n\u001b[1;32m      <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=8'>9</a>\u001b[0m \u001b[39m#complete = [210086001,210086003,210086008,210086009]\u001b[39;00m\n\u001b[1;32m     <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=9'>10</a>\u001b[0m \u001b[39m#t1.title.loc[t1.pkgid.isin(complete)] = t1.title.loc[t1.pkgid.isin(complete)] + u'\\u2020'\u001b[39;00m\n\u001b[1;32m     <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=10'>11</a>\u001b[0m \n\u001b[1;32m     <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=11'>12</a>\u001b[0m \u001b[39m# Just drop the most useful NPP package from the table (Sigh)\u001b[39;00m\n\u001b[1;32m     <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=12'>13</a>\u001b[0m \u001b[39m# and the deprecated soil water\u001b[39;00m\n\u001b[0;32m---> <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=13'>14</a>\u001b[0m t1 \u001b[39m=\u001b[39m t1\u001b[39m.\u001b[39;49mdrop([\u001b[39m210011001\u001b[39;49m,\u001b[39m210013002\u001b[39;49m])\n\u001b[1;32m     <a href='vscode-notebook-cell:/home/greg/GitHub/diagnostics/datapackage_tables_2024prop.ipynb#X23sZmlsZQ%3D%3D?line=14'>15</a>\u001b[0m t1\u001b[39m.\u001b[39mhead()\n",
      "File \u001b[0;32m~/data/miniconda3/envs/standard/lib/python3.10/site-packages/pandas/core/frame.py:5344\u001b[0m, in \u001b[0;36mDataFrame.drop\u001b[0;34m(self, labels, axis, index, columns, level, inplace, errors)\u001b[0m\n\u001b[1;32m   5196\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mdrop\u001b[39m(\n\u001b[1;32m   5197\u001b[0m     \u001b[39mself\u001b[39m,\n\u001b[1;32m   5198\u001b[0m     labels: IndexLabel \u001b[39m|\u001b[39m \u001b[39mNone\u001b[39;00m \u001b[39m=\u001b[39m \u001b[39mNone\u001b[39;00m,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   5205\u001b[0m     errors: IgnoreRaise \u001b[39m=\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mraise\u001b[39m\u001b[39m\"\u001b[39m,\n\u001b[1;32m   5206\u001b[0m ) \u001b[39m-\u001b[39m\u001b[39m>\u001b[39m DataFrame \u001b[39m|\u001b[39m \u001b[39mNone\u001b[39;00m:\n\u001b[1;32m   5207\u001b[0m \u001b[39m    \u001b[39m\u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m   5208\u001b[0m \u001b[39m    Drop specified labels from rows or columns.\u001b[39;00m\n\u001b[1;32m   5209\u001b[0m \n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m   5342\u001b[0m \u001b[39m            weight  1.0     0.8\u001b[39;00m\n\u001b[1;32m   5343\u001b[0m \u001b[39m    \"\"\"\u001b[39;00m\n\u001b[0;32m-> 5344\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39msuper\u001b[39;49m()\u001b[39m.\u001b[39;49mdrop(\n\u001b[1;32m   5345\u001b[0m         labels\u001b[39m=\u001b[39;49mlabels,\n\u001b[1;32m   5346\u001b[0m         axis\u001b[39m=\u001b[39;49maxis,\n\u001b[1;32m   5347\u001b[0m         index\u001b[39m=\u001b[39;49mindex,\n\u001b[1;32m   5348\u001b[0m         columns\u001b[39m=\u001b[39;49mcolumns,\n\u001b[1;32m   5349\u001b[0m         level\u001b[39m=\u001b[39;49mlevel,\n\u001b[1;32m   5350\u001b[0m         inplace\u001b[39m=\u001b[39;49minplace,\n\u001b[1;32m   5351\u001b[0m         errors\u001b[39m=\u001b[39;49merrors,\n\u001b[1;32m   5352\u001b[0m     )\n",
      "File \u001b[0;32m~/data/miniconda3/envs/standard/lib/python3.10/site-packages/pandas/core/generic.py:4711\u001b[0m, in \u001b[0;36mNDFrame.drop\u001b[0;34m(self, labels, axis, index, columns, level, inplace, errors)\u001b[0m\n\u001b[1;32m   4709\u001b[0m \u001b[39mfor\u001b[39;00m axis, labels \u001b[39min\u001b[39;00m axes\u001b[39m.\u001b[39mitems():\n\u001b[1;32m   4710\u001b[0m     \u001b[39mif\u001b[39;00m labels \u001b[39mis\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[0;32m-> 4711\u001b[0m         obj \u001b[39m=\u001b[39m obj\u001b[39m.\u001b[39;49m_drop_axis(labels, axis, level\u001b[39m=\u001b[39;49mlevel, errors\u001b[39m=\u001b[39;49merrors)\n\u001b[1;32m   4713\u001b[0m \u001b[39mif\u001b[39;00m inplace:\n\u001b[1;32m   4714\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_update_inplace(obj)\n",
      "File \u001b[0;32m~/data/miniconda3/envs/standard/lib/python3.10/site-packages/pandas/core/generic.py:4753\u001b[0m, in \u001b[0;36mNDFrame._drop_axis\u001b[0;34m(self, labels, axis, level, errors, only_slice)\u001b[0m\n\u001b[1;32m   4751\u001b[0m         new_axis \u001b[39m=\u001b[39m axis\u001b[39m.\u001b[39mdrop(labels, level\u001b[39m=\u001b[39mlevel, errors\u001b[39m=\u001b[39merrors)\n\u001b[1;32m   4752\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[0;32m-> 4753\u001b[0m         new_axis \u001b[39m=\u001b[39m axis\u001b[39m.\u001b[39;49mdrop(labels, errors\u001b[39m=\u001b[39;49merrors)\n\u001b[1;32m   4754\u001b[0m     indexer \u001b[39m=\u001b[39m axis\u001b[39m.\u001b[39mget_indexer(new_axis)\n\u001b[1;32m   4756\u001b[0m \u001b[39m# Case for non-unique axis\u001b[39;00m\n\u001b[1;32m   4757\u001b[0m \u001b[39melse\u001b[39;00m:\n",
      "File \u001b[0;32m~/data/miniconda3/envs/standard/lib/python3.10/site-packages/pandas/core/indexes/base.py:7000\u001b[0m, in \u001b[0;36mIndex.drop\u001b[0;34m(self, labels, errors)\u001b[0m\n\u001b[1;32m   6998\u001b[0m \u001b[39mif\u001b[39;00m mask\u001b[39m.\u001b[39many():\n\u001b[1;32m   6999\u001b[0m     \u001b[39mif\u001b[39;00m errors \u001b[39m!=\u001b[39m \u001b[39m\"\u001b[39m\u001b[39mignore\u001b[39m\u001b[39m\"\u001b[39m:\n\u001b[0;32m-> 7000\u001b[0m         \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m{\u001b[39;00mlabels[mask]\u001b[39m.\u001b[39mtolist()\u001b[39m}\u001b[39;00m\u001b[39m not found in axis\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[1;32m   7001\u001b[0m     indexer \u001b[39m=\u001b[39m indexer[\u001b[39m~\u001b[39mmask]\n\u001b[1;32m   7002\u001b[0m \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mdelete(indexer)\n",
      "\u001b[0;31mKeyError\u001b[0m: '[210011001, 210013002] not found in axis'"
     ]
    }
   ],
   "source": [
    "t1.authors.loc[t1.pkgid.isin([210011001, 210011002, 210011003, 210011004, 210287001])] = 'Peters, Debra; Huenneke, Laura'\n",
    "t1.authors.loc[t1.pkgid.isin([210002001])] = 'Peters, Debra; Anderson, John; Huenneke, Laura'\n",
    "t1.authors.loc[t1.pkgid.isin([210121005])] = 'Peters, Debra; Anderson, John; Rango, Al; Huenneke, Laura'\n",
    "t1.authors.loc[t1.pkgid.isin([210308005])] = 'Peters, Debra; Herrick, Jeff; Okin, Gregory S'\n",
    "t1.authors.loc[t1.pkgid.isin([210351003])] = 'Bestelmeyer, Brandon; Yao, Jin'\n",
    "# Append symbol to SMES and similar packages (multiyear=asterisk or complete=dagger)\n",
    "multiyr = [210086002,210086004,210086005,210086007,210120001,210120002]\n",
    "t1.title.loc[t1.pkgid.isin(multiyr)] = t1.title.loc[t1.pkgid.isin(multiyr)] + '*'\n",
    "#complete = [210086001,210086003,210086008,210086009]\n",
    "#t1.title.loc[t1.pkgid.isin(complete)] = t1.title.loc[t1.pkgid.isin(complete)] + u'\\u2020'\n",
    "\n",
    "# Just drop the most useful NPP package from the table (Sigh)\n",
    "# and the deprecated soil water\n",
    "t1 = t1.drop([210011001,210013002])\n",
    "t1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "t1_out = t1.loc[:, ['authors','pubdate','title','doi','corearea','drange',\n",
    "                    'status','top10']]\n",
    "t1_out.to_excel('tables_out/JRN8_SuppTable1_draft.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}