{"cells":[{"metadata":{"id":"KwinS1WNPPyw","colab_type":"text"},"cell_type":"markdown","source":"**EDA for training data**"},{"metadata":{"trusted":true,"id":"5OwCRSiEPPy0","colab_type":"code","colab":{}},"cell_type":"code","source":"\"\"\"reading training data\"\"\"\nimport pandas as pd\nimport plotly.express as px\n\ntrain_data= pd.read_csv(\"../input/ashrae-energy-prediction/train.csv\")\nweather = pd.read_csv(\"../input/ashrae-energy-prediction/weather_train.csv\")\nmetadata = pd.read_csv(\"../input/ashrae-energy-prediction/building_metadata.csv\")\n","execution_count":null,"outputs":[]},{"metadata":{"id":"gR7v6hYPPPzW","colab_type":"text"},"cell_type":"markdown","source":"Reducing memory size"},{"metadata":{"trusted":true,"id":"M5SgDBa3PPzf","colab_type":"code","outputId":"99dcd32a-2a8d-43a6-bf6a-ff3cca7ef238","colab":{"base_uri":"https://localhost:8080/","height":70}},"cell_type":"code","source":"## Function to reduce the DF size\nimport numpy as np\ndef reduce_mem_usage(df, verbose=True):\n    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    start_mem = df.memory_usage().sum() / 1024**2    \n    for col in df.columns:\n        col_type = df[col].dtypes\n        if col_type in numerics:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)    \n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df\n\n\ntrain_data = reduce_mem_usage(train_data)\n\nweather = reduce_mem_usage(weather)\n\nmetadata = reduce_mem_usage(metadata)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"id":"h-iWSNCHPPzs","colab_type":"code","outputId":"e147a537-4698-4362-ff7f-5189523acad5","colab":{"base_uri":"https://localhost:8080/","height":287}},"cell_type":"code","source":"\"\"\"getting description\"\"\"\ntrain_data.describe()","execution_count":null,"outputs":[]},{"metadata":{"id":"utTjIlxgPP0B","colab_type":"text"},"cell_type":"markdown","source":"**Cnclusion**\nFrom the description we can conclude following\n* there is no missing value present \n* meter_reading is right skewed\n"},{"metadata":{"trusted":true,"id":"4IoKNzh3PP0L","colab_type":"code","outputId":"39c9eb70-a135-49b4-cbe1-dc16c9c1a976","colab":{"base_uri":"https://localhost:8080/","height":616}},"cell_type":"code","source":"\"\"\"EDA for each variable\"\"\"\nimport plotly.express as px\nout = train_data.meter.value_counts().reset_index()\n\nfig = px.bar(out, x='index' ,  y='meter')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"wgu3iu6nXBMN","colab_type":"text"},"cell_type":"markdown","source":"**Average meter readin for each meter**"},{"metadata":{"id":"3hWbcGejThUU","colab_type":"code","outputId":"36a56b54-be8c-4ae8-cfdd-be7113b2ec4a","colab":{"base_uri":"https://localhost:8080/","height":616},"trusted":true},"cell_type":"code","source":"temp = train_data.groupby(['meter']).mean().reset_index()\nfig = px.bar(temp, x=\"meter\" ,  y='meter_reading')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"id":"2dmJgLySPP0Q","colab_type":"code","outputId":"325ced17-296e-4485-a988-88b36bc39b7a","colab":{"base_uri":"https://localhost:8080/","height":306}},"cell_type":"code","source":"\"\"\"histogram for skewed meter_reading\"\"\"\nimport matplotlib.pyplot as plt\ntrain_data['meter_reading'].hist(bins = 100)\n","execution_count":null,"outputs":[]},{"metadata":{"id":"LAfTBxFHSRZu","colab_type":"code","outputId":"390e7a3a-b87b-4f2b-8a4c-7b5864309901","colab":{"base_uri":"https://localhost:8080/","height":282},"trusted":true},"cell_type":"code","source":"import numpy as np\ntrain_data['log_meter_reading'] = np.log1p(train_data['meter_reading']) \ntrain_data['log_meter_reading'].hist(bins = 100)\n","execution_count":null,"outputs":[]},{"metadata":{"id":"ca5UnpYuXjl-","colab_type":"text"},"cell_type":"markdown","source":"**dealing with time series expansion**"},{"metadata":{"trusted":true,"colab_type":"code","id":"5fYIvPNje93V","colab":{}},"cell_type":"code","source":"\"\"\"conevrting time stamp into timeseries type\"\"\"\ntrain_data['timestamp'] = pd.to_datetime(train_data['timestamp'] )\n\n","execution_count":null,"outputs":[]},{"metadata":{"id":"P_GkqCqfb7Cy","colab_type":"text"},"cell_type":"markdown","source":"Grouping different meter according to their tyep and then plotting with date time "},{"metadata":{"id":"1PBpH3QqZlOt","colab_type":"code","outputId":"a8a713f0-4fff-4557-ab21-418c57326d8d","colab":{"base_uri":"https://localhost:8080/","height":542},"trusted":true},"cell_type":"code","source":"from plotly.subplots import make_subplots\nimport plotly.graph_objects as go\n\nfig = make_subplots(rows=2, cols=2)\n\n\"\"\"for meter type 0\"\"\"\ntemp = train_data.loc[train_data['meter'] == 0].groupby(['timestamp']).sum().reset_index()\nfig.add_trace(\n    go.Scatter(x=temp['timestamp'], y=temp['meter_reading']),\n    row=1, col=1\n)\n\n\"\"\"for meter type 1\"\"\"\ntemp = train_data.loc[train_data['meter'] == 1].groupby(['timestamp']).sum().reset_index()\nfig.add_trace(\n    go.Scatter(x=temp['timestamp'], y=temp['meter_reading']),\n    row=1, col=2\n)\n\n\n\"\"\"for meter type 2\"\"\"\ntemp = train_data.loc[train_data['meter'] == 2].groupby(['timestamp']).sum().reset_index()\nfig.add_trace(\n    go.Scatter(x=temp['timestamp'], y=temp['meter_reading']),\n    row=2, col=1\n)\n\n\"\"\"for meter type 3\"\"\"\ntemp = train_data.loc[train_data['meter'] == 3].groupby(['timestamp']).sum().reset_index()\nfig.add_trace(\n    go.Scatter(x=temp['timestamp'], y=temp['meter_reading']),\n    row=2, col=2\n)\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"Dz4qSxWgeWWg","colab_type":"text"},"cell_type":"markdown","source":"**Conclusion**\n\n\n1.   Only meter type 0 follows the seasonality pattern\n2.   Bills for meter type 1,2 are preety high\n"},{"metadata":{"id":"1CqA84Nffxpc","colab_type":"text"},"cell_type":"markdown","source":"**moving on with Building Metadata**"},{"metadata":{"id":"K8wsG-6YdEXE","colab_type":"code","outputId":"8703f562-2864-468e-8517-3c4536a5d492","colab":{"base_uri":"https://localhost:8080/","height":141},"trusted":true},"cell_type":"code","source":"\"\"\"getting missing data\"\"\"\nmetadata.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"id":"Gu6dWju3iBSN","colab_type":"text"},"cell_type":"markdown","source":"**Conclusion**\n\n\n1.   Year Built column: since 774 columns are empty, this may be becaue either building is under construction or building is before 1900 which is the min year present in data\n\n\n\n2.   List item\n\n\n\n"},{"metadata":{"id":"m-nY4Nqofiyf","colab_type":"code","outputId":"5cce0d7a-231f-4ab0-b693-80e217e0860a","colab":{"base_uri":"https://localhost:8080/","height":616},"trusted":true},"cell_type":"code","source":"fig = px.box(metadata, y=\"year_built\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"p0Any0NXkBl_","colab_type":"text"},"cell_type":"markdown","source":"Hence 50% of houses bult between 1949-1995 as here q1 starts and q3 stops"},{"metadata":{"id":"Nih4gzfvgSff","colab_type":"code","outputId":"ef470b77-cb19-4f00-a3af-264a52eae4b5","colab":{"base_uri":"https://localhost:8080/","height":616},"trusted":true},"cell_type":"code","source":"fig = px.box(metadata, y=\"floor_count\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"Gb7IPIBlk5h2","colab_type":"text"},"cell_type":"markdown","source":"**Merging Building metadata with training data** "},{"metadata":{"id":"uxbszEYShRue","colab_type":"code","colab":{},"trusted":true},"cell_type":"code","source":"merged_data = pd.merge(train_data, metadata, on=\"building_id\")\n","execution_count":null,"outputs":[]},{"metadata":{"id":"b6m77dQCrrhb","colab_type":"text"},"cell_type":"markdown","source":"**Getting energy usage of various building types**"},{"metadata":{"id":"v2--WoaBo1FR","colab_type":"code","outputId":"89449d29-522c-4eec-ec61-77050ff13ebb","colab":{"base_uri":"https://localhost:8080/","height":1000},"trusted":true},"cell_type":"code","source":"from plotly.subplots import make_subplots\nimport plotly.graph_objects as go\n\nfig = make_subplots(rows=4, cols=4)\nusage = list(merged_data.primary_use.unique())\ncount = 0\n\n\nfor rows in [1,2,3,4]:\n  for cols in [1,2,3,4]:\n\n    temp = merged_data.loc[merged_data.primary_use == usage[count]].groupby(['timestamp']).sum().reset_index()\n    count += 1\n    fig.add_trace(\n        go.Scatter(x=temp['timestamp'], y=temp['meter_reading']),\n        row=rows, col=cols\n    )\n\n\nfig.update_layout(height=1200, width=1600, title_text=\"Subplots\")\ncount = 0\nfor rows in [1,2,3,4]:\n  for cols in [1,2,3,4]:\n    fig.update_xaxes(title_text=usage[count], row=rows, col=cols)\n    count += 1\n\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"m61dEsiksClb","colab_type":"text"},"cell_type":"markdown","source":"**Getting average usage of each building type**"},{"metadata":{"id":"UO2ISlSRqn4o","colab_type":"code","outputId":"2c50dc39-39af-4f78-a686-5abd4736e7f3","colab":{"base_uri":"https://localhost:8080/","height":616},"trusted":true},"cell_type":"code","source":"\ntemp = merged_data.groupby([\"primary_use\"]).mean()['meter_reading'].reset_index()\nfig = px.bar(temp, x=\"primary_use\" ,  y='meter_reading')\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{"id":"E2UjJhyss8G7","colab_type":"text"},"cell_type":"markdown","source":"**Getting overall usage of each building type**"},{"metadata":{"id":"-jAQERopsByS","colab_type":"code","outputId":"c0025142-a910-4795-dcdb-3e33c7222f7d","colab":{"base_uri":"https://localhost:8080/","height":616},"trusted":true},"cell_type":"code","source":"temp = merged_data.groupby([\"primary_use\"]).sum()['meter_reading'].reset_index()\nfig = px.bar(temp, x=\"primary_use\" ,  y='meter_reading')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"JZZ-6hqVtQum","colab_type":"text"},"cell_type":"markdown","source":"**Moving on next metadatfile i.e. Weather**"},{"metadata":{"id":"UDwV569Ms36R","colab_type":"code","outputId":"230eddc0-0e15-4e0e-f11c-7763bc7d8f8a","colab":{"base_uri":"https://localhost:8080/","height":287},"trusted":true},"cell_type":"code","source":"weather.describe()","execution_count":null,"outputs":[]},{"metadata":{"id":"Si0V811EPWDl","colab_type":"code","outputId":"11aac7b6-2670-44c2-b9aa-fc8a32d31237","colab":{"base_uri":"https://localhost:8080/","height":616},"trusted":true},"cell_type":"code","source":"#weather.air_temperature.nunique()\nfig = px.box(weather, y=\"air_temperature\")\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"id":"nToChLofLVm7","colab_type":"text"},"cell_type":"markdown","source":"**merging weather data with prevous merged data data**"},{"metadata":{"id":"4I1ocmgXi1FO","colab_type":"code","colab":{},"trusted":true},"cell_type":"code","source":"\"\"\"converting timestamp data to timestamp type\"\"\"\nweather['timestamp'] = pd.to_datetime(weather['timestamp'] )","execution_count":null,"outputs":[]},{"metadata":{"id":"yQWjvJsJjbCP","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":197},"outputId":"287608b5-8f56-4008-c4c2-b15ca99b93c9","trusted":true},"cell_type":"code","source":"weather.head(5)","execution_count":null,"outputs":[]},{"metadata":{"id":"5OCRgLNSjfW7","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":167},"outputId":"d7a2e03d-4927-4ba2-e0d7-fcf22412aa41","trusted":true},"cell_type":"code","source":"merged_data.head(4)\n\n\n","execution_count":null,"outputs":[]},{"metadata":{"id":"YPbgM7tBtwTB","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":34},"outputId":"a102eff2-7b97-486a-f511-85dd164a4f8d","trusted":true},"cell_type":"code","source":"\"\"\"merging dataframes on site_id and timestamp\"\"\"\n\nnew_merges = pd.merge(merged_data, weather, on=['timestamp','site_id'])\n\n\"\"\"verifying length \"\"\"\nprint(len(new_merges))","execution_count":null,"outputs":[]},{"metadata":{"id":"vFJTKlRYohkG","colab_type":"text"},"cell_type":"markdown","source":"**COnclusion**\n\nHere length of merged data is not equal to original data hence there are some sets of timestamp and site_id combination whch are not present in weather data."},{"metadata":{"id":"M1JINJn7ogZ2","colab_type":"code","colab":{},"trusted":true},"cell_type":"code","source":"\"\"\"counting the number of missing timestamp and site combination data from weather file\"\"\"\nprint('missing' ,  len(set(merged_data.timestamp.apply(str) + \"_\" + merged_data.site_id.apply(str)) - set(weather.timestamp.apply(str) + \"_\" + weather.site_id.apply(str)) ))","execution_count":null,"outputs":[]},{"metadata":{"id":"We0EglIRpWaw","colab_type":"text"},"cell_type":"markdown","source":"But this occurs for multiple buildings and hence total count rose to following"},{"metadata":{"id":"sSSFrK56NjWy","colab_type":"code","outputId":"60b1d362-9f71-4e04-b619-118abe22f094","colab":{"base_uri":"https://localhost:8080/","height":34},"trusted":true},"cell_type":"code","source":"print(\"data lost due to missing of wether data\" , len(merged_data)- len(new_merges))","execution_count":null,"outputs":[]},{"metadata":{"id":"bR-2aqaXpvHe","colab_type":"text"},"cell_type":"markdown","source":"Taking pictorial look over the data"},{"metadata":{"id":"jHg7we-NlEV-","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":663},"outputId":"27a2c018-d356-4709-f4c2-6a143a056762","trusted":true},"cell_type":"code","source":"\"\"\"checking missing values\"\"\"\nnew_merges.isnull().sum().plot(kind= \"bar\")\nprint(new_merges.isnull().sum())","execution_count":null,"outputs":[]},{"metadata":{"id":"3U5Q5Ng3lpnK","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":616},"outputId":"e8fc5d3d-9aa4-4cb3-817f-5a1d006230e5","trusted":true},"cell_type":"code","source":"import plotly.express as px\ntemp = new_merges.groupby(['air_temperature']).sum()['meter_reading'].reset_index()\nfig = px.bar(temp, x=\"air_temperature\" ,  y='meter_reading')\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{"id":"VQqtIhdbm2Lj","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":616},"outputId":"3e677188-b005-4499-fb47-7781cf4aa717","trusted":true},"cell_type":"code","source":"import plotly.express as px\ntemp = new_merges.groupby(['wind_speed']).sum()['meter_reading'].reset_index()\nfig = px.bar(temp, x=\"wind_speed\" ,  y='meter_reading')\nfig.show()\n","execution_count":null,"outputs":[]},{"metadata":{"id":"R91r1v4ktoe_","colab_type":"code","colab":{"base_uri":"https://localhost:8080/","height":616},"outputId":"a3e27552-5d38-4439-af32-08d43edc79e8","trusted":true},"cell_type":"code","source":"import plotly.express as px\ntemp = new_merges.groupby(['dew_temperature']).sum()['meter_reading'].reset_index()\nfig = px.bar(temp, x=\"dew_temperature\" ,  y='meter_reading')\nfig.show()\n","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"},"colab":{"name":"ASHREYA EDA with Plots.ipynb","provenance":[],"collapsed_sections":[]}},"nbformat":4,"nbformat_minor":1}