<jupyter_start><jupyter_text>The current script is used to import the Excel workbook which contains data from the research done by Gert-<NAME>. The data will be selected based on the time period under review, namely the first of June 2017 until the 31st of May 2018. The dataset will then be stored to be used in the later validation of models produced in this project  Install required packages if not present, if so uncomment next cell, and load libraries<jupyter_code>#import sys
#!{sys.executable} -m pip install openpyxl
#!{sys.executable} -m pip install pandas
#!{sys.executable} -m pip install datetime
import openpyxl
import pandas as pd
import datetime<jupyter_output><empty_output><jupyter_text>Read the sheets of the validation dataset into a dictionary, create an empty dictionary to store the transformed data. Set the start and end time of the project for selection of relevant measurements<jupyter_code>all_data = pd.read_excel("val_dat.xlsx",sheet_name=None)
year_data  = {}
start_time = datetime.datetime.strptime("01/06/2017 00:00","%d/%m/%Y %H:%M")
end_time = datetime.datetime.strptime("31/05/2018 23:59","%d/%m/%Y %H:%M")<jupyter_output><empty_output><jupyter_text>Create a function to remove one erronous float value that prevents the date time strings being converted into datetime<jupyter_code>def rem_float(x):
    try:
        float(x)
    except ValueError:
        return True
    return False<jupyter_output><empty_output><jupyter_text>Loop over the keys of the dictionary which hold the dataframes containing the measurements for the related stations. For each dataframe, remove the possible float value in the UTC column holding strings containing measurement date and time. Then convert the column to datetime format and select the relevant time period. Store the converted dataframe in a new dictionary.<jupyter_code>for key in all_data:
    df = all_data[key]
    searchfloat = df["UTC"].apply(rem_float)
    df = df[searchfloat]
    df["UTC"] = pd.to_datetime(df["UTC"],format=" %d/%m/%Y %H:%M")
    df = df[(df["UTC"] >= start_time) & (df["UTC"] <= end_time)]
    year_data[key] = df<jupyter_output><ipython-input-29-7768dde9c7a2>:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df["UTC"] = pd.to_datetime(df["UTC"],format=" %d/%m/%Y %H:%M")<jupyter_text>Write the dataframes back into an excel workbook to use for validation later<jupyter_code>with pd.ExcelWriter('validation.xlsx') as writer:  
    for entry in year_data:
        df1 = year_data[entry]
        df1.to_excel(writer, sheet_name=entry)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09562744400995378, 'fraction_numerical': 0.016352648418059012, 'mean_word_length': 5.157549234135668, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '267419', 'license': 'Apache-2.0', 'n_tokens_mistral': 895, 'n_tokens_neox': 857, 'n_words': 302, 'path': 'processing_validation_dataset.ipynb', 'repo_name': 'zinajanssen/DSSE', 'size': '2820'}