<reponame>KazukiOnodera/Microsoft-Malware-Prediction<filename>py/trash/004_datecountEnc.py
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Thu Jan 31 21:03:02 2019

@author: Kazuki
"""

import numpy as np
import pandas as pd
import os, gc
from datetime import datetime
import utils
utils.start(__file__)

PREF = 'f004'


col_cat = [
#         'ProductName',
#         'EngineVersion',
#         'AppVersion',
         'AvSigVersion',
#         'Platform',
#         'Processor',
#         'OsVer',
#         'OsPlatformSubRelease',
         'OsBuildLab',
#         'SkuEdition',
#         'PuaMode',
#         'SmartScreen',
#         'Census_MDC2FormFactor',
#         'Census_DeviceFamily',
#         'Census_ProcessorClass',
#         'Census_PrimaryDiskTypeName',
#         'Census_ChassisTypeName',
#         'Census_PowerPlatformRoleName',
#         'Census_InternalBatteryType',
         'Census_OSVersion',
#         'Census_OSArchitecture',
#         'Census_OSBranch',
#         'Census_OSEdition',
#         'Census_OSSkuName',
#         'Census_OSInstallTypeName',
#         'Census_OSWUAutoUpdateOptionsName',
#         'Census_GenuineStateName',
#         'Census_ActivationChannel',
#         'Census_FlightRing'
         ]


tr = pd.read_feather('../data/train.f')[col_cat]

te = pd.read_feather('../data/test.f')[col_cat]


# AS timestamp
datedictAS = np.load('../external/AvSigVersionTimestamps.npy')[()]
tr['AvSigVersion_datetime'] = tr['AvSigVersion'].map(datedictAS)
te['AvSigVersion_datetime'] = te['AvSigVersion'].map(datedictAS)

# OS timestamp
datedictOS = np.load('../external/OSVersionTimestamps.npy')[()]
tr['Census_OSVersion_datetime'] = tr['Census_OSVersion'].map(datedictOS)
te['Census_OSVersion_datetime'] = te['Census_OSVersion'].map(datedictOS)

# BL timestamp
def convert(x):
    try:
        d = datetime.strptime(x.split('.')[4],'%y%m%d-%H%M')
    except:
        d = np.nan
    return d

tr['OsBuildLab_datetime'] = tr['OsBuildLab'].map(convert)
te['OsBuildLab_datetime'] = te['OsBuildLab'].map(convert)

tr_f_list = []
te_f_list = []

for timeseries in ['AvSigVersion', 'Census_OSVersion', 'OsBuildLab']:
    
    tr[f'{timeseries}_date'] = tr[f'{timeseries}_datetime'].dt.date
    te[f'{timeseries}_date'] = te[f'{timeseries}_datetime'].dt.date
    
    if True:
        tmp1 = tr[f'{timeseries}_date'].value_counts(normalize=1) / tr.shape[0]
        tmp2 = te[f'{timeseries}_date'].value_counts(normalize=1) / te.shape[0]
        tmp = pd.concat([tmp1, tmp2], axis=1, join='outer').fillna(0)
        tbl = (tmp.iloc[:,0] + tmp.iloc[:,1]).reset_index()
        tbl.columns = [f'{timeseries}_date', f'{timeseries}_cnt']
    else:
        # date cnt
        tbl = tr[[f'{timeseries}_date']].append(te[[f'{timeseries}_date']]).groupby(f'{timeseries}_date').size()
        tbl.name = f'{timeseries}_cnt'
        tbl = tbl.reset_index()
    
    tbl[f'{timeseries}_cnt_norm'] = tbl[f'{timeseries}_cnt'] / tbl[f'{timeseries}_cnt'].sum()
    
    alldate = pd.DataFrame()
    alldate[f'{timeseries}_date'] = pd.Series(pd.date_range(tbl[f'{timeseries}_date'].min(), 
                                              tbl[f'{timeseries}_date'].max())).dt.date
    
    
    tbl = pd.merge(alldate, tbl, on=f'{timeseries}_date', how='outer')
    
    tbl.fillna(0, inplace=True)
    
    tbl[f'{timeseries}_lead_10d_sum'] = tbl[f'{timeseries}_cnt'].rolling(10).sum().shift(-9)
    tbl[f'{timeseries}_lead_10d_mean'] = tbl[f'{timeseries}_cnt'].rolling(10).mean().shift(-9)
    tbl[f'{timeseries}_lead_10d_std'] = tbl[f'{timeseries}_cnt'].rolling(10).std().shift(-9)
    
    tbl.sort_values(f'{timeseries}_date', ascending=False, inplace=True)
    tbl[f'{timeseries}_lag_10d_sum']  = tbl[f'{timeseries}_cnt'].rolling(10).sum().shift(-9)
    tbl[f'{timeseries}_lag_10d_mean'] = tbl[f'{timeseries}_cnt'].rolling(10).mean().shift(-9)
    tbl[f'{timeseries}_lag_10d_std']  = tbl[f'{timeseries}_cnt'].rolling(10).std().shift(-9)
    
    #tbl.set_index(f'{timeseries}_date', inplace=True)
    
    tr_f = pd.DataFrame(tr[f'{timeseries}_date'])
    te_f = pd.DataFrame(te[f'{timeseries}_date'])
    
    tr_f = pd.merge(tr_f, tbl, on=f'{timeseries}_date', how='left')
    te_f = pd.merge(te_f, tbl, on=f'{timeseries}_date', how='left')
    
    del tr_f[f'{timeseries}_date'], te_f[f'{timeseries}_date']
    
    utils.reduce_mem_usage(tr_f)
    utils.reduce_mem_usage(te_f)
    
    tr_f_list.append(tr_f)
    te_f_list.append(te_f)

pd.concat(tr_f_list, axis=1).add_prefix(PREF+'_').to_feather(f'../data/train_{PREF}.f')
pd.concat(te_f_list, axis=1).add_prefix(PREF+'_').to_feather(f'../data/test_{PREF}.f')



utils.end(__file__)


#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Thu Jan 31 21:03:02 2019

@author: Kazuki
"""

