NMAS_dataclean.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "import csv\n",
        "import pandas as pd\n",
        "from google.colab import files"
      ],
      "metadata": {
        "id": "I3CxUOIOZ66A"
      },
      "execution_count": 38,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def filter_empty_strings(value):\n",
        "    return value != ' '"
      ],
      "metadata": {
        "id": "7fiCe4XSi-Yd"
      },
      "execution_count": 48,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 55,
      "metadata": {
        "id": "2UXzdTQ1WKdE"
      },
      "outputs": [],
      "source": [
        "column=[]\n",
        "with open(\"NMAS_PDF_csv.csv\", 'r') as file:\n",
        "  csvreader = csv.reader(file)\n",
        "  for row in csvreader:\n",
        "    for cell in row:\n",
        "      cells = cell.split('\\n')\n",
        "      columns = list(filter(filter_empty_strings, cells))\n",
        "      column.append(columns)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.DataFrame(column)"
      ],
      "metadata": {
        "id": "E9CgHrhdXCgj"
      },
      "execution_count": 56,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.to_excel(\"NMAS_dataclean.xlsx\", index=False)\n",
        "files.download('NMAS_dataclean.xlsx')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 17
        },
        "id": "YP0G85-nXSzE",
        "outputId": "0b2e7af4-10bc-4697-a6a9-19ed17854ac0"
      },
      "execution_count": 60,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "\n",
              "    async function download(id, filename, size) {\n",
              "      if (!google.colab.kernel.accessAllowed) {\n",
              "        return;\n",
              "      }\n",
              "      const div = document.createElement('div');\n",
              "      const label = document.createElement('label');\n",
              "      label.textContent = `Downloading \"${filename}\": `;\n",
              "      div.appendChild(label);\n",
              "      const progress = document.createElement('progress');\n",
              "      progress.max = size;\n",
              "      div.appendChild(progress);\n",
              "      document.body.appendChild(div);\n",
              "\n",
              "      const buffers = [];\n",
              "      let downloaded = 0;\n",
              "\n",
              "      const channel = await google.colab.kernel.comms.open(id);\n",
              "      // Send a message to notify the kernel that we're ready.\n",
              "      channel.send({})\n",
              "\n",
              "      for await (const message of channel.messages) {\n",
              "        // Send a message to notify the kernel that we're ready.\n",
              "        channel.send({})\n",
              "        if (message.buffers) {\n",
              "          for (const buffer of message.buffers) {\n",
              "            buffers.push(buffer);\n",
              "            downloaded += buffer.byteLength;\n",
              "            progress.value = downloaded;\n",
              "          }\n",
              "        }\n",
              "      }\n",
              "      const blob = new Blob(buffers, {type: 'application/binary'});\n",
              "      const a = document.createElement('a');\n",
              "      a.href = window.URL.createObjectURL(blob);\n",
              "      a.download = filename;\n",
              "      div.appendChild(a);\n",
              "      a.click();\n",
              "      div.remove();\n",
              "    }\n",
              "  "
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.Javascript object>"
            ],
            "application/javascript": [
              "download(\"download_d624e15c-09f8-42b2-abb1-9d25a6203d96\", \"NMAS_dataclean.xlsx\", 45199)"
            ]
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 852
        },
        "id": "wztAQWiFZdaA",
        "outputId": "331159da-a042-4d9f-e6fe-a21a11a9492d"
      },
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                    0   \\\n",
              "0                                                   ﻿0   \n",
              "1                                              pg. 21    \n",
              "2         Stuart McFarland, Associate Marine Surveyor    \n",
              "3           Accepts Assignments in Commercial Fishing    \n",
              "4    Certified in Cargo||Commercial Fishing Vessel|...   \n",
              "..                                                 ...   \n",
              "397                   Certified in Hull and Machinery    \n",
              "398                      Daniel Boltz, NAMS-CMS, 1998    \n",
              "399                                Certified in Cargo    \n",
              "400     Accepts Assignments in Yachts and Small Craft    \n",
              "401                                               None   \n",
              "\n",
              "                                                 1   \\\n",
              "0                                              None   \n",
              "1     Geographical listing of USA Surveyor Members    \n",
              "2                             10890 Bells Flat Rd     \n",
              "3                   Vessel||Yachts and Small Craft    \n",
              "4                Machinery||Yachts and Small Craft    \n",
              "..                                              ...   \n",
              "397      Accepts Assignments in Commercial Fishing    \n",
              "398                         Inland Surveyors, Inc.    \n",
              "399  Accepts Assignments in Container Loads||Heavy    \n",
              "400                                            None   \n",
              "401                                            None   \n",
              "\n",
              "                                                    2   \\\n",
              "0                                                 None   \n",
              "1                                             Alabama    \n",
              "2                                    Kodiak, AK 99615    \n",
              "3                 Christopher Collier, NAMS-CMS, 1983    \n",
              "4           Accepts Assignments in Commercial Fishing    \n",
              "..                                                 ...   \n",
              "397  Vessel||Commercial Vessels under 1600 GT||Cont...   \n",
              "398                              2626 W Lake Isle Dr     \n",
              "399                                     Lift||Salvage    \n",
              "400                                               None   \n",
              "401                                               None   \n",
              "\n",
              "                                                    3   \\\n",
              "0                                                 None   \n",
              "1                                                 None   \n",
              "2                                 Cell # 907.942.1810    \n",
              "3                           C.E. Collier & Associates    \n",
              "4    Vessel||Container Loads||Marina Facilities||Sa...   \n",
              "..                                                 ...   \n",
              "397  Loads||Hull and Machinery||Marina Facilities||...   \n",
              "398                                  Mequon, WI 53092    \n",
              "399  Richard Reichelsdorfer, Associate Marine Surve...   \n",
              "400                                               None   \n",
              "401                                               None   \n",
              "\n",
              "                                           4                               5   \\\n",
              "0                                        None                            None   \n",
              "1                                        None                            None   \n",
              "2           office@alaskamarinesurveyors.com                             None   \n",
              "3                             P. O. Box 643             Coden, AL 36523-0643    \n",
              "4             Glenn Mitchell, NAMS-CMS, 2017   1509 Government St. Suite 103    \n",
              "..                                        ...                             ...   \n",
              "397                                Wisconsin                             None   \n",
              "398  Work # 312.914.4721 Cell # 312-914-4721         Danielboltz@comcast.net    \n",
              "399                 Marine Pro Service, LLC.                    P.O. Box 99     \n",
              "400                                      None                            None   \n",
              "401                                      None                            None   \n",
              "\n",
              "                                           6   \\\n",
              "0                                        None   \n",
              "1                                        None   \n",
              "2                                        None   \n",
              "3    Work # 251.873.4382 Cell # 251.610.1546    \n",
              "4                           Mobile, AL 36604    \n",
              "..                                        ...   \n",
              "397                                      None   \n",
              "398                                      None   \n",
              "399                        Belgium, WI 53004    \n",
              "400                                      None   \n",
              "401                                      None   \n",
              "\n",
              "                                           7   \\\n",
              "0                                        None   \n",
              "1                                        None   \n",
              "2                                        None   \n",
              "3                          CEmarine1@aol.com    \n",
              "4    Work # 251.433.9997 Cell # 251.776.8848    \n",
              "..                                        ...   \n",
              "397                                      None   \n",
              "398                                      None   \n",
              "399  Work # 920.889.0758 Cell # 920.889.0758    \n",
              "400                                      None   \n",
              "401                                      None   \n",
              "\n",
              "                                      8                          9     10  \\\n",
              "0                                   None                       None  None   \n",
              "1                                   None                       None  None   \n",
              "2                                   None                       None  None   \n",
              "3                                   None                       None  None   \n",
              "4    glenn.mitchell@sabinesurveyors.com    www.sabinesurveyors.com   None   \n",
              "..                                   ...                        ...   ...   \n",
              "397                                 None                       None  None   \n",
              "398                                 None                       None  None   \n",
              "399           rich@marineproservice.com   www.marineproservice.com   None   \n",
              "400                                 None                       None  None   \n",
              "401                                 None                       None  None   \n",
              "\n",
              "       11    12  \n",
              "0    None  None  \n",
              "1    None  None  \n",
              "2    None  None  \n",
              "3    None  None  \n",
              "4    None  None  \n",
              "..    ...   ...  \n",
              "397  None  None  \n",
              "398  None  None  \n",
              "399  None  None  \n",
              "400  None  None  \n",
              "401  None  None  \n",
              "\n",
              "[402 rows x 13 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-500a1d8f-1195-468c-b3bb-75e5236d3f83\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>0</th>\n",
              "      <th>1</th>\n",
              "      <th>2</th>\n",
              "      <th>3</th>\n",
              "      <th>4</th>\n",
              "      <th>5</th>\n",
              "      <th>6</th>\n",
              "      <th>7</th>\n",
              "      <th>8</th>\n",
              "      <th>9</th>\n",
              "      <th>10</th>\n",
              "      <th>11</th>\n",
              "      <th>12</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>﻿0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>pg. 21</td>\n",
              "      <td>Geographical listing of USA Surveyor Members</td>\n",
              "      <td>Alabama</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Stuart McFarland, Associate Marine Surveyor</td>\n",
              "      <td>10890 Bells Flat Rd</td>\n",
              "      <td>Kodiak, AK 99615</td>\n",
              "      <td>Cell # 907.942.1810</td>\n",
              "      <td>office@alaskamarinesurveyors.com</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Accepts Assignments in Commercial Fishing</td>\n",
              "      <td>Vessel||Yachts and Small Craft</td>\n",
              "      <td>Christopher Collier, NAMS-CMS, 1983</td>\n",
              "      <td>C.E. Collier &amp; Associates</td>\n",
              "      <td>P. O. Box 643</td>\n",
              "      <td>Coden, AL 36523-0643</td>\n",
              "      <td>Work # 251.873.4382 Cell # 251.610.1546</td>\n",
              "      <td>CEmarine1@aol.com</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Certified in Cargo||Commercial Fishing Vessel|...</td>\n",
              "      <td>Machinery||Yachts and Small Craft</td>\n",
              "      <td>Accepts Assignments in Commercial Fishing</td>\n",
              "      <td>Vessel||Container Loads||Marina Facilities||Sa...</td>\n",
              "      <td>Glenn Mitchell, NAMS-CMS, 2017</td>\n",
              "      <td>1509 Government St. Suite 103</td>\n",
              "      <td>Mobile, AL 36604</td>\n",
              "      <td>Work # 251.433.9997 Cell # 251.776.8848</td>\n",
              "      <td>glenn.mitchell@sabinesurveyors.com</td>\n",
              "      <td>www.sabinesurveyors.com</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</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",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>397</th>\n",
              "      <td>Certified in Hull and Machinery</td>\n",
              "      <td>Accepts Assignments in Commercial Fishing</td>\n",
              "      <td>Vessel||Commercial Vessels under 1600 GT||Cont...</td>\n",
              "      <td>Loads||Hull and Machinery||Marina Facilities||...</td>\n",
              "      <td>Wisconsin</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>398</th>\n",
              "      <td>Daniel Boltz, NAMS-CMS, 1998</td>\n",
              "      <td>Inland Surveyors, Inc.</td>\n",
              "      <td>2626 W Lake Isle Dr</td>\n",
              "      <td>Mequon, WI 53092</td>\n",
              "      <td>Work # 312.914.4721 Cell # 312-914-4721</td>\n",
              "      <td>Danielboltz@comcast.net</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>399</th>\n",
              "      <td>Certified in Cargo</td>\n",
              "      <td>Accepts Assignments in Container Loads||Heavy</td>\n",
              "      <td>Lift||Salvage</td>\n",
              "      <td>Richard Reichelsdorfer, Associate Marine Surve...</td>\n",
              "      <td>Marine Pro Service, LLC.</td>\n",
              "      <td>P.O. Box 99</td>\n",
              "      <td>Belgium, WI 53004</td>\n",
              "      <td>Work # 920.889.0758 Cell # 920.889.0758</td>\n",
              "      <td>rich@marineproservice.com</td>\n",
              "      <td>www.marineproservice.com</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>400</th>\n",
              "      <td>Accepts Assignments in Yachts and Small Craft</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>401</th>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>402 rows × 13 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-500a1d8f-1195-468c-b3bb-75e5236d3f83')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-500a1d8f-1195-468c-b3bb-75e5236d3f83 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-500a1d8f-1195-468c-b3bb-75e5236d3f83');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [],
      "metadata": {
        "id": "jrgsYUdagupI"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}