<jupyter_start><jupyter_text>**The purpose of this notebook is to study the difference between train and test datasets in order to develop a robust validation scheme. This is important to the generalization capability of models to unseen dataset (test data on private leaderboard).** I'm also trying to use cudf and nvstring as much as possible.  Table of contents[1. Previous CV schemes](prev)[2. Functions](func)[3. Visualizations](vis)[4. Conclusions](conclusions)  1. Previous CV schemesPreviously, I used the naive __[K-Fold cross validation](https://scikit-learn.org/stable/modules/generated/sklearn.model_selection.KFold.html)__ with random shuffling and observed some discrepencies between cross validation AUC (CV) and leaderboard AUC (LB) as follows:<jupyter_code>scores = pd.DataFrame({'models':['lgb1','lgb2','ffm','nn'],'CV':[0.730,0.732,0.727,0.729],'LB':[0.675,0.672,0.680,0.678]})
scores['description'] = ['lightGBM','lightGBM with mean target features','Field aware factorization machine', 'Neural network']
scores = scores.set_index('models')
scores<jupyter_output><empty_output><jupyter_text>Two points to be noted from the table above:1. **CV AUC is higher than LB** which means there is some overfitting.2. **Improvement of CV doesn't lead to improvement of LB** which means the split between train and test dataset is *not* the same as the KFOLD random split.Actually, the dataset provided here has been roughly __[split by time](https://www.kaggle.com/c/microsoft-malware-prediction/data)__. And random split for time series is __[not a good idea](https://www.datapred.com/blog/advanced-cross-validation-tips)__. I believe a time-based split will 1) reduce the gap between CV and LB and more importantly 2) align the improvement of CV to LB so that we can evaluate ETL, feature/model selection locally without submitting to kaggle. What's annoying is the dataset **doesn't include an explicit timestamp column**, so we have to infer the timing information from other columns. My intuitive assumption is that the version number of the defender software contains timing information: higher version number means more recent observations. Hence, in this notebook, I'll study these 4 columns:1. *ProductName* - Defender state information e.g. win8defender2. *EngineVersion* - Defender state information e.g. 1.1.12603.03. *AppVersion* - Defender state information e.g. 4.9.10586.04. *AvSigVersion* - Defender state information e.g. 1.217.1014.0  2. Functions<jupyter_code>def get_dtype(path,nrows=10):
    """get data type for cudf.read_csv
    by using pandas's read_csv with a small number of rows.
    
    Parameters
    ----------
    path : str, path of the csv file
    nrows : int, default: 10
        number of rows to read for pd.read_csv
        
    Returns
    ----------
    col2dtype : dictionary, column name => data type
    """
    if nrows is not None:
        train = pd.read_csv(path,nrows=nrows)
    else:
        train = pd.read_pickle(path.replace('.csv','.pkl'))
    col2dtype = OrderedDict()
    for col in train.columns:
        if train[col].dtype=='O':
            col2dtype[col] = 'str'
        elif train[col].dtype==np.int64:
            col2dtype[col] = 'int32'
        else:
            col2dtype[col] = 'float32'
    return col2dtype

def rmse(a,b):
    """compute root mean square error of two numpy arrays
    """
    return np.mean((a-b)**2)**0.5
def read_csv_to_nvstring(path,cols):
    """read a csv file to a dictionary of nvstring objects
    Parameters
    ----------
    path : str, path of the csv file
    cols : list, a list of column names to be read
        
    Returns
    ----------
    str_cols : dictionary, column name => nvstring object
    """
    dic = get_dtype(path,1000)
    col_names = [i for i in dic]
    dtypes = [dic[i] for i in col_names]
    
    gd_cols = gd.io.csv.read_csv_strings(path,
                        names=col_names, dtype=dtypes, 
                        skiprows=1)
    str_cols = {} # column name => nvstring object
    for name,dtype,ds in zip(col_names,dtypes,gd_cols):
        if dtype =='str' and name in cols:
            str_cols[name] = ds           
    del gd_cols
    return str_cols