import numpy as np
import pandas as pd
import os, gc
from datetime import datetime
import utils
utils.start(__file__)

PREF = 'f004'


col_cat = [
#         'ProductName',
#         'EngineVersion',
#         'AppVersion',
         'AvSigVersion',
#         'Platform',
#         'Processor',
#         'OsVer',
#         'OsPlatformSubRelease',
         'OsBuildLab',
#         'SkuEdition',
#         'PuaMode',
#         'SmartScreen',
#         'Census_MDC2FormFactor',
#         'Census_DeviceFamily',
#         'Census_ProcessorClass',
#         'Census_PrimaryDiskTypeName',
#         'Census_ChassisTypeName',
#         'Census_PowerPlatformRoleName',
#         'Census_InternalBatteryType',
         'Census_OSVersion',
#         'Census_OSArchitecture',
#         'Census_OSBranch',
#         'Census_OSEdition',
#         'Census_OSSkuName',
#         'Census_OSInstallTypeName',
#         'Census_OSWUAutoUpdateOptionsName',
#         'Census_GenuineStateName',
#         'Census_ActivationChannel',
#         'Census_FlightRing'
         ]


tr = pd.read_feather('../data/train.f')[col_cat]

te = pd.read_feather('../data/test.f')[col_cat]


# AS timestamp
datedictAS = np.load('../external/AvSigVersionTimestamps.npy')[()]
tr['AvSigVersion_datetime'] = tr['AvSigVersion'].map(datedictAS)
te['AvSigVersion_datetime'] = te['AvSigVersion'].map(datedictAS)

# OS timestamp
datedictOS = np.load('../external/OSVersionTimestamps.npy')[()]
tr['Census_OSVersion_datetime'] = tr['Census_OSVersion'].map(datedictOS)
te['Census_OSVersion_datetime'] = te['Census_OSVersion'].map(datedictOS)

# BL timestamp
def convert(x):
    try:
        d = datetime.strptime(x.split('.')[4],'%y%m%d-%H%M')
    except:
        d = np.nan
    return d

tr['OsBuildLab_datetime'] = tr['OsBuildLab'].map(convert)
te['OsBuildLab_datetime'] = te['OsBuildLab'].map(convert)

tr_f_list = []
te_f_list = []

