<jupyter_start><jupyter_text>This code will clean the OB datasets and combine all the cleaned data into one Dataset name: O-05-<NAME> Sub-folders: * NIOLM data* Plug-in meters data - unprocessed* Smart meter data - unprocessed<jupyter_code>import os
import glob
import pandas as pd
root_path = 'D:/yapan_office_D/Data/Annex-79-OB-Database/2021-05-28-1130-raw-data/Annex 79 Data Collection/O-05-Hamed Nabizadeh Rafsanjani/'
processed_path = 'D:/yapan_office_D/Data/Annex-79-OB-Database/2021-05-28-1130-raw-data/Annex 79 Data Collection/O-05-Hamed Nabizadeh Rafsanjani/_yapan_processing/'<jupyter_output><empty_output><jupyter_text>1. NIOLM data  This dataset is the processed results... Not raw data combine csv files using pandas takes a lot of time, should use windows shell command<jupyter_code># set up the working directory
os.chdir(root_path+'NIOLM data')

# find all the files within this folder
all_filenames = [i for i in glob.glob('*.{}'.format('csv'))]

# pandas combine all the csv files wihtin the folder
combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames ])<jupyter_output><empty_output><jupyter_text>2. Plug-in meters data Question: How to normalize the datetime? What is the timezone of the data?<jupyter_code>%%time

# this folder has 1-6 sub-folders, combine all the data under each sub-folder, assign the id as subfolder name
for index in range(1,7):
    # folder index
    print(f'Dealing with data under folder {index}')
    
    # set up the working directory
    os.chdir(root_path+f'Plug-in meters data - unprocessed/{index}')

    # create empty dataframe for combining all the data in this folder
    df = pd.DataFrame(columns=['Appliance_Usage_ID', 'Date_Time', 'Electric_Power'])

    # find all the files within this folder
    all_filenames = [i for i in glob.glob('*.{}'.format('csv'))]

    # pandas combine all the csv files wihtin the folder
    combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames ])

    # select desire data
    cols_to_keep = ['DATE/TIME','WATTS']
    combined_csv = combined_csv.loc[:, cols_to_keep]
    combined_csv.columns = ['Date_Time', 'Electric_Power'] # rename the columns as standard 

    combined_csv['Date_Time'] = pd.to_datetime(combined_csv['Date_Time'], format="%Y/%m/%d %H:%M:%S") # convert object to datetime
    combined_csv['Appliance_Usage_ID'] = index # assign the ID to this data

    # save data to a csv file
    combined_csv.to_csv(processed_path+f'Plug-in meters data - combined-{index}.csv', index=False)<jupyter_output>Dealing with data under folder 1
Dealing with data under folder 2
Dealing with data under folder 3
Dealing with data under folder 4
Dealing with data under folder 5
Dealing with data under folder 6
Wall time: 5min 33s<jupyter_text>3. Smart meter data<jupyter_code># set up the working directory
os.chdir(root_path+'Smart meter data - unprocessed')

# find all the files within this folder
all_filenames = [i for i in glob.glob('*.{}'.format('csv'))]

# pandas combine all the csv files wihtin the folder
combined_csv = pd.concat([pd.read_csv(f) for f in all_filenames ])
# combined_csv.dtypes

# keep the desired data
cols_to_keep = [combined_csv.columns[0],combined_csv.columns[1],combined_csv.columns[2]]
combined_csv = combined_csv.loc[:, cols_to_keep]
combined_csv.columns = ['Appliance_Usage_ID', 'Date_Time', 'Electric_Power'] # rename the columns as standard 
# specify the format of the time could significantly speed up the processing time
combined_csv['Date_Time'] = pd.to_datetime(combined_csv['Date_Time'], format="%m/%d/%Y %H:%M:%S") # convert object to datetime
combined_csv

combined_csv.head(100)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10553586037632942, 'fraction_numerical': 0.01636214889555495, 'mean_word_length': 5.032894736842105, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '553398', 'license': 'MIT', 'n_tokens_mistral': 1276, 'n_tokens_neox': 1202, 'n_words': 374, 'path': 'Phase_1/O-05-HamedNabizadehRafsanjani.ipynb', 'repo_name': 'yapanliu/ashrae-ob-database', 'size': '3687'}