Session 4 - Unstructured Files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Session 4 -- Unstructured Files\n",
    "If you take a look at our data files for Session 4, you will see that our data is not neatly structured as it has been until now. Unfortunately, this is very common. But fear not, as we can programmatically handle these as shape the data to be nicely structured."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Exercise 1\n",
    "In Exercise 1, we will only be dealing with metadata. Our goal will be to extract the metadata that resides above the data table, and just for some added utility, we will add in some of our own user-set metadata as well as some calculations.  \n",
    "![Exercise 1](img/04_exercise1goal.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "First we'll import packages. We will need `pandas` and `os` as usual, and as mentioned above, we'll do some calculations, so we will need the `math` package as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import math"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "We'll just deal with one input file first. Let's get the path to one of the files in our sample data for Exercise 1:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "infile = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise1\\111ALGN.CSV\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Next let's read that data into a `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df0 = pd.read_csv(infile)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "You'll see that our `DataFrame` doesn't quite look right..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "So let's deal with that data at the top first. It looks like the first 6 rows is metadata, so let's get those 6 rows first. Notice that these aren't in a format where the first row is column names with data beneath it, and since `read_csv` defaults that first row to column headers, we can specify that we do not have a header row to read:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df0 = pd.read_csv(infile,header=None,nrows=6)\n",
    "df0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "See now that the column names are just numbers now."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "There's a couple of ways to extract the data here. My preferred way is to extract the desired data using the locations in the `DataFrame` for maximum readability. We can use this using the `iloc[row,column]` function. I like to store these in a `dict`ionary just to keep the data organized and in its own structure, so we will first create an empty `dict`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "metadata = {}\n",
    "metadata['Date'] = df0.iloc[0,1]\n",
    "metadata['Time'] = df0.iloc[1,1]\n",
    "metadata['Wafer ID'] = df0.iloc[2,1]\n",
    "metadata['Amount'] = df0.iloc[3,1]\n",
    "metadata['Phase'] = df0.iloc[4,1]\n",
    "metadata['OrfDir'] = df0.iloc[5,1]\n",
    "metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Since our metadata is so neatly organized, we can do this using a loop as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "metadata_loop = {}\n",
    "for x in range(6):\n",
    "    metadata_loop[df0.iloc[x,0]] = df0.iloc[x,1]\n",
    "metadata_loop"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Alternatively, we can also transpose the `df0` to match a structured format. We can use the `DataFrame` method `transpose`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df1 = df0.transpose()\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Now that this `DataFrame` is in the right shape, we can set the column names to match the first row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df1.columns = df1.iloc[0]\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Now that the headers are correct, we will want to delete that extra first row of \"data\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df1 = df1.drop([0])\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And finally, you'll see that the row index on the left is messed up -- that row of data should be row 0. We can fix this by resetting the index with the `reset_index` method. The default behavior of this method is to just slap on a new index column, but by specifying `drop=True` in the parenthesis, we can make it replace the index column that already exists:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df1 = df1.reset_index(drop=True)\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Note:\n",
    "Note that in order to do the looping `dict` method and the `transpose` method, the data has to be in a relatively organized format. This is not always the case, so be careful of that."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Let's do a quick show of throwing in some calculations. Before we do the actual math, we have to do some conversions. We'll preserve our original data by creating new key/value pairs and columns.\n",
    "\n",
    "In the `dict` method, we have `str` types which we have to convert to float:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "type(metadata[\"Amount\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "metadata['Amount_num'] = float(metadata['Amount'])\n",
    "metadata['Phase_num'] = float(metadata['Phase'])\n",
    "metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "...and in the `DataFrame` method, we have a `Series` object, which we have to conver to `str` and then to `float`. Since a `Series` is not a built-in type of Python, we have to use the `pandas` conversion method `astype`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "type(df1['Amount'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df1['Amount_num'] = df1['Amount'].astype(str).astype(float)\n",
    "df1['Phase_num'] = df1['Phase'].astype(str).astype(float)\n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And finally, let's do the actual conversions. These are some predetermined formulas, so no need to actually know them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "metadata['RealAngle'] = 360 - metadata['Phase_num']\n",
    "metadata['x_offset'] = metadata['Amount_num'] * math.cos(metadata['RealAngle']*(math.pi/180))\n",
    "metadata['y_offset'] = metadata['Amount_num'] * math.sin(metadata['RealAngle']*(math.pi/180))\n",
    "metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Or if using the tranpose version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df1['RealAngle'] = 360 - df1['Phase_num']\n",
    "df1['x_offset'] = df1['Amount_num'] * math.cos(df1['RealAngle']*(math.pi/180))\n",
    "df1['y_offset'] = df1['Amount_num'] * math.sin(df1['RealAngle']*(math.pi/180)) \n",
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And to get the final output that we saw at the beginning of the exercise, we would just use `insert` commands on some user-defined metadata to tack onto `df1`, or in the case of the `dict` method, `pandas` has a function to convert a `dict` into a `DataFrame` format. We'll skip demonstrating that for now and go straight into the next level -- going through a folder of files and extracting and making calculations on each file using a loop."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Exercise 1\n",
    "Let's now get the metadata from each file in the Exercise 1 data folder and compile it into a sort of index file, with the metadata from each file on one line of our output file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Import packages -- still just `pandas`, `os`, and `math`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import math"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Get the the input folder and a list of the files inside of it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "inpath = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise1\"\n",
    "inpathfiles = os.listdir(inpath)\n",
    "inpathfiles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Create an empty list to contain all of the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "dfs = []"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Begin the loop -- let's filter to just get the files with \"ALGN\" in the name\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "for filename in inpathfiles:\n",
    "    if \"ALGN\" in filename:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Get the full file path of each file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        filename = inpath + \"\\\\\" + filename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Then, we can go through the whole process we went through above for one file:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "`dict` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df = pd.read_csv(filename,header=None,nrows=6)\n",
    "        metadata = {}\n",
    "        for x in range(6):\n",
    "            metadata[df0.iloc[x,0]] = df0.iloc[x,1]\n",
    "        metadata['Amount_num'] = float(metadata['Amount'])\n",
    "        metadata['Phase_num'] = float(metadata['Phase'])\n",
    "        metadata['RealAngle'] = 360 - metadata['Phase_num']\n",
    "        metadata['x_offset'] = metadata['Amount_num'] * math.cos(metadata['RealAngle']*(math.pi/180))\n",
    "        metadata['y_offset'] = metadata['Amount_num'] * math.sin(metadata['RealAngle']*(math.pi/180))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's that function that is able to create a `DataFrame` from a `dict`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df  = pd.DataFrame([metadata], columns=metadata.keys())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "`transpose` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df = pd.read_csv(filename,header=None,nrows=6)\n",
    "        df = df.transpose()\n",
    "        df.columns = df.iloc[0]\n",
    "        df = df.drop([0])\n",
    "        df = df.reset_index(drop=True)\n",
    "        df['Amount_num'] = df['Amount'].astype(str).astype(float)\n",
    "        df['Phase_num'] = df['Phase'].astype(str).astype(float)\n",
    "        df['RealAngle'] = 360 - df1['Phase_num']\n",
    "        df['x_offset'] = df['Amount_num'] * math.cos(df1['RealAngle']*(math.pi/180))\n",
    "        df['y_offset'] = df['Amount_num'] * math.sin(df1['RealAngle']*(math.pi/180))\n",
    "        dfs.append(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Combine all the collected `DataFrame`s into one:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "result = pd.concat(dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Create some metadata to insert -- let's use \"TNS\" as an equipment name and \"Test\" as a test name. Then insert it into our `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "eqname = \"TNS\"\n",
    "testname = \"Test\"\n",
    "result.insert(0,\"Equipment Name\",eqname)\n",
    "result.insert(1,\"Test Name\", testname)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And finally, go through the steps to output a file -- create the output path, create folders if necessary, create the file name, and create the file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "outpath = inpath + r\"\\output\"\n",
    "if not os.path.exists(outpath):\n",
    "    os.makedirs(outpath)\n",
    "outfile = outpath + r\"\\session4output1.csv\"\n",
    "result.to_csv(outfile,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "The final script should look like the following:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "`dict` version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# import packages -- os, pandas, and math\n",
    "import os\n",
    "import pandas as pd\n",
    "import math\n",
    "\n",
    "# %% Loop through folder and combine\n",
    "# input path\n",
    "inpath = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise1\"\n",
    "# list of files\n",
    "inpathfiles = os.listdir(inpath)\n",
    "\n",
    "# empty list\n",
    "dfs = []\n",
    "\n",
    "# %% loop\n",
    "for filename in inpathfiles:\n",
    "    # condition/filter -- match with \"ALGN\"\n",
    "    if \"ALGN\" in filename:\n",
    "        # append path to filename\n",
    "        filename = inpath + \"\\\\\" + filename\n",
    "        # read header data of file into dataframe\n",
    "        df = pd.read_csv(filename,header=None,nrows=6)\n",
    "        \n",
    "        # empty dict\n",
    "        metadata = {}\n",
    "        # collect data into dict\n",
    "        for x in range(6):\n",
    "            metadata[df.iloc[x,0]] = df.iloc[x,1]\n",
    "            \n",
    "        # convert data to float\n",
    "        metadata['Amount_num'] = float(metadata['Amount'])\n",
    "        metadata['Phase_num'] = float(metadata['Phase'])\n",
    "        \n",
    "        # calculations\n",
    "        metadata['RealAngle'] = 360 - metadata['Phase_num']\n",
    "        metadata['x_offset'] = metadata['Amount_num'] * math.cos(metadata['RealAngle']*(math.pi/180))\n",
    "        metadata['y_offset'] = metadata['Amount_num'] * math.sin(metadata['RealAngle']*(math.pi/180))\n",
    "        \n",
    "        # insert data into DataFrame\n",
    "        df  = pd.DataFrame([metadata], columns=metadata.keys())\n",
    "        \n",
    "        # add data to list\n",
    "        dfs.append(df)\n",
    "        \n",
    "#%% combine data\n",
    "result = pd.concat(dfs)\n",
    "\n",
    "# %% Create metadata variables\n",
    "# EqName: TNS\n",
    "# TestName: Test\n",
    "eqname = \"TNS\"\n",
    "testname = \"Test\"\n",
    "\n",
    "# insert metadata\n",
    "df.insert(0,\"EqName\",eqname)\n",
    "df.insert(1,\"TestName\",testname)\n",
    "\n",
    "# %% output file\n",
    "# set output directory -- create if non-existent\n",
    "outpath = inpath + r\"\\output\"\n",
    "if not os.path.exists(outpath):\n",
    "    os.makedirs(outpath)\n",
    "# create full output path\n",
    "outfile = outpath + r\"\\session4output1dict.csv\"\n",
    "\n",
    "# create ouput file\n",
    "result.to_csv(outfile,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "`transpose` version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# import packages -- os, pandas, and math\n",
    "import os\n",
    "import pandas as pd\n",
    "import math\n",
    "\n",
    "# %% Loop through folder and combine\n",
    "# input path\n",
    "inpath = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise1\"\n",
    "# list of files\n",
    "inpathfiles = os.listdir(inpath)\n",
    "\n",
    "# empty list\n",
    "dfs = []\n",
    "\n",
    "# %% loop\n",
    "for filename in inpathfiles:\n",
    "    # condition/filter -- match with \"ALGN\"\n",
    "    if \"ALGN\" in filename:\n",
    "        # append path to filename\n",
    "        filename = inpath + \"\\\\\" + filename\n",
    "        # read header data of file into dataframe\n",
    "        df = pd.read_csv(filename,header=None,nrows=6)\n",
    "        \n",
    "        # extract column names\n",
    "        # transpose dataframe\n",
    "        df = df.transpose()\n",
    "        # set first row as column names\n",
    "        df.columns = df.iloc[0]\n",
    "        # drop first row\n",
    "        df = df.drop([0])\n",
    "        # reset index\n",
    "        df = df.reset_index(drop=True)\n",
    "        \n",
    "        # calculations\n",
    "        # convert Amount and Phase columns to numeric\n",
    "        df['Amount_num'] = df['Amount'].astype(str).astype(float)\n",
    "        df['Phase_num'] = df['Phase'].astype(str).astype(float)\n",
    "        \n",
    "        # calculate real angle and offsets\n",
    "        df['RealAngle'] = 360 - df['Phase_num']\n",
    "        df['x_offset'] = df['Amount_num'] * math.cos(df['RealAngle']*(math.pi/180))\n",
    "        df['y_offset'] = df['Amount_num'] * math.sin(df['RealAngle']*(math.pi/180))\n",
    "        \n",
    "        # add data to list\n",
    "        dfs.append(df)\n",
    "        \n",
    "#%% combine data\n",
    "result = pd.concat(dfs)\n",
    "\n",
    "# %% Create metadata variables\n",
    "# EqName: TNS\n",
    "# TestName: Test\n",
    "eqname = \"TNS\"\n",
    "testname = \"Test\"\n",
    "\n",
    "# insert metadata\n",
    "result.insert(0,\"EqName\",eqname)\n",
    "result.insert(1,\"TestName\",testname)\n",
    "\n",
    "# %% output file\n",
    "# set output directory -- create if non-existent\n",
    "outpath = inpath + r\"\\output\"\n",
    "if not os.path.exists(outpath):\n",
    "    os.makedirs(outpath)\n",
    "# create full output path\n",
    "outfile = outpath + r\"\\session4output1transpose.csv\"\n",
    "# create ouput file\n",
    "result.to_csv(outfile,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Melting Data\n",
    "Before proceeding to Exercise 2, we are going to go over the concept called \"melting\" data, also known as \"stacking\" data in some applications. This takes a wide dataset and crams it into a vertical stacked format. We'll see how this can be useful as we cover PowerBI in our next session, but essentially it enables data to be easily filtered, like one might use in a pivot table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "For example, a dataset that looks like this:\n",
    "\n",
    "![Pre-melt](img/04_premelt.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "...would look like this after being melted:\n",
    "\n",
    "![Melted](img/04_postmelt.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Exercise 2\n",
    "In exercise 2, we'll step things up a bit more. If you look at our data in the Exercise 2 folder, you'll see that it looks pretty messy -- metadata at the top, multiple datasets placed next to each other, two rows of column data. We'll be extracting that metadata, melting the data, and placing the metadata alongside the melted datatable."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Initializing\n",
    "The first steps should be familiar by now -- import packages, get the input files, and start implementing the loop. We'll filter by \"WaferFlow\" in this exercise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "inpath = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise2\"\n",
    "inpathfiles = os.listdir(inpath)\n",
    "\n",
    "dfs = []\n",
    "\n",
    "for filename in inpathfiles:\n",
    "    if \"WaferFlow\" in filename:\n",
    "        filenamefull = inpath + \"\\\\\" + filename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Next comes the logic we just went through in Exercise 1 to grab the metadata."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "`dict` version -- note that we can just as easily insert `dict` data into a `DataFrame` as we can another `DataFrame`, so we will skip the conversion step in this case:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df_head = pd.read_csv(filenamefull,header=None,nrows=4)\n",
    "        metadata = {}\n",
    "        for x in range(df_head.shape[0]):\n",
    "            metadata[df_head.iloc[x,0]] = df_head.iloc[x,1]        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "`transpose` version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df_head = pd.read_csv(filenamefull,header=None,nrows=4)\n",
    "        df_head = df_head.transpose()\n",
    "        df_head.columns = df_head.iloc[0]\n",
    "        df_head = df_head.drop([0])\n",
    "        df_head = df_head.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Next, let's get the rest of the `DataFrame`. We can do this by using `skiprows` rather than `nrows`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df = pd.read_csv(filenamefull,skiprows=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Next, let's drop that extra row of units that is under the column header names:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        df = df.drop([0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Next, we'll melt the data. `pandas` has a built-in function to do this. `id_vars` is which variables to keep as an index, and the names of the variable and value columns are customizable using `var_name` and `value_name`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        stacked = pd.melt(df, id_vars=[\"sec\"], var_name=\"Variable\",value_name=\"Value\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "The only data that we're interested in this file is actually the numeric values. The `step` column is not useful in this case, as all of them are just \"PreWait\". So let's think of how to just isolate the numbers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        pd.to_numeric(stacked[\"Value\"], errors='coerce')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Now that those values are `NaN`, we can get rid of them using the `notnull` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        pd.to_numeric(stacked[\"Value\"], errors='coerce').notnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "This gives us all the rows not to include in our final cleaned `DataFrame`. So let's create that cleaned `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        stacked_clean = stacked[pd.to_numeric(stacked[\"Value\"], errors='coerce').notnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Now that we have our cleaned `DataFrame`, let's insert the metadata. We can do this using basic `insert` statements. Here's the `dict` version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        stacked_clean.insert(0, 'FileName', filename)\n",
    "        stacked_clean.insert(1, 'Base Lot Name', metadata['Base Lot Name'])\n",
    "        stacked_clean.insert(2, 'Wafer Flow Name', metadata['Wafer Flow Name'])\n",
    "        stacked_clean.insert(3, 'Recipe Step From', metadata['Recipe Step From'])\n",
    "        stacked_clean.insert(4, 'Recipe Step To', metadata['Recipe Step To'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And the `transpose` version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        stacked_clean.insert(0, 'FileName', filename)\n",
    "        stacked_clean.insert(1, 'Base Lot Name', df_head.iloc[0]['Base Lot Name'])\n",
    "        stacked_clean.insert(2, 'Wafer Flow Name', df_head.iloc[0]['Wafer Flow Name'])\n",
    "        stacked_clean.insert(3, 'Recipe Step From', df_head.iloc[0]['Recipe Step From'])\n",
    "        stacked_clean.insert(4, 'Recipe Step To', df_head.iloc[0]['Recipe Step To'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Alternatively, we can do this programmatically by looping through the `dict` if using the `dict` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        for key in metadata:\n",
    "            stacked_clean.insert(0,key,metadata[key])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "...or if using the `transpose` method, loop through a `list` of the columns of `df_head`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        columns = list(df_head.columns)\n",
    "        for x in range(len(columns)):\n",
    "            stacked_clean.insert(x, columns[x],df_head.iloc[0][columns[x]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "We also want to insert the file name, which wasn't part of the metadata, so let's insert that as well:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        stacked_clean.insert(0, 'FileName', filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And finally, let's finish the loop by appending our cleaned `DataFrame` to our list:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "        dfs.append(stacked_clean)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "The rest should be easy -- combine the list into one `DataFrame`, set an output path, and output to a file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "result = pd.concat(dfs)\n",
    "\n",
    "outpath = inpath + \"\\output\"\n",
    "if not os.path.exists(outpath):\n",
    "    os.makedirs(outpath)\n",
    "outfile = outpath + \"\\\\session4output2.csv\"\n",
    "\n",
    "result.to_csv(outfile,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Our final script looks like the following:\n",
    "\n",
    "If using the `dict` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# import packages -- os and pandas\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# %% Loop through folder and combine\n",
    "# input path\n",
    "# escape sequences: \\newline,\\\\,\\',\\\",\\a,\\b,\\f,\\n,\\N,\\r,\\t,\\u,\\U,\\v,\\[0-9],\\x\n",
    "inpath = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise2\"\n",
    "# list of files\n",
    "inpathfiles = os.listdir(inpath)\n",
    "\n",
    "# empty list\n",
    "dfs = []\n",
    "\n",
    "# %% loop\n",
    "for filename in inpathfiles:\n",
    "    # condition/filter -- match with \"WaferFlow\"\n",
    "    if \"WaferFlow\" in filename:\n",
    "        # append path to filename\n",
    "        filenamefull = inpath + \"\\\\\" + filename\n",
    "        \n",
    "        # get metadata\n",
    "        df_head = pd.read_csv(filenamefull,header=None,nrows=4)\n",
    "        metadata = {}\n",
    "        for x in range(df_head.shape[0]):\n",
    "            metadata[df_head.iloc[x,0]] = df_head.iloc[x,1]      \n",
    "        \n",
    "        # read body of file into dataframe\n",
    "        df = pd.read_csv(filenamefull,skiprows=5)\n",
    "        \n",
    "        # delete units row\n",
    "        df = df.drop([0])\n",
    "        \n",
    "        # EXTRA EXPLANATION: create stacked dataframe -- NEW\n",
    "        stacked = pd.melt(df, id_vars=[\"sec\"], var_name=\"Variable\",value_name=\"Value\")\n",
    "        # remove nulls -- NEW\n",
    "        stacked_clean = stacked[pd.to_numeric(stacked[\"Value\"], errors='coerce').notnull()]\n",
    "        \n",
    "        # loop\n",
    "        for key in metadata:\n",
    "            stacked_clean.insert(0,key,metadata[key])\n",
    "            \n",
    "        # add filename\n",
    "        stacked_clean.insert(0, 'FileName', filename)\n",
    "        \n",
    "        # add data to list\n",
    "        dfs.append(stacked_clean)\n",
    "#%% combine data\n",
    "result = pd.concat(dfs)\n",
    "# %% output file\n",
    "# set output directory -- create if non-existent\n",
    "outpath = inpath + \"\\output\"\n",
    "if not os.path.exists(outpath):\n",
    "    os.makedirs(outpath)\n",
    "# create full output path\n",
    "outfile = outpath + \"\\\\session4output2dict.csv\"\n",
    "# create ouput file\n",
    "result.to_csv(outfile,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "And for the `transpose` version:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# import packages -- os and pandas\n",
    "import os\n",
    "import pandas as pd\n",
    "\n",
    "# %% Loop through folder and combine\n",
    "# input path\n",
    "# escape sequences: \\newline,\\\\,\\',\\\",\\a,\\b,\\f,\\n,\\N,\\r,\\t,\\u,\\U,\\v,\\[0-9],\\x\n",
    "inpath = r\"C:\\Users\\161289\\Py-R\\data\\session4\\exercise2\"\n",
    "# list of files\n",
    "inpathfiles = os.listdir(inpath)\n",
    "\n",
    "# empty list\n",
    "dfs = []\n",
    "\n",
    "# %% loop\n",
    "for filename in inpathfiles:\n",
    "    # condition/filter -- match with \"WaferFlow\"\n",
    "    if \"WaferFlow\" in filename:\n",
    "        # append path to filename\n",
    "        filenamefull = inpath + \"\\\\\" + filename\n",
    "        # read header data of file into dataframe\n",
    "        df_head = pd.read_csv(filenamefull,header=None,nrows=4)\n",
    "        \n",
    "        # extract column names\n",
    "        # transpose dataframe\n",
    "        df_head = df_head.transpose()\n",
    "        # set first row as column names\n",
    "        df_head.columns = df_head.iloc[0]\n",
    "        # drop first row\n",
    "        df_head = df_head.drop([0])\n",
    "        # reset index\n",
    "        df_head = df_head.reset_index(drop=True)\n",
    "        \n",
    "        # read body of file into dataframe\n",
    "        df = pd.read_csv(filenamefull,skiprows=5)\n",
    "        \n",
    "        # delete units row\n",
    "        df = df.drop([0])\n",
    "        \n",
    "        # EXTRA EXPLANATION: create stacked dataframe -- NEW\n",
    "        stacked = pd.melt(df, id_vars=[\"sec\"], var_name=\"Variable\",value_name=\"Value\")\n",
    "        # remove nulls -- NEW\n",
    "        stacked_clean = stacked[pd.to_numeric(stacked[\"Value\"], errors='coerce').notnull()]\n",
    "        \n",
    "        # loop\n",
    "        # create list of df_head columns\n",
    "        columns = list(df_head.columns)\n",
    "        # loop through columns\n",
    "        for x in range(len(columns)):\n",
    "            stacked_clean.insert(x, columns[x],df_head.iloc[0][columns[x]])\n",
    "            \n",
    "        # add filename\n",
    "        stacked_clean.insert(0, 'FileName', filename)\n",
    "        \n",
    "        # add data to list\n",
    "        dfs.append(stacked_clean)\n",
    "#%% combine data\n",
    "result = pd.concat(dfs)\n",
    "# %% output file\n",
    "# set output directory -- create if non-existent\n",
    "outpath = inpath + \"\\output\"\n",
    "if not os.path.exists(outpath):\n",
    "    os.makedirs(outpath)\n",
    "# create full output path\n",
    "outfile = outpath + \"\\\\session4output2transpose.csv\"\n",
    "# create ouput file\n",
    "result.to_csv(outfile,index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Wrap Up\n",
    "And that's it for this session! Now you have the tools to manipulate the most intricate of files to get them into a neat, structured format -- you can read files (and/or pieces of them) into `DataFrames`, access specific locations, transpose data, add/delete/modify columns, perform calculations, and more! It all comes down to thinking through the logic of what pieces of the files to read. And with that, we'll move on to visualizations in our next session."
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}