import pandas as pd
import numpy as np

import matplotlib.pyplot as plt

# Display the first few rows of each DataFrame as scrollable tables in Jupyter Notebook
from IPython.display import display
from tabulate import tabulate
import holidays

from sklearn.linear_model import LinearRegression

from sklearn.linear_model import Lasso
from sklearn.linear_model import LassoCV
from sklearn.model_selection import GridSearchCV

from sklearn.linear_model import Ridge

from sklearn.metrics import mean_squared_error, mean_absolute_error, r2_score

import warnings
warnings.filterwarnings('ignore')

# Load the electricity consumption dataset
consumption_filepath = 'C:/Users/madsh/OneDrive/Dokumenter/kandidat/Fællesmappe/Speciale/Forecasting-energy-consumption-in-Denmark/Data/Energy/Production and Consumption - Settlement.csv'
consumption_df = pd.read_csv(consumption_filepath)

# Display the electricity consumption DataFrame
print("Electricity Consumption Data:")
display(consumption_df.head())

# Convert the relevant columns to datetime format
consumption_df['HourDK'] = pd.to_datetime(consumption_df['HourDK'])

# Summing up GrossConsumptionMWh for both DK1 and DK2 for each time slot
consumption_grouped_df = consumption_df.groupby('HourDK')['GrossConsumptionMWh'].sum().reset_index()

# Show the first few rows of the grouped DataFrame
consumption_grouped_df.head()

# Generate a complete date range
complete_date_range = pd.date_range(start=consumption_grouped_df['HourDK'][0], end=consumption_grouped_df['HourDK'][len(consumption_grouped_df['HourDK'])-1], freq='H')

# Put HourDK as DataFrame index
consumption_grouped_df.set_index('HourDK', inplace=True)

# Reindex the DataFrame to include all dates and set NaN for missing dates
consumption_grouped_df = consumption_grouped_df.reindex(complete_date_range)

# Reset index to make 'HourDK' a column again
line_numbers = consumption_grouped_df.index[consumption_grouped_df['GrossConsumptionMWh'].isna()].tolist()
selected_rows = consumption_grouped_df.loc[line_numbers]
print(selected_rows)
missing_before = consumption_grouped_df['GrossConsumptionMWh'].isna().sum()
print(f"\nNumber of missing values before interpolation: {missing_before}")

# Perform linear interpolation
combined_daily_interpolation_df = consumption_grouped_df.interpolate(method='linear')

line_numbers = consumption_grouped_df.index[consumption_grouped_df['GrossConsumptionMWh'].isna()].tolist()
selected_rows = combined_daily_interpolation_df.loc[line_numbers]
print(selected_rows)

# Count and print the number of missing values after interpolation
missing_after = combined_daily_interpolation_df['GrossConsumptionMWh'].isna().sum()
print(f"\nNumber of missing values after interpolation: {missing_after}")

# Reset index to make 'HourDK' a column again
combined_daily_interpolation_df.reset_index(inplace=True)
combined_daily_interpolation_df.rename(columns={'index': 'HourDK'}, inplace=True)

# Print the DataFrame to check the results
print(combined_daily_interpolation_df)

consumption_df = pd.read_csv(consumption_filepath)

# Convert the relevant columns to datetime format
consumption_df['HourDK'] = pd.to_datetime(consumption_df['HourDK'])

# Summing up GrossConsumptionMWh for both DK1 and DK2 for each time slot
consumption_grouped_df['HourDK'] = pd.to_datetime(consumption_df['HourDK'])
consumption_grouped_flagged_df = consumption_df.groupby('HourDK')['GrossConsumptionMWh'].sum().reset_index()

# Generate a complete date range
complete_date_range = pd.date_range(start=consumption_grouped_flagged_df['HourDK'].min(), end=consumption_grouped_flagged_df['HourDK'].max(), freq='H')

# Put HourDK as DataFrame index
consumption_grouped_flagged_df.set_index('HourDK', inplace=True)

# Reindex the DataFrame to include all dates and set NaN for missing dates
consumption_grouped_flagged_df = consumption_grouped_flagged_df.reindex(complete_date_range)

# Check the number of missing values before flagging
missing_before = consumption_grouped_flagged_df['GrossConsumptionMWh'].isna().sum()
print(f"Number of missing values before flagging: {missing_before}")

# Create the 'flagged' column
consumption_grouped_flagged_df['flagged'] = np.where(
    (consumption_grouped_flagged_df['GrossConsumptionMWh'] < 1) | 
    consumption_grouped_flagged_df['GrossConsumptionMWh'].isna(), 
    1, 
    0
)

# Reset the index to make HourDK a column again and remove the duplicate index
consumption_grouped_flagged_df.reset_index(inplace=True)
consumption_grouped_flagged_df.rename(columns={'index': 'HourDK'}, inplace=True)

