<jupyter_start><jupyter_text>Users<jupyter_code>import pandas as pd

users = pd.read_csv('data_old/sc_report_user.csv')
print(users.shape)
users.head()
del users['user_type']
users.head()
users.to_csv('data_table/user.csv', index = False)<jupyter_output><empty_output><jupyter_text>Project<jupyter_code>projects = pd.read_csv('data_old/sc_report_projects.csv')
print(projects.shape)
projects<jupyter_output>(10, 2)<jupyter_text>Geo maps in Data StudioA Data Studio geo map requires you to provide 3 pieces of information:- a geographic dimension, such as Country, City, Region, etc.- a metric, such as Sessions, Units Sold, Population, etc.- the map's zoom area<jupyter_code>location = pd.read_csv('data_old/AdWords_API_Location_Criteria.csv')
location[location['Name'] == 'Bangkok'].head()<jupyter_output><empty_output><jupyter_text>Latitude & Longitude Phaya Thai- Samsen Nai, Bangkok 10400, "13.774123, 100.538318"Lumphini- Pathum Wan, Bangkok 10330, "13.733438, 100.547931"Khlong Ton Sai- Khlong San, Bangkok 10600, "13.724766, 100.504329"Huai Khwang- Bangkok 10310, "13.760450, 100.568187"Suan Luang- Bangkok, "13.744777, 100.632045"Bang Yi Khan- Bang Phlat, Bangkok 10700, "13.769454, 100.491626"Chom Phon- Chatuchak, Bangkok 10900, "13.818133, 100.569217"<NAME>- <NAME>, Bangkok 10400, "13.778457, 100.546215"<jupyter_code>location_list = ["13.774123, 100.538318",
                 "13.733438, 100.547931",
                 "13.724766, 100.504329", 
                 "13.760450, 100.568187",
                 "13.744777, 100.632045",
                 "13.769454, 100.491626",
                 "13.818133, 100.569217"]
import numpy as np
from random import randint

location = []
for i in range(len(projects)):
    location.append(location_list[randint(0,len(location_list)-1)])
projects['location'] = location
projects
projects.to_csv('data_table/project.csv', index = False)<jupyter_output><empty_output><jupyter_text>Article<jupyter_code>articles = pd.read_csv('data_old/sc_report_topics1.csv')
print(articles.shape)
articles.head()
if 'read_lenght' in articles:
    del articles['read_lenght']
articles.head()
articles.to_csv('data_table/article.csv', index = False)
articles['id'].unique()
articles.min()<jupyter_output><empty_output><jupyter_text>User-Project<jupyter_code>user_project = pd.read_csv('data_old/user_project.csv')
print(user_project.shape)
user_project.head()
user_project_grouped = user_project.groupby(['user_id','project_id']).count()
user_project_grouped.head()
new_user_project = user_project.drop_duplicates(subset=['user_id', 'project_id'], keep='first')
new_user_project.groupby(['user_id','project_id']).count().head()
new_user_project.to_csv('data_table/user_project.csv', index = False)<jupyter_output><empty_output><jupyter_text>Article-Project<jupyter_code>article_project = pd.read_csv('data_old/article_project.csv')
print(article_project.shape)
article_project.head()
new_article_project = article_project.drop_duplicates(subset=['article_id', 'project_id'], keep='first')
new_article_project.shape
new_article_project.to_csv('data_table/article_project.csv', index = False)<jupyter_output><empty_output><jupyter_text>Seen<jupyter_code>import random
import time

def strTimeProp(start, end, format, prop):
    """Get a time at a proportion of a range of two formatted times.

    start and end should be strings specifying times formated in the
    given format (strftime-style), giving an interval [start, end].
    prop specifies how a proportion of the interval to be taken after
    start.  The returned time will be in the specified format.
    """

    stime = time.mktime(time.strptime(start, format))
    etime = time.mktime(time.strptime(end, format))

    ptime = stime + prop * (etime - stime)

    return time.strftime(format, time.localtime(ptime))


def randomDate(start, end, prop):
    return strTimeProp(start, end, '%Y-%m-%d %H:%M:%S', prop)
seen = pd.DataFrame(columns=['article_id','user_id','seen_at'])
seen
from random import randint

