Transmission Auction Data - Transform Data v3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "1786b6c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7c65f8fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "dat = {'month': ['January', 'February', 'March', \n",
    "         'April','May','June',\n",
    "         'July', 'August','September',\n",
    "         'October', 'November', 'December'],\n",
    "       'nrDays': [31, 28, 31,\n",
    "          30, 31, 30,\n",
    "          31, 31, 30,\n",
    "          31, 30, 31]\n",
    "      }\n",
    "monthDays = pd.DataFrame(dat)\n",
    "\n",
    "days = ['01','02','03','04','05','06','07','08','09','10',\n",
    "        '11','12','13','14','15','16','17','18','19','20',\n",
    "        '21','22','23','24','25','26','27','28','29','30', '31']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d42d0c8b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 14.1 s, sys: 206 ms, total: 14.4 s\n",
      "Wall time: 14.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "# Get list of files to use\n",
    "fileList = glob.glob(\"Results*\")\n",
    "dataNewFormat = pd.DataFrame()\n",
    "\n",
    "for i in fileList:\n",
    "    #print(i)\n",
    "    xls = pd.ExcelFile(i)\n",
    "    month = i.split()[5]\n",
    "    for ii in range(1,monthDays.loc[monthDays['month'] == month].iloc[0][1]+1):\n",
    "        #print(ii)\n",
    "        df = xls.parse(days[ii-1] + ' ' + month)\n",
    "        dataNewFormat = pd.concat([dataNewFormat, df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "4c271001",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataNewFormat['Date'] = pd.to_datetime(dataNewFormat['Date'], format=\"%d.%m.%Y\", errors='coerce')\n",
    "dataNewFormat['Date'] = pd.to_datetime(dataNewFormat['Date']).dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "632562ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataNewFormat = dataNewFormat.loc[dataNewFormat['Auction ID'] != 'Auction ID']\n",
    "dataNewFormat = dataNewFormat.loc[dataNewFormat['Time'] != '02:00 - 02:00']\n",
    "dataNewFormat = dataNewFormat.loc[dataNewFormat['Time'] != '01:00 - 03:00']\n",
    "dataNewFormat = dataNewFormat.fillna(0)\n",
    "dataNewFormat['Connection'] = dataNewFormat['Auction ID'].str[:4]\n",
    "dataNewFormat['Hour'] = dataNewFormat['Time'].str[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "53e4030f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7699436f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 8.49 s, sys: 94.5 ms, total: 8.59 s\n",
      "Wall time: 8.59 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "fileList = glob.glob(\"DiffResults*\")\n",
    "dataOldFormat = pd.DataFrame()\n",
    "\n",
    "for i in fileList:\n",
    "    #print(i)\n",
    "    xls = pd.ExcelFile(i)\n",
    "    month = i.split()[5]\n",
    "    for ii in range(1,monthDays.loc[monthDays['month'] == month].iloc[0][1]+1):\n",
    "        #print(ii)\n",
    "        df = xls.parse(days[ii-1] + ' ' + month, header=None)\n",
    "        mask = df[0].str.contains('Auction', case=False, na=False)\n",
    "        df[9] = df[mask][0]\n",
    "        df[9] = df[9].fillna(method='ffill')\n",
    "        df = df[df[1].notnull()]\n",
    "        df = df.loc[df[1] != 'Time']\n",
    "        dataOldFormat = pd.concat([dataOldFormat, df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "d8c3ef90",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataOldFormat[9] = dataOldFormat[9].str.replace('Auction ID ','')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "94b56ba4",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataOldFormat[10] = '0'\n",
    "dataOldFormat1 = dataOldFormat[[9,0,1,2,3,4,5,10,6,7,8]]\n",
    "dataOldFormat1['Connection'] = dataOldFormat1[9].str.split('-').str[0]\n",
    "dataOldFormat1['Hour'] = dataOldFormat1[1].str[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "f2439492",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataOldFormat1.columns = ['Auction ID', 'Date', 'Time', 'Offered Capacity [MW]',\n",
    "                            'Total Requested Capacity [MW]', 'Total Allocated Capacity [MW]',\n",
    "                            'Auction Clearing Price [EUR/MWh]', 'Congestion Income [EUR]',\n",
    "                            'Number of Auction Participants', 'Number of Successful Participants',\n",
    "                            'Number of Auction Bids', 'Connection', 'Hour']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "5e11ac41",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataOldFormat1['Date'] = pd.to_datetime(dataOldFormat1['Date']).dt.date\n",
    "dataOldFormat1['Connection'] = dataOldFormat1['Connection'].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "6bcd36a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataCombined = pd.concat([dataNewFormat, dataOldFormat1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a15b7baf",
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {'Connection': dataCombined['Connection'].unique(), \n",
    "     'Export': ['BA','HR','ME','BA','AL','ME','XK','ME',\n",
    "                'XK','AL','AL','GR','GR','TR','MK','GR',\n",
    "                'IT','ME','MK','XK'],\n",
    "     'ExportFull': ['Bosnia','Croatia','Montenegro','Bosnia','Albania','Montenegro','Kosovo','Montenegro',\n",
    "                    'Kosovo','Albania','Albania','Greece','Greece','Turkey','Macedonia','Greece',\n",
    "                    'Italy','Montenegro','Macedonia','Kosovo'],\n",
    "     'Import': ['HR','BA','BA','ME','ME','AL','ME','XK',\n",
    "                'AL','XK','GR','AL','TR','GR','GR','MK',\n",
    "                'ME','IT','XK','MK'],\n",
    "     'ImportFull': ['Croatia','Bosnia','Bosnia','Montenegro','Montenegro','Albania','Montenegro','Kosovo',\n",
    "                    'Albania','Kosovo','Greece','Albania','Turkey','Greece','Greece','Macedonia',\n",
    "                    'Montenegro','Italy','Kosovo','Macedonia']\n",
    "    }\n",
    "connects = pd.DataFrame(data=d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "c3ff51ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataCombined1 = dataCombined.merge(connects, left_on='Connection', right_on='Connection')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "95ddca19",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataCombined1.columns = ['AuctionID', 'Date', 'Time', 'Offered[MW]',\n",
    "       'Requested[MW]', 'Allocated[MW]',\n",
    "       'Price[EUR/MWh]', 'CongestionIncome[EUR]',\n",
    "       'NrParticipants', 'NrSuccessParticipants',\n",
    "       'NrBids', 'Connection', 'Hour', 'Export', 'ExportFull',\n",
    "       'Import', 'ImportFull']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "e9d058c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "#dataCombined5.head(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6bae01fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataCombined1.to_csv(\"totalData-v1.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}