Argo-USCore-Profile-Comparison-old.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6405b704-c423-4b1c-b441-9d84a7adb82a",
   "metadata": {
    "tags": []
   },
   "source": [
    "#  Compare Dataframes by key using pandas\n",
    "\n",
    "Compare dataframes by a key:\n",
    "\n",
    "1. \"normalize both dataframe to same shape to compare with the df compare function\n",
    "      - merge dataframe key columns together and indicate where they came from\n",
    "      - concat unique rows from one data frame to the other so the number of rows is the same\n",
    "      - sort based on key\n",
    "1. use data frame compare function to compare rows\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3929a456-3143-4d70-bf29-a3f0a324fffb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import itertools\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3ca416e4-7bf1-436a-92a1-798bb0533f41",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "argo_path = 'out/csv'\n",
    "uscore_path = '/Users/ehaas/Documents/FHIR/US-Core/output/'\n",
    "out_path_csv = 'out/compare_csv' \n",
    "out_path_excel = 'out/compare_excel'\n",
    "#image_path = '/Users/ehaas/Documents/FHIR/US-Core/input/images'\n",
    "image_path = 'out/image'\n",
    "my_key = 'Path_id'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f138ffdc-b258-4c81-afed-1c61d83b3069",
   "metadata": {
    "tags": []
   },
   "source": [
    "### clean up directory first "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "964bf6c7-dd28-4f88-88fe-546b580f745f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "path = Path() / out_path_csv\n",
    "path_list =  path.glob(\"*.csv\")\n",
    "[f.unlink() for f in path_list if f.is_file()]\n",
    "path = Path() / out_path_excel\n",
    "path_list =  path.glob(\"*.xlsx\")\n",
    "[f.unlink() for f in path_list if f.is_file()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c8c5d6a-2b33-42b4-9213-c14a7870cba7",
   "metadata": {},
   "source": [
    "### Manual Create Profile Map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9663184-f655-4542-af60-fb3d5a035dba",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "file_map = {\n",
    "'StructureDefinition-argo-device':'StructureDefinition-us-core-implantable-device',\n",
    "'StructureDefinition-argo-immunization':'StructureDefinition-us-core-immunization',\n",
    "#'StructureDefinition-argo-medicationstatement':'StructureDefinition-us-core-medicationstatement',\n",
    "'StructureDefinition-argo-ethnicity':'StructureDefinition-us-core-ethnicity',\n",
    "'StructureDefinition-argo-medication':'StructureDefinition-us-core-medication',\n",
    "'StructureDefinition-argo-careteam':'StructureDefinition-us-core-careteam',\n",
    "'StructureDefinition-argo-medicationorder':'StructureDefinition-us-core-medicationrequest',\n",
    "'StructureDefinition-argo-vitalsigns':'StructureDefinition-us-core-vital-signs',\n",
    "'StructureDefinition-argo-goal':'StructureDefinition-us-core-goal',\n",
    "'StructureDefinition-argo-birthsex':'StructureDefinition-us-core-birthsex',\n",
    "#'StructureDefinition-argo-profile-link':'StructureDefinition-us-core-profile-link',\n",
    "'StructureDefinition-argo-procedure':'StructureDefinition-us-core-procedure',\n",
    "'StructureDefinition-argo-observationresults':'StructureDefinition-us-core-observation-lab',\n",
    "'StructureDefinition-argo-smokingstatus':'StructureDefinition-us-core-smokingstatus',\n",
    "'StructureDefinition-argo-diagnosticreport':'StructureDefinition-us-core-diagnosticreport-lab',\n",
    "'StructureDefinition-argo-condition':'StructureDefinition-us-core-condition-problems-health-concerns',\n",
    "#'StructureDefinition-argo-resource-documentation':'StructureDefinition-us-core-resource-documentation',\n",
    "'StructureDefinition-argo-documentreference':'StructureDefinition-us-core-documentreference',\n",
    "'StructureDefinition-argo-race':'StructureDefinition-us-core-race',\n",
    "'StructureDefinition-argo-patient':'StructureDefinition-us-core-patient',\n",
    "# 'StructureDefinition-argo-careplan':'StructureDefinition-us-core-careplan',\n",
    "}   \n",
    "\n",
    "# df1 = pd.read_csv(r'test_files/StructureDefinition-C4BB-Coverage.csv')\n",
    "# df2 = pd.read_csv(r'test_files/StructureDefinition-hrex-coverage.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03f90a85-4b96-41a4-a556-1e84a49f54a1",
   "metadata": {},
   "source": [
    "### Normalize Both Dataframe To Same Shape To Compare With The Df Compare Function\n",
    "\n",
    "- save as csv and excel in out folders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05c3ee7c-c0dd-4a96-8443-66ab490c4702",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "for k,v in file_map.items():\n",
    "    print()\n",
    "    print('='*80)\n",
    "    print('='*80)\n",
    "    print(f'comparing {k} with {v}')\n",
    "    df1 = pd.read_csv(Path() / argo_path / f'{k}.csv' )\n",
    "    df1 = df1.loc[:, ~df1.columns.str.contains(\"Mapping\")] #drop mapping columns\n",
    "    df1 = df1.applymap(lambda x: x.strip() if isinstance(x, str) else x) #trim\n",
    "    df1['Path_id'] = df1['Path'] # create unique Path_id\n",
    "    for i, slice in enumerate(df1['Slice Name']):\n",
    "        # print(i, df1.Path_id[i], df1['Slice Name'][i] )\n",
    "        if slice==slice:  # not a NaN\n",
    "           df1['Path_id'][i]=f\"{df1.at[i,'Path_id']}-{df1.at[i,'Slice Name']}\"  # ignore warning for now\n",
    "    #     print(i, df1.Path_id[i], df1['Slice Name'][i] )\n",
    "    #     print()\n",
    "    # with pd.option_context(\"display.max_rows\", 1000):\n",
    "    #    display(df1.Path_id)\n",
    "\n",
    "\n",
    "    df2 = pd.read_csv(Path() / uscore_path / f'{v}.csv' )\n",
    "    df2 = df2.loc[:, ~df2.columns.str.contains(\"Mapping\")] #drop mapping columns\n",
    "    df2 = df2.applymap(lambda x: x.strip() if isinstance(x, str) else x) #trim\n",
    "    df2['Path_id'] = df2['Path'] # create unique Path_id\n",
    "    for i, slice in enumerate(df2['Slice Name']):\n",
    "        # print(i, df2.Path_id[i], df2['Slice Name'][i] )\n",
    "        if slice==slice:  # not a NaN\n",
    "           df2['Path_id'][i]=f\"{df2.at[i,'Path_id']}-{df2.at[i,'Slice Name']}\" # ignore warning for now\n",
    "    #     print(i, df2.Path_id[i], df2['Slice Name'][i] )\n",
    "    #     print()\n",
    "    # with pd.option_context(\"display.max_rows\", 1000):\n",
    "    #     display(df2.Path_id)\n",
    "    # break\n",
    "\n",
    "    print(f'df1.shape={df1.shape}, df2.shape={df2.shape}')\n",
    "    \n",
    "\n",
    "    \n",
    "    df_all = df1[[my_key]].merge(df2[[my_key]], on=my_key, \n",
    "                       how='outer', indicator=True)\n",
    "\n",
    "    both = df_all.query(\"_merge == 'both'\")\n",
    "    left_only = df_all.query('_merge == \"left_only\"')\n",
    "    right_only = df_all.query('_merge == \"right_only\"')\n",
    "\n",
    "    #print(f'df_all=\\n{df_all}')\n",
    "    print(f'both={both.shape}')\n",
    "    print(f'Argo DQ only={left_only.shape}')\n",
    "    print(f'US Core only={right_only.shape}')\n",
    "\n",
    "    df1_new = pd.concat([df1,right_only], ignore_index=True)\n",
    "    df1_new = df1_new.set_index(my_key)\n",
    "    df1_new = df1_new.sort_index()\n",
    "\n",
    "    #print(df1_new.head())\n",
    "\n",
    "    df2_new = pd.concat([df2,left_only], ignore_index=True)\n",
    "    df2_new = df2_new.set_index(my_key)\n",
    "    df2_new = df2_new.sort_index()\n",
    "\n",
    "    #print(df2_new.head())\n",
    "    print(f'df1_new.shape={df1_new.shape}, df2_new.shape={df2_new.shape}')\n",
    "    \n",
    "    try:\n",
    "        df_compare = df1_new.compare(df2_new, align_axis = 1)\n",
    "    except ValueError as e:\n",
    "        print('='*80)\n",
    "        print(e)\n",
    "        print('='*80)\n",
    "        print('argo','us-core')\n",
    "        \n",
    "        print(df1_new.columns == df2_new.columns)\n",
    "        print('df2_new', df2_new.shape)\n",
    "        \n",
    "        for i, my_index in enumerate(df2_new.index):\n",
    "            try:\n",
    "             print(i, my_index, df1_new.index[i])\n",
    "            except:\n",
    "                print('is this the problem?', i,my_index)\n",
    "    print(\"k\", k, type(k))\n",
    "    print(\"v\", v, type(v))\n",
    "\n",
    "    #df_compare.head()\n",
    "    print(f\"printing {out_path_excel}/compare-{k.replace('StructureDefinition-','')}-{v.replace('StructureDefinition-','')}.xlsx...\")\n",
    "    df_compare.to_excel(f\"{out_path_excel}/compare-{k.replace('StructureDefinition-','')}-{v.replace('StructureDefinition-','')}.xlsx\")\n",
    "    print(f\"printing {out_path_csv}/compare-{k.replace('StructureDefinition-','')}-{v.replace('StructureDefinition-','')}.csv...\")\n",
    "    df_compare.to_csv(f\"{out_path_csv}/compare-{k.replace('StructureDefinition-','')}-{v.replace('StructureDefinition-','')}.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dbb05ee8-de7a-4886-8f3d-d5df434555df",
   "metadata": {},
   "source": [
    "### Compress Output Folders to Zip files \n",
    "\n",
    "- includes a static README.txt file explaining how to read"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "db84c732-6dd9-4189-a8ba-8ad5e13cade3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=================================================================\n",
      "===zip up csv and excel files and put in===\n",
      "===out/image/argo-compare.zips file for downloads===\n",
      "=================================================================\n",
      "updating: compare-argo-birthsex-us-core-birthsex.xlsx (deflated 8%)\n",
      "updating: compare-argo-careteam-us-core-careteam.xlsx (deflated 5%)\n",
      "updating: compare-argo-condition-us-core-condition-problems-health-concerns.xlsx (deflated 6%)\n",
      "updating: compare-argo-device-us-core-implantable-device.xlsx (deflated 7%)\n",
      "updating: compare-argo-diagnosticreport-us-core-diagnosticreport-lab.xlsx (deflated 5%)\n",
      "updating: compare-argo-documentreference-us-core-documentreference.xlsx (deflated 8%)\n",
      "updating: compare-argo-ethnicity-us-core-ethnicity.xlsx (deflated 8%)\n",
      "updating: compare-argo-goal-us-core-goal.xlsx (deflated 5%)\n",
      "updating: compare-argo-immunization-us-core-immunization.xlsx (deflated 8%)\n",
      "updating: compare-argo-medication-us-core-medication.xlsx (deflated 7%)\n",
      "updating: compare-argo-medicationorder-us-core-medicationrequest.xlsx (deflated 4%)\n",
      "updating: compare-argo-observationresults-us-core-observation-lab.xlsx (deflated 6%)\n",
      "updating: compare-argo-patient-us-core-patient.xlsx (deflated 8%)\n",
      "updating: compare-argo-procedure-us-core-procedure.xlsx (deflated 7%)\n",
      "updating: compare-argo-race-us-core-race.xlsx (deflated 8%)\n",
      "updating: compare-argo-smokingstatus-us-core-smokingstatus.xlsx (deflated 6%)\n",
      "updating: compare-argo-vitalsigns-us-core-vital-signs.xlsx (deflated 5%)\n",
      "  adding: README.md (deflated 47%)\n",
      "updating: compare-argo-birthsex-us-core-birthsex.csv (deflated 72%)\n",
      "updating: compare-argo-careteam-us-core-careteam.csv (deflated 81%)\n",
      "updating: compare-argo-condition-us-core-condition-problems-health-concerns.csv (deflated 81%)\n",
      "updating: compare-argo-device-us-core-implantable-device.csv (deflated 84%)\n",
      "updating: compare-argo-diagnosticreport-us-core-diagnosticreport-lab.csv (deflated 80%)\n",
      "updating: compare-argo-documentreference-us-core-documentreference.csv (deflated 84%)\n",
      "updating: compare-argo-ethnicity-us-core-ethnicity.csv (deflated 91%)\n",
      "updating: compare-argo-goal-us-core-goal.csv (deflated 79%)\n",
      "updating: compare-argo-immunization-us-core-immunization.csv (deflated 85%)\n",
      "updating: compare-argo-medication-us-core-medication.csv (deflated 85%)\n",
      "updating: compare-argo-medicationorder-us-core-medicationrequest.csv (deflated 81%)\n",
      "updating: compare-argo-observationresults-us-core-observation-lab.csv (deflated 81%)\n",
      "updating: compare-argo-patient-us-core-patient.csv (deflated 83%)\n",
      "updating: compare-argo-procedure-us-core-procedure.csv (deflated 82%)\n",
      "updating: compare-argo-race-us-core-race.csv (deflated 90%)\n",
      "updating: compare-argo-smokingstatus-us-core-smokingstatus.csv (deflated 81%)\n",
      "updating: compare-argo-vitalsigns-us-core-vital-signs.csv (deflated 80%)\n",
      "  adding: README.md (deflated 47%)\n"
     ]
    }
   ],
   "source": [
    "%%bash -s \"$out_path_excel\" \"$out_path_csv\" \"$image_path\"\n",
    "echo \"=================================================================\"\n",
    "echo \"===zip up csv and excel files and put in===\"\n",
    "echo \"===$3/argo-compare.zips file for downloads===\"\n",
    "echo \"=================================================================\"\n",
    "zip -j $3/argo-compare-excel.zip $1/*.*\n",
    "zip -j $3/argo-compare-csv.zip $2/*.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aff9b598-ef01-4b08-945b-df59a9836fb8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}