for i in range(36500):
    article_id = randint(1,100)
    user_id = randint(1,100)
    seen_at = randomDate("2017-04-04 04:00:00", "2018-04-11 00:00:00", random.random())
    
    seen_row = pd.DataFrame([[article_id, user_id, seen_at]], columns=['article_id','user_id','seen_at'])
    seen = seen.append(seen_row, ignore_index=True)
print(seen.shape)
seen.head()<jupyter_output>(36500, 3)<jupyter_text>คนจะเห็นข่าวได้ต้องอยู่ในโครงการที่ข่าวประกาศไป จะได้จำนวนคนที่มีโอกาสเห็นข่าวจากแต่ละโครงการจากการเอา ตาราง **article_project** ไป merge กับ **user_project**<jupyter_code>article_project_user = pd.merge(new_article_project, new_user_project, on='project_id')
article_project_user.groupby('project_id')['user_id'].count()<jupyter_output><empty_output><jupyter_text>ตัวเลขที่เห็นนี้คือจำนวนคนที่มีโอกาสเห็นข่าวจากแต่ละโครงการรวม All time<jupyter_code>article_project_user.head()<jupyter_output><empty_output><jupyter_text>เมื่อผนวกข้อมูลว่าข่าวประกาศไปโครงการไหนบ้าง กับใครอยู่ในโครงการนั้นบ้าง แล้วคนในโครงการใครบ้างที่เห็นข่าว จะได้จำนวนดังนั้นคนเห็นข่าวจริงๆ ดังนี้<jupyter_code>is_seen = pd.merge(article_project_user, seen,  how='inner', left_on=['article_id','user_id'], right_on = ['article_id','user_id'])
print(is_seen.shape)
is_seen.head()<jupyter_output>(57730, 5)<jupyter_text>ลบแถวที่ซ้ำออก (อย่างอื่นซ้ำหมดแต่มี seen_at ไม่ตรงกัน)<jupyter_code>is_seen = is_seen.drop_duplicates(subset=['article_id', 'project_id', 'user_id', 'user_type'], keep='first')
print(is_seen.shape)
is_seen.head() 
is_seen.to_csv('data_table/seen.csv', index = False)<jupyter_output><empty_output><jupyter_text>Click click อาจจะเป็น sampling ของ seen (กรณีที่เข้าได้จากทาง app อย่างเดียว) เอาสัก 40%<jupyter_code>click = seen.sample(frac=0.4)
print(click.shape)
click.head()<jupyter_output>(14600, 3)<jupyter_text>แล้วทำแบบ Seen<jupyter_code>is_click = pd.merge(article_project_user, click,  how='inner', left_on=['article_id','user_id'], right_on = ['article_id','user_id'])
print(is_click.shape)
is_click.head()<jupyter_output>(23176, 5)<jupyter_text>เพิ่มเวลา click ให้หน่อย<jupyter_code>from datetime import datetime  
from datetime import timedelta
from random import randint

def addRandomMinute(time,max_minute):
    now = datetime.strptime(time,'%Y-%m-%d %H:%M:%S')
    now_plus_minute = now + timedelta(minutes = randint(1,max_minute))
    return now_plus_minute.strftime('%Y-%m-%d %H:%M:%S')
addRandomMinute('2018-01-02 11:59:00',10)
new_is_click = is_click[:]
new_is_click['seen_at'] = is_click['seen_at'].apply(addRandomMinute, args=(10,))
new_is_click = new_is_click.rename(columns={'seen_at': 'clicked_at'})
new_is_click.head()
new_is_click.to_csv('data_table/click.csv', index = False)<jupyter_output><empty_output><jupyter_text>Read read เป็น sampling ของ click เอาสัก 70%<jupyter_code>read = click.sample(frac=0.7)
print(read.shape)
read.head()<jupyter_output>(10220, 3)<jupyter_text>แล้วทำแบบ Seen<jupyter_code>is_read = pd.merge(article_project_user, read,  how='inner', left_on=['article_id','user_id'], right_on = ['article_id','user_id'])
print(is_read.shape)
is_read.head()<jupyter_output>(16227, 5)<jupyter_text>เพิ่ม column read_time(0-1200 วินาที), read_length(0-100%)<jupyter_code>import numpy as np
len(is_read)

