PI Type Concat FY 21 INMARSAT Totals.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FY21 Concanted PI Type Files Collection 1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import pandas as pd\n",
    "os.chdir(\"./FY 21 PI Type Revised\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "extension = 'xlsx'\n",
    "all_filenames = [i for i in glob.glob('*.{}'.format(extension))]\n",
    "#combine all files in the list\n",
    "combined_excel = pd.concat([pd.read_excel(f) for f in all_filenames ])\n",
    "#export to csv\n",
    "combined_excel.to_excel( \"all_data_pi_type collection 1.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "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></th>\n",
       "      <th>Dec'20 Amounts Due</th>\n",
       "      <th>Jan'21 Amounts Due</th>\n",
       "      <th>NOV'20 Amounts Due</th>\n",
       "      <th>Oct'20 Amounts Due</th>\n",
       "      <th>FY 21 Collection 1 Totals</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PI</th>\n",
       "      <th>Type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Cronin</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cross</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dziak</th>\n",
       "      <th>Non-RUDICS</th>\n",
       "      <td>47.99</td>\n",
       "      <td>47.99</td>\n",
       "      <td>47.99</td>\n",
       "      <td>47.99</td>\n",
       "      <td>191.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Feely</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>18.19</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>64.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Johnson</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Klinck</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>McPhaden</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.94</td>\n",
       "      <td>15.44</td>\n",
       "      <td>55.94</td>\n",
       "      <td>15.44</td>\n",
       "      <td>102.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meinig - Dziak</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meinig - Feely</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meinig Chaba</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>16.94</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>63.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meinig Former DART</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meinig GNSS GPS</th>\n",
       "      <th>SHORT BURST DATA</th>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>55.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Meinig SPURS2</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>61.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mordy IOTAS-ITAE</th>\n",
       "      <th>SHORT BURST DATA</th>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>55.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mordy ITAE</th>\n",
       "      <th>RUDICS</th>\n",
       "      <td>15.44</td>\n",
       "      <td>15.44</td>\n",
       "      <td>16.94</td>\n",
       "      <td>78.44</td>\n",
       "      <td>126.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Stabeno</th>\n",
       "      <th>SHORT BURST DATA</th>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>13.79</td>\n",
       "      <td>55.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sutton</th>\n",
       "      <th>Non-RUDICS</th>\n",
       "      <td>49.44</td>\n",
       "      <td>46.94</td>\n",
       "      <td>41.69</td>\n",
       "      <td>51.69</td>\n",
       "      <td>189.76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Dec'20 Amounts Due  Jan'21 Amounts Due  \\\n",
       "PI                 Type                                                       \n",
       "Cronin             RUDICS                         15.44               15.44   \n",
       "Cross              RUDICS                         15.44               15.44   \n",
       "Dziak              Non-RUDICS                     47.99               47.99   \n",
       "Feely              RUDICS                         15.44               18.19   \n",
       "Johnson            RUDICS                         15.44               15.44   \n",
       "Klinck             RUDICS                         15.44               15.44   \n",
       "McPhaden           RUDICS                         15.94               15.44   \n",
       "Meinig - Dziak     RUDICS                         15.44               15.44   \n",
       "Meinig - Feely     RUDICS                         15.44               15.44   \n",
       "Meinig Chaba       RUDICS                         15.44               16.94   \n",
       "Meinig Former DART RUDICS                         15.44               15.44   \n",
       "Meinig GNSS GPS    SHORT BURST DATA               13.79               13.79   \n",
       "Meinig SPURS2      RUDICS                         15.44               15.44   \n",
       "Mordy IOTAS-ITAE   SHORT BURST DATA               13.79               13.79   \n",
       "Mordy ITAE         RUDICS                         15.44               15.44   \n",
       "Stabeno            SHORT BURST DATA               13.79               13.79   \n",
       "Sutton             Non-RUDICS                     49.44               46.94   \n",
       "\n",
       "                                     NOV'20 Amounts Due  Oct'20 Amounts Due  \\\n",
       "PI                 Type                                                       \n",
       "Cronin             RUDICS                         15.44               15.44   \n",
       "Cross              RUDICS                         15.44               15.44   \n",
       "Dziak              Non-RUDICS                     47.99               47.99   \n",
       "Feely              RUDICS                         15.44               15.44   \n",
       "Johnson            RUDICS                         15.44               15.44   \n",
       "Klinck             RUDICS                         15.44               15.44   \n",
       "McPhaden           RUDICS                         55.94               15.44   \n",
       "Meinig - Dziak     RUDICS                         15.44               15.44   \n",
       "Meinig - Feely     RUDICS                         15.44               15.44   \n",
       "Meinig Chaba       RUDICS                         15.44               15.44   \n",
       "Meinig Former DART RUDICS                         15.44               15.44   \n",
       "Meinig GNSS GPS    SHORT BURST DATA               13.79               13.79   \n",
       "Meinig SPURS2      RUDICS                         15.44               15.44   \n",
       "Mordy IOTAS-ITAE   SHORT BURST DATA               13.79               13.79   \n",
       "Mordy ITAE         RUDICS                         16.94               78.44   \n",
       "Stabeno            SHORT BURST DATA               13.79               13.79   \n",
       "Sutton             Non-RUDICS                     41.69               51.69   \n",
       "\n",
       "                                     FY 21 Collection 1 Totals  \n",
       "PI                 Type                                         \n",
       "Cronin             RUDICS                                61.76  \n",
       "Cross              RUDICS                                61.76  \n",
       "Dziak              Non-RUDICS                           191.96  \n",
       "Feely              RUDICS                                64.51  \n",
       "Johnson            RUDICS                                61.76  \n",
       "Klinck             RUDICS                                61.76  \n",
       "McPhaden           RUDICS                               102.76  \n",
       "Meinig - Dziak     RUDICS                                61.76  \n",
       "Meinig - Feely     RUDICS                                61.76  \n",
       "Meinig Chaba       RUDICS                                63.26  \n",
       "Meinig Former DART RUDICS                                61.76  \n",
       "Meinig GNSS GPS    SHORT BURST DATA                      55.16  \n",
       "Meinig SPURS2      RUDICS                                61.76  \n",
       "Mordy IOTAS-ITAE   SHORT BURST DATA                      55.16  \n",
       "Mordy ITAE         RUDICS                               126.26  \n",
       "Stabeno            SHORT BURST DATA                      55.16  \n",
       "Sutton             Non-RUDICS                           189.76  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('all_data_pi_type collection 1.xlsx')\n",
    "df = df.groupby(['PI','Type']).sum()\n",
    "df = df.drop(columns=['IDs'])\n",
    "df['FY 21 Collection 1 Totals'] = df.iloc[:, 0:14].sum(axis=1)\n",
    "df.to_excel('FY 21 INMARSAT Collection 1 Totals PI Type.xlsx')\n",
    "df"
   ]
  }
 ],
 "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
}