<jupyter_start><jupyter_text>df_zcode_required<jupyter_code>df_zcode_required = spark.read.option("inferSchema", True).option("header", True).csv(path_zcode_required)
df_zcode_required.show(n=2)
print(f'Number of entries/rows in the dataframe are {df_zcode_required.count()}')<jupyter_output>Number of entries/rows in the dataframe are 86<jupyter_text>df_item_count<jupyter_code>df_item_count = spark.read.option("inferSchema", True).parquet(path_count_item + '/*.parquet')
df_item_count = df_item_count.join(df_zcode_required, 'zcodes', 'inner').select('zcodes', 'count')
df_item_count.show(n=2)
pdf = df_item_count.toPandas()
pdf.head(2)
pdf.to_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_item_count_input.csv', header=True, index=False)
pdf.shape<jupyter_output>(86, 2)<jupyter_text>Filter Scores<jupyter_code>df_scores = spark.read.option("inferSchema", True).parquet(path_scores_parquet + '/*.parquet')
df_scores = df_scores.join(df_item_count,
                           df_scores.zcode == df_item_count.zcodes,
                           'inner').select(df_scores['*'], df_item_count['count'].alias('rank_cutoff'))
df_scores.printSchema()
# https://stackoverflow.com/questions/44968912/difference-in-dense-rank-and-row-number-in-spark
df_scores = df_scores.withColumn('rank', rank().over(Window.partitionBy('zcode').orderBy(desc('prediction'))))
df_scores.printSchema()
path_root_dest
df_scores.write.parquet(path_root_dest + 'df_scores_filtered_w_rank')
df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_scores_filtered_w_rank/*.parquet')
df_scores.printSchema()
df_scores_item_counts = df_scores.groupBy('zcode').count()
df_scores_item_counts.printSchema()
pdf_scores_item_counts = df_scores_item_counts.toPandas()
pdf_scores_item_counts.head()
pdf_scores_item_counts.to_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_item_count_scores.csv', header=True, index=False)<jupyter_output><empty_output><jupyter_text>Intersection 1<jupyter_code>df_input = spark.read.option("inferSchema", True).parquet(path_user_item_interactions + '/*.parquet')
df_input.printSchema()
df_input.show(n=2)
df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_scores_filtered_w_rank/*.parquet')
df_scores.printSchema()
df_scores.show(n=2)
df_scores = df_scores.join(df_input,
                           ((df_scores.user_id==df_input.user_id) & (df_scores.zcode==df_input.zcodes)),
                           'inner').select(df_scores['*'], df_input['count'].alias('count_interactions'))
df_scores.printSchema()
df_scores.write.parquet(path_root_dest + 'df_intersection_input_n_scores')
df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_intersection_input_n_scores/*.parquet')

df_scores.printSchema()
df_scores_intersection_item_counts = df_scores.groupBy('zcode').count()
df_scores_intersection_item_counts.printSchema()
pdf_scores_intersection_item_counts = df_scores_intersection_item_counts.toPandas()
pdf_scores_intersection_item_counts.head()
pdf_scores_intersection_item_counts.to_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_scores_intersection_item_counts.csv', header=True, index=False)<jupyter_output><empty_output><jupyter_text>Filter scores at rank cutoff<jupyter_code>df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_scores_filtered_w_rank/*.parquet')
df_scores.printSchema()
df_scores = df_scores.withColumn('filter_flag', F.when(df_scores['rank'] < df_scores['rank_cutoff'], 1).otherwise(0))
df_scores = df_scores.filter(df_scores['filter_flag'] == 1)
df_scores.printSchema()
# df_scores = df_scores.select(['user_id', 'zcode', 'prediction'])
df_scores = df_scores.filter(df_scores['prediction'] > 0)
# df_scores = df_scores.withColumn("zcodes", regexp_replace(col("zcode"), 'z', 'zp'))
# df_scores = df_scores.selectExpr('user_id as cookie_sizmek', 'zcodes as zcode', 'prediction as score')
path_root_dest
df_scores.write.parquet(path_root_dest+'df_scores_filtered_100')
df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_scores_filtered_100/*.parquet')
df_scores.printSchema()
df_scores_filtered_100_item_counts = df_scores.groupBy('zcode').count()
df_scores_filtered_100_item_counts.printSchema()
pdf_scores_filtered_100_item_counts = df_scores_filtered_100_item_counts.toPandas()
pdf_scores_filtered_100_item_counts.head()
pdf_scores_filtered_100_item_counts.to_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_scores_filtered_100_item_counts.csv', header=True, index=False)<jupyter_output><empty_output><jupyter_text>Intersection 2<jupyter_code>df_input = spark.read.option("inferSchema", True).parquet(path_user_item_interactions + '/*.parquet')
df_input.printSchema()
df_input.show(n=2)
df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_scores_filtered_100/*.parquet')
df_scores.printSchema()
df_scores.show(n=2)
df_scores = df_scores.join(df_input,
                           ((df_scores.user_id==df_input.user_id) & (df_scores.zcode==df_input.zcodes)),
                           'inner').select(df_scores['*'], df_input['count'].alias('count_interactions'))
df_scores.write.parquet(path_root_dest + 'df_intersection_input_n_filtered_scores')
df_scores = spark.read.option("inferSchema", True).parquet(path_root_dest + 'df_intersection_input_n_filtered_scores/*.parquet')

df_scores.printSchema()
df_filtered_scores_intersection_item_counts = df_scores.groupBy('zcode').count()
df_filtered_scores_intersection_item_counts.printSchema()
pdf_filtered_scores_intersection_item_counts = df_filtered_scores_intersection_item_counts.toPandas()
pdf_filtered_scores_intersection_item_counts.head()
pdf_filtered_scores_intersection_item_counts.to_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_filtered_scores_intersection_item_counts.csv', header=True, index=False)<jupyter_output><empty_output><jupyter_text>collate all CSVs<jupyter_code>pdf_1 = pd.read_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_item_count_input.csv')
pdf_1['count_1'] = pdf_1['count']
pdf_1['zcode'] = pdf_1['zcodes']
pdf_1 = pdf_1.set_index('zcode')
pdf_1 = pdf_1[['count', 'count_1']]
print(pdf_1.shape)
pdf_1.head(2)
pdf_2 = pd.read_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_item_count_scores.csv')

pdf_2['count_2'] = pdf_2['count']
pdf_2 = pdf_2.set_index('zcode')
print(pdf_2.shape)
pdf_2.head(2)
pdf_3 = pd.read_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_scores_intersection_item_counts.csv')

pdf_3['count_3'] = pdf_3['count']
pdf_3 = pdf_3.set_index('zcode')
print(pdf_3.shape)
pdf_3.head(2)
pdf_4 = pd.read_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_scores_filtered_100_item_counts.csv')

pdf_4['count_4'] = pdf_4['count']
pdf_4 = pdf_4.set_index('zcode')
print(pdf_4.shape)
pdf_4.head(2)
pdf_5 = pd.read_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_filtered_scores_intersection_item_counts.csv')

pdf_5['count_5'] = pdf_5['count']
pdf_5 = pdf_5.set_index('zcode')
print(pdf_5.shape)
pdf_5.head(2)
pdf_final = pd.concat([pdf_1, pdf_2, pdf_3, pdf_4, pdf_5], axis=1, sort=False, join='outer')
pdf_final = pdf_final[['count_1', 'count_2', 'count_3', 'count_4', 'count_5']]
pdf_final = pdf_final.sort_values('count_1', ascending=False)
pdf_final.index.name = 'zcode'
pdf_final = pdf_final.reset_index()

pdf_final.shape
pdf_final.head()
pdf_final.to_csv('s3://shubh-sandbox/ai-audiences/q4-2/df_final_counts.csv', header=True, index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14583894424993266, 'fraction_numerical': 0.020737947751144625, 'mean_word_length': 8.226086956521739, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '140401', 'license': 'MIT', 'n_tokens_mistral': 3043, 'n_tokens_neox': 2840, 'n_words': 267, 'path': 'q4_2020_work/32_get_counts.ipynb', 'repo_name': 'cipher982/GNN-playground', 'size': '7426'}