<jupyter_start><jupyter_text>Cleaning sensors' reads dataIn this notebook, we are going to clean and format the data loaded previously in a convenient way.As we stated previously, this dataset is composed of two file formats:- CSV files and,- JSON lines filesFortunately, all files have the same structure, meaning that they have the same column names. The column names are:- sensor: The name of the sensor which is performing the reads- value: The read value- time: Time when the read was performedThis fact is going to make the cleaning much simpler.Before starting, we copy the helper function defined in the previous notebook.<jupyter_code>import json
import shutil
from pathlib import Path
from functools import partial

import pandas as pd
def read_json_lines(f: str) -> pd.DataFrame:
    lines = Path(f).read_text().split('\n')
    json_data = [json.loads(o, encoding='utf-8') 
                 for o in lines if o]
    return pd.DataFrame(json_data)
RAW_DATA_PATH = Path('../../data/raw/measures')
PROCESSED_DATA_PATH = Path('../../data/processed/measures')
INTERIM_DATA_PATH = Path('../../data/interim/measures')

INTERIM_DATA_PATH.mkdir(exist_ok=True, parents=True)
PROCESSED_DATA_PATH.mkdir(exist_ok=True, parents=True)<jupyter_output><empty_output><jupyter_text>To obtain cleaned data, we are going to:1. Convert all JSON files to CSV files2. Join all reads coming from the same sensor type to the same file. For example, we are going to generate a single file from these two: `H-DHT11-measures.json`, `T-DHT11-measures.csv` JSON lines to CSVWe first list all JSON lines files inside the `measures` directory.<jupyter_code>json_lines_fnames = list(RAW_DATA_PATH.glob('*.json'))
json_lines_fnames<jupyter_output><empty_output><jupyter_text>For each JSON file, we read it using the `read_json_lines` function in order to load it as `pandas.DataFrame`, and later, we export it with the `to_csv` method to the intermediate processed data folder.<jupyter_code>def json_lines_to_csv(fname):
    fname_out = INTERIM_DATA_PATH / f'{fname.stem}.csv'
    df = read_json_lines(fname)
    df.to_csv(fname_out, index=False)

for f in json_lines_fnames:
    json_lines_to_csv(f)<jupyter_output><empty_output><jupyter_text>Done!To make the next steps simpler, we are going to copy all the remaining CSV files to the `interim` data folder.<jupyter_code>csv_fnames = RAW_DATA_PATH.glob('*.csv')
for f in csv_fnames:
    fname_out = INTERIM_DATA_PATH / f'{f.stem}.csv'
    shutil.copy(str(f), str(fname_out))<jupyter_output><empty_output><jupyter_text>At this point, we have all the CSV files under the `interim` data directory.<jupyter_code>list(map(str, INTERIM_DATA_PATH.iterdir()))<jupyter_output><empty_output><jupyter_text>Join sensor readsTaking a look at the files we can easily come up with a naming pattern. The files follow the naming convention described below:```{MEASURE}-{SENSOR_NAME}-measures.csv```Where MEASURE can be T (temperature), H (humidity), or P (pressure).Thanks to this naming convention, we will be able to group files by sensor name and make the files contain the all reads coming from the same sensor. Note, that this is possible because the sensor reads were made in approximately the same time interval.To show how our method works, we first do it for a single sensor. For instance, we are going to do it for the sensor `DHT11`.<jupyter_code>df_h = pd.read_csv(INTERIM_DATA_PATH / 'H-DHT11-measures.csv', index_col='time')
df_t = pd.read_csv(INTERIM_DATA_PATH / 'T-DHT11-measures.csv', index_col='time')

df_h.head()<jupyter_output><empty_output><jupyter_text>Since we are working with time series, having the time as the `DataFrame` index may be handy for some data transformations.Now we join the datasets by `time`.<jupyter_code>df_ht = df_h.merge(df_t, 'outer', 'time', suffixes=('_h', '_t'))
df_ht.head()<jupyter_output><empty_output><jupyter_text>We can rid off from the sensor name column because we already know from which sensor are the reads coming. Also,  we are going to rename the `value_h` and `value_t` to `humidity` and `temperature`.<jupyter_code>df_ht.drop('sensor_h', inplace=True, axis='columns')
df_ht.rename(columns=dict(
    sensor_t='sensor',
    value_h='humidity',
    value_t='temperature'), inplace=True)
df_ht.head()<jupyter_output><empty_output><jupyter_text>After performing the left join, we are going to have a few rows with NaNs. The reason for this NaNs, is because, in some concrete time intervals, the sensor was off, in this case, we have NaNs for the temperature column. Since NaNs means *no sensor activity*, we can safely set them to 0.<jupyter_code>print('Rows containing NaN:', df_ht.isna().any(axis=1).sum())
df_ht.isna().any(axis=0)
df_ht.sensor = 'DHT11'
df_ht.fillna(0., inplace=True)<jupyter_output><empty_output><jupyter_text>At this point, we know how to join two files from the same sensor. So we have to repeat it for each sensor.<jupyter_code>sensors = set(o.stem.split('-')[1] for o in INTERIM_DATA_PATH.iterdir())
sensors
units_mapper = dict(H='humidity', T='temperature', P='pressure')
read_sensor =  partial(pd.read_csv, 
                       index_col='time', 
                       usecols=['time', 'value'])

def join_files(sensor, files):
    reads = [(read_sensor(o), o.stem.split('-')[0]) 
             for o in files]
    dfs, units = zip(*reads)

    df = pd.merge(*dfs, 'outer', 'time')
    df.rename(columns=dict(
        value_x=units_mapper[units[0]],
        value_y=units_mapper[units[1]]), inplace=True)

    df['sensor'] = s
    df.fillna(0., inplace=True)

    return df

for s in list(sensors):
    print(f'Joining sensor {s} files...', end='')
    files = INTERIM_DATA_PATH.glob(f'*-{s}-*')
    s_df = join_files(s, files)
    s_df.to_csv(PROCESSED_DATA_PATH / f'{s}.csv')
    print(' done')<jupyter_output>Joining sensor BMP280 files... done
Joining sensor DM280 files... done
Joining sensor DHT22 files... done
Joining sensor HTU21 files... done
Joining sensor DHT11 files... done
{'fraction_non_alphanumeric': 0.10855043420173681, 'fraction_numerical': 0.006012024048096192, 'mean_word_length': 4.883104125736739, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '346558', 'license': 'MIT', 'n_tokens_mistral': 1984, 'n_tokens_neox': 1908, 'n_words': 638, 'path': 'notebooks/temperatures/2. Data Cleaning.ipynb', 'repo_name': 'danielb05/Basic-Data-Science-UDL', 'size': '5988'}