# Show the resulting DataFrame
print(consumption_grouped_flagged_df)

# Count and print the number of missing values after flagging
missing_after = consumption_grouped_flagged_df['GrossConsumptionMWh'].isna().sum()
print(f"\nNumber of missing values after Flagging: {missing_after}")
print(f"Number of 'Flagged' observations: {sum(consumption_grouped_flagged_df['flagged'])}")

# Resample the interpolated electricity consumption data to daily level and sum up GrossConsumptionMWh
combined_daily_interpolation_df = combined_daily_interpolation_df.resample('D', on='HourDK').sum().reset_index()

# Show the first few rows of the daily aggregated DataFrame
print("Interpolated energy data:")
print(combined_daily_interpolation_df)

# Resample the interpolated electricity consumption data to daily level and sum up GrossConsumptionMWh
consumption_daily_flagged_df = consumption_grouped_flagged_df.resample('D', on='HourDK').agg({'GrossConsumptionMWh': 'sum', 'flagged': 'max'}).reset_index()
consumption_daily_flagged_df['GrossConsumptionMWh'] = np.where(consumption_daily_flagged_df['flagged'] == 1, 0, consumption_daily_flagged_df['GrossConsumptionMWh'])
# Show the first few rows of the daily aggregated DataFrame
print("\nFlagged energy data:")
print(consumption_daily_flagged_df)

# Load the weather forecast dataset
weather_filepath = 'C:/Users/madsh/OneDrive/Dokumenter/kandidat/Fællesmappe/Speciale/Forecasting-energy-consumption-in-Denmark/Data/Weather forecasts/combined_forecasts_2005-2023.csv'
weather_df = pd.read_csv(weather_filepath)

# Display the weather forecast DataFrame
print("Weather Forecast Data:")
display(weather_df.head())

weather_df['valid_time'] = pd.to_datetime(weather_df['valid_time'])

# Resample the weather data to daily level and average the temperature (t2m)
weather_daily_df = weather_df.resample('D', on='valid_time').mean().reset_index()

# Show the first few rows of the daily aggregated DataFrame
weather_daily_df.head()

# Filter out the 00:00 observations for each day to keep the 'step' information
weather_step_df = weather_df[weather_df['valid_time'].dt.hour == 0]

# Merge the filtered 'step' DataFrame with the daily averaged temperature DataFrame on 'valid_time'
weather_daily_with_step_df = pd.merge(weather_daily_df, weather_step_df[['valid_time', 'step']], on='valid_time', how='left')

# Set the 'step' for the first day of the year to be "0 days"
weather_daily_with_step_df.loc[0, 'step'] = pd.Timedelta(days=0)
weather_daily_with_step_df['step'].fillna(pd.Timedelta('0 days 00:00:00'), inplace=True)


# Show the first few rows of the daily aggregated DataFrame with 'step'
weather_daily_with_step_df.head()

# First, convert 'step' to string
weather_daily_with_step_df['step'] = weather_daily_with_step_df['step'].astype(str)

# Extract the number of days and convert to float first (this will convert 'nan' to NaN)
weather_daily_with_step_df['step_days'] = weather_daily_with_step_df['step'].str.split(' ').str[0].astype(float)

# Now filter out the rows where 'step_days' is NaN if you want
weather_daily_with_step_df = weather_daily_with_step_df[weather_daily_with_step_df['step_days'].notna()]

# Finally, convert to integer
weather_daily_with_step_df['step_days'] = weather_daily_with_step_df['step_days'].astype(int)

# Drop the original 'step' column
weather_daily_with_step_df.drop('step', axis=1, inplace=True)

weather_daily_with_step_df.head()

# Merge the daily aggregated electricity consumption data with the daily aggregated weather data
combined_daily_interpolation_df = pd.merge(combined_daily_interpolation_df, weather_daily_with_step_df, 
                             left_on='HourDK', right_on='valid_time', how='inner')

# Drop the redundant 'valid_time' column
combined_daily_interpolation_df.drop('valid_time', axis=1, inplace=True)

# Show the first few rows of the combined DataFrame
print("Interpolated combined data:")
display(combined_daily_interpolation_df.head())

# Merge the daily aggregated electricity consumption data with the daily aggregated weather data
combined_daily_flagged_df = pd.merge(consumption_daily_flagged_df, weather_daily_with_step_df, 
                             left_on='HourDK', right_on='valid_time', how='inner')

# Drop the redundant 'valid_time' column
combined_daily_flagged_df.drop('valid_time', axis=1, inplace=True)

# Show the first few rows of the combined DataFrame
print("\nFlagged combined data:")
display(combined_daily_flagged_df.head())

# Add 'Day_of_Week' and 'Month_of_Year' columns
combined_daily_interpolation_df['Day_of_Week'] = combined_daily_interpolation_df['HourDK'].dt.day_name()
combined_daily_interpolation_df['Month_of_Year'] = combined_daily_interpolation_df['HourDK'].dt.month_name()

