# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3 (ipykernel)
#     language: python
#     name: python3
# ---

# # Cleaning Singlehousehold Data
# [Data Source](https://archive.ics.uci.edu/ml/datasets/individual+household+electric+power+consumption)

import pandas as pd
import numpy as np

# Change the path to the correct location of your source data
house = pd.read_csv('./household_power_consumption/household_power_consumption.txt', sep=';');
initial_rows = len(house);
initial_columns = len(house.columns);

print(initial_rows)
print(initial_columns)
house.head(10)
# Global active power (Kw) includes all the electrical energy consumption (not all of the energy consumption was captured by the submeters!!!)
# Submeters gives the energy consumption of different places from the house:
# Submeter1 : kitchen (W-h)
# Submeter2 : laundry room (W-h)
# Submeter3 : electric-water heater and airconditioning (W-h)


# ## Some rows have empty string and a questionmark:

# single row where missing data has been observed
print(house.loc[[190499]])

# ### Detection of columns with missing values

# detection of columns with missing values
house_missval_by_col = (house.isnull().sum())
house.replace('?', np.NaN, inplace=True)
print('--------------- all columns with missing values ---------------')
print(house_missval_by_col)
print('--------------- only columns with missing values ---------------')
print(house_missval_by_col[house_missval_by_col > 0])
print('--------------- percentage of missing values ---------------')
print(house_missval_by_col[house_missval_by_col > 0]/initial_rows * 100)

# ### Given that the last element is NaN, we will drop those rows using *dropna*

house.dropna(subset=['Sub_metering_3'], inplace=True)
effective_rows = len(house);
effective_columns = len(house.columns);
# The measurements are one minute apart and it is averaged. To convert the power consumption to energy consumption we multiply it by the factor 1/60 (to be done only when needed). The kW(Kilo Watts) units are appropiate to work with because avoids the use of decimal zeros and avoids rounding zeros. We also drop the colmuns that we are not interested in and insert new columns to be filled with synthetic data (data enrichment).
house_filtered = house.drop(columns=['Global_reactive_power', 'Voltage', 'Global_intensity', 'Sub_metering_1', 'Sub_metering_2', 'Sub_metering_3'])
# The columns below need to be filled up with synthetic data
house_filtered.insert(2,'Location', '48.783333, 2.283333') # in decimal coordinates (Sceaux city - France)
house_filtered.insert(3,'House_or_apartment', 0) # 0 house, 1 apartment
house_filtered.insert(4,'Area_of_residence', 0) # to be inserted in meters squared
house_filtered.insert(5,'House_is_isolated', 0) # 0 no, 1 yes
house_filtered.insert(6,'Number_of_residents', 0) # to be an unsigned number
house_filtered.insert(7,'Heating_type', 0) # 0 electrical, 1 gas
house_filtered.insert(8,'Solar_panels', 0) # 0 don't use, 1 use
house_filtered.head(10)

# change path to the location for the desired output
house_filtered.to_csv('./household_power_consumption/single_household_filtered.csv')

StarcoderdataJupyterScriptsDedupFiltered