BDA-503 Division Bell Group Project         Pre-processing the Data

                  Turkish Natural Gas Market

        We will start this project by preprocessing the data files so we can eliminate noisy data, correct the data format, remove NA values and clean the data. With this procedure, we will create an RDS file so in the next parts of our project it will be easier to conduct some analyses and create insight.

        Before we start the project, we will begin with loading necessary packages to process the data files which are GRP.xls and TTV.xls.

library(tidyr)
library(dplyr)
library(stringr)
library(lubridate)
library(ggplot2)
library(readxl)
library(tibble)
library(gridExtra)
library(scales)

1-) GRP Data

        This data contains daily gas reference prices between 01/09/2018 and 01/12/2021. The data obtained from here.

        We proceed to read the xls data file.

gas_prices <- read_excel("GRP.xls")

a-) Structure of the Data

        Let’s see the structure of the data file GRP.xls.

str(gas_prices)

tibble [1,188 x 2] (S3: tbl_df/tbl/data.frame)
 $ Gaz Gunu: chr [1:1188] "01/09/2018" "02/09/2018" "03/09/2018" "04/09/2018" ...
 $ GRF     : chr [1:1188] "1.650,00" "1.650,00" "1.656,45" "1.644,23" ...

        As seen above the data type in columns are inappropriate so we have to change them.

        We also see that change the column names since they are in Turkish. We changed Gaz Günü column to Date and GRF column to Gas_Reference_Price. Then take a look if there is any missing values.

names(gas_prices) <- c('Date',
                       'Gas_Reference_Price')

sum(is.na(gas_prices$Gas_Reference_Price))

[1] 0

sum(is.na(gas_prices$Date))

[1] 0

        As seen above there is no missing value.

b-) Processing the Columns

        We then proceed to format the Date column as date format type using lubridate.

gas_prices$Date <- dmy(gas_prices$Date)

        Because dot(.), is used as thousand separator and comma(,) as decimal point we had to deal with it for further processes. We have achieved that by using gsub function. Then we converted it into numeric.

gas_prices$Gas_Reference_Price <- gsub('.',
                                       '',
                                       gas_prices$Gas_Reference_Price,
                                       fixed = TRUE)
gas_prices$Gas_Reference_Price <- as.numeric(gsub(',',
                                                  '.',
                                                  gas_prices$Gas_Reference_Price,
                                                  fixed = TRUE))

        Now if we take a look at the data we will see everything is ready to analysis.

head(gas_prices)

2-) TTV Data

        This data contains daily gas trade volume between 01/09/2018 and 01/12/2021. The data obtained from here.

        Once again, we proceed to read the xls data file.

trade_volume <- read_excel("TTV.xls")

a-) Structure of the Data

        Let’s see the structure of the data file TTV.xls.

str(trade_volume)

tibble [1,188 x 2] (S3: tbl_df/tbl/data.frame)
 $ Gaz Günü          : chr [1:1188] "01.09.2018" "02.09.2018" "03.09.2018" "04.09.2018" ...
 $ Toplam Islem Hacmi: chr [1:1188] "378.501,00" "680.025,00" "1.371.846,25" "1.018.608,25" ...

        As seen above the data types in columns are character so we have to change them.

        We can also see that just like previous data the column names are inappropriate. We changed Gaz Günü column to Date and Toplam Islem Hacmi column to Total_Trade_Volume. Lastly, of course we took a look if there is any missing values.

names(trade_volume) <- c('Date',
                         'Total_Trade_Volume')

sum(is.na(trade_volume$Total_Trade_Volume))

[1] 0

sum(is.na(trade_volume$Date))

[1] 0

        As seen above there is no missing value.

b-) Processing the Columns

        We proceed to format the Date column as date format.

trade_volume$Date <- dmy(trade_volume$Date)

        Just like the previous dataset dot(.), is used as thousand separator and comma(,) as decimal point in this data. We have solved that issue by using gsub function. Then we converted it into numeric.

trade_volume$Total_Trade_Volume <- gsub('.',
                                       '',
                                       trade_volume$Total_Trade_Volume,
                                       fixed = TRUE)
trade_volume$Total_Trade_Volume <- as.numeric(gsub(',',
                                                  '.',
                                                  trade_volume$Total_Trade_Volume,
                                                  fixed = TRUE))

        Let’s take a look at the data, everything should be ready to analysis.

head(trade_volume)

c-) Feature Generation

        In this part we will create multiples columns out of Date so we can deepen our analyses comparing them yearly, monthly, weekly, seasonally and many more.

        We start by creating the weeks column.

trade_volume$week_num <- strftime((trade_volume$Date), format = "%V")
trade_volume$week_num <- as.numeric(trade_volume$week_num)

        Then, the months column.

trade_volume$month_num <- month(trade_volume$Date)
trade_volume$month_num <- as.numeric(trade_volume$month_num)

        Here, we created the day of the week column.

trade_volume$day_of_week <- wday(trade_volume$Date, label = TRUE, abbr = TRUE)

        Also, the year column.

trade_volume$year_num <- isoyear(trade_volume$Date)
trade_volume$year_num <- as.numeric(trade_volume$year_num)

        Lastly, the season column.

seasons <- NULL  # initialize empty vector
for(row in seq_len(nrow(trade_volume["Date"]))){
  if((trade_volume["month_num"][row, 1] == 12) | (trade_volume["month_num"][row, 1] == 1) | (trade_volume["month_num"][row, 1] == 2)){
    seasons <- append(seasons,"Winter")
  }
  else if((trade_volume["month_num"][row, 1] == 3) | (trade_volume["month_num"][row, 1] == 4) | (trade_volume["month_num"][row, 1] == 5)){
    seasons <- append(seasons,"Spring")
  }
  else if((trade_volume["month_num"][row, 1] == 6) | (trade_volume["month_num"][row, 1] == 7) | (trade_volume["month_num"][row, 1] == 8)){
    seasons <- append(seasons,"Summer")
  }
  else {
    seasons <- append(seasons,"Fall")
  }
}
trade_volume["season"] <- seasons

3-) Creating the RDS File

        Two data we will mainly use is ready to merge and go ahead with the further analyses. We merge these two data by Date column and name it as df.

df <- merge(x=trade_volume, y=gas_prices, by='Date')
head(df)

        Then, we saved merged df file as rds file named natural_gas_data so we all can use single and perfect data file.

saveRDS(df, "natural_gas_data.rds")

                                                                                                                                          Thanks for reading…