3_reformatting_data.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": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/europaenergy_today.csv\")\n",
    "\n",
    "# And remove the column with local currency – it is always missing\n",
    "df = df.drop(['price(currency/mwh)'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new dataframe of the mean energy prices for each country.\n",
    "df1 = round(df.groupby('country')['price(eur/mwh)'].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reorganize the order of the colums.\n",
    "df1.columns = ['country', 'price', 'date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# And save it to a csv.\n",
    "df1.to_csv('data/today_price_long.csv', index=True, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load in the data and add today's date. \n",
    "df = pd.read_csv('data/today_price_long.csv')\n",
    "df['date'] = pd.to_datetime('today').strftime(\"%m/%d/%Y\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Reorganize the columns.\n",
    "df1 = df[['date', 'country', 'price(eur/mwh)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Append today's long data to a csv containing data on all dates since the scrape started. \n",
    "df1.to_csv(\"data/prices_long.csv\", mode='a', index=True, header=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transform the data from long to wide - fits the visualization better. \n",
    "df2 = df1.pivot_table(index = 'date', columns = 'country', values = 'price(eur/mwh)', aggfunc = np.mean, dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# And save as a csv. \n",
    "df2.to_csv(\"data/price_wide.csv\", mode ='a', index=True, header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare the hourly data for datawrapper\n",
    "This includes removing the countries where there are no data and deleting columns that will not be used for visualization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/europaenergy_today.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove the column with local currency – we want all data to be in euros\n",
    "df = df.drop(['price(currency/mwh)'], axis=1)\n",
    "\n",
    "# Remove the area column. That won't be used in the visualization\n",
    "df = df.drop(['area'], axis=1)\n",
    "\n",
    "# And the same with the date\n",
    "df = df.drop(['date'], axis=1)\n",
    "\n",
    "df = df.dropna(subset=['price(eur/mwh)'])\n",
    "\n",
    "df.to_csv('data/prices_today_hourly_long.csv', index=False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.pivot_table(index = 'mtu', columns = 'country', values = 'price(eur/mwh)', aggfunc = np.mean, dropna=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/prices_today_hourly.csv', index=True, header=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.11.0rc2 64-bit ('3.11.0rc2')",
   "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.11.0rc2"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "fa07e5dff76123542c28abca75c0e47ea742948dbeeb360ccfbd2a5c7c277f8b"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}