read_times = np.array([])
read_lengths = np.array([])
for i in range(len(is_read)):
    read_times = np.append(read_times,[randint(0,1200)])
    read_lengths = np.append(read_lengths,[randint(0,100)])
    
is_read['read_time'] = read_times
is_read['read_length'] = read_lengths
if 'seen_at' in is_read:
    del is_read['seen_at'] 
is_read.head() 
is_read.to_csv('data_table/read.csv', index = False)<jupyter_output><empty_output><jupyter_text>Add Column is_readนับว่าอ่านเมื่อ$$\frac{\text{read time user}}{\text{read time article}} >= 0.7$$ และ $$\text{read length} > 70$$<jupyter_code>articles.head()
new_read = pd.merge(is_read, articles[['id','read_time']], how='inner', left_on=['article_id'], right_on = ['id'])
print(new_read.shape)
new_read.head() 
new_read['is_read'] = (new_read['read_time_x']/new_read['read_time_y'] > 0.7).astype(int)
if 'id' in new_read:
    del new_read['id'] 
if 'read_time_y' in new_read:
    del new_read['read_time_y']
new_read = new_read.rename(columns={'read_time_x':'read_time'})
new_read.head()<jupyter_output><empty_output><jupyter_text>Action action เป็น sampling ของ read เอาสัก 20%<jupyter_code>action = is_read.sample(frac=0.2)
print(action.shape)
action.head()<jupyter_output>(3245, 6)<jupyter_text>แล้วทำแบบ Seen<jupyter_code>new_action = pd.merge(article_project_user, action[['article_id','user_id']],  how='inner', left_on=['article_id','user_id'], right_on = ['article_id','user_id'])
print(new_action.shape)
new_action.head()<jupyter_output>(8122, 4)<jupyter_text>เพิ่ม Column `save_at, share_at,love_at,sad_at,angry_at`<jupyter_code>import numpy as np

save_at = np.array([])
share_at = np.array([])

love_at = np.array([])
sad_at = np.array([])
angry_at = np.array([])

for i in range(len(new_action)):
    save_data = randomDate("2017-04-04 04:00:00", "2018-04-11 00:00:00", random.random()) if randint(0,1) == 1 else ""
    save_at = np.append(save_at,[save_data])

    share_data = randomDate("2017-04-04 04:00:00", "2018-04-11 00:00:00", random.random()) if randint(0,1) == 1 else "" 
    share_at = np.append(share_at,[share_data])

    action_id = randint(1,3)
    if action_id == 1:
        love_at = np.append(love_at,[randomDate("2017-04-04 04:00:00", "2018-04-11 00:00:00", random.random())])
        sad_at = np.append(sad_at,[""])
        angry_at = np.append(angry_at,[""])
    elif action_id == 2:
        love_at = np.append(love_at,[""])
        sad_at = np.append(sad_at,[randomDate("2017-04-04 04:00:00", "2018-04-11 00:00:00", random.random())])
        angry_at = np.append(angry_at,[""])
    else:
        love_at = np.append(love_at,[""])
        sad_at = np.append(sad_at,[""])
        angry_at = np.append(angry_at,[randomDate("2017-04-04 04:00:00", "2018-04-11 00:00:00", random.random())])
        
new_action['save_at'] = save_at
new_action['share_at'] = share_at
new_action['love_at'] = love_at
new_action['sad_at'] = sad_at
new_action['angry_at'] = angry_at
new_action.head()
new_action.to_csv('data_table/action.csv', index = False)
from random import randint
randint(1,3)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1603023841829812, 'fraction_numerical': 0.05727854235316922, 'mean_word_length': 6.79380664652568, 'pattern_counts': {'":': 0, '<': 82, '<?xml version=': 0, '>': 85, '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': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '217951', 'license': 'Apache-2.0', 'n_tokens_mistral': 4668, 'n_tokens_neox': 4266, 'n_words': 620, 'path': 'data_processing.ipynb', 'repo_name': 'Kanbc/article-report-by-data-studio', 'size': '10326'}