VLOOKUP Automation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importing Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "from tabulate import tabulate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to Extract Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_files(file1,file2,sheet1,sheet2):\n",
    "    df1=pd.read_excel(file1, sheet_name=sheet1,    )\n",
    "    df2=pd.read_excel(file2, sheet_name=sheet2)\n",
    "    \n",
    "    return (df1,df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++="
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Function to Identify the Columns Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def select(com):\n",
    "    man=[]\n",
    "    common=[]\n",
    "    if com.lower()==\"manual\":\n",
    "        no=int(input(\"Enter the number of common columns you want to apply VLOOKUP:  \"))\n",
    "        i=0\n",
    "        while i<no:\n",
    "            name=input(\"Enter the common column name in exact format given in table: \")\n",
    "            man.append(name)\n",
    "            i=i+1\n",
    "            \n",
    "    elif com.lower()==\"common\":\n",
    "        for i in list(df1.columns):\n",
    "            if i in list(df2.columns):\n",
    "                common.append(i)\n",
    "\n",
    "    else:\n",
    "        print(\"Kindly try again !!\")\n",
    "    \n",
    "    return(man,common)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Function to Proceed with Common Columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def add(apply):\n",
    "    i=0\n",
    "    cols=[]\n",
    "    while i<apply:\n",
    "        col=input(\"Enter column \" + str(i) + \" name in exact format given in table 2 : \")\n",
    "        cols.append(col)\n",
    "        i=i+1\n",
    "    return (cols)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Alotting the values based on common columns "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def vlookup(cols,common):\n",
    "    for match_col in cols:\n",
    "        df1[match_col]=\" \"\n",
    "        for j in df1.index:\n",
    "            for k in df2.index:\n",
    "                if (df1[common].loc[j]==df2[common].loc[k]).all(axis=0) == True:\n",
    "                    df1[match_col][j]=df2[match_col][k] \n",
    "                    \n",
    "    return (df1,df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def final(cols,man,common):\n",
    "    if len(man)==0:\n",
    "        df1,df2=vlookup(cols,common)\n",
    "    \n",
    "    else:\n",
    "        df1,df2=vlookup(cols,man)\n",
    "        \n",
    "    return (df1,df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tables_view(view):\n",
    "    if view.upper()=='Y':\n",
    "        display(df1.head())\n",
    "        print()\n",
    "        print()\n",
    "        print()\n",
    "        print()\n",
    "        display(df2.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format1(ffomat,file1):\n",
    "    if ffomat=='csv':\n",
    "        df=pd.read_csv(file1)\n",
    "        \n",
    "    else:\n",
    "        sheet1=int(input(\"Sheet Number you want to work on: \"))\n",
    "        df=pd.read_excel(file1, sheet_name=sheet1)\n",
    "        \n",
    "    return (df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User Inputs and Final Outputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter the file 1 format: csv\n",
      "Enter the path of excel table you want to apply VLOOKUP in : D:DataSets/data1.csv\n",
      "Enter the file 2 format: csv\n",
      "Enter the path of excel table from you want to apply VLOOKUP : D:DataSets/data2.csv\n",
      "Do you want to view the two tables Y/N : Y\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CIK</th>\n",
       "      <th>CONAME</th>\n",
       "      <th>FYRMO</th>\n",
       "      <th>FDATE</th>\n",
       "      <th>FORM</th>\n",
       "      <th>SECFNAME</th>\n",
       "      <th>Fog Index</th>\n",
       "      <th>Average Sentence Length</th>\n",
       "      <th>Percentage of Complex Words</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199803</td>\n",
       "      <td>06-03-1998</td>\n",
       "      <td>10-K405</td>\n",
       "      <td>edgar/data/3662/0000950170-98-000413.txt</td>\n",
       "      <td>0.165959</td>\n",
       "      <td>0.207976</td>\n",
       "      <td>0.206921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199805</td>\n",
       "      <td>15-05-1998</td>\n",
       "      <td>10-Q</td>\n",
       "      <td>edgar/data/3662/0000950170-98-001001.txt</td>\n",
       "      <td>0.290581</td>\n",
       "      <td>0.204948</td>\n",
       "      <td>0.521504</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199808</td>\n",
       "      <td>13-08-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-000783.txt</td>\n",
       "      <td>25.903533</td>\n",
       "      <td>0.214388</td>\n",
       "      <td>64.544444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>12-11-1998</td>\n",
       "      <td>10-K/A</td>\n",
       "      <td>edgar/data/3662/0000950170-98-002145.txt</td>\n",
       "      <td>0.452119</td>\n",
       "      <td>0.211780</td>\n",
       "      <td>0.918518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>16-11-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-001203.txt</td>\n",
       "      <td>27.387445</td>\n",
       "      <td>0.215029</td>\n",
       "      <td>68.253584</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CIK            CONAME   FYRMO       FDATE     FORM  \\\n",
       "0  3662  SUNBEAM CORP/FL/  199803  06-03-1998  10-K405   \n",
       "1  3662  SUNBEAM CORP/FL/  199805  15-05-1998     10-Q   \n",
       "2  3662  SUNBEAM CORP/FL/  199808  13-08-1998  NT 10-Q   \n",
       "3  3662  SUNBEAM CORP/FL/  199811  12-11-1998   10-K/A   \n",
       "4  3662  SUNBEAM CORP/FL/  199811  16-11-1998  NT 10-Q   \n",
       "\n",
       "                                   SECFNAME  Fog Index  \\\n",
       "0  edgar/data/3662/0000950170-98-000413.txt   0.165959   \n",
       "1  edgar/data/3662/0000950170-98-001001.txt   0.290581   \n",
       "2  edgar/data/3662/0000950172-98-000783.txt  25.903533   \n",
       "3  edgar/data/3662/0000950170-98-002145.txt   0.452119   \n",
       "4  edgar/data/3662/0000950172-98-001203.txt  27.387445   \n",
       "\n",
       "   Average Sentence Length  Percentage of Complex Words  \n",
       "0                 0.207976                     0.206921  \n",
       "1                 0.204948                     0.521504  \n",
       "2                 0.214388                    64.544444  \n",
       "3                 0.211780                     0.918518  \n",
       "4                 0.215029                    68.253584  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CIK</th>\n",
       "      <th>CONAME</th>\n",
       "      <th>FYRMO</th>\n",
       "      <th>FDATE</th>\n",
       "      <th>FORM</th>\n",
       "      <th>SECFNAME</th>\n",
       "      <th>Fog Index</th>\n",
       "      <th>Average Sentence Length</th>\n",
       "      <th>Complex Word Count</th>\n",
       "      <th>Percentage of Complex Words</th>\n",
       "      <th>constraining words whole report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199803</td>\n",
       "      <td>06-03-1998</td>\n",
       "      <td>10-K405</td>\n",
       "      <td>edgar/data/3662/0000950170-98-000413.txt</td>\n",
       "      <td>0.165959</td>\n",
       "      <td>0.207976</td>\n",
       "      <td>35003</td>\n",
       "      <td>0.206921</td>\n",
       "      <td>723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199805</td>\n",
       "      <td>15-05-1998</td>\n",
       "      <td>10-Q</td>\n",
       "      <td>edgar/data/3662/0000950170-98-001001.txt</td>\n",
       "      <td>0.290581</td>\n",
       "      <td>0.204948</td>\n",
       "      <td>57937</td>\n",
       "      <td>0.521504</td>\n",
       "      <td>436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199808</td>\n",
       "      <td>13-08-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-000783.txt</td>\n",
       "      <td>25.903533</td>\n",
       "      <td>0.214388</td>\n",
       "      <td>58090</td>\n",
       "      <td>64.544444</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>12-11-1998</td>\n",
       "      <td>10-K/A</td>\n",
       "      <td>edgar/data/3662/0000950170-98-002145.txt</td>\n",
       "      <td>0.452119</td>\n",
       "      <td>0.211780</td>\n",
       "      <td>75989</td>\n",
       "      <td>0.918518</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>16-11-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-001203.txt</td>\n",
       "      <td>27.387445</td>\n",
       "      <td>0.215029</td>\n",
       "      <td>76171</td>\n",
       "      <td>68.253584</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CIK            CONAME   FYRMO       FDATE     FORM  \\\n",
       "0  3662  SUNBEAM CORP/FL/  199803  06-03-1998  10-K405   \n",
       "1  3662  SUNBEAM CORP/FL/  199805  15-05-1998     10-Q   \n",
       "2  3662  SUNBEAM CORP/FL/  199808  13-08-1998  NT 10-Q   \n",
       "3  3662  SUNBEAM CORP/FL/  199811  12-11-1998   10-K/A   \n",
       "4  3662  SUNBEAM CORP/FL/  199811  16-11-1998  NT 10-Q   \n",
       "\n",
       "                                   SECFNAME  Fog Index  \\\n",
       "0  edgar/data/3662/0000950170-98-000413.txt   0.165959   \n",
       "1  edgar/data/3662/0000950170-98-001001.txt   0.290581   \n",
       "2  edgar/data/3662/0000950172-98-000783.txt  25.903533   \n",
       "3  edgar/data/3662/0000950170-98-002145.txt   0.452119   \n",
       "4  edgar/data/3662/0000950172-98-001203.txt  27.387445   \n",
       "\n",
       "   Average Sentence Length  Complex Word Count  Percentage of Complex Words  \\\n",
       "0                 0.207976               35003                     0.206921   \n",
       "1                 0.204948               57937                     0.521504   \n",
       "2                 0.214388               58090                    64.544444   \n",
       "3                 0.211780               75989                     0.918518   \n",
       "4                 0.215029               76171                    68.253584   \n",
       "\n",
       "   constraining words whole report  \n",
       "0                              723  \n",
       "1                              436  \n",
       "2                                0  \n",
       "3                              335  \n",
       "4                                0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "To apply VLOOKUP using all common columns, type (Common) or to enter the common columns manually, type (Manual):   Manual\n",
      "Enter the number of common columns you want to apply VLOOKUP:  1\n",
      "Enter the common column name in exact format given in table: SECFNAME\n",
      "Number of columns you want to add data in first table:  1\n",
      "Enter column 0 name in exact format given in table 2 : constraining words whole report\n",
      "\n",
      " \n",
      " \n",
      " AFTER DATA \n",
      " \n",
      " \n",
      "\n",
      "Table 1\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CIK</th>\n",
       "      <th>CONAME</th>\n",
       "      <th>FYRMO</th>\n",
       "      <th>FDATE</th>\n",
       "      <th>FORM</th>\n",
       "      <th>SECFNAME</th>\n",
       "      <th>Fog Index</th>\n",
       "      <th>Average Sentence Length</th>\n",
       "      <th>Percentage of Complex Words</th>\n",
       "      <th>constraining words whole report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199803</td>\n",
       "      <td>06-03-1998</td>\n",
       "      <td>10-K405</td>\n",
       "      <td>edgar/data/3662/0000950170-98-000413.txt</td>\n",
       "      <td>0.165959</td>\n",
       "      <td>0.207976</td>\n",
       "      <td>0.206921</td>\n",
       "      <td>723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199805</td>\n",
       "      <td>15-05-1998</td>\n",
       "      <td>10-Q</td>\n",
       "      <td>edgar/data/3662/0000950170-98-001001.txt</td>\n",
       "      <td>0.290581</td>\n",
       "      <td>0.204948</td>\n",
       "      <td>0.521504</td>\n",
       "      <td>436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199808</td>\n",
       "      <td>13-08-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-000783.txt</td>\n",
       "      <td>25.903533</td>\n",
       "      <td>0.214388</td>\n",
       "      <td>64.544444</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>12-11-1998</td>\n",
       "      <td>10-K/A</td>\n",
       "      <td>edgar/data/3662/0000950170-98-002145.txt</td>\n",
       "      <td>0.452119</td>\n",
       "      <td>0.211780</td>\n",
       "      <td>0.918518</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>16-11-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-001203.txt</td>\n",
       "      <td>27.387445</td>\n",
       "      <td>0.215029</td>\n",
       "      <td>68.253584</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CIK            CONAME   FYRMO       FDATE     FORM  \\\n",
       "0  3662  SUNBEAM CORP/FL/  199803  06-03-1998  10-K405   \n",
       "1  3662  SUNBEAM CORP/FL/  199805  15-05-1998     10-Q   \n",
       "2  3662  SUNBEAM CORP/FL/  199808  13-08-1998  NT 10-Q   \n",
       "3  3662  SUNBEAM CORP/FL/  199811  12-11-1998   10-K/A   \n",
       "4  3662  SUNBEAM CORP/FL/  199811  16-11-1998  NT 10-Q   \n",
       "\n",
       "                                   SECFNAME  Fog Index  \\\n",
       "0  edgar/data/3662/0000950170-98-000413.txt   0.165959   \n",
       "1  edgar/data/3662/0000950170-98-001001.txt   0.290581   \n",
       "2  edgar/data/3662/0000950172-98-000783.txt  25.903533   \n",
       "3  edgar/data/3662/0000950170-98-002145.txt   0.452119   \n",
       "4  edgar/data/3662/0000950172-98-001203.txt  27.387445   \n",
       "\n",
       "   Average Sentence Length  Percentage of Complex Words  \\\n",
       "0                 0.207976                     0.206921   \n",
       "1                 0.204948                     0.521504   \n",
       "2                 0.214388                    64.544444   \n",
       "3                 0.211780                     0.918518   \n",
       "4                 0.215029                    68.253584   \n",
       "\n",
       "  constraining words whole report  \n",
       "0                             723  \n",
       "1                             436  \n",
       "2                               0  \n",
       "3                             335  \n",
       "4                               0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      " \n",
      "\n",
      "\n",
      "\n",
      "Tbale 2\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CIK</th>\n",
       "      <th>CONAME</th>\n",
       "      <th>FYRMO</th>\n",
       "      <th>FDATE</th>\n",
       "      <th>FORM</th>\n",
       "      <th>SECFNAME</th>\n",
       "      <th>Fog Index</th>\n",
       "      <th>Average Sentence Length</th>\n",
       "      <th>Complex Word Count</th>\n",
       "      <th>Percentage of Complex Words</th>\n",
       "      <th>constraining words whole report</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199803</td>\n",
       "      <td>06-03-1998</td>\n",
       "      <td>10-K405</td>\n",
       "      <td>edgar/data/3662/0000950170-98-000413.txt</td>\n",
       "      <td>0.165959</td>\n",
       "      <td>0.207976</td>\n",
       "      <td>35003</td>\n",
       "      <td>0.206921</td>\n",
       "      <td>723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199805</td>\n",
       "      <td>15-05-1998</td>\n",
       "      <td>10-Q</td>\n",
       "      <td>edgar/data/3662/0000950170-98-001001.txt</td>\n",
       "      <td>0.290581</td>\n",
       "      <td>0.204948</td>\n",
       "      <td>57937</td>\n",
       "      <td>0.521504</td>\n",
       "      <td>436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199808</td>\n",
       "      <td>13-08-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-000783.txt</td>\n",
       "      <td>25.903533</td>\n",
       "      <td>0.214388</td>\n",
       "      <td>58090</td>\n",
       "      <td>64.544444</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>12-11-1998</td>\n",
       "      <td>10-K/A</td>\n",
       "      <td>edgar/data/3662/0000950170-98-002145.txt</td>\n",
       "      <td>0.452119</td>\n",
       "      <td>0.211780</td>\n",
       "      <td>75989</td>\n",
       "      <td>0.918518</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3662</td>\n",
       "      <td>SUNBEAM CORP/FL/</td>\n",
       "      <td>199811</td>\n",
       "      <td>16-11-1998</td>\n",
       "      <td>NT 10-Q</td>\n",
       "      <td>edgar/data/3662/0000950172-98-001203.txt</td>\n",
       "      <td>27.387445</td>\n",
       "      <td>0.215029</td>\n",
       "      <td>76171</td>\n",
       "      <td>68.253584</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    CIK            CONAME   FYRMO       FDATE     FORM  \\\n",
       "0  3662  SUNBEAM CORP/FL/  199803  06-03-1998  10-K405   \n",
       "1  3662  SUNBEAM CORP/FL/  199805  15-05-1998     10-Q   \n",
       "2  3662  SUNBEAM CORP/FL/  199808  13-08-1998  NT 10-Q   \n",
       "3  3662  SUNBEAM CORP/FL/  199811  12-11-1998   10-K/A   \n",
       "4  3662  SUNBEAM CORP/FL/  199811  16-11-1998  NT 10-Q   \n",
       "\n",
       "                                   SECFNAME  Fog Index  \\\n",
       "0  edgar/data/3662/0000950170-98-000413.txt   0.165959   \n",
       "1  edgar/data/3662/0000950170-98-001001.txt   0.290581   \n",
       "2  edgar/data/3662/0000950172-98-000783.txt  25.903533   \n",
       "3  edgar/data/3662/0000950170-98-002145.txt   0.452119   \n",
       "4  edgar/data/3662/0000950172-98-001203.txt  27.387445   \n",
       "\n",
       "   Average Sentence Length  Complex Word Count  Percentage of Complex Words  \\\n",
       "0                 0.207976               35003                     0.206921   \n",
       "1                 0.204948               57937                     0.521504   \n",
       "2                 0.214388               58090                    64.544444   \n",
       "3                 0.211780               75989                     0.918518   \n",
       "4                 0.215029               76171                    68.253584   \n",
       "\n",
       "   constraining words whole report  \n",
       "0                              723  \n",
       "1                              436  \n",
       "2                                0  \n",
       "3                              335  \n",
       "4                                0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ffomat=input(\"Enter the file 1 format: \")\n",
    "file1=input(\"Enter the path of excel table you want to apply VLOOKUP in : \")\n",
    "df1=format1(ffomat,file1)\n",
    "\n",
    "ffomat2=input(\"Enter the file 2 format: \")\n",
    "file2=input(\"Enter the path of excel table from you want to apply VLOOKUP : \")\n",
    "df2=format1(ffomat2,file2)\n",
    "\n",
    "\n",
    "view=input(\"Do you want to view the two tables Y/N : \")\n",
    "tables_view(view)\n",
    "\n",
    "com=input(\"To apply VLOOKUP using all common columns, type (Common) or to enter the common columns manually, type (Manual):   \")\n",
    "man,common=select(com)\n",
    "\n",
    "\n",
    "apply=int(input(\"Number of columns you want to add data in first table:  \"))\n",
    "cols=add(apply)\n",
    "\n",
    "print(\"\\n \\n \\n AFTER DATA \\n \\n \\n\")\n",
    "df1,df2=final(cols,man,common)\n",
    "\n",
    "print('Table 1')\n",
    "display(df1.head())\n",
    "print(\"\\n \\n\\n\\n\")\n",
    "print(\"Tbale 2\")\n",
    "display(df2.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    
   ]
  }
 ],
 "metadata": {
  "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}