{"cells":[{"metadata":{},"cell_type":"markdown","source":"# EDA"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"import numpy  as np\nimport pandas as pd\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport gc\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n\n#将图表嵌入到notebook中\n%matplotlib inline   ","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"import os\nprint(os.listdir('/kaggle/input/ashrae-energy-prediction/'))","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"%%time\nroot='/kaggle/input/ashrae-energy-prediction/'\ntrain_df=pd.read_csv(root+'train.csv')\n#转换日期格式，方便以后的处理\ntrain_df[\"timestamp\"] = pd.to_datetime(train_df[\"timestamp\"], format='%Y-%m-%d %H:%M:%S')\nweather_train_df = pd.read_csv(root + 'weather_train.csv')\nweather_test_df = pd.read_csv(root + 'weather_test.csv')\n\nweather_train_df[\"timestamp\"] = pd.to_datetime(weather_train_df[\"timestamp\"], format='%Y-%m-%d %H:%M:%S')\nweather_test_df[\"timestamp\"] = pd.to_datetime(weather_test_df[\"timestamp\"], format='%Y-%m-%d %H:%M:%S')\n\nbuilding_meta_df = pd.read_csv(root + 'building_metadata.csv')\n\ntest_df = pd.read_csv(root + 'test.csv')\ntest_df[\"timestamp\"] = pd.to_datetime(test_df[\"timestamp\"], format='%Y-%m-%d %H:%M:%S')\n\nsample_submission = pd.read_csv(root + 'sample_submission.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# size of data"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"print('Size of train_df data', train_df.shape)\nprint('Size of weather_train_df data', weather_train_df.shape)\nprint('Size of weather_test_df data', weather_test_df.shape)\nprint('Size of building_meta_df data', building_meta_df.shape)\nprint('Size of test_df data', test_df.shape)\nprint('Size of sample_submission data', sample_submission.shape)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Take a quick look at each data"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"weather_train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"weather_test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"building_meta_df.head()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"sample_submission.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# we can learn lot from Data  download homepage"},{"metadata":{},"cell_type":"markdown","source":"building_metadata.csv\n> - site_id:0-15\n> - building_id:0-1448\n> - primary_use:Education Office an other(14) total 16\n> - square_feet:283-875k\n> - year_built:1900-2017\n> - floor_count:1-26\n\ntrain.csv\n> - timestamp :2016.1.1-2017.1.1\n> - 0: electricity, 1: chilledwater, 2: steam, 3: hotwater\n> - total data is a year\n\ntest.csv\n> - timestamp :2017.1.1-2019.1.1\n> - total data is 2 year"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"## Function to reduce the DF size\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","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Reduce Memory"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"train_df = reduce_mem_usage(train_df)\ntest_df = reduce_mem_usage(test_df)\n\nweather_train_df = reduce_mem_usage(weather_train_df)\nweather_test_df = reduce_mem_usage(weather_test_df)\nbuilding_meta_df = reduce_mem_usage(building_meta_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Exploratory Data Analysis"},{"metadata":{},"cell_type":"markdown","source":" Exploratory Data Analysis (EDA) is an open-ended process where we calculate statistics and make figures to find trends, anomalies, patterns, or relationships within the data.\n \n glimpse at meter_reading(y)"},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(15,5))\ntrain_df['meter_reading'].plot()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Analyse train.csv"},{"metadata":{},"cell_type":"markdown","source":"look that by ganfear:[ Missing data and zeros visualized](https://www.kaggle.com/ganfear/missing-data-and-zeros-visualized)\n\ndivide train data into building meter and time,\nthen seek missing data and zeros and the number of data"},{"metadata":{"trusted":true},"cell_type":"code","source":"#set_index\ntrain=train_df.set_index(['timestamp'])\n\n#plot missing values per building/meter\nf,a=plt.subplots(1,4,figsize=(20,30))\nfor meter in np.arange(4):\n    df=train[train.meter==meter].copy().reset_index()\n    df['timestamp']=pd.to_timedelta(df.timestamp-pd.to_datetime(\"2016-01-01\")).dt.total_seconds()/3600\n    df['timestamp']=df.timestamp.astype(int)\n    df.timestamp=df.timestamp-df.timestamp.min()\n    missmap=np.empty((1449,df.timestamp.max()+1))\n    missmap.fill(np.nan)\n    for l in df.values:\n        #print(l)\n        if l[2]!=meter:continue\n        missmap[int(l[1]),int(l[0])]=0 if l[3]==0 else 1\n    a[meter].set_title(f'meter {meter:d}')\n    sns.heatmap(missmap,cmap='Paired',ax=a[meter],cbar=True)\n        \n        ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Legend:\n* X axis: hours elapsed since Jan 1st 2016, for each of the 4 meter types\n* Y axis: building_id\n* Brown: meter reading available with non-zero value\n* Light blue: meter reading available with zero value\n* White: missing meter reading\n\nwe can draw some question:\n* building_id 0-100 time0-2315 meter_reading is all zero they are error data,so we should clear them\n* meter1 and meter3 are chillwater and hotwater so some building have Seasonal characteristics\n"},{"metadata":{},"cell_type":"markdown","source":"# Examine Missing Values"},{"metadata":{},"cell_type":"markdown","source":"checking missing data for train_df"},{"metadata":{"trusted":true},"cell_type":"code","source":"total = train_df.isnull().sum().sort_values(ascending=False)\npercent=(train_df.isnull().sum()/train_df.isnull().count()*100).sort_values(ascending=False)\nmissing_train_data=pd.concat([total,percent],axis=1,keys=['Total','Percent'])\nmissing_train_data","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"checking missing data for weather_train_df"},{"metadata":{"trusted":true},"cell_type":"code","source":"# checking missing data\ntotal = weather_train_df.isnull().sum().sort_values(ascending = False)\npercent = (weather_train_df.isnull().sum()/weather_train_df.isnull().count()*100).sort_values(ascending = False)\nmissing_weather_data  = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_weather_data.head(9)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"checking missing data for weather_test_df"},{"metadata":{"trusted":true},"cell_type":"code","source":"# checking missing data\ntotal = weather_test_df.isnull().sum().sort_values(ascending = False)\npercent = (weather_test_df.isnull().sum()/weather_test_df.isnull().count()*100).sort_values(ascending = False)\nmissing_weather_data  = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_weather_data.head(9)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"checking missing data for building_meta_df"},{"metadata":{"trusted":true},"cell_type":"code","source":"# checking missing data\ntotal = building_meta_df.isnull().sum().sort_values(ascending = False)\npercent = (building_meta_df.isnull().sum()/building_meta_df.isnull().count()*100).sort_values(ascending = False)\nmissing_building_meta_df  = pd.concat([total, percent], axis=1, keys=['Total', 'Percent'])\nmissing_building_meta_df.head(9)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df=train_df.merge(building_meta_df,on='building_id',how='left')\ntest_df=test_df.merge(building_meta_df,on='building_id',how='left')\n\ntrain_df=train_df.merge(weather_train_df,on=['site_id','timestamp'],how='left')\ntest_df=test_df.merge(weather_test_df,on=['site_id','timestamp'],how='left')\n\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 1, figsize=(14, 6), dpi=100)\ntrain_df[['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes, label='By hour', alpha=0.7).set_ylabel('Meter reading', fontsize=14);\ntrain_df[['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes, label='By day', alpha=1).set_ylabel('Meter reading', fontsize=14);\naxes.set_title('Mean Meter reading by hour and day', fontsize=16);\n\n#显示图中的标签\naxes.legend();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"meter_reading 按照小时以及天的平均值 从3月到7月以及11月的一段时间值很大，这有些奇怪。\n\n需要再做一下详细的分析。\n\nsite_id 有16个，可以分别看一下这16个site的meter_reading"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(8,2,figsize=(15, 30), dpi=100)\nfor i in range(train_df['site_id'].nunique()):\n    train_df[train_df['site_id'] == i][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[i%8][i//8], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\n    train_df[train_df['site_id'] == i][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[i%8][i//8], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n    axes[i%8][i//8].legend();\n    axes[i%8][i//8].set_title('site_id {}'.format(i), fontsize=13);\n    plt.subplots_adjust(hspace=0.45)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"we can see that the meter_reading of site_id 13 is too big and very like total data \n\nwe can see it deeply"},{"metadata":{},"cell_type":"markdown","source":"Mean meter reading by primary_use for site_id==13"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(8,2,figsize=(14, 30), dpi=100)\nfor i, use in enumerate(train_df['primary_use'].value_counts().index.to_list()):\n    try:\n        train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == use)][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[i%8][i//8], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\n        train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == use)][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[i%8][i//8], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n        axes[i%8][i//8].legend();\n    except TypeError:\n        pass\n    axes[i%8][i//8].set_title(use, fontsize=13);\n    plt.subplots_adjust(hspace=0.45)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\n\nOne more level of conclusions:\n\n* site_id == 13 and primary_use == Education looks a lot like a general mean for meter reading. So it is really invest a lost into the whole data\n* site_id == 13 and primary_use == Technology/Science also have 0's in meter readings in January. Just like site_id 0, that we found earlier.\n\nLets keep digging and see what meter type is responsible for such weird look of the meter reading for site_id 13 and primary_use Education."},{"metadata":{},"cell_type":"markdown","source":"Mean meter reading by meter type for primary_use==Education and site_id==13"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(3,1,figsize=(14, 18), dpi=100)\nfor i in train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == 'Education')]['meter'].value_counts(dropna=False).index.to_list():\n    train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == 'Education') & (train_df['meter'] == i)][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[i], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\n    train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == 'Education') & (train_df['meter'] == i)][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[i], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n    axes[i].legend();\n    axes[i].set_title('Meter: ' + str(i), fontsize=13);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Fine. What we have right now is that site_id 13 with primary_use Education and meter type 2 is responsible for this mess.\nBut we need to go deeper. There are 17 buildings that fall under these criteria. No problem. Another plot."},{"metadata":{"trusted":true},"cell_type":"code","source":"len(train_df[(train_df['primary_use']=='Education')&(train_df['site_id']==13)&(train_df['meter']==2)]['building_id'].value_counts().index.to_list())","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(9,2,figsize=(14, 36), dpi=100)\nfor i, building in enumerate(train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == 'Education') & (train_df['meter'] == 2)]['building_id'].value_counts(dropna=False).index.to_list()):\n    train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == 'Education') & (train_df['meter'] == 2) & (train_df['building_id'] == building)][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[i%9][i//9], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\n    train_df[(train_df['site_id'] == 13) & (train_df['primary_use'] == 'Education') & (train_df['meter'] == 2) & (train_df['building_id'] == building)][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[i%9][i//9], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n    axes[i%9][i//9].legend();\n    axes[i%9][i//9].set_title('building_id: ' + str(building), fontsize=13);\n    plt.subplots_adjust(hspace=0.45)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After going through all the buildings, we can find the problem ,It is building 1099"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(3,1,figsize=(14, 20), dpi=100)\n\ntrain_df[(train_df['meter'] == 2) & (train_df['building_id'] == 1099)][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[0], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\ntrain_df[(train_df['meter'] == 2) & (train_df['building_id'] == 1099)][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[0], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n\ntrain_df[['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[1], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\ntrain_df[['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[1], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n\ntrain_df[~((train_df['meter'] == 2) & (train_df['building_id'] == 1099))][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[2], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\ntrain_df[~((train_df['meter'] == 2) & (train_df['building_id'] == 1099))][['timestamp', 'meter_reading']].set_index('timestamp').resample('D').mean()['meter_reading'].plot(ax=axes[2], alpha=1, label='By day', color='tab:orange').set_xlabel('');\n\naxes[0].set_title('building_id==1099 and meter==2', fontsize=13);\naxes[1].set_title('Full dataset', fontsize=13);\naxes[2].set_title('building_id 1099 excluded', fontsize=13);\nplt.subplots_adjust(hspace=0.45)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" so we should clean data of building_id ==1099 and meter ==2 "},{"metadata":{},"cell_type":"markdown","source":"### Correlations\nOne way to try and understand the data is by looking for correlations between the features and the target. We can calculate the Pearson correlation coefficient between every variable and the target using the `.corr` dataframe method.\n\n* .00-.19 “very weak”\n*  .20-.39 “weak”\n*  .40-.59 “moderate”\n*  .60-.79 “strong”\n* .80-1.0 “very strong”"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Find correlations with the target and sort\ncorrelations = train_df.corr()['meter_reading'].sort_values()\n\n# Display correlations\nprint('Most Positive Correlations:\\n', correlations.tail(7))\nprint('\\nMost Negative Correlations:\\n', correlations.head(4))\n\ncorrs = train_df.corr()\ncorrs","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font color=#000000 size=6 face=\"宋体\"> plot dist curves for train and  test weather data for the given column name </font>"},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_dist_col(column):\n    fig,ax=plt.subplots(figsize=(10,10))\n    sns.distplot(weather_train_df[column].dropna(),color='green',ax=ax).set_title(column,fontsize=16)\n    sns.distplot(weather_test_df[column].dropna(),color='purple',ax=ax).set_title(column,fontsize=16)\n    plt.xlabel(column,fontsize=15)\n    plt.legend(['train','test'])\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('air_temperature')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('cloud_coverage')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df['cloud_coverage'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('dew_temperature')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('precip_depth_1_hr')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df['precip_depth_1_hr'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('sea_level_pressure')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('wind_direction')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('wind_speed')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#风速小于0，这里不确定是数据有问题还是因为逆风\nlen(weather_train_df[weather_train_df['precip_depth_1_hr']<0])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font color=#000000 size=6 face=\"宋体\"> plot dist curves for building data for the given column name </font>"},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_dist_col(column):\n    fig,ax=plt.subplots(figsize=(10,10))\n    sns.distplot(building_meta_df[column].dropna(),color='green',ax=ax).set_title(column,fontsize=16)\n       \n    plt.xlabel(column,fontsize=15)\n    plt.legend(['building'])\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('floor_count')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('year_built')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df['year_built'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_dist_col('square_feet')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font color='red' size=4 face=\"微软雅黑\">square_feet的值比较大，应该做平滑处理 </font>"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font color=#000000 size=6 face=\"微软雅黑\">simple time series analysis </font>"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df_sub_1099=train_df[~((train_df['meter'] == 2) & (train_df['building_id'] == 1099))]\nts=train_df_sub_1099.groupby([\"timestamp\"])[\"meter_reading\"].mean()\nts.astype('float')\nplt.figure(figsize=(16,8))\nplt.title('average meter_reading with time')\nplt.xlabel('timestamp')\nplt.ylabel('meter_reading')\nplt.plot(ts)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(16,6))\nplt.plot(ts.rolling(window=12).mean(),label='Rolling Mean')\nplt.plot(ts.rolling(window=12).std(),label='Rolling sd')\nplt.legend()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"标准差是有规律的，可能周期是一星期？"},{"metadata":{},"cell_type":"markdown","source":"<font  size=4 face=\"微软雅黑\">查看每个site_id 各有多少类型的建筑 </font>"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df.groupby('site_id').primary_use.agg(lambda x:x.value_counts().to_dict()).to_dict()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df.groupby('site_id').building_id.agg(lambda x:x.value_counts().to_dict()).to_dict()[0]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font  size=4 face=\"微软雅黑\">查看每个site_id 的24小时的平均温度数据 </font>"},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(8,2,figsize=(14, 20), dpi=100)\n#weather_train_df[weather_train_df['site_id']==0].plot()\n\nimport datetime\n# plt.figure(figsize=(16,6))\n\n# plt.xlabel('timestamp')\n# plt.ylabel('air_temperature')\n\n#plt.plot(weather_train_df[])\n#plt.legend()\nweather_train_df['hour']=weather_train_df.timestamp.dt.hour\nweather_train_df_mean_by_hour=weather_train_df.groupby(['site_id','hour']).mean()\n# #train_df[train_df['site_id'] == i][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(ax=axes[i%8][i//8], alpha=0.8, label='By hour', color='tab:blue').set_ylabel('Mean meter reading', fontsize=13);\nfor i in range(16):\n    weather_train_df_mean_by_hour[i*24:(i+1)*24]['air_temperature'].plot(ax=axes[i%8][i//8])\n    axes[i%8][i//8].legend();\n    axes[i%8][i//8].set_title('site_id {} max temperature hour is {}'.format(i,np.argmax(weather_train_df_mean_by_hour[i*24:(i+1)*24]['air_temperature'])[1]), fontsize=13);\nplt.subplots_adjust(hspace=0.8)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font  size=3 face=\"微软雅黑\">  &ensp; &ensp; 从上图我们可以发现一些问题，不同的site_id 的最高温度在不同的时间，比如site_id=13地区的最高温度在22点或23点左右，这是有问题的。这说明weather_data里面的timestamp是有问题的。我们需要校准时间，这样和traain_df表连接后才是有意义的。</font>"},{"metadata":{},"cell_type":"markdown","source":"<font  size=3 face=\"微软雅黑\">  &ensp; &ensp; 温度最高的时间应该是在14：00从上图我们 每个site_id的最高温度时间中，我们可以计算出时间差。</font> <br/><br/>&ensp; &ensp; <font  size=3 face=\"微软雅黑\" color=\"blue\">我们将会使用这个差来重新调整weather数据中的timestamp    site_GMT_offsets = [-5, 0, -9, -6, -8, 0, -6, -6, -5, -7, -8, -6, 0, -7, -6, -6]</font>"},{"metadata":{},"cell_type":"markdown","source":"<font  size=6 face=\"微软雅黑\">缺失值填补探索\n</font>"},{"metadata":{},"cell_type":"markdown","source":"<font  size=3 face=\"微软雅黑\">  &ensp; &ensp; 从建模后的特征重要性以及尝试各种特征后得出 air_temperature以及dew_temperature\n是和能源使用情况最相关，最重要的特征。训练集中的 air_temperature和dew_temperature分别有 4%和8%的缺失，测试集中分别有 4%和11%的缺失。\n</font>"},{"metadata":{},"cell_type":"markdown","source":"<font  size=5 face=\"微软雅黑\">  &ensp; &ensp; air_temperature</font>"},{"metadata":{},"cell_type":"markdown","source":"#load training data 2016\nweather=pd.read_csv(root + 'weather_train.csv',parse_dates=['timestamp'])\nweather.head()#pivot to plot"},{"metadata":{"trusted":true},"cell_type":"code","source":"#load training data 2016\nweather_train=pd.read_csv(root+'weather_train.csv', parse_dates=['timestamp'])\nweather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#pivot to plot\nwmatrix_train=weather_train.pivot(index='timestamp',columns='site_id',values='air_temperature')\nwmatrix_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#寻找训练集中有最多缺失值的site_id\nsite_id=wmatrix_train.count().idxmin()\n\n#挑选出一段时间进行观测\nstart_date,end_date=datetime.date(2016,1,1),datetime.date(2016,1,9)\n\n#初始化绘图\nf,ax=plt.subplots(figsize=(18,6))\n\n#load test data 2017-2018\nweather_test=pd.read_csv(root+'weather_test.csv', parse_dates=['timestamp'])\n\n#shift 2017 to 2016\nweather_test.timestamp=weather_test.timestamp-datetime.timedelta(365)\nwtmatrix=weather_test.pivot(index='timestamp',columns='site_id',values='air_temperature')\nwtmatrix.loc[start_date:end_date,site_id].plot(ax=ax,label=f'2017.1.1-2017.1.9 site:{site_id}',alpha=0.5)\n\n#shift 2018 to 2016\nweather_test.timestamp=weather_test.timestamp-datetime.timedelta(365)\nwtmatrix=weather_test.pivot(index='timestamp',columns='site_id',values='air_temperature')\nwtmatrix.loc[start_date:end_date,site_id].plot(ax=ax,label=f'2018.1.1.-2018.1.9 site:{site_id}',alpha=0.5)\n\n\ndef fill_with_polynomial(wmatrix):\n    return wmatrix.fillna(wmatrix.interpolate(method='polynomial',order=3))\n\ndef fill_with_lin(wmatrix):\n    return wmatrix.fillna(wmatrix.interpolate(method='linear'))\n\ndef fill_with_mix(wmatrix):\n    wmatrix=(wmatrix.fillna(wmatrix.interpolate(method='linear',limit_direction='both'))+ wmatrix.fillna(wmatrix.interpolate(method='polynomial', order=3, limit_direction='both')))*0.5  \n    \n    return wmatrix        # fill with second item\n    \nfill_with_lin(wmatrix_train).loc[start_date:end_date,site_id].plot(ax=ax,label=f'linear Jan 2016 site:{site_id}',alpha=0.5) \nfill_with_polynomial(wmatrix_train).loc[start_date:end_date,site_id].plot(ax=ax,label=f'polynomial Jan 2016 site:{site_id}',alpha=0.5)    \nfill_with_mix(wmatrix_train).loc[start_date:end_date,site_id].plot(ax=ax,label=f'mix Jan 2016 site:{site_id}',alpha=0.5)    \nwmatrix_train.loc[start_date:end_date, site_id].plot(ax=ax, label=f'Jan 2016 site:{site_id}')\nplt.legend()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font  size=3 face=\"微软雅黑\" color=\"blue\">  &ensp; &ensp; 看起来 是线性和多项式1:1混合修补缺失值比较平滑，但在建模的时候3种方法都尝试后发现，线性补值最好\n</font>"},{"metadata":{},"cell_type":"markdown","source":"### same for dew_temperature"},{"metadata":{"trusted":true},"cell_type":"code","source":"# pivot to plot\ncol = 'dew_temperature'\nwmatrix = weather_train.pivot(index='timestamp', columns='site_id', values=col)\n# site with largest amount of missing data points\nsite_id = wmatrix.count().idxmin()\n# plot perid\nstart_date, end_date = datetime.date(2016, 1, 1), datetime.date(2016, 1, 12)\nf,ax = plt.subplots(figsize=(18,6))\n\n_ = fill_with_lin(wmatrix).loc[start_date:end_date, site_id].plot(ax=ax, label=f'linear Jan 2016 site:{site_id}', alpha=0.5)\n_ = fill_with_polynomial(wmatrix).loc[start_date:end_date, site_id].plot(ax=ax, label=f'cubic Jan 2016 site:{site_id}', alpha=0.5)\n_ = fill_with_mix(wmatrix).loc[start_date:end_date, site_id].plot(ax=ax, label=f'mix Jan 2016 site:{site_id}', alpha=0.5)\n_ = wmatrix.loc[start_date:end_date, site_id].plot(ax=ax, label=f'Jan 2016 site:{site_id}')\n\n_ = plt.legend()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<font  size=3 face=\"微软雅黑\" color=\"blue\">  &ensp; &ensp; 同样看起来 是线性和多项式1:1混合修补缺失值比较平滑，但在建模的时候3种方法都尝试后发现，线性补值最好\n</font>"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.4"}},"nbformat":4,"nbformat_minor":4}