GCAF_codelist_Distribution.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!/usr/bin/env python\n",
    "# -*-coding:utf-8 -*-\n",
    "'''\n",
    "@File    :   GCAF_codelist_Distribution.ipynb\n",
    "@Time    :   2024/09/17 13:14:07\n",
    "@Author  :   Asra Aslam \n",
    "@Version :   1.0\n",
    "@Contact :   a.aslam2@leeds.ac.uk\n",
    "@License :   (C)Copyright Asra Aslam DynAIRX\n",
    "@Desc    :   None\n",
    "'''\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>eFI2 Deficits</th>\n",
       "      <th>Comments_eFI</th>\n",
       "      <th>Liverpool Diseases</th>\n",
       "      <th>Comments_LW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>'Abdominal pain',</td>\n",
       "      <td>Liverpool list doesn't include chronic abdo pa...</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>'Activity limitation',</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Abdominal Hernia</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'Alcohol',</td>\n",
       "      <td>I would prefer to have distinct lists for alco...</td>\n",
       "      <td>Acne</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>'Anaemia &amp; haematinic deficiency',</td>\n",
       "      <td>I would prefer to separate these out and have ...</td>\n",
       "      <td>Acoustic Neuroma</td>\n",
       "      <td>benign tumour - we could put all benign tumour...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>'Anxiety',</td>\n",
       "      <td>need to compare code lists for consistency.</td>\n",
       "      <td>Actinic keratosis</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Urolithiasis</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Uterovaginal Genital Prolapse</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Valve disorder non-rheumatic multiple</td>\n",
       "      <td>can merge all the valve disorder lists</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Visual Impairment and Blindness</td>\n",
       "      <td>compare with eFI visual impairment list and me...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vitiligo</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>182 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           eFI2 Deficits  \\\n",
       "0                      'Abdominal pain',   \n",
       "1                 'Activity limitation',   \n",
       "2                             'Alcohol',   \n",
       "3     'Anaemia & haematinic deficiency',   \n",
       "4                             'Anxiety',   \n",
       "..                                   ...   \n",
       "177                                  NaN   \n",
       "178                                  NaN   \n",
       "179                                  NaN   \n",
       "180                                  NaN   \n",
       "181                                  NaN   \n",
       "\n",
       "                                          Comments_eFI  \\\n",
       "0    Liverpool list doesn't include chronic abdo pa...   \n",
       "1                                                  NaN   \n",
       "2    I would prefer to have distinct lists for alco...   \n",
       "3    I would prefer to separate these out and have ...   \n",
       "4         need to compare code lists for consistency.    \n",
       "..                                                 ...   \n",
       "177                                                NaN   \n",
       "178                                                NaN   \n",
       "179                                                NaN   \n",
       "180                                                NaN   \n",
       "181                                                NaN   \n",
       "\n",
       "                        Liverpool Diseases  \\\n",
       "0                Abdominal Aortic Aneurysm   \n",
       "1                         Abdominal Hernia   \n",
       "2                                     Acne   \n",
       "3                         Acoustic Neuroma   \n",
       "4                        Actinic keratosis   \n",
       "..                                     ...   \n",
       "177                           Urolithiasis   \n",
       "178          Uterovaginal Genital Prolapse   \n",
       "179  Valve disorder non-rheumatic multiple   \n",
       "180        Visual Impairment and Blindness   \n",
       "181                               Vitiligo   \n",
       "\n",
       "                                           Comments_LW  \n",
       "0        Keep this - not currently covered by eFI list  \n",
       "1        Keep this - not currently covered by eFI list  \n",
       "2        Keep this - not currently covered by eFI list  \n",
       "3    benign tumour - we could put all benign tumour...  \n",
       "4        Keep this - not currently covered by eFI list  \n",
       "..                                                 ...  \n",
       "177      Keep this - not currently covered by eFI list  \n",
       "178      Keep this - not currently covered by eFI list  \n",
       "179             can merge all the valve disorder lists  \n",
       "180  compare with eFI visual impairment list and me...  \n",
       "181      Keep this - not currently covered by eFI list  \n",
       "\n",
       "[182 rows x 4 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read Excel file\n",
    "df_comments = pd.read_excel('Clincians_Intervention_Split_Keep_Group.xlsx')\n",
    "df_comments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to separate column1 values based on keywords in column2\n",
    "def separate_by_keywords(row):\n",
    "    # Categorize Column1 based on Column2\n",
    "    if pd.isna(row['Comments_eFI']):\n",
    "        group1 = 'Other'\n",
    "    elif 'compare'.lower() in str(row['Comments_eFI']).lower():\n",
    "        group1 = 'Combine'\n",
    "    elif 'keep'.lower() or 'include'.lower() in str(row['Comments_eFI']).lower():\n",
    "        group1 = 'Keep'\n",
    "    elif 'split'.lower() in str(row['Comments_eFI']).lower():\n",
    "        group1 = 'Split'\n",
    "    elif 'group'.lower() in str(row['Comments_eFI']).lower():\n",
    "        group1 = 'Combine'\n",
    "    else:\n",
    "        group1 = 'Other'\n",
    "    \n",
    "    group2 = 'Other'  # Default value for group2 in case no condition matches\n",
    "    # Categorize Column3 based on Column4\n",
    "    if pd.isna(row['Comments_LW']):\n",
    "        group2 = 'Other'\n",
    "    elif 'compare'.lower() in str(row['Comments_eFI']).lower():\n",
    "        group1 = 'Combine'\n",
    "    elif 'keep'.lower() in str(row['Comments_LW']).lower():\n",
    "        group2 = 'Keep'\n",
    "    elif 'split'.lower() in str(row['Comments_LW']).lower():\n",
    "        group2 = 'Split'\n",
    "    elif 'group'.lower() in str(row['Comments_LW']).lower():\n",
    "        group2 = 'Combine'\n",
    "    else:\n",
    "        group2 = 'Other'\n",
    "    \n",
    "    return row['eFI2 Deficits'], row['Comments_eFI'], group1, row['Liverpool Diseases'], row['Comments_LW'], group2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>eFI2 Deficits</th>\n",
       "      <th>Comments_eFI</th>\n",
       "      <th>Instructions_for_eFI</th>\n",
       "      <th>Liverpool Diseases</th>\n",
       "      <th>Comments_LW</th>\n",
       "      <th>Instructions_for_LW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>'Abdominal pain',</td>\n",
       "      <td>Liverpool list doesn't include chronic abdo pa...</td>\n",
       "      <td>Keep</td>\n",
       "      <td>Abdominal Aortic Aneurysm</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Keep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>'Activity limitation',</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Abdominal Hernia</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Keep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'Alcohol',</td>\n",
       "      <td>I would prefer to have distinct lists for alco...</td>\n",
       "      <td>Keep</td>\n",
       "      <td>Acne</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Keep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>'Anaemia &amp; haematinic deficiency',</td>\n",
       "      <td>I would prefer to separate these out and have ...</td>\n",
       "      <td>Keep</td>\n",
       "      <td>Acoustic Neuroma</td>\n",
       "      <td>benign tumour - we could put all benign tumour...</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>'Anxiety',</td>\n",
       "      <td>need to compare code lists for consistency.</td>\n",
       "      <td>Combine</td>\n",
       "      <td>Actinic keratosis</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Urolithiasis</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Keep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Uterovaginal Genital Prolapse</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Keep</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Valve disorder non-rheumatic multiple</td>\n",
       "      <td>can merge all the valve disorder lists</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Visual Impairment and Blindness</td>\n",
       "      <td>compare with eFI visual impairment list and me...</td>\n",
       "      <td>Other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>181</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Vitiligo</td>\n",
       "      <td>Keep this - not currently covered by eFI list</td>\n",
       "      <td>Keep</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>182 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           eFI2 Deficits  \\\n",
       "0                      'Abdominal pain',   \n",
       "1                 'Activity limitation',   \n",
       "2                             'Alcohol',   \n",
       "3     'Anaemia & haematinic deficiency',   \n",
       "4                             'Anxiety',   \n",
       "..                                   ...   \n",
       "177                                  NaN   \n",
       "178                                  NaN   \n",
       "179                                  NaN   \n",
       "180                                  NaN   \n",
       "181                                  NaN   \n",
       "\n",
       "                                          Comments_eFI Instructions_for_eFI  \\\n",
       "0    Liverpool list doesn't include chronic abdo pa...                 Keep   \n",
       "1                                                  NaN                Other   \n",
       "2    I would prefer to have distinct lists for alco...                 Keep   \n",
       "3    I would prefer to separate these out and have ...                 Keep   \n",
       "4         need to compare code lists for consistency.               Combine   \n",
       "..                                                 ...                  ...   \n",
       "177                                                NaN                Other   \n",
       "178                                                NaN                Other   \n",
       "179                                                NaN                Other   \n",
       "180                                                NaN                Other   \n",
       "181                                                NaN                Other   \n",
       "\n",
       "                        Liverpool Diseases  \\\n",
       "0                Abdominal Aortic Aneurysm   \n",
       "1                         Abdominal Hernia   \n",
       "2                                     Acne   \n",
       "3                         Acoustic Neuroma   \n",
       "4                        Actinic keratosis   \n",
       "..                                     ...   \n",
       "177                           Urolithiasis   \n",
       "178          Uterovaginal Genital Prolapse   \n",
       "179  Valve disorder non-rheumatic multiple   \n",
       "180        Visual Impairment and Blindness   \n",
       "181                               Vitiligo   \n",
       "\n",
       "                                           Comments_LW Instructions_for_LW  \n",
       "0        Keep this - not currently covered by eFI list                Keep  \n",
       "1        Keep this - not currently covered by eFI list                Keep  \n",
       "2        Keep this - not currently covered by eFI list                Keep  \n",
       "3    benign tumour - we could put all benign tumour...               Other  \n",
       "4        Keep this - not currently covered by eFI list               Other  \n",
       "..                                                 ...                 ...  \n",
       "177      Keep this - not currently covered by eFI list                Keep  \n",
       "178      Keep this - not currently covered by eFI list                Keep  \n",
       "179             can merge all the valve disorder lists               Other  \n",
       "180  compare with eFI visual impairment list and me...               Other  \n",
       "181      Keep this - not currently covered by eFI list                Keep  \n",
       "\n",
       "[182 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Apply the function to create new columns for Group1 (Column1 based on Column2) and Group2 (Column3 based on Column4)\n",
    "df_comments[['eFI2 Deficits', 'Comments_eFI', 'Instructions_for_eFI', 'Liverpool Diseases', 'Comments_LW', 'Instructions_for_LW']] = df_comments.apply(separate_by_keywords, axis=1, result_type=\"expand\")\n",
    "\n",
    "\n",
    "# # Define the new column order\n",
    "new_order = ['eFI2 Deficits', 'Comments_eFI', 'Instructions_for_eFI', 'Liverpool Diseases', 'Comments_LW', 'Instructions_for_LW']\n",
    "# # Reorder the DataFrame columns\n",
    "df_comments_with_automatic_instructions = df_comments[new_order]\n",
    "\n",
    "# View the updated DataFrame\n",
    "df_comments_with_automatic_instructions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the result to a new Excel file\n",
    "df_comments_with_automatic_instructions.to_excel('Output/GCAF_Generated_Distribution.xlsx', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}