<jupyter_start><jupyter_text>Data wrangling - transforming the data into a more suitable format<jupyter_code># necessary packages
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>The first thing to do is to load the hourly data that was pulled using the scripts in the data fetching section.<jupyter_code>ts = pd.read_csv("hourly_dropped.csv", parse_dates=['timestamp'])
ts.head()<jupyter_output><empty_output><jupyter_text>Next, we clean the data and restructure it into a more suitable format.* Group the `ts` dataframe by the locations since our data contain electricity demand records from multiple locations.* For each location, we create a dataframe. The index of each of these dataframes are the timestamps of the data (from 2017-January-01 to 2020-January-01). These dataframes are stored in the list `locs`.* For each dataframe, we delete the duplicated records if there are any.<jupyter_code>groups = ts.groupby('locationName')

# divide into different dataframes for each location
locs = [groups.get_group(df).set_index('timestamp').value for df in groups.groups]

# remove duplicated rows
locs = [df[~df.index.duplicated(keep='first')] for df in locs]<jupyter_output><empty_output><jupyter_text>Next, we further clean the data. As there may be locations whose data are not in hourly resolution, we need to reformat those dataframes so that all of them are in the hourly resolution.<jupyter_code># create a range of timestamps covering the time range of the data.
all_dates = pd.date_range(start='2017-01-01', end='2020-01-01', freq='1H')

for idx, loc in enumerate(locs):
    if len(loc) > len(all_dates): # are there more records (timestamps) than the default dataframe
        locs[idx] = locs[idx].resample('1H').sum()<jupyter_output><empty_output><jupyter_text>Next, we combine all dataframes in `locs` into 1 big dataframe `df`, in which each column is a location.<jupyter_code>df = pd.concat(locs, join='outer', axis=1, ignore_index=True)
df.head()<jupyter_output><empty_output><jupyter_text>Now, we can already spot that there are NaN entries in the dataframe. In other words, for those locations at those timestamps, there was no available data. Although it may be arbitrary, we decided to only take the columns (locations) in which there are less than 100 missing entries.<jupyter_code>df2 = df.loc[:,df.isna().sum() < 100].copy()
df2.head()<jupyter_output><empty_output><jupyter_text>However, our data might still have missing timestamps, which is why we insert rows whose index are the missing timestamps.<jupyter_code>for idx in all_dates.difference(df2.index):
    df2.loc[idx] = pd.Series(dtype='float64')<jupyter_output><empty_output><jupyter_text>As one solution to the missing entries problem, we decided to interpolate the data linearly, that is, for each column (location), the missing entries are filled with values that follow a linear trend to other entries. There are other interpolation methods as well, but we decided to go with this method. This interpolation should not affect the inference much as for each column there are only at most 100 missing entries, and the dataframe has more than 25000 rows.After the interpolation step, we sum over all columns (locations) as we only wish to predict the hourly "total" electricity demand.<jupyter_code>df3 = df2.sort_index().interpolate().sum(axis=1).rename('kWh')
df3.head()<jupyter_output><empty_output><jupyter_text>As a final step in this section, we save the aggregated data.<jupyter_code>file_name = 'hourly_total.csv'
df3.to_csv(file_name)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08662068965517242, 'fraction_numerical': 0.01572413793103448, 'mean_word_length': 5.973076923076923, 'pattern_counts': {'":': 0, '<': 38, '<?xml version=': 0, '>': 38, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '204072', 'license': 'CC0-1.0', 'n_tokens_mistral': 1114, 'n_tokens_neox': 1058, 'n_words': 423, 'path': 'hourly/.ipynb_checkpoints/wrangling-checkpoint.ipynb', 'repo_name': 'quan-possible/energy-demand-prediction', 'size': '3625'}