text_html_output_1.png
import matplotlib.pyplot as plt

import numpy as np # linear algebra

import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

train = pd.read_csv('../input/train.csv')
test = pd.read_csv('../input/test_id.csv')

train['UPDATE_TIME'] = train['UPDATE_TIME'].astype('datetime64[ns]')
test['UPDATE_TIME'] = test['UPDATE_TIME'].astype('datetime64[ns]')

zone_1 = pd.DataFrame()
zone_2 = pd.DataFrame()
zone_3 = pd.DataFrame()

for i in range(train.shape[0]):
    if (train.at[i,'ZONE_CODE'] == 'ZONE01'):
        zone_1 = zone_1.append(train.iloc[i])
    elif (train.at[i,'ZONE_CODE'] == 'ZONE02'):
        zone_2 = zone_2.append(train.iloc[i])
    else:
        zone_3 = zone_3.append(train.iloc[i])
        
for i in range(zone_1.shape[0]):
    zone_1.at[i,'HOUR_ID'] = i
    
for i in range(zone_2.shape[0]):
    zone_2.at[i,'HOUR_ID'] = i
    
for i in range(zone_3.shape[0]):
    zone_3.at[i,'HOUR_ID'] = i
    
import matplotlib.pyplot as plt

fig, ax = plt.subplots()
line_1 = ax.plot(zone_1['HOUR_ID'], zone_1['MAX_USER'], label="zone 1")
line_2 = ax.plot(zone_2['HOUR_ID'], zone_2['MAX_USER'], label="zone 2")
line_3 = ax.plot(zone_3['HOUR_ID'], zone_3['MAX_USER'], label="zone 3")
ax.legend()
plt.show()

train = train.loc[train.UPDATE_TIME >= '2019-03-01']

min_maxuser = train.groupby(['ZONE_CODE', 'HOUR_ID']).min().reset_index()
max_maxuser = train.groupby(['ZONE_CODE', 'HOUR_ID']).max().reset_index()
groupby_mean = train.groupby(['ZONE_CODE', 'HOUR_ID']).mean().reset_index()
groupby_mean['smape_maxuser'] = (max_maxuser['MAX_USER'] - min_maxuser['MAX_USER']) / (max_maxuser['MAX_USER'] + min_maxuser['MAX_USER']) * 100
groupby_mean['min_of_maxuser'] = min_maxuser['MAX_USER']
groupby_mean['smape_bandwidth'] = (max_maxuser['BANDWIDTH_TOTAL'] - min_maxuser['BANDWIDTH_TOTAL']) / (max_maxuser['MAX_USER'] + min_maxuser['MAX_USER']) * 100
groupby_mean['min_of_bandwidth'] = min_maxuser['BANDWIDTH_TOTAL']

df_val = train.drop(['BANDWIDTH_TOTAL', 'MAX_USER'], axis=1).join(groupby_mean.set_index(['ZONE_CODE', 'HOUR_ID']), on=['ZONE_CODE', 'HOUR_ID'])
df_test = test.join(groupby_mean.set_index(['ZONE_CODE', 'HOUR_ID']), on=['ZONE_CODE', 'HOUR_ID'])

THRESHOLD = 12
df_val.loc[(df_val['smape_bandwidth'] > THRESHOLD) & (df_val['min_of_bandwidth'] < 13), 'BANDWIDTH_TOTAL'] = np.nan
df_test.loc[(df_test['smape_bandwidth'] > THRESHOLD) & (df_test['min_of_bandwidth'] < 13), 'BANDWIDTH_TOTAL'] = np.nan
print(df_test['BANDWIDTH_TOTAL'].describe())