<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step1:  Check basic statistics of manually selected topics
Objective
Step3:  Manually compile a list of topics with keywords
Topics information (category, name, keywords_lst) are manually compiled into config.MANUALLY_SELECTED_TOPICS_LST
Step5:  Check number of associated news and tweets for each topic
Build pickle for news_id and tweets_id associated with each topic
Step7:  Recover pickle and print number of news and tweets for each topic
Step10:  Check news titles and sample tweets of each topic
<ASSISTANT_TASK:>
Python Code:
Initialization
'''
Standard modules
'''
import os
import pickle
import sqlite3
import time
from pprint import pprint
'''
Analysis modules
'''
import pandas as pd
'''
Custom modules
'''
import config
import utilities
'''
Misc
'''
nb_name = '20171011-daheng-check_topics_basic_statistics'
Explanation: Check basic statistics of manually selected topics
Objective: make sure manually selected topics have high quality.
 - Characteristic keywords: easy to regonize associated news.
 - Amout of disscussion: reasonable size of associated news and tweets
 - Consistent in meaning: no drift/disperse in content.
 - Evolution of event: reasonable time-span of associated news.
Last modified: 2017-10-18
Roadmap
Manually compile a list of topics with keywords
Check number of associated news and tweets for each topic
Check news titles and sample tweets of each topic
Check time-span of each topic
Steps
End of explanation
Print out manually selected topics information
for topic_ind, topic in enumerate(config.MANUALLY_SELECTED_TOPICS_LST):
    print('({}/{}) {}'.format(topic_ind+1, len(config.MANUALLY_SELECTED_TOPICS_LST), topic))
Explanation: Manually compile a list of topics with keywords
Topics information (category, name, keywords_lst) are manually compiled into config.MANUALLY_SELECTED_TOPICS_LST
End of explanation
%%time
Register
    TOPICS_LST_PKL = os.path.join(DATA_DIR, 'topics.lst.pkl')
in config.
if 0 == 1:
    supplement_topics_lst = []
    
    '''
    Load in pickle for news data over selected period.
    '''
    news_period_df = pd.read_pickle(config.NEWS_PERIOD_DF_PKL)
 
    for topic_ind, topic in enumerate(config.MANUALLY_SELECTED_TOPICS_LST):
        localtime = time.asctime(time.localtime(time.time()))
        print('({}/{}) processing topic: {} ... {}'.format(topic_ind+1,
                                                           len(config.MANUALLY_SELECTED_TOPICS_LST),
                                                           topic['name'],
                                                           localtime))
        '''
        Match out associated news titles.
        '''
        asso_news_native_ids_lst = []
        for ind, row in news_period_df.iterrows():
            if utilities.news_title_match(row['news_title'], topic['keywords_lst'], verbose=False):
                asso_news_native_ids_lst.append(row['news_native_id'])
        
        topic['news_native_ids_lst'] = asso_news_native_ids_lst
        
        '''
        Query associated tweets
        '''
        asso_tweets_ids_lst = []
        
        query_news_tweets = '''
        select tweet_id from tweets
        where news_native_id = :news_native_id
        order by tweet_id asc;'''
        
        with sqlite3.connect(config.NEWS_TWEETS_DB_FILE) as conn:
            cursor = conn.cursor()
            for news_native_id in topic['news_native_ids_lst']:
                cursor.execute(query_news_tweets, {'news_native_id': news_native_id})
                tweets_ids_lst = [item[0] for item in cursor.fetchall()]
                asso_tweets_ids_lst.extend(tweets_ids_lst)
                
        topic['tweets_ids_lst'] = asso_tweets_ids_lst
        
        supplement_topics_lst.append(topic)
    
    '''
    Make pickle
    '''
    with open(config.TOPICS_LST_PKL, 'wb') as f:
        pickle.dump(supplement_topics_lst, f)
Explanation: Check number of associated news and tweets for each topic
Build pickle for news_id and tweets_id associated with each topic
End of explanation
Test recover topics lst pkl
if 0 == 1:
    with open(config.TOPICS_LST_PKL, 'rb') as f:
        topics_lst = pickle.load(f)
    
    for topic_ind, topic in enumerate(topics_lst):
        print('{} Topic_name: {}; news_num: {}; tweets_num: {}'.format(topic_ind,
                                                                       topic['name'],
                                                                       len(topic['news_native_ids_lst']),
                                                                       len(topic['tweets_ids_lst'])))
Explanation: Recover pickle and print number of news and tweets for each topic
End of explanation
Recover pkl
if 1 == 1:
    with open(config.TOPICS_LST_PKL, 'rb') as f:
        topics_lst = pickle.load(f)
Select topic
if 1 == 1:
    target_topic_ind = 26
    topic = topics_lst[target_topic_ind]
'''
Print associated news titles
'''
if 1 == 1:
    print('TOPIC: {}; KEYWORDS: {}'.format(topic['name'], topic['keywords_lst']))
    
    # limit to first 100 news
    news_native_ids_lst = topic['news_native_ids_lst'][:100]
    
    query_news = '''
    select news_title, news_collected_time from news
    where news_native_id = :news_native_id
    order by news_native_id asc;'''
    with sqlite3.connect(config.NEWS_TWEETS_DB_FILE) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        for news_native_id in news_native_ids_lst:
            cursor.execute(query_news, {'news_native_id': news_native_id})
            for row in cursor.fetchall():
                print('{}: {}'.format(row['news_collected_time'], row['news_title']))
'''
Print associated tweets
'''
if 1 == 1:
    print('TOPIC: {}; KEYWORDS: {}'.format(topic['name'], topic['keywords_lst']))
    
    # limit to first 150 tweets
    tweets_ids_lst = topic['tweets_ids_lst'][:150]
    
    query_tweets = '''
    select tweet_text, tweet_collected_time from tweets
    where tweet_id = :tweet_id
    order by tweet_native_id asc;'''
    with sqlite3.connect(config.NEWS_TWEETS_DB_FILE) as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        for tweet_id in tweets_ids_lst:
            cursor.execute(query_tweets, {'tweet_id': tweet_id})
            for row in cursor.fetchall():
                print('{}: {}'.format(row['tweet_collected_time'], row['tweet_text']))
Explanation: Check news titles and sample tweets of each topic
End of explanation
<END_TASK>