Dict to Excel.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = OrderedDict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = pd.MultiIndex.from_product([[\"A\",\"B\"],[\"C\",\"D\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = np.random.randn(4,4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data,columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">A</th>\n",
       "      <th colspan=\"2\" halign=\"left\">B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.112933</td>\n",
       "      <td>-0.732711</td>\n",
       "      <td>-0.817716</td>\n",
       "      <td>0.374241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.105546</td>\n",
       "      <td>-0.874569</td>\n",
       "      <td>-0.478736</td>\n",
       "      <td>1.726494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.009386</td>\n",
       "      <td>1.280126</td>\n",
       "      <td>-0.506563</td>\n",
       "      <td>-0.452919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.639243</td>\n",
       "      <td>-0.452752</td>\n",
       "      <td>1.108618</td>\n",
       "      <td>-0.948373</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A                   B          \n",
       "          C         D         C         D\n",
       "0  0.112933 -0.732711 -0.817716  0.374241\n",
       "1  0.105546 -0.874569 -0.478736  1.726494\n",
       "2 -0.009386  1.280126 -0.506563 -0.452919\n",
       "3 -0.639243 -0.452752  1.108618 -0.948373"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[(\"\", \"E\")] = np.random.randn(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">A</th>\n",
       "      <th colspan=\"2\" halign=\"left\">B</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.112933</td>\n",
       "      <td>-0.732711</td>\n",
       "      <td>-0.817716</td>\n",
       "      <td>0.374241</td>\n",
       "      <td>-1.324994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.105546</td>\n",
       "      <td>-0.874569</td>\n",
       "      <td>-0.478736</td>\n",
       "      <td>1.726494</td>\n",
       "      <td>0.834351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.009386</td>\n",
       "      <td>1.280126</td>\n",
       "      <td>-0.506563</td>\n",
       "      <td>-0.452919</td>\n",
       "      <td>-1.228843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-0.639243</td>\n",
       "      <td>-0.452752</td>\n",
       "      <td>1.108618</td>\n",
       "      <td>-0.948373</td>\n",
       "      <td>-1.999290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          A                   B                    \n",
       "          C         D         C         D         E\n",
       "0  0.112933 -0.732711 -0.817716  0.374241 -1.324994\n",
       "1  0.105546 -0.874569 -0.478736  1.726494  0.834351\n",
       "2 -0.009386  1.280126 -0.506563 -0.452919 -1.228843\n",
       "3 -0.639243 -0.452752  1.108618 -0.948373 -1.999290"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{('A', 'C'): {0: 0.11293340016787719,\n",
       "  1: 0.10554610556533665,\n",
       "  2: -0.009385967151287361,\n",
       "  3: -0.6392433293042644},\n",
       " ('A', 'D'): {0: -0.7327109365017134,\n",
       "  1: -0.8745694401275363,\n",
       "  2: 1.2801261798896517,\n",
       "  3: -0.45275157069505195},\n",
       " ('B', 'C'): {0: -0.8177164826891347,\n",
       "  1: -0.47873647501202177,\n",
       "  2: -0.5065630204758796,\n",
       "  3: 1.1086182945670706},\n",
       " ('B', 'D'): {0: 0.374241216685047,\n",
       "  1: 1.726494100590016,\n",
       "  2: -0.4529193571875303,\n",
       "  3: -0.9483732400820882},\n",
       " ('', 'E'): {0: -1.3249937857969254,\n",
       "  1: 0.8343506040565625,\n",
       "  2: -1.228842736456401,\n",
       "  3: -1.9992898028252415}}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Final Code "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {},
   "outputs": [],
   "source": [
    "row = OrderedDict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [],
   "source": [
    "years = [i+2000 for i in range(10)]\n",
    "quarters = [i%4 for i in range(10)]\n",
    "days = [i for i in range(10)]\n",
    "bd_ids = list(np.random.randint(20000,30000,10))\n",
    "bd_names = list(np.random.randint(0,1000,10))\n",
    "random_values = list(np.random.uniform(low=0,high=1000,size=10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "years: [2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009],\n",
      "quarters: [0, 1, 2, 3, 0, 1, 2, 3, 0, 1],\n",
      "days: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9],\n",
      "bd_ids: [24852, 20459, 22294, 20174, 23198, 28929, 29785, 20854, 26964, 20674],\n",
      "bd_names: [954, 290, 361, 327, 889, 243, 309, 637, 924, 637]\n"
     ]
    }
   ],
   "source": [
    "print(f\"years: {years},\\nquarters: {quarters},\\ndays: {days},\\nbd_ids: {bd_ids},\\nbd_names: {bd_names}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(years) == len(quarters) == len(days) == len(bd_ids) == len(bd_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "metadata": {},
   "outputs": [],
   "source": [
    "row[(\"\",\"\",\"BD_NAME\")] = bd_ids\n",
    "row[(\"\",\"\",\"BD_ID\")] = bd_names\n",
    "row[(\"\",\"\",\"RANDOM\")] = random_values\n",
    "row[(\"Years\",\"Quarters\",\"Day\")] = \"\"\n",
    "for i, (y, q, d) in enumerate(zip(years,quarters,days)):\n",
    "    row[(y,q,d)] = \" \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OrderedDict([(('', '', 'BD_NAME'),\n",
       "              [24852,\n",
       "               20459,\n",
       "               22294,\n",
       "               20174,\n",
       "               23198,\n",
       "               28929,\n",
       "               29785,\n",
       "               20854,\n",
       "               26964,\n",
       "               20674]),\n",
       "             (('', '', 'BD_ID'),\n",
       "              [954, 290, 361, 327, 889, 243, 309, 637, 924, 637]),\n",
       "             (('', '', 'RANDOM'),\n",
       "              [870.7522557150284,\n",
       "               410.1808888313837,\n",
       "               780.942335463737,\n",
       "               738.3744273027708,\n",
       "               751.6981252438052,\n",
       "               639.1823660015033,\n",
       "               444.1051007017852,\n",
       "               105.39289742077406,\n",
       "               682.6179311368928,\n",
       "               470.336034584074]),\n",
       "             (('Years', 'Quarters', 'Day'), ''),\n",
       "             ((2000, 0, 0), ' '),\n",
       "             ((2001, 1, 1), ' '),\n",
       "             ((2002, 2, 2), ' '),\n",
       "             ((2003, 3, 3), ' '),\n",
       "             ((2004, 0, 4), ' '),\n",
       "             ((2005, 1, 5), ' '),\n",
       "             ((2006, 2, 6), ' '),\n",
       "             ((2007, 3, 7), ' '),\n",
       "             ((2008, 0, 8), ' '),\n",
       "             ((2009, 1, 9), ' ')])"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "metadata": {},
   "outputs": [],
   "source": [
    "row_df = pd.DataFrame(row,index=np.arange(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 293,
   "metadata": {},
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\"></th>\n",
       "      <th>Years</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\"></th>\n",
       "      <th>Quarters</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>BD_NAME</th>\n",
       "      <th>BD_ID</th>\n",
       "      <th>RANDOM</th>\n",
       "      <th>Day</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24852</td>\n",
       "      <td>954</td>\n",
       "      <td>870.752256</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>1</th>\n",
       "      <td>20459</td>\n",
       "      <td>290</td>\n",
       "      <td>410.180889</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>2</th>\n",
       "      <td>22294</td>\n",
       "      <td>361</td>\n",
       "      <td>780.942335</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>3</th>\n",
       "      <td>20174</td>\n",
       "      <td>327</td>\n",
       "      <td>738.374427</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>4</th>\n",
       "      <td>23198</td>\n",
       "      <td>889</td>\n",
       "      <td>751.698125</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>5</th>\n",
       "      <td>28929</td>\n",
       "      <td>243</td>\n",
       "      <td>639.182366</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>6</th>\n",
       "      <td>29785</td>\n",
       "      <td>309</td>\n",
       "      <td>444.105101</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>7</th>\n",
       "      <td>20854</td>\n",
       "      <td>637</td>\n",
       "      <td>105.392897</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>8</th>\n",
       "      <td>26964</td>\n",
       "      <td>924</td>\n",
       "      <td>682.617931</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>9</th>\n",
       "      <td>20674</td>\n",
       "      <td>637</td>\n",
       "      <td>470.336035</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Years 2000 2001 2002 2003 2004 2005 2006 2007  \\\n",
       "                            Quarters    0    1    2    3    0    1    2    3   \n",
       "  BD_NAME BD_ID      RANDOM      Day    0    1    2    3    4    5    6    7   \n",
       "0   24852   954  870.752256                                                    \n",
       "1   20459   290  410.180889                                                    \n",
       "2   22294   361  780.942335                                                    \n",
       "3   20174   327  738.374427                                                    \n",
       "4   23198   889  751.698125                                                    \n",
       "5   28929   243  639.182366                                                    \n",
       "6   29785   309  444.105101                                                    \n",
       "7   20854   637  105.392897                                                    \n",
       "8   26964   924  682.617931                                                    \n",
       "9   20674   637  470.336035                                                    \n",
       "\n",
       "  2008 2009  \n",
       "     0    1  \n",
       "     8    9  \n",
       "0            \n",
       "1            \n",
       "2            \n",
       "3            \n",
       "4            \n",
       "5            \n",
       "6            \n",
       "7            \n",
       "8            \n",
       "9            "
      ]
     },
     "execution_count": 293,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "row_df.to_csv(\"sub3.csv\",index=False)"
   ]
  }
 ],
 "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.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}