xc_stats.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 3469 recordings of grasshoppers in XC as of 28 Aug 2023.\n"
     ]
    }
   ],
   "source": [
    "# Read the metadata downloaded from XC and stored in csv file and populate a dataframe\n",
    "\n",
    "# List of filenames\n",
    "file = '/data/burooj/grp_2_recordings.csv'\n",
    "\n",
    "# Read each file and store in a list of DataFrames\n",
    "df = pd.read_csv(file, engine='python')\n",
    "\n",
    "# Now, final_df contains all the data from the files combined.\n",
    "\n",
    "# Get the number of rows\n",
    "num_rows = df.shape[0]\n",
    "\n",
    "print(f\"There are {num_rows} recordings of grasshoppers in XC as of 28 Aug 2023.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "France                1124\n",
      "Netherlands            594\n",
      "Italy                  529\n",
      "Germany                259\n",
      "Spain                  239\n",
      "Russian Federation      94\n",
      "Greece                  90\n",
      "Andorra                 76\n",
      "China                   61\n",
      "United States           50\n",
      "United Kingdom          41\n",
      "Cyprus                  38\n",
      "Portugal                37\n",
      "Austria                 37\n",
      "Romania                 22\n",
      "Poland                  21\n",
      "Belgium                 21\n",
      "Turkey                  12\n",
      "Sweden                  12\n",
      "Australia               10\n",
      "India                    9\n",
      "Croatia                  9\n",
      "Slovenia                 9\n",
      "Mongolia                 9\n",
      "Israel                   8\n",
      "Tunisia                  7\n",
      "South Africa             7\n",
      "Slovakia                 6\n",
      "Morocco                  4\n",
      "Switzerland              4\n",
      "Czech Republic           3\n",
      "Japan                    2\n",
      "Norway                   2\n",
      "Denmark                  2\n",
      "New Zealand              2\n",
      "Guinea-Bissau            2\n",
      "Montenegro               2\n",
      "Vietnam                  1\n",
      "Madagascar               1\n",
      "Cape Verde               1\n",
      "Ecuador                  1\n",
      "Canada                   1\n",
      "Mozambique               1\n",
      "Panama                   1\n",
      "Iran                     1\n",
      "Ukraine                  1\n",
      "Botswana                 1\n",
      "Uzbekistan               1\n",
      "Dominican Republic       1\n",
      "Gabon                    1\n",
      "Bulgaria                 1\n",
      "Zimbabwe                 1\n",
      "Name: cnt, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Group by 'cnt' column and count the number of recordings per country\n",
    "\n",
    "country_counts = df['cnt'].value_counts()\n",
    "\n",
    "print(country_counts)\n",
    "\n",
    "# Convert the Series to a DataFrame with custom column names\n",
    "country_counts_df = country_counts.reset_index()\n",
    "country_counts_df.columns = ['Country', 'Count']\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "#country_counts_df.to_csv('country_counts_grasshoppers.csv', index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Chorthippus        419\n",
      "Tettigonia         170\n",
      "Omocestus          147\n",
      "Gryllus            141\n",
      "Stenobothrus       126\n",
      "                  ... \n",
      "Corsteropleurus      1\n",
      "Sciobia              1\n",
      "Scintharista         1\n",
      "Broughtonia          1\n",
      "Froggattina          1\n",
      "Name: gen, Length: 152, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Group by 'gen' column and count the number of recordings per genus\n",
    "\n",
    "country_counts = df['gen'].value_counts()\n",
    "\n",
    "print(country_counts)\n",
    "\n",
    "# Convert the Series to a DataFrame with custom column names\n",
    "country_counts_df = country_counts.reset_index()\n",
    "country_counts_df.columns = ['Genus', 'Count']\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "country_counts_df.to_csv('genus_counts_grasshoppers.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "adult               2225\n",
      "uncertain            118\n",
      "adult, uncertain       1\n",
      "Name: stage, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Group by 'cnt' column and count the number of recordings per stage\n",
    "country_counts = df['stage'].value_counts()\n",
    "\n",
    "print(country_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by 'cnt' column and count the number of recordings for different sex\n",
    "country_counts = df['sex'].value_counts()\n",
    "\n",
    "print(country_counts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Baudewijn Odé                  1597\n",
      "Cedric Mroczko                  654\n",
      "Stanislas Wroza                 406\n",
      "Albert Lastukhin                 97\n",
      "Frank Holzapfel                  73\n",
      "                               ... \n",
      "Thomas ARMAND                     1\n",
      "Seyed                             1\n",
      "Grégoire Loïs, Adam Bruneau       1\n",
      "Sven Normant                      1\n",
      "Helena Backius                    1\n",
      "Name: rec, Length: 117, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Group by 'rec' column and count the number of recordings uploaded by recordists\n",
    "\n",
    "rec_counts = df['rec'].value_counts()\n",
    "\n",
    "print(rec_counts)\n",
    "\n",
    "# Convert the Series to a DataFrame with custom column names\n",
    "rec_counts_df = rec_counts.reset_index()\n",
    "rec_counts_df.columns = ['Recordist', 'Count']\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "rec_counts_df.to_csv('rec_counts_grasshoppers.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total length: 5746:46\n",
      "Total length in hours: 95\n",
      "0-5s               156\n",
      "5-10s              214\n",
      "10-30s             810\n",
      "30-60s             813\n",
      "1-2min             648\n",
      "2-4min             476\n",
      "4-10min            272\n",
      "10min and above     80\n",
      "Name: duration_range, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#  Count the number of recordings for different ranges of recording length \n",
    "\n",
    "# Split the 'length' column into minutes and seconds\n",
    "df['minutes'] = df['length'].str.split(':').str[0].astype(int)\n",
    "df['seconds'] = df['length'].str.split(':').str[1].astype(int)\n",
    "\n",
    "# Convert the 'minutes' and 'seconds' columns to seconds\n",
    "df['total_seconds'] = df['minutes']*60 + df['seconds']\n",
    "\n",
    "# Sum the total seconds\n",
    "total_seconds_sum = df['total_seconds'].sum()\n",
    "\n",
    "# Convert the total seconds back to min:sec format\n",
    "total_minutes = total_seconds_sum // 60\n",
    "remaining_seconds = total_seconds_sum % 60\n",
    "\n",
    "hours = total_minutes // 60 + remaining_seconds // 3600\n",
    "\n",
    "print(f\"Total length: {total_minutes}:{remaining_seconds:02}\")\n",
    "print(f\"Total length in hours: {hours}\")\n",
    "\n",
    "\n",
    "# Define the bins (ranges) for the durations\n",
    "\n",
    "bins = [0, 5, 10, 30, 60, 120, 240, 600, float('inf')]\n",
    "labels = ['0-5s', '5-10s', '10-30s', '30-60s', '1-2min', '2-4min', '4-10min', '10min and above']\n",
    "\n",
    "# Create a new column in the DataFrame to store which bin each recording belongs to\n",
    "df['duration_range'] = pd.cut(df['total_seconds'], bins=bins, labels=labels, right=False)\n",
    "\n",
    "# Compute the stats: count number of recordings in each range\n",
    "range_counts = df['duration_range'].value_counts().sort_index()\n",
    "\n",
    "print(range_counts)\n",
    "\n",
    "# Convert the Series to a DataFrame with custom column names\n",
    "range_counts_df = range_counts.reset_index()\n",
    "range_counts_df.columns = ['Length range', 'Count']\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "range_counts_df.to_csv('length_counts_grasshoppers.csv', index=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by 'smp' column and count the number of recordings for each unique sampling rate used\n",
    "\n",
    "smp_counts = df['smp'].value_counts()\n",
    "\n",
    "print(smp_counts)\n",
    "\n",
    "# Convert the Series to a DataFrame with custom column names\n",
    "smp_counts_df = smp_counts.reset_index()\n",
    "smp_counts_df.columns = ['Sampling rate', 'Count']\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "smp_counts_df.to_csv('smp_counts_grasshoppers.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group by 'q' column and count the number of recordings for each quality category\n",
    "\n",
    "q_counts = df['q'].value_counts()\n",
    "\n",
    "print(q_counts)\n",
    "\n",
    "# Convert the Series to a DataFrame with custom column names\n",
    "q_counts_df = q_counts.reset_index()\n",
    "q_counts_df.columns = ['Quality', 'Count']\n",
    "\n",
    "# Save the DataFrame to a CSV file\n",
    "q_counts_df.to_csv('q_counts_grasshoppers.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mystery mystery                 97\n",
      "Gryllus campestris              96\n",
      "Tettigonia viridissima          75\n",
      "Pseudochorthippus parallelus    71\n",
      "Chorthippus biguttulus          71\n",
      "                                ..\n",
      "Ephippigerida diluta             1\n",
      "Mecopoda niponensis              1\n",
      "Eunemobius carolinus             1\n",
      "Leptophyes discoidalis           1\n",
      "Stenobothrus rubicundulus        1\n",
      "Name: sp, Length: 428, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Group by 'cnt' column and count the number of rows for each country\n",
    "q_counts = df['sp'].value_counts()\n",
    "\n",
    "print(q_counts)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compute total durations of recordings per species (including both scientific and common names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 3469 recordings of grasshoppers in XC as of 28 Aug 2023.\n",
      "Total length: 5746:46\n",
      "Total length in hours: 95\n",
      "                              sp                               en  counts  \\\n",
      "137              Mystery mystery                 Identity unknown      97   \n",
      "98            Gryllus campestris                    Field Cricket      96   \n",
      "252       Tettigonia viridissima         Great Green Bush-cricket      75   \n",
      "226              Sonus naturalis                       Soundscape      63   \n",
      "248           Tettigonia cantans        Upland Green Bush-cricket      63   \n",
      "..                           ...                              ...     ...   \n",
      "177      Phyllopalpus pulchellus                    Handsome Trig       1   \n",
      "176  Pholidoptera transsylvanica  Transylvanian Dark Bush-cricket       1   \n",
      "86         Froggattina australis                Froggatt's buzzer       1   \n",
      "174    Pholidoptera frivaldszkyi          Green Dark Bush-cricket       1   \n",
      "68       Ephippiger provincialis     Provence Saddle Bush-cricket       1   \n",
      "\n",
      "    total_seconds_sum  \n",
      "137          00:46:24  \n",
      "98           03:14:51  \n",
      "252          01:21:01  \n",
      "226          01:19:43  \n",
      "248          02:42:10  \n",
      "..                ...  \n",
      "177          00:19:36  \n",
      "176          00:00:59  \n",
      "86           00:00:25  \n",
      "174          00:01:07  \n",
      "68           00:01:01  \n",
      "\n",
      "[264 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "# Compute total durations of recordings per species (including both scientific and common names)\n",
    "\n",
    "# Get the number of rows\n",
    "num_rows = df.shape[0]\n",
    "\n",
    "print(f\"There are {num_rows} recordings of grasshoppers in XC as of 28 Aug 2023.\")\n",
    "\n",
    "\n",
    "# Split the 'length' column into minutes and seconds\n",
    "df['minutes'] = df['length'].str.split(':').str[0].astype(int)\n",
    "df['seconds'] = df['length'].str.split(':').str[1].astype(int)\n",
    "\n",
    "# Convert the 'minutes' and 'seconds' columns to seconds\n",
    "df['total_seconds'] = df['minutes']*60 + df['seconds']\n",
    "\n",
    "# Sum the total seconds\n",
    "total_seconds_sum = df['total_seconds'].sum()\n",
    "\n",
    "# Convert the total seconds back to min:sec format\n",
    "total_minutes = total_seconds_sum // 60\n",
    "remaining_seconds = total_seconds_sum % 60\n",
    "\n",
    "hours = total_minutes // 60 + remaining_seconds // 3600\n",
    "\n",
    "print(f\"Total length: {total_minutes}:{remaining_seconds:02}\")\n",
    "print(f\"Total length in hours: {hours}\")\n",
    "\n",
    "\n",
    "\n",
    "# Concatenate 'gen' and 'sp' columns\n",
    "df['sp'] = df['gen'].astype(str) + ' ' + df['sp'].astype(str)\n",
    "\n",
    "\n",
    "# Group by 'sp' and 'en', then aggregate\n",
    "grouped_counts = df.groupby(['sp', 'en']).agg(\n",
    "    counts=pd.NamedAgg(column='sp', aggfunc='size'),  # Count rows for each group\n",
    "    total_seconds_sum=pd.NamedAgg(column='total_seconds', aggfunc='sum')  # Sum 'total_seconds' for each group\n",
    ").reset_index()\n",
    "\n",
    "# Convert total_seconds to minutes\n",
    "#grouped_counts['total_seconds_sum'] = grouped_counts['total_seconds_sum'] / 60\n",
    "\n",
    "# Convert summed total_seconds to 'hh:mm:ss' format\n",
    "grouped_counts['total_seconds_sum'] = grouped_counts['total_seconds_sum'].apply(\n",
    "    lambda x: f\"{divmod(divmod(x, 60)[0], 60)[0]:02d}:{divmod(divmod(x, 60)[0], 60)[1]:02d}:{divmod(x, 60)[1]:02d}\"\n",
    "\n",
    ")\n",
    "\n",
    "# Sort the DataFrame by 'counts' in decreasing order\n",
    "sorted_grouped_counts = grouped_counts.sort_values('counts', ascending=False)\n",
    "\n",
    "\n",
    "\n",
    "# Print the grouped DataFrame\n",
    "print(sorted_grouped_counts)\n",
    "\n",
    "# Save the sorted DataFrame to a CSV file\n",
    "#sorted_grouped_counts.to_csv('sorted_grouped_counts.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tensorflow",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}