project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#! python3\n",
    "import xlsxwriter\n",
    "import re\n",
    "import os\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from pathlib import Path\n",
    "from datetime import timedelta\n",
    "\n",
    "\"\"\"\n",
    "This script only extracts data. It does not clean them. It works only for blended data, but before running the script you\n",
    "have to prepare source.txt file. From source.txt file you have to remove additional ',' in:\n",
    "CADIZ \n",
    "BALLYHAISE CAVAN (x2)\n",
    "PLYMOUTH MOUNT BATTEN\n",
    "NOTTINGHAM WATNALL\n",
    "OAK PARK CARLOW \n",
    "\"\"\"\n",
    "\n",
    "\n",
    "class Data:\n",
    "\n",
    "    def __init__(self):\n",
    "        self.cnt = 0  # this is counter for how many files has been already proceed, just to see the progress\n",
    "        self.files = 'C:/Users/kinan/Desktop/Bioinformatyka/Modern Data Analytics/temp_min'  # path to folder with files INSERT YOUR OWN ONE HERE\n",
    "        self.excel = 'C:/Users/kinan/Desktop/temp_min.xlsx'  #  path to directory where xlsx fil will be created # INSERT YOUR OWN ONE HERE WITH .xlxs EXTENTION\n",
    "        self.pre = ''\n",
    "        self.date = []  # contains all dates\n",
    "        self.stations = []  # contains stations' info (ISO code, localization, id)\n",
    "        self.all_data = []  # contains data from every file\n",
    "\n",
    "    def df_prepare(self, text):\n",
    "        \"\"\"\n",
    "          Converting text from .txt file into data frame.\n",
    "          Step 1: transforming spaces into tabs\n",
    "          Step 2: Creating data frame (columns are split by ',')\n",
    "          \"\"\"\n",
    "        # this part is meant to transform block of spaces in .txt file into tabs\n",
    "        pattern = '\\\\s*\\\\s'\n",
    "        patter_regex = re.compile(pattern)\n",
    "        for i in range(0, len(text)):\n",
    "            text[i] = patter_regex.sub('', str(text[i]))\n",
    "\n",
    "        # this part is meant to transform string text into data frame\n",
    "        df = pd.DataFrame(text)\n",
    "        df = df[0].str.split(',', expand=True)\n",
    "        header = df.iloc[0, ]  # first row will be the header\n",
    "        df.columns = header\n",
    "        df = df.iloc[2:, :-1]  # last column and first row are unnecessary\n",
    "\n",
    "        return df\n",
    "\n",
    "    def source_file(self):\n",
    "        \"\"\"\n",
    "        Creating lists with STATID and localization info\n",
    "        \"\"\"\n",
    "\n",
    "        with open(Path(self.files)/'sources.txt', encoding='utf8') as file:\n",
    "            text = file.readlines()[23:]  # first 23 lines contains unnecessary description\n",
    "        df = self.df_prepare(text)\n",
    "\n",
    "        # creating lists that contain stations id and where are they located\n",
    "        stations = []\n",
    "        country = []\n",
    "        for i, row in df.iterrows():\n",
    "            # print(i, 'STAID:', row['STAID'])\n",
    "            if (int(row['START']) <= 19961231 and int(row['STOP']) > 20191231) and (row['STAID']) not in stations:\n",
    "                stations.append(row['STAID'])\n",
    "                country.append(str(row['CN']) + ' ' + str(row['SOUNAME']) + ' ' + str(row['STAID']))\n",
    "        self.stations = country\n",
    "\n",
    "        return stations, country\n",
    "\n",
    "    def create_file(self):\n",
    "        \"\"\"\n",
    "        Function to create Excel file and fill first row with dates\n",
    "        from 1996-01-01 to 2019-12-30\n",
    "        \"\"\"\n",
    "        end_date = datetime.date(2020, 1, 1)\n",
    "        begin_date = datetime.date(1996, 1, 1)\n",
    "        date = [begin_date]\n",
    "        i = 0\n",
    "        # calculating dates day by day from 1996-01-01 to 2020-01-01\n",
    "        while date[i] < end_date:\n",
    "            i += 1\n",
    "            date.append(date[0]+timedelta(days=i))\n",
    "        self.date = date\n",
    "\n",
    "    def file_to_excel(self, file):\n",
    "        \"\"\"\n",
    "        Function to open file, pull out data, save data as list\n",
    "        \"\"\"\n",
    "\n",
    "        with open(Path(self.files)/file, encoding=\"utf8\") as file:\n",
    "            text = file.readlines()[20:]\n",
    "        df = self.df_prepare(text)\n",
    "\n",
    "        # if for some reason file is not matching the description in source.txt, because the measurement\n",
    "        # starts after 1996-01-01 it is skipped and function returns None value\n",
    "        if int(df['DATE'].iloc[0]) > 19960101:\n",
    "            print('DELETING: ', self.stations[self.cnt]) # deleting station from list\n",
    "            del self.stations[self.cnt]\n",
    "            return False\n",
    "\n",
    "        self.cnt += 1\n",
    "        print(self.cnt)\n",
    "        data = []\n",
    "        for i, row in df.iterrows():\n",
    "            if 19960101 <= int(row['DATE']) <= 20191231:  # choosing only records within particular time period\n",
    "                if row[str(self.pre)] == '-9999':\n",
    "                    data.append('NA')  # transforming -9999 into NA\n",
    "                else:\n",
    "                    data.append(int(row[self.pre]))\n",
    "        self.all_data.append(data)\n",
    "\n",
    "    def write_to_excel(self, countries):\n",
    "        wb = xlsxwriter.Workbook(self.excel)\n",
    "        sheet = wb.add_worksheet()\n",
    "        print(len(self.all_data))\n",
    "        print(len(countries))\n",
    "        for i in range(0, len(self.date)):\n",
    "            sheet.write(0, i + 1, str(self.date[i]))  # writing date in first row\n",
    "        cnt = 1\n",
    "        for i in range(0, len(self.all_data)):\n",
    "            sheet.write(cnt, 0, countries[cnt-1])  # writing station's info in first column\n",
    "            for j in range(0, len(self.all_data[i])):\n",
    "                sheet.write(cnt, j + 1, self.all_data[i][j])  # writing variables\n",
    "            cnt += 1\n",
    "        wb.close()\n",
    "\n",
    "\n",
    "info = Data()\n",
    "files = os.listdir(info.files)  # list of files in directory\n",
    "pre = files[0] # zip file name\n",
    "info.pre = str(pre[-6:-4].upper())  # last two letters of zip file are prefix to every txt file\n",
    "pre = str(pre[-6:-4].upper())+'_STAID'\n",
    "print(info.pre)\n",
    "info.create_file()\n",
    "names, place = info.source_file()\n",
    "for i in range(0, len(names)):\n",
    "    names[i] = pre + str(0)*(6-len(names[i])) + str(names[i]) + '.txt'\n",
    "\n",
    "for filenames in files:\n",
    "    if filenames in names:\n",
    "        print(filenames)\n",
    "        info.file_to_excel(filenames)\n",
    "\n",
    "info.write_to_excel(place)\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}