1.group_silage_herbage_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a52cf0df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import geopandas as gp\n",
    "import matplotlib.pyplot as plt\n",
    "import glob\n",
    "import folium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "b5c0c856",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data\\\\Field Survey Data_Format 1_Herbage Survey_2013-01-01_2013-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data\\\\Field Survey Data_Format 1_Herbage Survey_2018-01-01_2018-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data\\\\Field Survey Data_Format 1_Herbage Survey_2019-01-01_2019-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data\\\\Field Survey Data_Format 1_Herbage Survey_2020-01-01_2020-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data\\\\Field Survey Data_Format 1_Herbage Survey_2021-01-01_2021-12-31.csv']"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List all herbage data\n",
    "path_herbage = 'C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data'\n",
    "files_herbage = glob.glob(path_herbage + '/' + '*Herbage Survey*.csv')\n",
    "files_herbage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b521047a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2011-01-01_2011-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2012-01-01_2012-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2013-01-01_2013-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2014-01-01_2014-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2015-01-01_2015-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2016-01-01_2016-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2017-01-01_2017-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2018-01-01_2018-12-31.csv',\n",
       " 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data\\\\Field Survey Data_Format 1_Silage Cut Survey_2019-01-01_2019-12-31.csv']"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List all silage files\n",
    "path_silage = 'C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data'\n",
    "files_silage = glob.glob(path_silage + '/' + '*Silage*.csv')\n",
    "files_silage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "e57d6848",
   "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>Experiment_Id</th>\n",
       "      <th>SP_ID</th>\n",
       "      <th>sample_distance (m)</th>\n",
       "      <th>Field</th>\n",
       "      <th>Flume</th>\n",
       "      <th>Sample_date</th>\n",
       "      <th>Sample_time</th>\n",
       "      <th>comments</th>\n",
       "      <th>Start_Sample_Date</th>\n",
       "      <th>GPS_Sample_Nr</th>\n",
       "      <th>Easting</th>\n",
       "      <th>Northing</th>\n",
       "      <th>Silage Dry Matter Yield (kg/ha)</th>\n",
       "      <th>Silage Dry Matter Yield Quality</th>\n",
       "      <th>Silage Dry Matter Yield Quality Last Modified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>68</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Little Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>Little Pecketsford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>265701.2343</td>\n",
       "      <td>99027.88295</td>\n",
       "      <td>6220.02</td>\n",
       "      <td>Not set</td>\n",
       "      <td>04/01/2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:01:00</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>265621.3570</td>\n",
       "      <td>99162.64359</td>\n",
       "      <td>8335.66</td>\n",
       "      <td>Not set</td>\n",
       "      <td>04/01/2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:02:00</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>265637.3313</td>\n",
       "      <td>99038.38699</td>\n",
       "      <td>7116.73</td>\n",
       "      <td>Not set</td>\n",
       "      <td>04/01/2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:03:00</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>265595.7734</td>\n",
       "      <td>99118.05719</td>\n",
       "      <td>7003.35</td>\n",
       "      <td>Not set</td>\n",
       "      <td>04/01/2017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:04:00</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>265615.0239</td>\n",
       "      <td>99241.73070</td>\n",
       "      <td>8485.22</td>\n",
       "      <td>Not set</td>\n",
       "      <td>04/01/2017</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Experiment_Id  SP_ID  sample_distance (m)               Field Flume  \\\n",
       "0     NW558_SC1     68                  NaN  Little Pecketsford     1   \n",
       "1     NW558_SC1     19                  NaN         Pecketsford     1   \n",
       "2     NW558_SC1     19                  NaN         Pecketsford     1   \n",
       "3     NW558_SC1     19                  NaN         Pecketsford     1   \n",
       "4     NW558_SC1     19                  NaN         Pecketsford     1   \n",
       "\n",
       "  Sample_date Sample_time            comments  Start_Sample_Date  \\\n",
       "0  25/05/2011    12:00:00  Little Pecketsford                NaN   \n",
       "1  25/05/2011    12:01:00         Pecketsford                NaN   \n",
       "2  25/05/2011    12:02:00         Pecketsford                NaN   \n",
       "3  25/05/2011    12:03:00         Pecketsford                NaN   \n",
       "4  25/05/2011    12:04:00         Pecketsford                NaN   \n",
       "\n",
       "  GPS_Sample_Nr      Easting     Northing  Silage Dry Matter Yield (kg/ha)  \\\n",
       "0             1  265701.2343  99027.88295                          6220.02   \n",
       "1             2  265621.3570  99162.64359                          8335.66   \n",
       "2             3  265637.3313  99038.38699                          7116.73   \n",
       "3             4  265595.7734  99118.05719                          7003.35   \n",
       "4             5  265615.0239  99241.73070                          8485.22   \n",
       "\n",
       "  Silage Dry Matter Yield Quality  \\\n",
       "0                         Not set   \n",
       "1                         Not set   \n",
       "2                         Not set   \n",
       "3                         Not set   \n",
       "4                         Not set   \n",
       "\n",
       "  Silage Dry Matter Yield Quality Last Modified  \n",
       "0                                    04/01/2017  \n",
       "1                                    04/01/2017  \n",
       "2                                    04/01/2017  \n",
       "3                                    04/01/2017  \n",
       "4                                    04/01/2017  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join all silage datasets\n",
    "files_concat_silage = pd.concat([pd.read_csv(f) for f in files_silage])\n",
    "files_concat_silage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "ad47f521",
   "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>Experiment_Id</th>\n",
       "      <th>SP_ID</th>\n",
       "      <th>sample_distance (m)</th>\n",
       "      <th>Field</th>\n",
       "      <th>Flume</th>\n",
       "      <th>Sample_date</th>\n",
       "      <th>Sample_time</th>\n",
       "      <th>comments</th>\n",
       "      <th>Start_Sample_Date</th>\n",
       "      <th>GPS_Sample_Nr</th>\n",
       "      <th>...</th>\n",
       "      <th>Total_Bo_NRM1 Quality Last Modified</th>\n",
       "      <th>Total C (%w/w) Herb</th>\n",
       "      <th>Total_C_H_NRM1 Quality</th>\n",
       "      <th>Total_C_H_NRM1 Quality Last Modified</th>\n",
       "      <th>C:N ratio</th>\n",
       "      <th>C_N_ratio_NRM1 Quality</th>\n",
       "      <th>C_N_ratio_NRM1 Quality Last Modified</th>\n",
       "      <th>Dry Matter Content (%) in-house</th>\n",
       "      <th>DM_H_NW1 Quality</th>\n",
       "      <th>DM_H_NW1 Quality Last Modified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FP010</td>\n",
       "      <td>2</td>\n",
       "      <td>50.0</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>09/07/2013</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/07/2013</td>\n",
       "      <td>ht_2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FP010</td>\n",
       "      <td>7</td>\n",
       "      <td>50.0</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>09/07/2013</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/07/2013</td>\n",
       "      <td>ht_7</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FP010</td>\n",
       "      <td>9</td>\n",
       "      <td>50.0</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>09/07/2013</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/07/2013</td>\n",
       "      <td>ht_9</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FP010</td>\n",
       "      <td>17</td>\n",
       "      <td>50.0</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>09/07/2013</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/07/2013</td>\n",
       "      <td>ht_17</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FP010</td>\n",
       "      <td>19</td>\n",
       "      <td>50.0</td>\n",
       "      <td>Pecketsford</td>\n",
       "      <td>1</td>\n",
       "      <td>09/07/2013</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>02/07/2013</td>\n",
       "      <td>ht_19</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 69 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Experiment_Id  SP_ID  sample_distance (m)        Field Flume Sample_date  \\\n",
       "0         FP010      2                 50.0  Pecketsford     1  09/07/2013   \n",
       "1         FP010      7                 50.0  Pecketsford     1  09/07/2013   \n",
       "2         FP010      9                 50.0  Pecketsford     1  09/07/2013   \n",
       "3         FP010     17                 50.0  Pecketsford     1  09/07/2013   \n",
       "4         FP010     19                 50.0  Pecketsford     1  09/07/2013   \n",
       "\n",
       "  Sample_time comments Start_Sample_Date GPS_Sample_Nr  ...  \\\n",
       "0    12:00:00      NaN        02/07/2013          ht_2  ...   \n",
       "1    12:00:00      NaN        02/07/2013          ht_7  ...   \n",
       "2    12:00:00      NaN        02/07/2013          ht_9  ...   \n",
       "3    12:00:00      NaN        02/07/2013         ht_17  ...   \n",
       "4    12:00:00      NaN        02/07/2013         ht_19  ...   \n",
       "\n",
       "   Total_Bo_NRM1 Quality Last Modified  Total C (%w/w) Herb  \\\n",
       "0                                  NaN                  NaN   \n",
       "1                                  NaN                  NaN   \n",
       "2                                  NaN                  NaN   \n",
       "3                                  NaN                  NaN   \n",
       "4                                  NaN                  NaN   \n",
       "\n",
       "   Total_C_H_NRM1 Quality Total_C_H_NRM1 Quality Last Modified C:N ratio  \\\n",
       "0                     NaN                                  NaN       NaN   \n",
       "1                     NaN                                  NaN       NaN   \n",
       "2                     NaN                                  NaN       NaN   \n",
       "3                     NaN                                  NaN       NaN   \n",
       "4                     NaN                                  NaN       NaN   \n",
       "\n",
       "   C_N_ratio_NRM1 Quality C_N_ratio_NRM1 Quality Last Modified  \\\n",
       "0                     NaN                                  NaN   \n",
       "1                     NaN                                  NaN   \n",
       "2                     NaN                                  NaN   \n",
       "3                     NaN                                  NaN   \n",
       "4                     NaN                                  NaN   \n",
       "\n",
       "  Dry Matter Content (%) in-house  DM_H_NW1 Quality  \\\n",
       "0                             NaN               NaN   \n",
       "1                             NaN               NaN   \n",
       "2                             NaN               NaN   \n",
       "3                             NaN               NaN   \n",
       "4                             NaN               NaN   \n",
       "\n",
       "  DM_H_NW1 Quality Last Modified  \n",
       "0                            NaN  \n",
       "1                            NaN  \n",
       "2                            NaN  \n",
       "3                            NaN  \n",
       "4                            NaN  \n",
       "\n",
       "[5 rows x 69 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join all herbage datasets\n",
    "files_concat_herbage = pd.concat([pd.read_csv(f) for f in files_herbage])\n",
    "files_concat_herbage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "946a1088",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Little Pecketsford',\n",
       " 'Pecketsford',\n",
       " 'Great Field',\n",
       " 'Ware Park',\n",
       " 'Top Burrows',\n",
       " 'Burrows',\n",
       " 'Bottom Burrows',\n",
       " 'Orchard Dean, Orchard Dean North',\n",
       " 'Lower Wyke Moor',\n",
       " 'Middle Wyke Moor',\n",
       " 'Dairy South',\n",
       " 'Dairy Corner',\n",
       " 'Poor Field',\n",
       " 'Higher Wyke Moor',\n",
       " 'Burrows, Burrows',\n",
       " 'Golden Rove',\n",
       " 'Bottom Burrows, Bottom Burrows',\n",
       " 'Orchard Dean, Orchard Dean South',\n",
       " 'Dairy East',\n",
       " 'Lower Wheaty',\n",
       " 'Dairy North',\n",
       " 'Longlands South',\n",
       " 'Longlands North',\n",
       " 'Longlands East']"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check field names\n",
    "#files_concat_herbage['Field'].unique().tolist()\n",
    "files_concat_silage['Field'].unique().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "862dd612",
   "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>Experiment_Id</th>\n",
       "      <th>Field</th>\n",
       "      <th>Flume</th>\n",
       "      <th>Sample_date</th>\n",
       "      <th>Sample_time</th>\n",
       "      <th>comments</th>\n",
       "      <th>GPS_Sample_Nr</th>\n",
       "      <th>Easting</th>\n",
       "      <th>Northing</th>\n",
       "      <th>Silage Dry Matter Yield (kg/ha)</th>\n",
       "      <th>Silage Dry Matter Yield Quality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:01:00</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>20</td>\n",
       "      <td>265555.2912</td>\n",
       "      <td>98391.25314</td>\n",
       "      <td>7449.53</td>\n",
       "      <td>Not set</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:02:00</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>21</td>\n",
       "      <td>265592.1536</td>\n",
       "      <td>98314.89750</td>\n",
       "      <td>6059.88</td>\n",
       "      <td>Not set</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:03:00</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>22</td>\n",
       "      <td>265678.8192</td>\n",
       "      <td>98259.63132</td>\n",
       "      <td>7009.69</td>\n",
       "      <td>Not set</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:04:00</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>23</td>\n",
       "      <td>265742.8685</td>\n",
       "      <td>98240.71768</td>\n",
       "      <td>5927.21</td>\n",
       "      <td>Not set</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NW558_SC1</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>12:05:00</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>24</td>\n",
       "      <td>265694.5264</td>\n",
       "      <td>98413.41024</td>\n",
       "      <td>7060.46</td>\n",
       "      <td>Not set</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Experiment_Id    Field Flume Sample_date Sample_time comments GPS_Sample_Nr  \\\n",
       "0     NW558_SC1  Burrows     4  25/05/2011    12:01:00  Burrows            20   \n",
       "1     NW558_SC1  Burrows     4  25/05/2011    12:02:00  Burrows            21   \n",
       "2     NW558_SC1  Burrows     4  25/05/2011    12:03:00  Burrows            22   \n",
       "3     NW558_SC1  Burrows     4  25/05/2011    12:04:00  Burrows            23   \n",
       "4     NW558_SC1  Burrows     4  25/05/2011    12:05:00  Burrows            24   \n",
       "\n",
       "       Easting     Northing  Silage Dry Matter Yield (kg/ha)  \\\n",
       "0  265555.2912  98391.25314                          7449.53   \n",
       "1  265592.1536  98314.89750                          6059.88   \n",
       "2  265678.8192  98259.63132                          7009.69   \n",
       "3  265742.8685  98240.71768                          5927.21   \n",
       "4  265694.5264  98413.41024                          7060.46   \n",
       "\n",
       "  Silage Dry Matter Yield Quality  \n",
       "0                         Not set  \n",
       "1                         Not set  \n",
       "2                         Not set  \n",
       "3                         Not set  \n",
       "4                         Not set  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select only Field 'Burrows'\n",
    "fields_silage = ['Burrows', 'Burrows, Burrows']\n",
    "filesBurrows_silage = files_concat_silage[files_concat_silage.Field.isin(fields_silage)].reset_index(drop = True)\n",
    "filesBurrows_silage.drop(['SP_ID', 'sample_distance (m)', 'Start_Sample_Date', 'Silage Dry Matter Yield Quality Last Modified'], \n",
    "                  axis = 1, inplace = True)\n",
    "filesBurrows_silage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "9e485d1c",
   "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>Experiment_Id</th>\n",
       "      <th>SP_ID</th>\n",
       "      <th>sample_distance (m)</th>\n",
       "      <th>Field</th>\n",
       "      <th>Flume</th>\n",
       "      <th>Sample_date</th>\n",
       "      <th>Sample_time</th>\n",
       "      <th>comments</th>\n",
       "      <th>Start_Sample_Date</th>\n",
       "      <th>GPS_Sample_Nr</th>\n",
       "      <th>...</th>\n",
       "      <th>Total_Bo_NRM1 Quality Last Modified</th>\n",
       "      <th>Total C (%w/w) Herb</th>\n",
       "      <th>Total_C_H_NRM1 Quality</th>\n",
       "      <th>Total_C_H_NRM1 Quality Last Modified</th>\n",
       "      <th>C:N ratio</th>\n",
       "      <th>C_N_ratio_NRM1 Quality</th>\n",
       "      <th>C_N_ratio_NRM1 Quality Last Modified</th>\n",
       "      <th>Dry Matter Content (%) in-house</th>\n",
       "      <th>DM_H_NW1 Quality</th>\n",
       "      <th>DM_H_NW1 Quality Last Modified</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NW686-H</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>18/04/2018</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>Burrows - Spring 2018 -  Bulked - virtual samp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW686-H078</td>\n",
       "      <td>...</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>47.9</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>13.9</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NW686-H</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>30/07/2018</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>Burrows - Summer 2018 -  Bulked - virtual samp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW686-H239</td>\n",
       "      <td>...</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>48.2</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>21.0</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NW686-H</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>23/10/2018</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>Burrows - Autumn 2018 -  Bulked - virtual samp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW686-H260</td>\n",
       "      <td>...</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>52.4</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>13.9</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NW686-H</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>14/01/2019</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>Burrows - Winter 2019 -  Bulked - virtual samp...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW686-S281</td>\n",
       "      <td>...</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>45.1</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>12.1</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NW686-HP</td>\n",
       "      <td>2005</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Burrows</td>\n",
       "      <td>4</td>\n",
       "      <td>18/04/2018</td>\n",
       "      <td>12:01:00</td>\n",
       "      <td>Burrows - Archived - sample location</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NW686-H079_BR2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.83</td>\n",
       "      <td>Not set</td>\n",
       "      <td>19/04/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 69 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Experiment_Id  SP_ID  sample_distance (m)    Field Flume Sample_date  \\\n",
       "0       NW686-H   2005                  NaN  Burrows     4  18/04/2018   \n",
       "1       NW686-H   2005                  NaN  Burrows     4  30/07/2018   \n",
       "2       NW686-H   2005                  NaN  Burrows     4  23/10/2018   \n",
       "3       NW686-H   2005                  NaN  Burrows     4  14/01/2019   \n",
       "4      NW686-HP   2005                  NaN  Burrows     4  18/04/2018   \n",
       "\n",
       "  Sample_time                                           comments  \\\n",
       "0    12:00:00  Burrows - Spring 2018 -  Bulked - virtual samp...   \n",
       "1    12:00:00  Burrows - Summer 2018 -  Bulked - virtual samp...   \n",
       "2    12:00:00  Burrows - Autumn 2018 -  Bulked - virtual samp...   \n",
       "3    12:00:00  Burrows - Winter 2019 -  Bulked - virtual samp...   \n",
       "4    12:01:00               Burrows - Archived - sample location   \n",
       "\n",
       "  Start_Sample_Date   GPS_Sample_Nr  ...  Total_Bo_NRM1 Quality Last Modified  \\\n",
       "0               NaN      NW686-H078  ...                           19/04/2020   \n",
       "1               NaN      NW686-H239  ...                           19/04/2020   \n",
       "2               NaN      NW686-H260  ...                           19/04/2020   \n",
       "3               NaN      NW686-S281  ...                           19/04/2020   \n",
       "4               NaN  NW686-H079_BR2  ...                                  NaN   \n",
       "\n",
       "   Total C (%w/w) Herb  Total_C_H_NRM1 Quality  \\\n",
       "0                 47.9                 Not set   \n",
       "1                 48.2                 Not set   \n",
       "2                 52.4                 Not set   \n",
       "3                 45.1                 Not set   \n",
       "4                  NaN                     NaN   \n",
       "\n",
       "  Total_C_H_NRM1 Quality Last Modified C:N ratio  C_N_ratio_NRM1 Quality  \\\n",
       "0                           19/04/2020      13.9                 Not set   \n",
       "1                           19/04/2020      21.0                 Not set   \n",
       "2                           19/04/2020      13.9                 Not set   \n",
       "3                           19/04/2020      12.1                 Not set   \n",
       "4                                  NaN       NaN                     NaN   \n",
       "\n",
       "  C_N_ratio_NRM1 Quality Last Modified Dry Matter Content (%) in-house  \\\n",
       "0                           19/04/2020                             NaN   \n",
       "1                           19/04/2020                             NaN   \n",
       "2                           19/04/2020                             NaN   \n",
       "3                           19/04/2020                             NaN   \n",
       "4                                  NaN                           14.83   \n",
       "\n",
       "   DM_H_NW1 Quality DM_H_NW1 Quality Last Modified  \n",
       "0               NaN                            NaN  \n",
       "1               NaN                            NaN  \n",
       "2               NaN                            NaN  \n",
       "3               NaN                            NaN  \n",
       "4           Not set                     19/04/2020  \n",
       "\n",
       "[5 rows x 69 columns]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select only Field 'Burrows'\n",
    "fields_herbage = ['Burrows']\n",
    "filesBurrows_herbage = files_concat_herbage[files_concat_herbage.Field.isin(fields_herbage)].reset_index(drop = True)\n",
    "filesBurrows_herbage.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "4eb0c7b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['25/05/2011', '09/08/2011', '25/05/2012', '15/09/2014',\n",
       "       '27/05/2015', '18/05/2017', '20/05/2019'], dtype=object)"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check dates\n",
    "filesBurrows_herbage['Sample_date'].unique()\n",
    "filesBurrows_silage['Sample_date'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "8507721a",
   "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>Sample_date</th>\n",
       "      <th>Easting</th>\n",
       "      <th>Northing</th>\n",
       "      <th>Silage Dry Matter Yield (kg/ha)</th>\n",
       "      <th>silage_gm2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09/08/2011</td>\n",
       "      <td>265638.81</td>\n",
       "      <td>98327.11</td>\n",
       "      <td>4864.09</td>\n",
       "      <td>486.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15/09/2014</td>\n",
       "      <td>265642.86</td>\n",
       "      <td>98271.43</td>\n",
       "      <td>6418.45</td>\n",
       "      <td>641.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18/05/2017</td>\n",
       "      <td>265630.00</td>\n",
       "      <td>98325.00</td>\n",
       "      <td>6025.40</td>\n",
       "      <td>602.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20/05/2019</td>\n",
       "      <td>265595.00</td>\n",
       "      <td>98295.00</td>\n",
       "      <td>4882.40</td>\n",
       "      <td>488.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25/05/2011</td>\n",
       "      <td>265628.33</td>\n",
       "      <td>98330.50</td>\n",
       "      <td>6657.17</td>\n",
       "      <td>665.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>25/05/2012</td>\n",
       "      <td>265642.86</td>\n",
       "      <td>98271.43</td>\n",
       "      <td>3851.20</td>\n",
       "      <td>385.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>27/05/2015</td>\n",
       "      <td>265658.33</td>\n",
       "      <td>98316.67</td>\n",
       "      <td>5923.02</td>\n",
       "      <td>592.30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Sample_date    Easting  Northing  Silage Dry Matter Yield (kg/ha)  \\\n",
       "0  09/08/2011  265638.81  98327.11                          4864.09   \n",
       "1  15/09/2014  265642.86  98271.43                          6418.45   \n",
       "2  18/05/2017  265630.00  98325.00                          6025.40   \n",
       "3  20/05/2019  265595.00  98295.00                          4882.40   \n",
       "4  25/05/2011  265628.33  98330.50                          6657.17   \n",
       "5  25/05/2012  265642.86  98271.43                          3851.20   \n",
       "6  27/05/2015  265658.33  98316.67                          5923.02   \n",
       "\n",
       "   silage_gm2  \n",
       "0      486.41  \n",
       "1      641.84  \n",
       "2      602.54  \n",
       "3      488.24  \n",
       "4      665.72  \n",
       "5      385.12  \n",
       "6      592.30  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group by dates and convert to g_m2\n",
    "burrowsSilage_by_date = filesBurrows_silage.groupby(['Sample_date']).mean().reset_index(drop = False).round(2)\n",
    "burrowsHerbage_by_date = filesBurrows_herbage.groupby(['Sample_date']).mean().reset_index(drop = False).round(2)\n",
    "\n",
    "burrowsSilage_by_date['silage_gm2'] = (burrowsSilage_by_date['Silage Dry Matter Yield (kg/ha)'] / 10).round(2)\n",
    "burrowsSilage_by_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "396dbbac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export\n",
    "burrowsSilage_by_date.to_csv(\"C:/Users/neliq/Documents/NIF/Rothamsted/1.1.Silage_data/burrowsSilage_by_date.csv\", \n",
    "                             index = False)\n",
    "burrowsHerbage_by_date.to_csv(\"C:/Users/neliq/Documents/NIF/Rothamsted/1.2.Herbage_data/burrowsHerbage_by_date.csv\", \n",
    "                              index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "geo_env",
   "language": "python",
   "name": "geo_env"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}