<jupyter_start><jupyter_text>Dataset 1:  Selecting for covid-19 confirmed cases only those with positive RT/PCR results data balancing with stratified sampling strategy using the col: AGE_GROUP (with 0.05 of fraction for each age group)<jupyter_code>count = super_srag.filter(F.col('PP_PCR_RESUL') == '2').count()
super_srag.filter(F.col('PP_PCR_RESUL') == '2')\
          .select(['AGE_GROUP']).groupBy('AGE_GROUP').count().orderBy('AGE_GROUP')\
          .withColumn('%', (F.col('count')/count)*100).show()
fractions = {1 : 0.05, 2 : 0.05, 3 : 0.05, 4 : 0.05, 5 : 0.05, 6 : 0.05,
             7 : 0.05, 8 : 0.05, 9 : 0.05, 10 : 0.05, 11 : 0.05, 12 : 0.05}

df_1_covid = super_srag.filter(F.col('PP_PCR_RESUL') == '2').sampleBy('AGE_GROUP', fractions=fractions, seed=2021)
count = df_1_covid.filter(F.col('PP_PCR_RESUL') == '2').count()
print(count)
df_1_covid.filter(F.col('PP_PCR_RESUL') == '2')\
          .select(['AGE_GROUP']).groupBy('AGE_GROUP').count().orderBy('AGE_GROUP')\
          .withColumn('%', (F.col('count')/count)*100).show()
df_1_not_covid = super_srag.filter((F.col('CLASSI_FIN') ==  '1') | (F.col('CLASSI_FIN') ==  '2')).withColumn('CLASSI_FIN', F.lit('1'))
df_1_not_covid.count()
df_1 = df_1_not_covid.union(df_1_covid)
df_1.select('CLASSI_FIN').groupBy('CLASSI_FIN').count().show()
df_1 = df_1.select(cols).persist()<jupyter_output><empty_output><jupyter_text>Preprocessing Dataset 1 some description before the script<jupyter_code>for col in cols: 
    if col != "NU_NOTIFIC":
        
        print("==== " + col + " ====")
        
        df_1.select(col).printSchema()
        
        print("GroupBy: all")
        
        count = df_1.count()
        df_1.select([col]).groupBy(col).count().orderBy(col)\
            .withColumn('%', (F.col('count')/count)*100).show()
        
        print("GroupBy: covid")
        
        count = df_1.filter(F.col('CLASSI_FIN') == '5').count()
        df_1.filter(F.col('CLASSI_FIN') == '5').select([col]).groupBy(col).count().orderBy(col)\
                                               .withColumn('%', (F.col('count')/count)*100).show()
        
        print("GroupBy: not covid")
        count = count = df_1.filter(F.col('CLASSI_FIN') == '1').count()
        df_1.filter(F.col('CLASSI_FIN') == '1').select([col]).groupBy(col).count().orderBy(col)\
                                               .withColumn('%', (F.col('count')/count)*100).show()
        
        print('=========================')
        print('=========================')<jupyter_output>==== CLASSI_FIN ====
root
 |-- CLASSI_FIN: string (nullable = true)

GroupBy: all
+----------+-----+------------------+
|CLASSI_FIN|count|                 %|
+----------+-----+------------------+
|         1|28423|40.084899939357186|
|         5|42484|59.915100060642814|
+----------+-----+------------------+

GroupBy: covid
+----------+-----+-----+
|CLASSI_FIN|count|    %|
+----------+-----+-----+
|         5|42484|100.0|
+----------+-----+-----+

GroupBy: not covid
+----------+-----+-----+
|CLASSI_FIN|count|    %|
+----------+-----+-----+
|         1|28423|100.0|
+----------+-----+-----+

=========================
=========================
==== CRITERIO ====
root
 |-- CRITERIO: string (nullable = true)

GroupBy: all
+----------+-----+--------------------+
|  CRITERIO|count|                   %|
+----------+-----+--------------------+
|      null| 1631|  2.3001960314214394|
|         1|68676|   96.85362517099864|
|         2|  138|  0.1946211234433836|
|24/11/2020|    1|0.0014102979959[...]<jupyter_text>Casting variables<jupyter_code>cols_to_cast = ['RAIOX_RES', 'UTI', 'SUPORT_VEN', 'YEAR_SIN_PRI', 'AGE_GROUP']
for col in cols_to_cast:
    df_1 = df_1.withColumn(col, F.col(col).cast('string'))<jupyter_output><empty_output><jupyter_text>Filling nulls<jupyter_code># null to "9"
cols_null_to_9 = ['TOMO_RES', 'RAIOX_RES', 'HOSPITAL', 'UTI', 'SUPORT_VEN', 'EVOLUCAO', 'CRITERIO']
for col in cols_null_to_9:
    df_1 = df_1.withColumn(col, F.when(F.col(col).isNull(), '9').otherwise(F.col(col)))