def get_topk_token_count(nvs,k=5):
    """get top-k token counts of a nvstring object
    
    Parameters
    ----------
    nvs : a nvstring object, 
    k : integer, for top-k
        
    Returns
    ----------
    nvs_count : a dictionary (collections.Counter) token (str) => count (int)
        including all tokens of that nvstring
    nvs_count_topk : a dictionary (collections.Counter) token (str) => count (int)
        including top-k frequent tokens of that nvstring
    """
    nvs_count = get_token_counts(nvs)
    nvs_count_topk = dict(nvs_count.most_common(k)) 
    sum_top = sum([j for i,j in nvs_count_topk.items()])
    ratio = '%.4f'%(sum_top/nvs.size())
    ratio = colored(ratio,'red')
    print('# of unique values: %d, top%d %s, top%d percentage:'%(len(nvs_count),k,str(nvs_count_topk),k),ratio)    
    return nvs_count,nvs_count_topk
def overlap_piechart(train_count,test_count,title):
    """draw a pie chart of overlapped ratio between two datasets
    Parameters
    ----------
    train_count : a dictionary (collections.Counter) token (str) => count (int)
        for train data
    test_count : a dictionary (collections.Counter) token (str) => count (int)
        for test data
        
    """
    train_in_test = is_in(train_count,test_count)
    test_in_train = is_in(test_count,train_count)
    t1 = colored('%.3f'%train_in_test,'red')
    t2 = colored('%.3f'%test_in_train,'red')
    print('train_in_test ratio',t1,' test_in_train ratio',t2)
    data = []
    data.append({'in test':train_in_test,'not in test':1-train_in_test})
    data.append({'in train':test_in_train,'not in train':1-test_in_train})
    tags = ['train data','test data']
    draw.pie_chart(data,tags,title=title,figsize=(16,4))<jupyter_output><empty_output><jupyter_text>3. Visualizations<jupyter_code>%%time
