<jupyter_start><jupyter_text>Pandas cannot handel 6G data while simply reading it.There are four solutions I can think of:1. reading only a few columns and analyze at once. However, calculation such as correlation might be a problem in the future even I successfully read in the data.2. specify each dtype since default dtype takes more memories. However it might have future problem asa point 1.3. load data in as matrix4. use sparkOf all the solutioins above, I will try second method first just to test if I can load in the data this way and I am just so familiar analyzing data using pandas, can't give it up so quick without giving it a shot.However, I think it makes more sense to use spark for following reasons:1. It can ba a lot faster when doing calculation.2. The dataset I have only cover 7 days of behavoior data, In the real world setting, I think this type of data is the classic "big data" people are talking all the time. And spark is designed for handling this type of data! 3. Because the reason in bullet point 2, even I am not dealing with sreaming data, It is better to showcase I have this skillset to the employers.  Load Data<jupyter_code>from pyspark.sql import SparkSession
from pyspark.sql import functions as F
spark = SparkSession \
    .builder \
    .appName("EDA") \
    .getOrCreate()
spark.sparkContext.getConf().getAll()
data_dir = "../MLE_Project_Data"
data_file_name = "train_data.csv"
data_path = os.path.join(data_dir,data_file_name)

raw_df = spark.read.load(data_path,format="csv",delimiter="|",header=True)<jupyter_output><empty_output><jupyter_text>Examine Dataset<jupyter_code>raw_df_shape = (raw_df.count(),len(raw_df.columns))
print(f"The shape: {raw_df_shape}")
# head
raw_df_head = [row.asDict() for row in raw_df.head(5)]
pd.DataFrame(raw_df_head)
# tail
raw_df_tail = [row.asDict() for row in raw_df.tail(5)]
pd.DataFrame(raw_df_tail)
# dtypes
raw_df.schema.fields
raw_df.columns
# describe
describe = raw_df.describe()
describe.toPandas().set_index("summary").T<jupyter_output><empty_output><jupyter_text>Most of the columns are acutallly categorical variables, doesn't make sense to lokking at those statistics.However, this summary is a way to confirm whether those categorical columns are really categorical<jupyter_code>numeric_columns = ["age","device_size",'his_app_size','app_score','device_price']
# describe
describe = raw_df.select(numeric_columns) \
    .describe()
describe.toPandas().set_index("summary").T<jupyter_output><empty_output><jupyter_text>Seems like even these are numerical variables but they are already benn grouped by a different bucket levels.Because there is no further explanation, I need to make a few assumptions:1. value = -1 equal missing value2. value of app_score is actual score3. device price 1-7 means cheap to expensive4. age 1-7 means young to oldThis discovery means when processing data for models, I need to create a lot dummy variables.  Explore Data  label distribution<jupyter_code>label_count = raw_df.select("label").groupby("label").count().toPandas()
label_count["pct"] = np.round(label_count["count"].values/raw_df_shape[0],decimals=3)
label_count
plot = sns.barplot(label_count["label"].values,label_count["count"].values/raw_df_shape[0])<jupyter_output><empty_output><jupyter_text>The label distribution is imbalance. But it is expected. This means CTR in this dataset is around 3.5% which is better than the average CTR number I googled.  ID columns<jupyter_code>raw_df.columns
id_columns = ['uid','task_id','adv_id','creat_type_cd','adv_prim_id','dev_id','inter_type_cd','slot_id','spread_app_id']
for col in id_columns:
    raw_df.select(F.countDistinct(col)).distinct().show()<jupyter_output>+-------------------+
|count(DISTINCT uid)|
+-------------------+
|            1081436|
+-------------------+

+-----------------------+
|count(DISTINCT task_id)|
+-----------------------+
|                   4329|
+-----------------------+

+----------------------+
|count(DISTINCT adv_id)|
+----------------------+
|                  5319|
+----------------------+

+-----------------------------+
|count(DISTINCT creat_type_cd)|
+-----------------------------+
|                            8|
+-----------------------------+

+---------------------------+
|count(DISTINCT adv_prim_id)|
+---------------------------+
|                        101|
+---------------------------+

