<jupyter_start><jupyter_text>EXPLORATORY DATA ANALYST  LOCAL ENERGY CONSUMPTION  Load the dataThe data provided by Dynamic Defense Solution were stored in an AWS RDS relational database powered by Posgres engine.Connecting the Juyter Notebook with the RDS of AWS<jupyter_code>%load_ext sql
%sql postgresql://william:*****@ds4a.<EMAIL>.us-east-1.<EMAIL>/proyecto<jupyter_output><empty_output><jupyter_text>Showing the tables in the RDS<jupyter_code>%%sql
\dt<jupyter_output>* postgresql://william:***@<EMAIL>/proyecto
26 rows affected.<jupyter_text>Performing a query to the 'Local Consumption' table to look at the data<jupyter_code>%%sql
SELECT * FROM local_consuption LIMIT 5<jupyter_output>* postgresql://william:***@ds4a.ci<EMAIL>esfwje7.us-east-1.rds.amazonaws.com/proyecto
5 rows affected.<jupyter_text>Bringing the data to Jupiter and creating the DataFrame<jupyter_code>df = %sql SELECT * FROM local_consuption
df = df.DataFrame()<jupyter_output>* postgresql://william:***@ds4a.ci<EMAIL>esfwje7.us-east-1.rds.amazonaws.com/proyecto
8761 rows affected.<jupyter_text>Showing some information about the data<jupyter_code>df.sample(10)
df.dtypes<jupyter_output><empty_output><jupyter_text>None of the records have null data.<jupyter_code>df.isnull().sum()<jupyter_output><empty_output><jupyter_text>-------  Data Manipulation Impoting some libraries<jupyter_code>import seaborn as sns
import matplotlib.pyplot as plt
import pandas as pd<jupyter_output><empty_output><jupyter_text>Creating the column called 'date' to transform the data in the 'timebucket' column with the appropiate type<jupyter_code>df["date"] = pd.to_datetime(df["timebucket"])
df["date"].head()<jupyter_output><empty_output><jupyter_text>Showing the date range<jupyter_code>df["date"].min()
df["date"].max()<jupyter_output><empty_output><jupyter_text>For the first day of measurement there are four records while the other days have between 21 and 25 records<jupyter_code>df.groupby(["timebucket"])["timebucket"].count().unique()<jupyter_output><empty_output><jupyter_text>Creating the column called 'month' to extract the number of month for each date.<jupyter_code>df["month"] = df["date"].dt.month
df["month"].sample(5)<jupyter_output><empty_output><jupyter_text>Creating a dictionary to change the name of each month in a new column called 'month_name'.<jupyter_code>dict_month = {1: 'jan', 2:"feb", 3:"mar", 4:"apr", 5:"may", 6:"jun", 7:"jul", 8:"ago", 9:"sep", 10:"oct", 11:"nov", 12:"dic"}
df["month_name"]=df["month"].replace(dict_month)
df["month_name"].sample(5)<jupyter_output><empty_output><jupyter_text>Creating the column 'total' like the sum of the consumption of the 10 types of devices.<jupyter_code>df["total"] = df["jfp_1"]+df["jfp_2"]+df["heater"]+df["oven"]+df["fridge"]+df["washer_dryer"]+df["washer_dryer"]+df["room_outlets"]+df["fam_outlets"]+df["halllight"]+df["familylight"]
df["total"].head()<jupyter_output><empty_output><jupyter_text>Creating the column 'year_month' to extract just the year and month for each date.<jupyter_code>df["year_month"] = df["timebucket"].astype(str).str.slice(stop=7)
df["year_month"].head()<jupyter_output><empty_output><jupyter_text>The new DataFrame<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>-------   Univariate Analysis To analyze the variability in energy consumption measurements, the types of sensors are divided into three main categories: Connectors, appliances and lighting elements. **a) Connectors** Statistical data and boxplot<jupyter_code>df[["jfp_1", "jfp_2"]].describe()
plt.figure(figsize = (6,3))
ax = sns.boxplot(data=df[["jfp_1", "jfp_2"]], orient="h", palette="Set2")
#plt.title('Mean Consumption by Sensor')
plt.xlabel("Energy Consumption [kWhs]")
plt.ylabel("Sensor")
ax.yaxis.grid(False) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>**b) Household appliances** Statistical data and boxplot<jupyter_code>df[["heater", "oven", "fridge", "washer_dryer", "room_outlets", "fam_outlets"]].describe() 
plt.figure(figsize = (10,5))
ax = sns.boxplot(data=df[["heater", "oven", "fridge", "washer_dryer", "room_outlets", "fam_outlets"]], orient="h", palette="Set2")
plt.xlabel("Energy Consumption [kWhs]")
plt.ylabel("Sensor")
ax.yaxis.grid(False) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>**c) Lighting** Statistical data and boxplot<jupyter_code>df[["halllight", "familylight"]].describe()
plt.figure(figsize = (5,2))
ax = sns.boxplot(data=df[["halllight", "familylight"]], orient="h", palette="Set2")
plt.xlabel("Energy Consumption [kWhs]")
plt.ylabel("Sensor")
ax.yaxis.grid(False) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>Showing the average power consumption for each type of sensor<jupyter_code>mean_sensor = df.describe().loc["mean"].drop(["month","total"], axis=0)
mean_sensor=pd.DataFrame(mean_sensor).reset_index() 
mean_sensor
#sns.set_theme(style="whitegrid")
plt.figure(figsize = (10,4))
ax = sns.barplot(x="index", y="mean", data=mean_sensor)
plt.title('Mean Consumption by Sensor')
plt.xlabel("Sensor")
plt.ylabel("Energy Consumption [kWhs]")
ax.yaxis.grid(True) 
ax.xaxis.grid(False)<jupyter_output><empty_output><jupyter_text>Statistical data and boxplot from the total consumption<jupyter_code>pd.DataFrame(df["total"].describe())
plt.figure(figsize = (6,3))
ax = sns.boxplot(data=df["total"], orient="h", palette="Set2")
plt.xlabel("Energy Consumption [kWhs]")
plt.ylabel("Total Consumption")
ax.yaxis.grid(False) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>Complete statistical data<jupyter_code>df.describe().drop(["month"], axis=1)<jupyter_output><empty_output><jupyter_text>-------  Time series analysis Creating a DataFrame to show the average energy consumption of a home for each of the months in the dataset and then making a lineplot for each category of sensor and the Total consumption through the time.<jupyter_code>year_month = df.groupby(["year_month"])[["heater", "oven", "fridge", "washer_dryer",  "halllight", "familylight"]].mean()
year_month<jupyter_output><empty_output><jupyter_text>**a) Connectors**<jupyter_code>year_monthC1 = df.groupby(["year_month"])[["jfp_1", "jfp_2"]].mean()
#"jfp_1", "jfp_2","room_outlets", "fam_outlets",
plt.figure(figsize = (13,4))
ax=sns.lineplot(data=year_monthC1)
plt.title('Energy consumption over time')
plt.xlabel("Date")
plt.ylabel("Energy Consumption [kWhs]")
ax.yaxis.grid(True) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>**b) Household appliances**<jupyter_code>year_monthC2 = df.groupby(["year_month"])[["heater", "oven", "fridge", "washer_dryer", "room_outlets", "fam_outlets"]].mean()
#"jfp_1", "jfp_2","room_outlets", "fam_outlets",
plt.figure(figsize = (13,6))
ax=sns.lineplot(data=year_monthC2)
plt.title('Energy consumption over time')
plt.xlabel("Date")
plt.ylabel("Energy Consumption [kWhs]")
ax.yaxis.grid(True) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>**c) Lighting**<jupyter_code>year_monthC3 = df.groupby(["year_month"])[[ "halllight", "familylight"]].mean()
#"jfp_1", "jfp_2","room_outlets", "fam_outlets",
plt.figure(figsize = (13,4))
ax=sns.lineplot(data=year_monthC3)
plt.title('Energy consumption over time')
plt.xlabel("Date")
plt.ylabel("Energy Consumption [kWhs]")
ax.yaxis.grid(True) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>**d) Total consumption**<jupyter_code>year_month_total = df.groupby(["year_month"])["total"].mean()
year_month_total
plt.figure(figsize = (13,4))
ax=sns.lineplot(data=year_month_total)
plt.title('Total consumption over time')
plt.xlabel("Date")
plt.ylabel("Energy Consumption [kWhs]")
ax.yaxis.grid(True) 
ax.xaxis.grid(True)<jupyter_output><empty_output><jupyter_text>-------  Grouping by months Calculating the average energy consumption reported by each of the sensors and grouped for each month of the year<jupyter_code>month_mean = df.groupby(["month"])[["jfp_1", "jfp_2","heater", "oven", "fridge", "washer_dryer", "room_outlets", "fam_outlets", "halllight", "familylight"]].mean()
month_mean.rename(index=dict_month, inplace=True)
month_mean<jupyter_output><empty_output><jupyter_text>Heatmap of the average energy consumption<jupyter_code>plt.figure(figsize = (12,6))
sns.heatmap(month_mean, cmap="Reds")
plt.title('Energy consumption [kWhs]')<jupyter_output><empty_output><jupyter_text>Calculating the total energy consumption in a home for each of the months of the year.<jupyter_code>month_meanT = df.groupby(["month"])["total"].mean()
month_meanT.rename(index=dict_month, inplace=True)
month_meanT=pd.DataFrame(month_meanT).reset_index()
month_meanT<jupyter_output><empty_output><jupyter_text>Barplot of the total energy consumption for each of the months.<jupyter_code>plt.figure(figsize = (10,3))
ax = sns.barplot(x="month", y="total", data=month_meanT)
plt.title('Mean Consumption by Month')
plt.xlabel("Month")
plt.ylabel("Energy Consumption [kWhs]")
ax.yaxis.grid(True) 
ax.xaxis.grid(False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1629200979301135, 'fraction_numerical': 0.011128421989761852, 'mean_word_length': 9.294387170675831, 'pattern_counts': {'":': 0, '<': 123, '<?xml version=': 0, '>': 123, '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': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '563861', 'license': 'MIT', 'n_tokens_mistral': 3459, 'n_tokens_neox': 3307, 'n_words': 601, 'path': 'Exploratory Data Analysis/1.EDA_Local_Consumption.ipynb', 'repo_name': 'eljimenezj/Team_51_DS4A_2021', 'size': '9031'}