# peak gpu memory usage is 17GB!
cols = ['ProductName', 'EngineVersion', 'AppVersion', 'AvSigVersion']
train = read_csv_to_nvstring(path='%s/train.csv'%PATH,cols=cols)
test = read_csv_to_nvstring(path='%s/test.csv'%PATH,cols=cols)<jupyter_output>CPU times: user 2.96 s, sys: 2.61 s, total: 5.58 s
Wall time: 5.59 s<jupyter_text>**ProductName: name of the defender software**<jupyter_code>%%time
col = 'ProductName'
train_count = get_token_counts(train[col])
print('train',train_count)
test_count = get_token_counts(test[col])
print('test',test_count)<jupyter_output>1:float32
2:int32
3:int64
train Counter({'win8defender': 8826520, 'mse': 94873, 'mseprerelease': 53, 'scep': 22, 'windowsintune': 8, 'fep': 7})
1:float32
2:int32
3:int64
test Counter({'win8defender': 7797245, 'mse': 55946, 'mseprerelease': 34, 'scep': 16, 'fep': 7, 'windowsintune': 5})
CPU times: user 1.28 s, sys: 216 ms, total: 1.5 s
Wall time: 1.53 s<jupyter_text>**The following pie charts show that**:1. there are 6 difference product names in both train and test2. most samples are from product *win8defender*3. the distribution is similar in train and test<jupyter_code>draw.pie_chart([train_count,test_count],['train','test'],title=col,figsize=(16,4))
draw.pie_chart([train_count,test_count],['train','test'],title=col,transpose=True,figsize=(16,2))
overlap_piechart(train_count,test_count,col)<jupyter_output>train_in_test ratio [31m1.000[0m  test_in_train ratio [31m1.000[0m<jupyter_text>**AvSigVersion**<jupyter_code>%%time
col = 'AvSigVersion'
k = 5
print("==============Train==============")
train_count,train_count_topk = get_topk_token_count(train[col],k)
print("==============Test==============")
test_count,test_count_topk = get_topk_token_count(test[col],k)<jupyter_output>==============Train==============
1:float32
2:int32
3:int64
# of unique values: 8309, top5 {'1.273.1420.0': 102317, '172.16.58.3': 76837, '1.275.1209.0': 66393, '1.273.810.0': 65895, '1.273.781.0': 44347}, top5 percentage: [31m0.0399[0m
==============Test==============
1:float32
2:int32
3:int64
# of unique values: 9217, top5 {'1.277.515.0': 80393, '1.277.96.0': 57377, '1.277.1044.0': 54981, '1.277.1102.0': 52147, '172.16.58.3': 47158}, top5 percentage: [31m0.0372[0m
CPU times: user 612 ms, sys: 252 ms, total: 864 ms
Wall time: 873 ms<jupyter_text>**Observations:**1. there are 8k~9k difference AvSigVersions in train and test2. the top 5 AvSigVersions in terms of counts account for 3% of the total population. We look at the top-5 AvSigVersions with the pie charts below<jupyter_code>draw.pie_chart([train_count_topk,test_count_topk],['train','test'],title=col+' top 5',figsize=(16,4))
draw.pie_chart([train_count_topk,test_count_topk],['train','test'],title=col+' top 5',figsize=(18,2),transpose=True)<jupyter_output><empty_output><jupyter_text>**Observations**1. The top-5 AvSigVersion of train and test are completely different and they have no overlap. 2. For example, `1.277.1102.0` can only be found in test data but not in train data. Similarly, `172.16.58.3` can only be found in train data but not in test data.3. the top 5 of train are 1.251.x ~ 1.275.x and 4 of the top 5 of test is above 1.277.x. This may indicate that higher version number is from more recent observations. 4. we could use AvSigVersion as a timestamp to split data<jupyter_code>overlap_piechart(train_count,test_count,col)<jupyter_output>train_in_test ratio [31m0.981[0m  test_in_train ratio [31m0.153[0m<jupyter_text>**We also look at overall token counts instead of just top-5 tokens**1. for test data, there are only 15.3% of samples whose AvSigVersion present in train data. This indicates that most of test data are with new AvSigVersions.2. for train data, there are 97.5% samples whose AvSigVersion present in test data. This indicates the 15.3% test data (the red slice of the left pie chart) actually contain most of the AvSigVersions of train.  **The similar analysis is also done to EngineVersion and AppVersion**<jupyter_code>%%time
col = 'EngineVersion'
k = 5
print("==============Train==============")
train_count,train_count_topk = get_topk_token_count(train[col],k)
print("==============Test==============")
test_count,test_count_topk = get_topk_token_count(test[col],k)
draw.pie_chart([train_count_topk,test_count_topk],['train','test'],title=col,figsize=(16,4))
draw.pie_chart([train_count_topk,test_count_topk],['train','test'],title=col,figsize=(18,2),transpose=True)
overlap_piechart(train_count,test_count,col)
%%time
col = 'AppVersion'
k = 5
print("==============Train==============")
train_count,train_count_topk = get_topk_token_count(train[col],k)
print("==============Test==============")
test_count,test_count_topk = get_topk_token_count(test[col],k)
draw.pie_chart([train_count_topk,test_count_topk],['train','test'],title=col,figsize=(16,4))
draw.pie_chart([train_count_topk,test_count_topk],['train','test'],title=col,figsize=(18,2),transpose=True)
overlap_piechart(train_count,test_count,col)<jupyter_output>train_in_test ratio [31m0.999[0m  test_in_train ratio [31m0.704[0m
{'fraction_non_alphanumeric': 0.14591229020032484, 'fraction_numerical': 0.04674246525897852, 'mean_word_length': 4.72764857881137, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 38, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 3, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '88874', 'license': 'Apache-2.0', 'n_tokens_mistral': 4032, 'n_tokens_neox': 3672, 'n_words': 1027, 'path': 'kaggle/malware/malware_time_column_explore.ipynb', 'repo_name': 'taureandyernv/notebooks', 'size': '11078'}