csv-json-expansion.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3ebb5ad2",
   "metadata": {},
   "source": [
    "# CSV file with JSON columns\n",
    "\n",
    "This notebook will take a csv file with one or more columns in json format and expand those json columns. I use this notebook to process some O365 Security and Compliance log files in the absence of the higher-tiered licensing that provide in-place analysis.\n",
    "\n",
    "#### Requirements: The CSV file should have headers. Each cell within a JSON column should contain the same fields/names. Non-integer row indices are okay. Row indices are preserved."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31a17929",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "290c21c9",
   "metadata": {},
   "source": [
    "### The following cell identifies the CSV file to be processed\n",
    "\n",
    "You can statically specify your file in the leading section or else enter it when prompted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1faab88",
   "metadata": {},
   "outputs": [],
   "source": [
    "####################\n",
    "#ENTER YOUR DATA HERE: Path to csv file\n",
    "#If file path is not statically specified here, the user will be prompted on execution of cell.\n",
    "csv_file_path = \"\"\n",
    "####################\n",
    "\n",
    "file_found = False\n",
    "while file_found == False:\n",
    "\n",
    "    #Prompt user for file path and name if not entered above statically\n",
    "    if csv_file_path == \"\":\n",
    "        print(\"Please specify the path and filename of your csv file:\")\n",
    "        csv_file_path = input()\n",
    "\n",
    "    #Convert csv file into a dataframe\n",
    "    try:\n",
    "        csv_df = pd.read_csv(csv_file_path)\n",
    "        file_found = True\n",
    "        break\n",
    "    except FileNotFoundError:\n",
    "        print(\"File not found: Please check your csv file path and name and rerun this cell.\")\n",
    "    except pd.errors.EmptyDataError:\n",
    "        print(\"Data is Empty: Please check the contents of your file and rerun this cell.\")\n",
    "    except pd.errors.ParserError:\n",
    "        print(\"Parsing Error: Please check the format of your file contents and rerun this cell.\")\n",
    "    csv_file_path = \"\"\n",
    "    \n",
    "if csv_df.shape[0] < 1:\n",
    "    print(\"Zero rows found. Please ensure your csv file has data in its rows.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33b20b61",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Identify columns and preview dataframe\n",
    "columns = csv_df.columns\n",
    "print(columns)\n",
    "csv_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fe8e8a9",
   "metadata": {},
   "source": [
    "### The following cell determines which columns contain JSON and should be expanded\n",
    "\n",
    "You can statically specify the columns, allow the code to autodect (based on the contents of the first row), or indicate column by column which to classify as json columns.\n",
    "\n",
    "If you leave json_columns as an empty array, you will be presented with the columns that were autodetected as json and you can press enter (submit an empty response to the input request) to accept that default and continue. If you submit a non-empty response, you will be prompted to identify each column. You may also consider stopping the cell after the columns have been autodetected so you can copy and paste the columns into the leading section if you have only a small tweak to make."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bd288c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "####################\n",
    "#ENTER YOUR DATA HERE: Specify json columns as a list of column names (strings).\n",
    "#Columns are printed from the previous cell for review. User will be prompted if not specified here.\n",
    "json_columns = []\n",
    "####################\n",
    "\n",
    "if len(json_columns) == 0:\n",
    "    for col in columns:\n",
    "        if type(csv_df.iloc[0][col]) == str:\n",
    "            try:\n",
    "                json.loads(csv_df.iloc[0][col])\n",
    "                json_columns.append(col)\n",
    "            except json.JSONDecodeError: \n",
    "                continue\n",
    "print(\"The following have been identified as json columns. Please rerun this cell if not correct.\")\n",
    "print(json_columns)\n",
    "print(\"Press Enter to accept, otherwise enter any character to manually choose columns instead.\")\n",
    "manual_select = input()\n",
    "if manual_select != \"\":\n",
    "    json_columns = []\n",
    "    #Prompt user to identify json columns if not identified statically above\n",
    "    print(\"For each column, enter 1 to identify a json column and 0 otherwise.\")\n",
    "    for col in columns:\n",
    "        print(col,end=\": \")\n",
    "        response = input()\n",
    "        if response == '1':\n",
    "            json_columns.append(col)\n",
    "    print(\"The following have been identified as json columns. Please rerun this cell if not correct.\")\n",
    "    print(json_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a9061fe",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#Construct a new column list with extra columns to accomodate the json expansions\n",
    "new_columns = []\n",
    "for col in columns:\n",
    "    if col in json_columns:\n",
    "        json_string = csv_df.iloc[0][col]\n",
    "        example = json.loads(json_string)\n",
    "        sub_columns = list(example.keys())\n",
    "        for sub_col in sub_columns:\n",
    "            #Prefix columns derived from json expansion with original column name\n",
    "            new_columns.append(col+\"_\"+sub_col)\n",
    "    else:\n",
    "        new_columns.append(col)\n",
    "\n",
    "#Construct an empty dataframe with new column names\n",
    "json_expanded_df = pd.DataFrame(columns = new_columns)\n",
    "json_expanded_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e92755c",
   "metadata": {},
   "outputs": [],
   "source": [
    "new_row = []\n",
    "for index, row in csv_df.iterrows():\n",
    "    for col, value in csv_df.loc[index].items():\n",
    "        if col in json_columns:\n",
    "            new_row.extend(list(json.loads(value).values())) \n",
    "        else:\n",
    "            new_row.append(value)\n",
    "    json_expanded_df.loc[index] = new_row\n",
    "    new_row = []\n",
    "    \n",
    "json_expanded_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5710f24a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append _json_expanded to original filename\n",
    "if len(csv_file_path) > 4 and csv_file_path[len(csv_file_path)-4:] == \".csv\":\n",
    "    csv_json_expanded_path = csv_file_path[:len(csv_file_path)-4]+'_json_expanded.csv'\n",
    "else:\n",
    "    print('There was a problem modifying the file path. The csv file will be saved in the root directory for jupyter notebooks as csv_json_expanded.csv instead.')\n",
    "    csv_json_expanded_path = 'csv_json_expanded.csv'\n",
    "\n",
    "json_expanded_df.to_csv(csv_json_expanded_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3388a6ee",
   "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}