# null to string
cols_null_to_string = ['SG_UF']
for col in cols_null_to_string:
    df_1 = df_1.withColumn(col, F.when(F.col(col).isNull(), 'NULL').otherwise(F.col(col)))<jupyter_output><empty_output><jupyter_text>Taking out invalid values<jupyter_code>df_1 = df_1.filter(F.col('TOMO_RES') != '20/11/2020')
df_1 = df_1.filter(F.col('HOSPITAL') != 'RJ')<jupyter_output><empty_output><jupyter_text>Showing final descriptive<jupyter_code>for col in cols: 
    if col != "NU_NOTIFIC":
        
        print("==== " + col + " ====")
        
        df_1.select(col).printSchema()
        
        print("GroupBy: all")
        
        count = df_1.count()
        df_1.select([col]).groupBy(col).count().orderBy(col)\
            .withColumn('%', (F.col('count')/count)*100).show()
        
        print("GroupBy: covid")
        
        count = df_1.filter(F.col('CLASSI_FIN') == '5').count()
        df_1.filter(F.col('CLASSI_FIN') == '5').select([col]).groupBy(col).count().orderBy(col)\
                                               .withColumn('%', (F.col('count')/count)*100).show()
        
        print("GroupBy: not covid")
        count = count = df_1.filter(F.col('CLASSI_FIN') == '1').count()
        df_1.filter(F.col('CLASSI_FIN') == '1').select([col]).groupBy(col).count().orderBy(col)\
                                               .withColumn('%', (F.col('count')/count)*100).show()
        
        print('=========================')
        print('=========================')<jupyter_output>==== CLASSI_FIN ====
root
 |-- CLASSI_FIN: string (nullable = true)

GroupBy: all
+----------+-----+------------------+
|CLASSI_FIN|count|                 %|
+----------+-----+------------------+
|         1|28422|40.084054945984825|
|         5|42484|59.915945054015175|
+----------+-----+------------------+

GroupBy: covid
+----------+-----+-----+
|CLASSI_FIN|count|    %|
+----------+-----+-----+
|         5|42484|100.0|
+----------+-----+-----+

GroupBy: not covid
+----------+-----+-----+
|CLASSI_FIN|count|    %|
+----------+-----+-----+
|         1|28422|100.0|
+----------+-----+-----+

=========================
=========================
==== CRITERIO ====
root
 |-- CRITERIO: string (nullable = true)

GroupBy: all
+--------+-----+-------------------+
|CRITERIO|count|                  %|
+--------+-----+-------------------+
|       1|68676|  96.85499111499732|
|       2|  138|0.19462386821989675|
|       3|  348| 0.4907906242066962|
|       4|  113| 0.1593659210786111|
|       9| 163[...]<jupyter_text><jupyter_code>df = {}

df_keys = range(0,500)

for df_key in df_keys:
    
    # establishing the proportion for each category on AGE_GROUP attribute
    fractions = {1 : 0.05, 2 : 0.05, 3 : 0.05, 4 : 0.05, 5 : 0.05, 6 : 0.05,
             7 : 0.05, 8 : 0.05, 9 : 0.05, 10 : 0.05, 11 : 0.05, 12 : 0.05}
    
    # getting stratified sample for super srag by AGE_GROUP when PCR_RESULT is positive for covid
    df_covid = super_srag.filter(F.col('PP_PCR_RESUL') == '2')\
                         .sampleBy('AGE_GROUP', fractions=fractions, seed=df_key)
    
    # getting all NOT COVID records from super srag
    df_not_covid = super_srag.filter((F.col('CLASSI_FIN') ==  '1') | (F.col('CLASSI_FIN') ==  '2'))\
                             .withColumn('CLASSI_FIN', F.lit('1'))
    
    # unioning the datasets and putting into a sample dataset to be written and submitted to the model
    df[df_key] = df_not_covid.union(df_covid)

    
    # writing the sample dataset
    pad_key = str(df_key).zfill(3)
    id_dataset = 'positivepcrforcovid'
    
    df[df_key].write.parquet('gs://ai-covid19-datalake/trusted/XGB-StratifiedSampleDatasets-1/dataset_1_'+id_dataset+'_'+pad_key+'.parquet', mode='overwrite')
#     df[df_key].coalesce(1).write.csv('gs://ai-covid19-datalake/trusted/XGB-StratifiedSampleDatasets-1/dataset_1_'+id_dataset+'_'+pad_key+'.csv', header='true'
print('finished')<jupyter_output>finished
{'fraction_non_alphanumeric': 0.2693366708385482, 'fraction_numerical': 0.06595744680851064, 'mean_word_length': 2.315767634854772, 'pattern_counts': {'":': 2, '<': 26, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '130677', 'license': 'MIT', 'n_tokens_mistral': 3182, 'n_tokens_neox': 2879, 'n_words': 415, 'path': '04-preprocessing-for-models/01-poc/06-selecting-datasets-for-models-dataset1-age-group.ipynb', 'repo_name': 'pierrepita/super-srag-ai-covid19-google-cidacs', 'size': '7990'}