3) Conviction Rate classifer.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cf64dd96",
   "metadata": {},
   "source": [
    "This notebook creates the input data for the second visualisation for D3, which is a line graph for the conviction rate of people charged with at least one violent crime and are convicted of at least one violent crime, and the same for sex offences.\n",
    "\n",
    "This notebook takes the general dataset of court martials (or an edited version with just the relevant columns), after it has been cleaned with the first notebook + extra manual cleaning in Google Sheets using array formulas and the like to create columns that assess the Charge (s) column for each type of charge. It also requires that you have gone through the Violent Charges column and Sexual Offence charges column and marked in new columns whether they were found guilty of those offences. This is currently a lot of manual work, hopefully I will find a more efficient way to do all of this at some point! \n",
    "So the requirements for starting this notebook is a CSV with the following columns: 1) Year, 2) Guilty of Violent Offence, 3) Guilty of Sex offence, 4) A-Violent Charges, 5) B-Sex offence charges.\n",
    "\n",
    "The guilty columns should be marked with 'Yes' where relevant, and the charges columns should be 'Contains' or 'Doesn't Contain'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bef67b4d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(  Reference number       Rank Service Unit Trial Court Sentencing Date  \\\n",
       " 0              NaN  Signaller    Army  NaN  Colchester       07-Jan-10   \n",
       " 1              NaN    Colonel    Army  NaN  Sennelager       08-Jan-10   \n",
       " 2              NaN  Guardsman    Army  NaN  Colchester       13-Jan-10   \n",
       " 3              NaN  Signaller    Army  NaN  Colchester       13-Jan-10   \n",
       " 4              NaN  Guardsman    Army  NaN  Colchester       21-Jan-10   \n",
       " \n",
       "    Year of Sentencing                                         Charge (s)  \\\n",
       " 0                2010  2 x Battery, 1 x Assault Occasioning Actual Bo...   \n",
       " 1                2010  5 x Obtaining a money transfer by deception, 6...   \n",
       " 2                2010                          1 x Absence Without Leave   \n",
       " 3                2010                          1 x Absence Without Leave   \n",
       " 4                2010           1 x Desertion, 1 x Absence Without Leave   \n",
       " \n",
       "   Act charged under                      Finding  ...      G - Alcohol  \\\n",
       " 0               NaN   1 x Not Guilty, 2 x Guilty  ...  Doesn't Contain   \n",
       " 1               NaN  11 x Not Guilty, 2 x Guilty  ...  Doesn't Contain   \n",
       " 2               NaN                       Guilty  ...  Doesn't Contain   \n",
       " 3               NaN                       Guilty  ...  Doesn't Contain   \n",
       " 4               NaN           Not Guilty, Guilty  ...  Doesn't Contain   \n",
       " \n",
       "   H - Child sex offence charge I - Firearms charges  \\\n",
       " 0              Doesn't Contain      Doesn't Contain   \n",
       " 1              Doesn't Contain      Doesn't Contain   \n",
       " 2              Doesn't Contain      Doesn't Contain   \n",
       " 3              Doesn't Contain      Doesn't Contain   \n",
       " 4              Doesn't Contain      Doesn't Contain   \n",
       " \n",
       "   J - Damage to private property K - Vehicle Negligence   L - Harassment  \\\n",
       " 0                Doesn't Contain        Doesn't Contain  Doesn't Contain   \n",
       " 1                Doesn't Contain        Doesn't Contain  Doesn't Contain   \n",
       " 2                Doesn't Contain        Doesn't Contain  Doesn't Contain   \n",
       " 3                Doesn't Contain        Doesn't Contain  Doesn't Contain   \n",
       " 4                Doesn't Contain        Doesn't Contain  Doesn't Contain   \n",
       " \n",
       "   M - Manslaughter N - Perverting Course of Justice O - Resisting Arrest  \\\n",
       " 0  Doesn't Contain                  Doesn't Contain      Doesn't Contain   \n",
       " 1  Doesn't Contain                  Doesn't Contain      Doesn't Contain   \n",
       " 2  Doesn't Contain                  Doesn't Contain      Doesn't Contain   \n",
       " 3  Doesn't Contain                  Doesn't Contain      Doesn't Contain   \n",
       " 4  Doesn't Contain                  Doesn't Contain      Doesn't Contain   \n",
       " \n",
       "   P - Use of Internet  \n",
       " 0     Doesn't Contain  \n",
       " 1     Doesn't Contain  \n",
       " 2     Doesn't Contain  \n",
       " 3     Doesn't Contain  \n",
       " 4     Doesn't Contain  \n",
       " \n",
       " [5 rows x 32 columns],\n",
       " Index(['Reference number', 'Rank', 'Service', 'Unit', 'Trial Court',\n",
       "        'Sentencing Date', 'Year of Sentencing', 'Charge (s)',\n",
       "        'Act charged under', 'Finding', 'Guilty > 0',\n",
       "        'Guilty of violent offence', 'Guilty of sex offence',\n",
       "        'Guilty of child sex offence', 'Unnamed: 14', 'Sentence',\n",
       "        'A - Violent charges', 'B - Sex offence charges', 'C - Theft charges',\n",
       "        'D - Military misdemeanours', 'E - Identity-based discrimination',\n",
       "        'F - Drugs', 'G - Alcohol', 'H - Child sex offence charge',\n",
       "        'I - Firearms charges', 'J - Damage to private property',\n",
       "        'K - Vehicle Negligence', 'L - Harassment', 'M - Manslaughter',\n",
       "        'N - Perverting Course of Justice', 'O - Resisting Arrest',\n",
       "        'P - Use of Internet'],\n",
       "       dtype='object'))"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "file_path = 'GeneralDataset2010-2023.csv'\n",
    "data = pd.read_csv(file_path)\n",
    "\n",
    "data.head(), data.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e23eb106",
   "metadata": {},
   "source": [
    "Now need to isolate the columns you want to keep. If you are using the general dataset, this will remove a lot of columns. If you have already isolate the relevant columns then this will not really change the dataset but is important to run this code nonetheless."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0eee4db7",
   "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>Year of Sentencing</th>\n",
       "      <th>Guilty of violent offence</th>\n",
       "      <th>Guilty of sex offence</th>\n",
       "      <th>A - Violent charges</th>\n",
       "      <th>B - Sex offence charges</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>Contains</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6066</th>\n",
       "      <td>2023</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Contains</td>\n",
       "      <td>Contains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6067</th>\n",
       "      <td>2023</td>\n",
       "      <td>Yes</td>\n",
       "      <td>No</td>\n",
       "      <td>Contains</td>\n",
       "      <td>Contains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6068</th>\n",
       "      <td>2023</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Contains</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6069</th>\n",
       "      <td>2023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "      <td>Doesn't Contain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6070</th>\n",
       "      <td>2023</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Contains</td>\n",
       "      <td>Contains</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6071 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year of Sentencing Guilty of violent offence Guilty of sex offence  \\\n",
       "0                   2010                       Yes                    No   \n",
       "1                   2010                       NaN                    No   \n",
       "2                   2010                       NaN                    No   \n",
       "3                   2010                       NaN                    No   \n",
       "4                   2010                       NaN                    No   \n",
       "...                  ...                       ...                   ...   \n",
       "6066                2023                        No                    No   \n",
       "6067                2023                       Yes                    No   \n",
       "6068                2023                       Yes                   NaN   \n",
       "6069                2023                       NaN                   NaN   \n",
       "6070                2023                       Yes                   Yes   \n",
       "\n",
       "     A - Violent charges B - Sex offence charges  \n",
       "0               Contains         Doesn't Contain  \n",
       "1        Doesn't Contain         Doesn't Contain  \n",
       "2        Doesn't Contain         Doesn't Contain  \n",
       "3        Doesn't Contain         Doesn't Contain  \n",
       "4        Doesn't Contain         Doesn't Contain  \n",
       "...                  ...                     ...  \n",
       "6066            Contains                Contains  \n",
       "6067            Contains                Contains  \n",
       "6068            Contains         Doesn't Contain  \n",
       "6069     Doesn't Contain         Doesn't Contain  \n",
       "6070            Contains                Contains  \n",
       "\n",
       "[6071 rows x 5 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns_to_keep = ['Year of Sentencing', 'Guilty of violent offence', 'Guilty of sex offence', 'A - Violent charges', 'B - Sex offence charges']\n",
    "filtered_data = data[columns_to_keep]\n",
    "\n",
    "filtered_data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dff32f41",
   "metadata": {},
   "source": [
    "The below code does the following 1) creates filtered data for all the rows where there is a violent charge, 2) creates filtered data for all the rows where there is a violent conviction, 3) repeats the process for sex offence charges and convictions, 4) creates counts for each of the filtered data categories just mentioned, 5) creates a dataframe for the violent charges and convictions by year, and the sex offence charges and convictions by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "111e050b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                    Violent Charges  Violent Convictions\n",
      "Year of Sentencing                                      \n",
      "2010                            170                   99\n",
      "2011                            215                  155\n",
      "2012                            184                  124\n",
      "2013                            263                  152\n",
      "2014                            249                  150\n",
      "2015                            235                  162\n",
      "2016                            239                  154\n",
      "2017                            200                  117\n",
      "2018                            203                  127\n",
      "2019                            246                  156\n",
      "2020                            181                  124\n",
      "2021                            206                  109\n",
      "2022                            197                  106\n",
      "2023                            140                   72\n",
      "                    Sex Offence Charges  Sex offence Convictions\n",
      "Year of Sentencing                                              \n",
      "2010                                 29                       20\n",
      "2011                                 34                       29\n",
      "2012                                 30                       19\n",
      "2013                                 45                       27\n",
      "2014                                 47                       26\n",
      "2015                                 45                       29\n",
      "2016                                 55                       34\n",
      "2017                                 81                       42\n",
      "2018                                 57                       33\n",
      "2019                                 57                       24\n",
      "2020                                 51                       31\n",
      "2021                                 89                       45\n",
      "2022                                104                       41\n",
      "2023                                 78                       44\n",
      "      Year of Sentencing Guilty of violent offence Guilty of sex offence  \\\n",
      "0                   2010                       Yes                    No   \n",
      "6                   2010                        No                    No   \n",
      "24                  2010                       Yes                    No   \n",
      "27                  2010                        No                    No   \n",
      "32                  2010                        No                    No   \n",
      "...                  ...                       ...                   ...   \n",
      "6064                2023                       Yes                   Yes   \n",
      "6066                2023                        No                    No   \n",
      "6067                2023                       Yes                    No   \n",
      "6068                2023                       Yes                   NaN   \n",
      "6070                2023                       Yes                   Yes   \n",
      "\n",
      "     A - Violent charges B - Sex offence charges  \n",
      "0               Contains         Doesn't Contain  \n",
      "6               Contains                Contains  \n",
      "24              Contains         Doesn't Contain  \n",
      "27              Contains         Doesn't Contain  \n",
      "32              Contains         Doesn't Contain  \n",
      "...                  ...                     ...  \n",
      "6064            Contains                Contains  \n",
      "6066            Contains                Contains  \n",
      "6067            Contains                Contains  \n",
      "6068            Contains         Doesn't Contain  \n",
      "6070            Contains                Contains  \n",
      "\n",
      "[2928 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "violent_charges = filtered_data[filtered_data['A - Violent charges'] == 'Contains']\n",
    "\n",
    "# Filter the rows where 'Guilty of violent offence' contains 'Yes'\n",
    "violent_convictions = filtered_data[filtered_data['Guilty of violent offence'] == 'Yes']\n",
    "\n",
    "sexoffence_charges = filtered_data[filtered_data['B - Sex offence charges'] == 'Contains']\n",
    "sexoffence_convictions = filtered_data[filtered_data['Guilty of sex offence'] == 'Yes']\n",
    "\n",
    "# Group by 'Year of Sentencing' and count the occurrences\n",
    "violent_charges_counts = violent_charges.groupby('Year of Sentencing').size()\n",
    "violent_convictions_counts = violent_convictions.groupby('Year of Sentencing').size()\n",
    "\n",
    "sexoffence_charges_counts = sexoffence_charges.groupby('Year of Sentencing').size()\n",
    "sexoffence_convictions_counts = sexoffence_convictions.groupby('Year of Sentencing').size()\n",
    "\n",
    "# Combine the counts into a single DataFrame for comparison\n",
    "violent_counts_by_year = pd.DataFrame({\n",
    "    'Violent Charges': violent_charges_counts,\n",
    "    'Violent Convictions': violent_convictions_counts\n",
    "}).fillna(0)\n",
    "\n",
    "sexoffence_counts_by_year = pd.DataFrame({\n",
    "    'Sex Offence Charges': sexoffence_charges_counts,\n",
    "    'Sex offence Convictions': sexoffence_convictions_counts\n",
    "}).fillna(0)\n",
    "\n",
    "print(sexoffence_counts_by_year)\n",
    "print(violent_charges)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71c85da7",
   "metadata": {},
   "source": [
    "The below calculates the violent conviction rate by year, by dividing the number of convictions by the the number of charges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "623cca7e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year of Sentencing\n",
       "2010    58.235294\n",
       "2011    72.093023\n",
       "2012    67.391304\n",
       "2013    57.794677\n",
       "2014    60.240964\n",
       "2015    68.936170\n",
       "2016    64.435146\n",
       "2017    58.500000\n",
       "2018    62.561576\n",
       "2019    63.414634\n",
       "2020    68.508287\n",
       "2021    52.912621\n",
       "2022    53.807107\n",
       "2023    51.428571\n",
       "Name: Violent Conviction Rate, dtype: float64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "violent_counts_by_year['Violent Conviction Rate'] = (violent_counts_by_year['Violent Convictions'] / violent_counts_by_year['Violent Charges']) * 100\n",
    "\n",
    "violent_counts_by_year['Violent Conviction Rate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56d43185",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "8dfe0fdf",
   "metadata": {},
   "source": [
    "The below calculates the sex offence conviction rate by year, by dividing the number of convictions by the the number of charges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "c41dc6b6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Year of Sentencing\n",
       "2010    68.965517\n",
       "2011    85.294118\n",
       "2012    63.333333\n",
       "2013    60.000000\n",
       "2014    55.319149\n",
       "2015    64.444444\n",
       "2016    61.818182\n",
       "2017    51.851852\n",
       "2018    57.894737\n",
       "2019    42.105263\n",
       "2020    60.784314\n",
       "2021    50.561798\n",
       "2022    39.423077\n",
       "2023    56.410256\n",
       "Name: Sex Offence Conviction Rate, dtype: float64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sexoffence_counts_by_year['Sex Offence Conviction Rate'] = (sexoffence_counts_by_year['Sex offence Convictions'] / sexoffence_counts_by_year['Sex Offence Charges']) * 100\n",
    "\n",
    "sexoffence_counts_by_year['Sex Offence Conviction Rate']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f5a7c18",
   "metadata": {},
   "source": [
    "The below takes the new dataframe with violent conviction rate and converts into a new dataframe with the same column name. And does the same with the sex offence conviction rate. It also resets the index for both, so that each row of the new dataframe has a new sequential unique identifier. Then the code creates a new dataframe which merges the dataframes for violent charges and sex offence charges, using the 'Year of Sentencing' as the shared identifier between them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "f127532e",
   "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>Year of Sentencing</th>\n",
       "      <th>Violent Conviction Rate</th>\n",
       "      <th>Sex Offence Conviction Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010</td>\n",
       "      <td>58.24</td>\n",
       "      <td>68.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011</td>\n",
       "      <td>72.09</td>\n",
       "      <td>85.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2012</td>\n",
       "      <td>67.39</td>\n",
       "      <td>63.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013</td>\n",
       "      <td>57.79</td>\n",
       "      <td>60.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014</td>\n",
       "      <td>60.24</td>\n",
       "      <td>55.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year of Sentencing  Violent Conviction Rate  Sex Offence Conviction Rate\n",
       "0                2010                    58.24                        68.97\n",
       "1                2011                    72.09                        85.29\n",
       "2                2012                    67.39                        63.33\n",
       "3                2013                    57.79                        60.00\n",
       "4                2014                    60.24                        55.32"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "violent_conviction_rate_df = violent_counts_by_year['Violent Conviction Rate'].to_frame(name='Violent Conviction Rate').round(2)\n",
    "sex_offence_conviction_rate_df = sexoffence_counts_by_year['Sex Offence Conviction Rate'].to_frame(name='Sex Offence Conviction Rate').round(2)\n",
    "\n",
    "violent_conviction_rate_df.reset_index(inplace=True)\n",
    "sex_offence_conviction_rate_df.reset_index(inplace=True)\n",
    "\n",
    "combined_conviction_rates_df = pd.merge(violent_conviction_rate_df, sex_offence_conviction_rate_df, on='Year of Sentencing')\n",
    "\n",
    "combined_conviction_rates_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b3e5459",
   "metadata": {},
   "source": [
    "Now, like with the second notebook, our dataframe is much more easily visualised in D3 if it is a long form, \"melted\" dataframe  that goes through each 'category' (here: Violent Conviction Rate and Sex Offence Conviction Rate') one by one. I melt the columns to reflect this and also rename the columns to something more simple for my Javascript code: 'year', 'category', 'value'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "19a1b9b5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   year                 category  value\n",
      "0  2010  Violent Conviction Rate  58.24\n",
      "1  2011  Violent Conviction Rate  72.09\n",
      "2  2012  Violent Conviction Rate  67.39\n",
      "3  2013  Violent Conviction Rate  57.79\n",
      "4  2014  Violent Conviction Rate  60.24\n"
     ]
    }
   ],
   "source": [
    "long_format_df = pd.melt(combined_conviction_rates_df, id_vars=['Year of Sentencing'],\n",
    "                         value_vars=['Violent Conviction Rate', 'Sex Offence Conviction Rate'],\n",
    "                         var_name='category', value_name='value')\n",
    "\n",
    "long_format_df.rename(columns={'Year of Sentencing': 'year'}, inplace=True)\n",
    "\n",
    "\n",
    "print(long_format_df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d95146be",
   "metadata": {},
   "source": [
    "Export this CSV, which is now ready to be turned into the second visualisation!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "decfdcc9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data exported successfully to 'long_format_conviction_rates.csv'\n"
     ]
    }
   ],
   "source": [
    "long_format_df.to_csv('long_format_conviction_rates.csv', index=False)\n",
    "print(\"Data exported successfully to 'long_format_conviction_rates.csv'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c64c9af9",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}