Open_Data_APD.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3515c2d5",
   "metadata": {},
   "source": [
    "# Account Period Data for a BSC Party from a SAA-I014 Data file"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cf81717",
   "metadata": {},
   "source": [
    "**Author:** Muhammad Raees Usman\n",
    "\n",
    "**Created Date:** 31/10/2022  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "075cccad",
   "metadata": {},
   "source": [
    "This simple script retrieves the Account Period Data for a single BSC Party and Settlement Date using a SAA-I014 text file as an input. The script is intended to be used as an example of how the SAA-I014 file can be imported and manipulated in Python using Pandas given the complex nested structure of the data. The manipulations completed below are in line with the Interface Design Document structure format of the data flow. A link to this file can be found in the accompanying Insight Article.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "814791c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The relevant Python modules are imported\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae28a9d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# A user input is requested for the data file location and BSC Party Id\n",
    "# An example input could be: C:\\Users\\Name\\Desktop\\S0142_20220815_SF_20220907113309.txt\n",
    "input_loc = input('Please enter the file location:')\n",
    "\n",
    "# User input of the desired BSC Party Id for which data is retrieved\n",
    "bsc_party_id = input('Please enter the BSC Party Id:')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39763888",
   "metadata": {},
   "source": [
    "The file conatins rows that have differing number of columns. Therefore, a DataFrame needs to be created that matches the maximum size of the data file in terms of rows and columns. This is achieved in the code block below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e295890a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Opening the data file\n",
    "with open(input_loc, 'r') as temp_data:\n",
    "    # Retrive the No. of columns for each row in the file\n",
    "    col_count = [len(l.split(\"|\")) for l in temp_data.readlines()]\n",
    "    \n",
    "# Create the column names from 0 - (maximum column No. -1)\n",
    "column_names = [i for i in range(0, max(col_count))]\n",
    "\n",
    "# Import the data into a Pandas DataFrame\n",
    "df = pd.read_csv(input_loc, header=None, delimiter=\"|\", names=column_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2a4e7ac",
   "metadata": {},
   "source": [
    "The code block below looks through the DataFrame and retrieves data for the user inputted BSC Party Id. This is achieved by using the headers in the data structure file to identify which rows in the file initiate the nested data for a specific BSC Party ID and Settlement Period. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0ed865f",
   "metadata": {},
   "outputs": [],
   "source": [
    "low_lim = 0 # Initiate low limit variable\n",
    "\n",
    "# Loop through each row in the DataFrame\n",
    "for i, line in df.iterrows(): \n",
    "    if line[0] == 'BPH':            # Identify the BSC Party through the BSC Party Header (BPH) rows in the file\n",
    "        if line[8] == bsc_party_id: # Check if the row corresponds to the user inputted BSC Party Id\n",
    "            low_lim = i             # Assign a low limit for the row No. for which the data file will be iterated\n",
    "\n",
    "df1 = pd.DataFrame()                # Create an empty DataFrame to store the data\n",
    "df1['Settlement Period'] = 0        # Add a Settlement Period column\n",
    "\n",
    "#Loop through all nested rows for the BSC Party Id using the low row limit\n",
    "for i, line in df[low_lim+1:].iterrows():\n",
    "    if line[0] == 'SP7':        # Identify the Settlement Period\n",
    "        SP = line[1]\n",
    "    if line[0] == 'APD':        # Identify the APD for the Settlement Period\n",
    "        df1 = df1.append(line)\n",
    "        df1.loc[i, 'Settlement Period'] = SP\n",
    "    if line[0] == 'BPH':        # Exit loop once next BPH has been detected\n",
    "        break\n",
    "        \n",
    "settlement_date = df.loc[1,1]             # Identify Settlement Period for csv file name\n",
    "run_type = df.loc[1,2]                    # Identify Settlement Run Type\n",
    "df1['Settlement Date'] = settlement_date[6:8] + '/' + settlement_date[4:6] + '/' + settlement_date[0:4] # Add Settlement Date to DataFrame\n",
    "df1['Settlement Run Type'] = run_type     # Add Settlement Run Type to DataFrame\n",
    "        \n",
    "df_final = df1.loc[:,['Settlement Date','Settlement Period','Settlement Run Type',1,2,3,4,5,6,7,8,9,10]] # Extract relevant columns of interest\n",
    "\n",
    "# Rename column in line with data file IDD\n",
    "df_final.rename(columns={1: 'Production/Consumption Flag', 2: 'BSSC Limited Cost Allocation', 3: 'Energy Imbalance Charge', 4: 'Information Imbalance Charge', 5: 'Residual Cashflow Reallocation Charge', 6: 'Account Bilateral Contract Volume', 7: 'Account Period Balancing Services Volume', 8: 'Account Energy Imbalance Volume', 9: 'Account Credited Energy Volume', 10: 'Residual Cashflow Reallocation Proportion'}, inplace=True)\n",
    "df_final.reset_index(drop=True, inplace=True) # Reset Index of DataFrame\n",
    "df_final.to_csv('APD_' + bsc_party_id + '_' +  settlement_date + '_' + run_type + '.csv', index=False) # Export data to a csv file\n",
    "df_final # Display the Dataframe inline for reference"
   ]
  }
 ],
 "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}