Preparation of Data

First, we import the .xls file from the computer and check it.

data <- readxl::read_xls("/Users/cucar/Downloads/ptf-smf.xls")
data %>% glimpse()
## Rows: 720
## Columns: 6
## $ Tarih                                 <chr> "01.09.20 00:00", "01.09.20 01:…
## $ PTF                                   <chr> "302,39", "300,25", "292,64", "…
## $ SMF                                   <chr> "332,39", "325,25", "317,64", "…
## $ `Pozitif Dengesizlik Fiyatı (TL/MWh)` <chr> "293,32", "291,24", "283,86", "…
## $ `Negatif Dengesizlik Fiyatı (TL/MWh)` <chr> "342,36", "335,01", "327,17", "…
## $ `SMF Yön`                             <chr> "↑ Enerji Açığı", "↑ Enerji Açı…

Then, we change data types into appropriate ones (dttm, double and strings) and rename headlines. After that, we prepare new data table with headlines we are interested in for analyzing.

First seven data are shown on the table;

data$Tarih=as.POSIXct(data$Tarih,format='%d.%m.%y %H:%M')

chr_to_double = function(char_string){
  numeric = as.numeric(sub(',', '.',sub('.', '', char_string, fixed = TRUE)))
  return(numeric)
}

data$PTF <- chr_to_double(data$PTF)
data$SMF <- chr_to_double(data$SMF)
data$`Pozitif Dengesizlik Fiyatı (TL/MWh)` <- chr_to_double(data$`Pozitif Dengesizlik Fiyatı (TL/MWh)`)
data$`Negatif Dengesizlik Fiyatı (TL/MWh)` <- chr_to_double(data$`Negatif Dengesizlik Fiyatı (TL/MWh)`)

names(data)[1]="Date_Time"
names(data)[2]="MCP"
names(data)[3]="SMP"
names(data)[4]="Positive Imbalance Price (TRY/MWh)"
names(data)[5]="Negative Imbalance Price (TRY/MWh)"
names(data)[6]="Energy Balance Check"

data_1 <- data %>% select(Date_Time, MCP, SMP) %>% mutate(Day=weekdays(Date_Time)) %>% mutate(Day_Number=day(Date_Time)) %>% mutate(Hours=hour(Date_Time))

knitr::kable(data_1[1:7,], format="markdown", align='cc')
Date_Time MCP SMP Day Day_Number Hours
2020-09-01 00:00:00 302.39 332.39 Salı 1 0
2020-09-01 01:00:00 300.25 325.25 Salı 1 1
2020-09-01 02:00:00 292.64 317.64 Salı 1 2
2020-09-01 03:00:00 290.00 320.00 Salı 1 3
2020-09-01 04:00:00 290.00 330.00 Salı 1 4
2020-09-01 05:00:00 290.00 339.00 Salı 1 5
2020-09-01 06:00:00 292.01 342.01 Salı 1 6

Analyzing the data by visualizing it with plots

After arranging the data table, we will look for average market clearing price and average system marginal price on hourly basis for September 2020 in Turkey by this code;

data_1 %>% group_by(Hours) %>% summarise(avg_MCP=mean(MCP),avg_SMP=mean(SMP)) %>% pivot_longer(.,-Hours) %>% ggplot(aes(x=Hours, y=value ,color=name)) + geom_line() + theme_minimal()
## `summarise()` ungrouping output (override with `.groups` argument)

As is seen from the plot, average SMP is lower than average MCP after midnight until morning. Therefore, we say that system has energy deficit when people are awake in Turkey. Peak time for energy consumption is around 16:00-17:00.

Next, we draw scatter plot below to see what average price is for both market clearing and system marginal prices on hourly basis. Except peak hours, mostly prices fluctuates between TRY/MWh 300.00-400.00. In addition, we see almost all of dots are on the upper of plot (closer to y-axis). Therefore, system mostly has energy deficit.

data_1  %>% filter(Hours >= 8, Hours <= 24) %>% group_by(Day,Hours) %>% 
  summarise(avg_MCP=mean(MCP),avg_SMP=mean(SMP)) %>% 
  ggplot(.,aes(x=avg_SMP,y=avg_MCP, color=as.character(Hours))) + 
    geom_point()
## `summarise()` regrouping output by 'Day' (override with `.groups` argument)