+----------------------+
|count(DISTINCT dev_id)|
+----------------------+
|                    55|
+----------------------+

+-----------------------------+
|count(DISTINCT inter_type_cd)|
+-----------------------------+
|                            4|
+-----------------------------+

+-----------------------+
|count([...]<jupyter_text>After looking at id columns closely, I discoverd 1. some features are really ID, some  features are not2. some id values are actully a feature to another higher level features.With these two findings I constructed following feature map:1. users:    - uid    - age     - city        - city_rank        - residence    - career    - gender    - up_life_duration    - up_membership_grade    - membership_life_duration    - consume_purchase    - communication_onlinerate    - communication_avgonline_30d2. ads    - task_id    - adv_id    - creat_type_cd    - adv_prim_id    - dev_id    - inter_typ_cd    - slot_id    - indu_name3. apps    - spread_app_id    - tags    - app_first_class    - app_second_class    - his_app_size    - his_on_shelf_time    - app_score4. device    - device_name    - device_size    - emui_dev    - list_time    - device_price6. behavior/event    - net_type    - pt_d  uid examine how frequent users being display the adsthis cound be a user feature<jupyter_code>uid_count = raw_df.select("uid").groupby("uid").count()
uid_count_df = uid_count.toPandas()
uid_count_df["count"].describe()
sns.boxplot(uid_count_df["count"])<jupyter_output><empty_output><jupyter_text>CTR of each user in different category<jupyter_code>pd.cut(uid_count_df["count"],[0,5,15,46,41*1.5 + 46,1600]).value_counts().sort_index().plot.bar()<jupyter_output><empty_output><jupyter_text>From the first glance, the conclusing might be that people being showed 15-46 times have hight Click count. But this didn't consider no very much user could have chance to be showed an ad 46 times. So we need to calculated CTR not just count see the relation of frequency being displayed an ad between Click<jupyter_code>user_click_count = raw_df.select("uid","label").groupby("uid").agg({'label':'sum'}).toPandas()
df = pd.merge(uid_count_df,user_click_count,on='uid')
pd.cut((df["sum(label)"]/df['count']).sort_values(ascending=False),bins=20).value_counts().sort_index().plot.bar()
uid_count_df["bucket"] = pd.cut(uid_count_df["count"],[0,5,15,46,41*1.5 + 46,1600])
event_shown = uid_count_df.groupby("bucket")['count'].sum()
event_shown
shown_click = pd.merge(uid_count_df,user_click_count,on='uid').groupby('bucket')['sum(label)'].sum()<jupyter_output><empty_output><jupyter_text>CTR of each bucket<jupyter_code>(shown_click/event_shown).plot.bar()<jupyter_output><empty_output><jupyter_text>age since each row is ad event based. examing ages uf unique users will make more sense<jupyter_code>raw_df = raw_df.withColumn("label_int", raw_df["label"].cast("integer"))
age_df = raw_df.select("uid","age","label_int").groupBy("uid","age").sum("label_int").toPandas()
users_diff_age_count = age_df.groupby("uid")['age'].count().sort_values(ascending=False)
users_diff_age_count.value_counts()<jupyter_output><empty_output><jupyter_text>This is something interesting, seems like a user even has fiv different age type. To deal with this 1. replace -1 value if a user has other age registered.2. I will create another age type "multiple age" covers users who has more than 2 different ages.for users who has two ages, it accounts about 1/5 users.I guess the reason is that 1. these users didn't give their birthday when they register until being asked later.2. some users grew older during these 7 days.either way for users who has only 2 there is two options:1. always pick larger or smaller group2. create another age group "two ages"<jupyter_code>age_df["age"].value_counts().sort_index()<jupyter_output><empty_output><jupyter_text>Here I just choose the max value but I am interested in replace -1 with the min vale of others<jupyter_code>age_df.groupby("uid")['age'].max().value_counts().sort_index()
age_df.groupby("uid")['age'].max().value_counts().sort_index().plot.bar()<jupyter_output><empty_output><jupyter_text>I think this makes sense if the higher the value, the older the user group.<jupyter_code>agg_df = age_df.groupby("uid").agg({"age":max,"sum(label_int)":sum})
age_click = agg_df[["age",	"sum(label_int)"]].groupby("age")["sum(label_int)"].sum()
age_click
age_click.plot.bar()<jupyter_output><empty_output><jupyter_text>This check how each age group clicked the ads. It is interesting that 2 and 3 has lower counts while 1 higher than them.Let's see the CTR of each group<jupyter_code>event_shown_age = pd.merge(agg_df,uid_count_df,on="uid").groupby("age")['count'].sum()
event_shown_age<jupyter_output><empty_output><jupyter_text>CTR of each age group<jupyter_code>(age_click/event_shown_age).plot.bar()<jupyter_output><empty_output><jupyter_text>City<jupyter_code>raw_df.select("city","city_rank").show()
raw_df.select(F.countDistinct("city")).show()
def calculate_CTR(click,total_shown):
    return click/total_shown<jupyter_output><empty_output><jupyter_text>city_rank<jupyter_code>raw_df.select(F.countDistinct("city_rank")).show()
raw_df.select("city_rank").groupBy("city_rank").count().show()
df = raw_df.select("city_rank").groupBy("city_rank").count().toPandas()
df_click = raw_df.select("city_rank","label_int").groupBy("city_rank").sum("label_int").toPandas()
df_click = df_click.sort_values("city_rank").set_index("city_rank")
df_click
# how many times ads shown in different city_rank 
df = df.sort_values("city_rank").set_index("city_rank")
df.plot.bar()
df
(df_click["sum(label_int)"]/df['count']).plot.bar()<jupyter_output><empty_output><jupyter_text>seems like city_rank 2 has a higher CTR  Career<jupyter_code>raw_df.select(F.countDistinct('career')).show()
career_count = raw_df.select('career').groupBy('career').count().toPandas()
career_count
career_click = raw_df.select('career',"label_int").groupBy('career').sum("label_int").toPandas()
career_click
df = pd.merge(career_click,career_count,on="career").set_index("career").sort_index()
df
(df["sum(label_int)"]/df["count"]).plot.bar()<jupyter_output><empty_output><jupyter_text>seems like career 10, 5,6,and 9 have higher CTRbut how's the actual career distribution among users? if it's possible that these 4 careers are minor careers among users so they can have a higer CTR?<jupyter_code>df = raw_df['uid','career'].groupBy('career').agg(F.countDistinct("uid")).toPandas()
df
df.iloc[:,1].sum()
df.set_index("career").sort_index().plot.bar()<jupyter_output><empty_output><jupyter_text>This chart confirm the reason career 10 has higher CTR because less career 10.career 9 has the highest count but also highest CTR  gender<jupyter_code>dist = raw_df['uid','gender'].groupBy('gender').agg(F.countDistinct("uid")).toPandas()
dist
dist.set_index("gender").plot.bar()
count = raw_df.select('gender').groupBy('gender').count().toPandas()
count
click = raw_df.select('gender',"label_int").groupBy('gender').sum("label_int").toPandas()
click
click.set_index("gender")["sum(label_int)"]/count.set_index("gender")["count"]
(click.set_index("gender")["sum(label_int)"]/count.set_index("gender")["count"]).plot.bar()<jupyter_output><empty_output><jupyter_text>gender CTR  residence<jupyter_code>dist = raw_df['uid','residence'].groupBy('residence').agg(F.countDistinct("uid")).toPandas()
dist
dist.set_index("residence").sort_values("count(uid)").plot.bar()
count = raw_df.select('residence').groupBy('residence').count().toPandas().set_index("residence")
click = raw_df.select('residence',"label_int").groupBy('residence').sum("label_int").toPandas().set_index("residence")
(click["sum(label_int)"]/count["count"]).plot.bar()<jupyter_output><empty_output><jupyter_text>wow, 11 and 22 has two most highest CTR we I've seen sot far in EDAbut again, 22 has higer CTR because it shows up less in the whole dataset.11 seems ligit though  up_life_duration<jupyter_code>dist = raw_df['uid','up_life_duration'].groupBy('up_life_duration').agg(F.countDistinct("uid")).toPandas()
dist
dist.set_index("up_life_duration").sort_values("count(uid)").plot.bar()
col = "up_life_duration"
count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
count.index = count.index.astype(int)
count.sort_index().plot.bar()<jupyter_output><empty_output><jupyter_text>following cell check if ads distributed across up_life_duration evenly<jupyter_code>click.index = click.index.astype(int)
click.sort_index().plot.bar()
(click.iloc[:,0]/count.iloc[:,0]).sort_index().plot.bar()
plt.hlines(0.035, 0, 24,linestyles='dashed',colors="r")<jupyter_output><empty_output><jupyter_text>I suspect the reason 1- 17 can have higer CTR is because they havea smaller denominator.What interesting is that even 19 has a smaller total count, it still has ower CTR  up_membership_grade<jupyter_code>col = "up_membership_grade"
dist = raw_df['uid',col].groupBy(col).agg(F.countDistinct("uid")).toPandas()
dist.set_index(col).sort_index().plot.bar()
dist
count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
print("CTR")
(click.iloc[:,0]/count.iloc[:,0]).sort_values().plot.bar()<jupyter_output>CTR<jupyter_text>seems like most of them are -1, and the highest CTR here doesn't surpass total CTR which is 3.5%this column seems not very useful to me  communication_onlinerate<jupyter_code>raw_df.select("communication_onlinerate").show()
raw_df.select(F.split(raw_df.communication_onlinerate,"^")).show()<jupyter_output>+--------------------------------------+
|split(communication_onlinerate, ^, -1)|
+--------------------------------------+
|                  [0^1^2^3^4^5^6^7^...|
|                  [3^4^5^6^7^8^9^10...|
|                  [0^1^2^3^4^5^6^7^...|
|                  [5^6^7^8^9^10^11^...|
|                  [2^3^4^5^6^7^8^9^...|
|                  [6^7^8^9^10^11^12...|
|                  [0^1^2^3^4^5^6^7^...|
|                  [14^15^16^17^18^1...|
|                  [1^2^3^4^5^6^7^8^...|
|                  [6^7^8^9^10^11^12...|
|                  [6^7^8^9^10^11^12...|
|                            [21^22^23]|
|                  [2^3^4^5^6^7^8^9^...|
|                  [6^7^8^9^10^11^12...|
|                  [4^5^6^7^8^9^10^1...|
|                  [7^8^9^10^11^12^1...|
|                  [6^7^8^9^10^11^12...|
|                  [6^7^8^9^10^11^12...|
|                  [4^5^6^7^8^9^10^1...|
|                  [6^7^8^9^10^11^12...|
+--------------------------------------+
only showing top[...]<jupyter_text>So weird that it didn't split on "^", I have to try another way<jupyter_code>raw_df = raw_df.withColumn("start_communication_onlinerate",raw_df.communication_onlinerate.substr(1,2))
raw_df = raw_df.withColumn("start_communication_onlinerate",F.regexp_extract(raw_df.start_communication_onlinerate,r'(\d+)',1))

raw_df = raw_df.withColumn("last_communication_onlinerate",raw_df.communication_onlinerate.substr(-2,2))
raw_df = raw_df.withColumn("last_communication_onlinerate",F.regexp_extract(raw_df.last_communication_onlinerate,r'(\d+)',1))

raw_df.select("start_communication_onlinerate","last_communication_onlinerate").show()
raw_df.select(raw_df.last_communication_onlinerate == 0).show()<jupyter_output>+-----------------------------------+
|(last_communication_onlinerate = 0)|
+-----------------------------------+
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
|                              false|
+-----------------------------------+
only showing top 20 rows<jupyter_text>There are entries ends with 0, replace it with 24<jupyter_code>raw_df = raw_df.withColumn("last_communication_onlinerate"\
                           ,raw_df.last_communication_onlinerate + 24 - raw_df.last_communication_onlinerate)
raw_df = raw_df.withColumn("duration_communication_onlinerate"\
                  ,raw_df.last_communication_onlinerate-raw_df.start_communication_onlinerate)

col = "duration_communication_onlinerate"
dist = raw_df['uid',col].groupBy(col).agg(F.countDistinct("uid")).toPandas()
dist.set_index(col).sort_index().plot.bar()<jupyter_output><empty_output><jupyter_text>interesting that a lot users active either 18 or 24 hours a day.I suppose users active 18 hours means that those users are using plone all the time while awake.I guess users active 24 hours means there is some app still open so the plone didn't go off. or they do keep awake 24 hours and using it 24 hrs.<jupyter_code>count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
print("CTR")
(click.iloc[:,0]/count.iloc[:,0]).sort_index().plot.bar()
plt.hlines(0.05, 0, 24,linestyles='dashed',colors="r")<jupyter_output>CTR<jupyter_text>seems like being active 2 to 6 hours means true "active" users. especially 3 hrs.furthermore, among this range, being active 3 hrs-ish makes people want to click the ads.My assumption is that 1. 3 hrs is the limit people can resist himself from clicking ads that keeps showing again and again  2. or users was tired using same app 3 hrs, his dropped his guard and then finally click the ads3. or there is an ad super interesing that most users will click, however 3 hrs is a cycle that all ads can be dispyayed to users. so using app 3hrs gurantee at least once that fun ad was displayed to users. I will combine this assumption with later ads discovery to confirm my theory  communication_avgonline_30d<jupyter_code>col = "communication_avgonline_30d"
dist = raw_df.select(col).groupBy(col).count().toPandas()
dist = dist.set_index(col)
dist.index = dist.index.astype(int)
dist.sort_index().plot.bar()
dist = raw_df['uid',col].groupBy(col).agg(F.countDistinct("uid")).toPandas()
dist = dist.set_index(col)
dist.index = dist.index.astype(int)
dist.sort_index().plot.bar()
count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
print("CTR")
df = click.iloc[:,0]/count.iloc[:,0]
df.index = df.index.astype(int)
df.sort_index().plot.bar()
plt.hlines(0.035, 0, 24,linestyles='dashed',colors="r")<jupyter_output>CTR<jupyter_text>consume_purchase<jupyter_code>col = "consume_purchase"
raw_df.select(col).groupBy(col).count().toPandas().set_index(col).sort_index().plot.bar()
dist = raw_df['uid',col].groupBy(col).agg(F.countDistinct("uid")).toPandas()
dist.set_index(col).sort_index().plot.bar()
count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
print("CTR")
(click.iloc[:,0]/count.iloc[:,0]).sort_index().plot.bar()
plt.hlines(0.035, 0, 24,linestyles='dashed',colors="r")<jupyter_output>CTR<jupyter_text>ouch seems like this feature doesn't help at all  task_id 1. how many different ads?2. how were ads being distributed over 7 days?<jupyter_code>raw_df.select(F.countDistinct("task_id")).show()<jupyter_output>+-----------------------+
|count(DISTINCT task_id)|
+-----------------------+
|                   4329|
+-----------------------+<jupyter_text>Is an ad be more likely to be clicked if it shown more times?<jupyter_code>total_count = raw_df.select("task_id").groupBy("task_id").count().toPandas()
total_count = total_count.set_index("task_id")
total_count["count"].plot.bar()
total_count["bucket"] = pd.cut(total_count["count"],bins=[1,94,1269,5231,(5231-94)*1.5,548685])
total_click = raw_df.select("task_id","label_int").groupBy("task_id").sum("label_int").toPandas()
df = pd.merge(total_count,total_click,on="task_id")
df["count"].describe()
df[["bucket","count"]].groupby("bucket")["count"].sum().plot.bar(x="bucket",y="count")
df[["bucket","sum(label_int)"]].groupby("bucket")["sum(label_int)"].sum().plot.bar(x="bucket",y="sum(label_int)")
sns.boxplot(x="bucket", y="sum(label_int)", data=df)
%matplotlib inline
df[["bucket","count","sum(label_int)"]].groupby("bucket").sum().apply(lambda x: x[1]/x[0],axis=1).plot.bar()
plt.hlines(0.035, 0, 100,linestyles='dashed',colors="r")<jupyter_output><empty_output><jupyter_text>seems like the more times ads shown it got more likelihood to be clicked. Shoud research user/ads which has high CTR and look at their features then extract feature out there  adv_id column<jupyter_code>col = "adv_id"
raw_df.select(col).groupBy(col).count().toPandas().set_index(col).sort_index().plot.bar()
raw_df.select(F.countDistinct("adv_id")).show()
total_count = raw_df.select(col).groupBy(col).count().toPandas()
total_count = total_count.set_index(col)
total_count["count"].plot.bar()
total_click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas()
df = pd.merge(total_count,total_click,on=col)
df["count"].describe()
df["bucket"] = pd.cut(df["count"],bins=[1,48,604,3855,3855+(3855-604)*1.5,548684])
df
df[["bucket","count"]].groupby("bucket")["count"].sum().plot.bar(x="bucket",y="count")
df[["bucket","count","sum(label_int)"]].groupby("bucket").sum().apply(lambda x: x[1]/x[0],axis=1).plot.bar()
plt.hlines(0.035, 0, 100,linestyles='dashed',colors="r")<jupyter_output><empty_output><jupyter_text>creat_type_cd<jupyter_code>col = "creat_type_cd"
dist = raw_df['task_id',col].groupBy(col).agg(F.countDistinct("task_id")).toPandas()
dist.set_index(col).sort_index().plot.bar()
count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
print("CTR")
(click.iloc[:,0]/count.iloc[:,0]).sort_index().plot.bar()
plt.hlines(0.035, 0, 24,linestyles='dashed',colors="r")
def cat_analysis(raw_df,col,prime_id):
    dist = raw_df[prime_id,col].groupBy(col).agg(F.countDistinct(prime_id)).toPandas()
    dist.set_index(col).sort_index().plot.bar()
    plt.show()
    count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
    click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
    print("CTR")
    (click.iloc[:,0]/count.iloc[:,0]).sort_index().plot.bar()
    plt.hlines(0.035, -1, 100,linestyles='dashed',colors="r")
cat_analysis(raw_df,"inter_type_cd","task_id")
cat_analysis(raw_df,"slot_id","task_id")
cat_analysis(raw_df,"indu_name","task_id")
cat_analysis(raw_df,"adv_prim_id","task_id")
cat_analysis(raw_df,"dev_id","task_id")<jupyter_output><empty_output><jupyter_text>App<jupyter_code>cat_analysis(raw_df,"tags","spread_app_id")
cat_analysis(raw_df,"app_first_class","spread_app_id")
cat_analysis(raw_df,"app_second_class","spread_app_id")
cat_analysis(raw_df,"his_app_size","spread_app_id")
cat_analysis(raw_df,"his_on_shelf_time","spread_app_id")
cat_analysis(raw_df,"app_score","spread_app_id")<jupyter_output><empty_output><jupyter_text>Device<jupyter_code>cat_analysis(raw_df,"device_size","device_name")
cat_analysis(raw_df,"emui_dev","device_name")
cat_analysis(raw_df,"list_time","device_name")
cat_analysis(raw_df,"device_price","device_name")
cat_analysis(raw_df,"net_type","net_type")
cat_analysis(raw_df,"pt_d","pt_d")<jupyter_output><empty_output><jupyter_text>Examine ID columns which has hight CTR<jupyter_code>df = raw_df.select("uid","label_int").groupBy("uid").agg({"*":"count","label_int":"sum"}).toPandas()
df = df.set_index("uid")
df["uid_ctr"] = df.apply(lambda row: row[1]/row[0],axis=1)
df[df["uid_ctr"] >= 0.035]
sns.distplot(df["uid_ctr"])
sns.kdeplot(df["uid_ctr"],cumulative = True)
print(df["uid_ctr"].describe())
df["ctr_bin"] = pd.cut(df["uid_ctr"],bins=[-1,0.015,0.06,0.2,0.2+(0.2-0.015)*1.5,1.1])
df.groupby("ctr_bin").count()["count(1)"].plot.bar()
df[df["uid_ctr"] >= 0.2+(0.2-0.015)*1.5]
def id_analysis(raw_df,id_col):
    df = raw_df.select(id_col,"label_int").groupBy(id_col).agg({"*":"count","label_int":"sum"}).toPandas()
    df = df.set_index(id_col)
    
    # CTR of each ID
    df[f"{id_col}_ctr"] = df.apply(lambda row: row[1]/row[0],axis=1)
    print(f"CTR distribution by {id_col}")
    print(df[f"{id_col}_ctr"].describe())
    
    # dist plot
    sns.distplot(df[f"{id_col}_ctr"])
    plt.show()
    # cumulative plot
    sns.kdeplot(df[f"{id_col}_ctr"],cumulative = True)
    plt.show()
    
    # Bin CTR to decide high or low CTR in this category
    df["ctr_qbin"] = pd.qcut(df[f"{id_col}_ctr"], 4)
    qbin_df = df.groupby("ctr_qbin").sum()
    (qbin_df["sum(label_int)"]/qbin_df["count(1)"]).plot.bar()
    plt.show()
    
    # see how is high ctr group performing
    sns.boxplot(x="ctr_qbin",y=f"{id_col}_ctr",data=df)
    plt.show()
    high_ctr_ids = df[df[f"{id_col}_ctr"] >= df[f"{id_col}_ctr"].quantile(0.75)].index
    return high_ctr_ids
%%time
high_ctr_users = id_analysis(raw_df,"uid")
def cat_analysis(raw_df,col,prime_id):
    dist = raw_df[prime_id,col].groupBy(col).agg(F.countDistinct(prime_id)).toPandas()
    dist.set_index(col).sort_index().plot.bar()
    plt.show()
    count = raw_df.select(col).groupBy(col).count().toPandas().set_index(col)
    click = raw_df.select(col,"label_int").groupBy(col).sum("label_int").toPandas().set_index(col)
    print("CTR")
    (click.iloc[:,0]/count.iloc[:,0]).sort_index().plot.bar()
    plt.hlines(0.035, -1, 100,linestyles='dashed',colors="r")<jupyter_output><empty_output><jupyter_text>What more can be done in EDA1. CTR heatmap by two categorical variables2. Seperate ids which have 100% CTR but appeared only once in dataset<jupyter_code>def high_low_ctr_group_dist(raw_df,col,prime_col,high_ctr_tags):
    df = raw_df.select(prime_col,col,"label_int").groupBy("uid",col).agg({"*":"count","label_int":"sum"}).toPandas()
    df["ctr_group"] = df[prime_col].apply(lambda x: "high_ctr" if x in high_ctr_tags else "low_ctr")
    agg_df = df.groupby([col,"ctr_group"]).sum()
    agg_df = agg_df.reset_index()
    high = agg_df[agg_df["ctr_group"] == "high_ctr"]
    high = high.set_index(col)["count(1)"].sum()
    low = agg_df[agg_df["ctr_group"] == "low_ctr"]
    low = low.set_index(col)["count(1)"].sum()
    agg_df["count_pct"] = np.where(agg_df["ctr_group"] == "high_ctr",agg_df["count(1)"]/high,agg_df["count(1)"]/low)
    sns.barplot(x=col,y="count_pct",hue="ctr_group",data=agg_df)
    plt.show()
high_low_ctr_group_dist(raw_df,"age","uid",high_ctr_users)
from EDA import EDA
eda = EDA(0.035)
eda.cat_analysis(raw_df,"age","uid")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1835268505079826, 'fraction_numerical': 0.02093613933236575, 'mean_word_length': 4.344386271087842, 'pattern_counts': {'":': 8, '<': 145, '<?xml version=': 0, '>': 148, 'https://': 0, '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': '38', 'dir': 'jupyter-structured-clean-dedup', 'id': '472805', 'license': 'MIT', 'n_tokens_mistral': 9924, 'n_tokens_neox': 9526, 'n_words': 1715, 'path': 'EDA.ipynb', 'repo_name': 'xwilchen/MLE_Nanodegree_Capstone', 'size': '27560'}