# Show the first few rows of the combined DataFrame
print("Interpolated combined data:")
display(combined_daily_interpolation_df.head())

# Add 'Day_of_Week' and 'Month_of_Year' columns
combined_daily_flagged_df['Day_of_Week'] = combined_daily_flagged_df['HourDK'].dt.day_name()
combined_daily_flagged_df['Month_of_Year'] = combined_daily_flagged_df['HourDK'].dt.month_name()

# Show the first few rows to confirm the added columns
print("\nFlagged combined data:")
display(combined_daily_flagged_df.head())

# Generate the list of holidays for Denmark for the years 2005-2023
danish_holidays = [date for year in range(2005, 2024) for date, _ in holidays.Denmark(years=year).items()]
# Convert the list to a pandas Series and make sure it's in datetime format
danish_holidays = pd.Series(pd.to_datetime(danish_holidays))
# Convert the holiday dates to datetime format
danish_holidays = pd.to_datetime(danish_holidays)

# Create a new column 'Is_Holiday' and set it to 1 if the date is a holiday, 0 otherwise
combined_daily_interpolation_df['Is_Holiday'] = combined_daily_interpolation_df['HourDK'].isin(danish_holidays).astype(int)
combined_daily_flagged_df['Is_Holiday'] = combined_daily_flagged_df['HourDK'].isin(danish_holidays).astype(int)

# Show the first few rows of the combined DataFrame
print("Interpolated combined data:")
display(combined_daily_interpolation_df.head())

# Show the first few rows to confirm the added columns
print("\nFlagged combined data:")
display(combined_daily_flagged_df.head())

# Generate dummy variables for 'Day_of_Week' and 'Month_of_Year'
day_of_week_dummies = pd.get_dummies(combined_daily_interpolation_df['Day_of_Week'], prefix='Day')
month_of_year_dummies = pd.get_dummies(combined_daily_interpolation_df['Month_of_Year'], prefix='Month')

# Concatenate the original DataFrame with the dummy variables
combined_daily_interpolation_df = pd.concat([combined_daily_interpolation_df, day_of_week_dummies, month_of_year_dummies], axis=1)
combined_daily_flagged_df       = pd.concat([combined_daily_flagged_df,       day_of_week_dummies, month_of_year_dummies], axis=1)

# Drop the original 'Day_of_Week' and 'Month_of_Year' columns
combined_daily_interpolation_df.drop(['Day_of_Week', 'Month_of_Year'], axis=1, inplace=True)
combined_daily_flagged_df.drop(['Day_of_Week', 'Month_of_Year']     , axis=1, inplace=True)

# Define the desired column order
day_columns = ['Day_Monday', 'Day_Tuesday', 'Day_Wednesday', 'Day_Thursday', 'Day_Friday', 'Day_Saturday', 'Day_Sunday']
month_columns = ['Month_January', 'Month_February', 'Month_March', 'Month_April', 'Month_May', 'Month_June', 'Month_July', 'Month_August', 'Month_September', 'Month_October', 'Month_November', 'Month_December']
other_columns_inter = ['HourDK', 'GrossConsumptionMWh', 't2m', 'step_days','Is_Holiday']
other_columns_flag  = ['HourDK', 'GrossConsumptionMWh','flagged', 't2m', 'step_days','Is_Holiday']

# Combine all columns in the desired order
ordered_columns_inter = other_columns_inter + day_columns + month_columns
ordered_columns_flag = other_columns_flag + day_columns + month_columns

# Reorder the columns in the DataFrame
combined_daily_interpolation_df = combined_daily_interpolation_df[ordered_columns_inter]
combined_daily_flagged_df       = combined_daily_flagged_df[ordered_columns_flag]

# Show the first few rows to confirm the new column order
print("Interpolated combined data:")
display(combined_daily_interpolation_df.head())

# Show the first few rows to confirm the added columns
print("\nFlagged combined data:")
display(combined_daily_flagged_df.head())

combined_daily_interpolation_df = combined_daily_interpolation_df[combined_daily_interpolation_df['HourDK'] <= '2022-12-31']
combined_daily_flagged_df = combined_daily_flagged_df[combined_daily_flagged_df['HourDK'] <= '2022-12-31']

# Show the first few rows to confirm the new column order
print("Interpolated combined data:")
display(combined_daily_interpolation_df.tail())

# Show the first few rows to confirm the added columns
print("\nFlagged combined data:")
display(combined_daily_flagged_df.tail())

# Export combined_daily_interpolation_df to a CSV file
combined_daily_interpolation_df.to_csv('combined_daily_interpolation.csv', index=False)

# Export combined_daily_flagged_df to a CSV file
combined_daily_flagged_df.to_csv('combined_daily_flagged.csv', index=False)