for timeseries in ['AvSigVersion', 'Census_OSVersion', 'OsBuildLab']:
    
    tr[f'{timeseries}_date'] = tr[f'{timeseries}_datetime'].dt.date
    te[f'{timeseries}_date'] = te[f'{timeseries}_datetime'].dt.date
    
    if True:
        tmp1 = tr[f'{timeseries}_date'].value_counts(normalize=1) / tr.shape[0]
        tmp2 = te[f'{timeseries}_date'].value_counts(normalize=1) / te.shape[0]
        tmp = pd.concat([tmp1, tmp2], axis=1, join='outer').fillna(0)
        tbl = (tmp.iloc[:,0] + tmp.iloc[:,1]).reset_index()
        tbl.columns = [f'{timeseries}_date', f'{timeseries}_cnt']
    else:
        # date cnt
        tbl = tr[[f'{timeseries}_date']].append(te[[f'{timeseries}_date']]).groupby(f'{timeseries}_date').size()
        tbl.name = f'{timeseries}_cnt'
        tbl = tbl.reset_index()
    
    tbl[f'{timeseries}_cnt_norm'] = tbl[f'{timeseries}_cnt'] / tbl[f'{timeseries}_cnt'].sum()
    
    alldate = pd.DataFrame()
    alldate[f'{timeseries}_date'] = pd.Series(pd.date_range(tbl[f'{timeseries}_date'].min(), 
                                              tbl[f'{timeseries}_date'].max())).dt.date
    
    
    tbl = pd.merge(alldate, tbl, on=f'{timeseries}_date', how='outer')
    
    tbl.fillna(0, inplace=True)
    
    tbl[f'{timeseries}_lead_10d_sum'] = tbl[f'{timeseries}_cnt'].rolling(10).sum().shift(-9)
    tbl[f'{timeseries}_lead_10d_mean'] = tbl[f'{timeseries}_cnt'].rolling(10).mean().shift(-9)
    tbl[f'{timeseries}_lead_10d_std'] = tbl[f'{timeseries}_cnt'].rolling(10).std().shift(-9)
    
    tbl.sort_values(f'{timeseries}_date', ascending=False, inplace=True)
    tbl[f'{timeseries}_lag_10d_sum']  = tbl[f'{timeseries}_cnt'].rolling(10).sum().shift(-9)
    tbl[f'{timeseries}_lag_10d_mean'] = tbl[f'{timeseries}_cnt'].rolling(10).mean().shift(-9)
    tbl[f'{timeseries}_lag_10d_std']  = tbl[f'{timeseries}_cnt'].rolling(10).std().shift(-9)
    
    #tbl.set_index(f'{timeseries}_date', inplace=True)
    
    tr_f = pd.DataFrame(tr[f'{timeseries}_date'])
    te_f = pd.DataFrame(te[f'{timeseries}_date'])
    
    tr_f = pd.merge(tr_f, tbl, on=f'{timeseries}_date', how='left')
    te_f = pd.merge(te_f, tbl, on=f'{timeseries}_date', how='left')
    
    del tr_f[f'{timeseries}_date'], te_f[f'{timeseries}_date']
    
    utils.reduce_mem_usage(tr_f)
    utils.reduce_mem_usage(te_f)
    
    tr_f_list.append(tr_f)
    te_f_list.append(te_f)

pd.concat(tr_f_list, axis=1).add_prefix(PREF+'_').to_feather(f'../data/train_{PREF}.f')
pd.concat(te_f_list, axis=1).add_prefix(PREF+'_').to_feather(f'../data/test_{PREF}.f')



utils.end(__file__)
#!/usr/bin/env python3 # -*- coding: utf-8 -*- Created on Thu Jan 31 21:03:02 2019  @author: Kazuki #         'ProductName', #         'EngineVersion', #         'AppVersion', #         'Platform', #         'Processor', #         'OsVer', #         'OsPlatformSubRelease', #         'SkuEdition', #         'PuaMode', #         'SmartScreen', #         'Census_MDC2FormFactor', #         'Census_DeviceFamily', #         'Census_ProcessorClass', #         'Census_PrimaryDiskTypeName', #         'Census_ChassisTypeName', #         'Census_PowerPlatformRoleName', #         'Census_InternalBatteryType', #         'Census_OSArchitecture', #         'Census_OSBranch', #         'Census_OSEdition', #         'Census_OSSkuName', #         'Census_OSInstallTypeName', #         'Census_OSWUAutoUpdateOptionsName', #         'Census_GenuineStateName', #         'Census_ActivationChannel', #         'Census_FlightRing' # AS timestamp # OS timestamp # BL timestamp # date cnt #tbl.set_index(f'{timeseries}_date', inplace=True)