<jupyter_start><jupyter_text>GoalThe purpose of this notebook is to > explore data sources, packages and ways to inform about trending topics within ESG.The approach can then be generalized to other domains, such as data science, programing, food, ETFs or travel. The ideal data should have *search interest* for each keyword at time *t*.  pytrendInpsect `build_payload` Related Queries gprop: `['', 'images', 'news', 'youtube', 'froogle']`  TODO get queries across geo and gprop```GEO = ['global', 'us', 'gb', 'au', 'in', 'ca']GPROP = ['', 'news', 'youtube']```<jupyter_code>import pandas as pd
from pytrends.request import TrendReq
from datetime import datetime

# ~------------------ CONFIG ------------------~
SELECT_PROJECT = 'sustainable-finance-trends' # 'data-science-trends' # 
EXPORT_DATA    = True # store csv 

timestamp = datetime.now().strftime("%y%m%d_%H%M")
PROJECT = {
            'sustainable-finance-trends': {
                'file_name': f'../data/0_external/sustainable_finance_{timestamp}.csv',
                'kw_list': ['esg', 'sustainable finance',  'sustainable investment', 'responsible investment'], 
                'geo': 'global'
            },
           'data-science-trends': {
               'file_name': f'../data/0_external/data_science_{timestamp}.csv',
               'kw_list': ['data science', 'machine learning', 'deep learning', 'artificial intelligence', 'ai'], 
               'geo': 'global'
           },
}

print(f"Collect pytrend related searches for project:\t{SELECT_PROJECT}\n")
print(pd.DataFrame(PROJECT[SELECT_PROJECT].items()).explode(1).to_markdown())

# ~------------------ EXTRACT ------------------~
def get_queries(kw_list):
    """ Calls pytrend' related_queries with a list of keywords and geo settings 
    Input
        pytrend: TrendReq() session of pytrend
        kw_list: list of strings, used as input for query and passed to TrendReq().build_payload() 
    Return
        Dataframe with query result
    """    
    df_related_queries = pd.DataFrame()
    try:
        pytrend = TrendReq() 
        pytrend.build_payload(kw_list)
        df_related_queries = pytrend.related_queries()

        print(f"Query succeeded for", *kw_list, sep='\n\t')
    except Exception as e:
        print(e, "\nQuery not unsuccessful\n", '='*42)

    return df_related_queries

# ~------------------ TRANSFORM ------------------~
def process_queries(related_queries, kw, ranking, geo):
    try:
        df = related_queries[kw][ranking]
        df[['keyword', 'ranking', 'geo', 'query_timestamp']] = [kw, ranking, geo, datetime.now()]
    except:
        print(f"Append empty dataframe for {ranking}: {kw}")
        return pd.DataFrame(columns=['query', 'value', 'keyword', 'ranking', 'geo', 'query_timestamp'])
    
    return df

def create_df_related_queries(df_related_queries, geo='global'):
    """ Build the dataframe for all keywords
        Flatten Json format with nested lists to DataFrame
    """
    try: 
        df = pd.concat(
            [process_queries(
                df_related_queries, kw, ranking=r, geo=geo
            ) for r in [*df_related_queries[
                            [*df_related_queries][0]]
                       ] for kw in [*df_related_queries]])
    except:
        print("Google Trends returned nothing when calling related_queries(). Restart kernel and retry.")
        
    return df

# ~------------------ LOAD ------------------~
def write_to_csv(df, filename):
    print('_'*42, f'\nExport data, dimension: {df.shape} to\t{filename}\n')
    print(df.head().to_markdown())
    df.to_csv(f'{filename}', index=False) 

dict_related_queries = get_queries(kw_list=PROJECT[SELECT_PROJECT]['kw_list'])
df = create_df_related_queries(dict_related_queries)


if EXPORT_DATA:
    write_to_csv(df, filename=PROJECT[SELECT_PROJECT]['file_name'])<jupyter_output>Collect pytrend related searches for project:	sustainable-finance-trends

|    | 0         | 1                                                      |
|---:|:----------|:-------------------------------------------------------|
|  0 | file_name | ../data/0_external/sustainable_finance_210228_0929.csv |
|  1 | kw_list   | esg                                                    |
|  1 | kw_list   | sustainable finance                                    |
|  1 | kw_list   | sustainable investment                                 |
|  1 | kw_list   | responsible investment                                 |
|  2 | geo       | global                                                 |
Query succeeded for
	esg
	sustainable finance
	sustainable investment
	responsible investment
__________________________________________ 
Export data, dimension: (151, 6) to	../data/0_external/sustainable_finance_210228_0929.csv

|    | query         |   value | keyword   | ranking   | geo    | query_timestamp       [...]<jupyter_text>---  Trends dataTODO: links to google with texttemplate List files Plot Export to chart studio<jupyter_code>from glob import glob
for i, f in enumerate(glob('../data/0_external/*.csv')):
    print(i,f)
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import chart_studio.plotly as py

# ~------------------ SELECT INPUT DATA ------------------~
DATA_LOC = '../data/0_external\sustainable_finance_210228_0929.csv'
df_raw = pd.read_csv(DATA_LOC)
df = df_raw.copy()

## feature engineering: totals and normalize
grouped = df.groupby(['ranking']).value # group values by ranking
df['value_total'] = grouped.transform('sum') # total sum 
df['value_normalized'] = (df.value-grouped.transform('min'))/(grouped.transform('max')-grouped.transform('min')) # normalize 
df['value_normalized_total'] = df.groupby(['ranking']).value_normalized.transform('sum') # total sum of normalized values 
df['date'] = pd.to_datetime(df.query_timestamp).dt.strftime("%d. %B %Y")

# restrict on 5 hightest
# df = df.loc[df.groupby(["ranking", "keyword"]).value_normalized.nlargest(5).reset_index().level_2,:] 

## labels
df['labels'] = df['query'].apply(lambda x: x.replace(' ', '<br>')) # linebreaks
df['ranking_label'] = df.ranking.replace({'top': f'Evergreens - updated {df.date[0]}',
                                          'rising': f'Trending - updated {df.date[0]}'})


# link labels
def create_href(x, link_html="<a style='color:#9BCAB8; cursor:pointer' target='_blank' href='http://www.google.com/search?q={}'>{}</a>"): # #<a href='http://www.google.com/search?q={} '> {}</a>
    return link_html.format(x['query_insert_href'], x['labels'])

df['query_insert_href'] = df['query'].str.replace(" ", '+')
df['labels_href'] = df.apply(create_href, axis=1)

## top and rising
dfr, dft = df.query('ranking == "rising"'),  df.query('ranking == "top"')
df.date[0]
fig = make_subplots(2, 1,
                    specs=[[{"type": "domain"}], [ {"type": "domain"}]])

fig.add_trace(go.Treemap(
    labels = dfr['labels'], #_href
    parents = dfr.ranking_label, 
    values = dfr.value_normalized, 
), 1, 1)

fig.add_trace(go.Treemap(
    labels = dft['labels'],
    parents = dft.ranking_label, 
    values = dft.value_normalized
), 2, 1)

fig.update_layout(margin=dict(t=10, b=10, r=10, l=10), 
#                   width=1200,
#                   height=900, 
                  plot_bgcolor= "rgba(0, 0, 0, 0)",
                  paper_bgcolor= "rgba(0, 0, 0, 0)",
#                  title_text=f'Updated: {df.date[0]}', 
#                  title_font_size=14
                 )

fig.update_traces(
    opacity=1, 
    textposition='middle center', 
    textfont={'family':"Arial", 'size': 20}, 
    hoverinfo= "label", # "skip",
    tiling = {'squarifyratio': 1, 'pad': 0}, 
    textfont_size=24, 
    marker={
#         'colors': df.index,
        'depthfade': True,
        'cauto': True,
#         'colorscale': 'Greys'
    }
)

fig.show()

py.plot(fig, filename=SELECT_PROJECT)
SELECT_PROJECT<jupyter_output><empty_output><jupyter_text>---  Dash<jupyter_code># %load ../output/dash/app.py
"""

This is an example of the app.py file for a dash app deployed on Heroku. 
It creates http://esg-trending-test.herokuapp.com/

Follow the steps on https://dash.plotly.com/deployment 
to delploy the app with Heroku.

Note: 
    Put your data into the same folder or subfolder where `app.py` lives.

    I used a conda virtual environment, but installed all required packages 
    with `pip install` instead of `conda install`. This ensures that the 
    requirements.txt is readable by the Heroku Dyno. 


Author: <NAME>
License: MIT

"""

import os
import pandas as pd

# plotly
import plotly.graph_objects as go
from plotly.subplots import make_subplots

# dash
import dash
import dash_core_components as dcc
import dash_html_components as html


#~ --------------- SETTINGS --------------- ~
DATA_DIR = './data/'                # <WHERE YOUR DATA LIVES>
FILENAME = 'related_queries_210218_2021.csv' # <NAME OF THE FILE>.csv
#~ ---------------------------------------- ~



#~ --------------- DATA PREPARATION --------------- ~
def load_data(data_dir, filename):
    df_raw = pd.read_csv(os.path.join(DATA_DIR, FILENAME))
    df = df_raw.copy()

    return df

def prepare_data(df):
    """ Feature engineering for Google trends 'related queries'
    
    Input
        df: dataframe
    Return
        dfr, dft: dataframes with rising search interest (r) and top search interest (t)  

    """
    # feature engineering: totals and normalize
    grouped = df.groupby(['ranking']).value # group values by ranking
    df['value_total'] = grouped.transform('sum') # total sum 
    df['value_normalized'] = (df.value-grouped.transform('min'))/(grouped.transform('max')-grouped.transform('min')) # normalize 
    df['value_normalized_total'] = df.groupby(['ranking']).value_normalized.transform('sum') # total sum of normalized values 
    
    # labelling
    df['labels'] = df['query'].apply(lambda x: x.replace(' ', '<br>'))
    df['ranking_label'] = df.ranking.replace({'top': 'Evergreens', 'rising': 'Trending'})
    
    # rankings: top (t) and rising (r)
    dfr, dft = df.query('ranking == "rising"'),  df.query('ranking == "top"')

    return dfr, dft

#~ --------------- PIPELINE --------------- ~
dfr, dft = (load_data(DATA_DIR, FILENAME)
                .pipe(prepare_data))

#~ --------------- PLOT --------------- ~
fig = make_subplots(2, 1,
                    specs=[[{"type": "domain"}], [ {"type": "domain"}]])

fig.add_trace(go.Treemap(
    labels = dfr['labels'],
    parents = dfr.ranking_label, 
    values = dfr.value_normalized, 
), 1, 1)

fig.add_trace(go.Treemap(
    
    labels = dft['labels'],
    parents = dft.ranking_label, 
    values = dft.value_normalized
), 2, 1)

fig.update_layout(margin=dict(t=10, b=10, r=10, l=10))

fig.update_traces(
    opacity=1, 
    textposition='middle center', 
    textfont={'family':"Arial", 'size': 18}, 
    hoverinfo= "label",
    tiling = {'squarifyratio': 1, 'pad': 0}, 
    marker = {'depthfade': False}
)


external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']


app = dash.Dash(__name__, external_stylesheets=external_stylesheets)
server = app.server

app.layout = html.Div(children=[
    html.H1(children='Trending topics in sustainable finance'),

    html.Div(children='''
        Know what people search
    '''),

    dcc.Graph(
        id='example-graph',
        figure=fig
    )
])

if __name__ == '__main__':
    app.run_server(debug=True)<jupyter_output><empty_output><jupyter_text>Defining the Layout of Your Dash ApplicationNext, you’ll define the layout property of your application. This property dictates the look of your app. In this case, you’ll use a heading with a description below it and two graphs. Here’s how you define it:  Dump  Pandas profiling<jupyter_code>from pandas_profiling import ProfileReport

from glob import glob


df = pd.read_csv(glob('../data/0_external/*210213*.csv')[0])
profile = ProfileReport(df, title="Pandas Profiling Report", explorative=True)

profile<jupyter_output><empty_output><jupyter_text>Query overview  Other approach, refactored<jupyter_code># unpack nested dict {keyword: {ranking: {DataFrame}} }
final_df = pd.DataFrame()
for keyword, ranking in related_queries_dict.items():
    for ranking_name, df in ranking.items():
        print(keyword, '>>>',ranking_name, '>>> DataFrame',df.shape, df.columns.values)

        
#     print(key, value.keys())
#     df = value
#     df.loc[:,'Country'] = key
#     final_df = pd.concat([df, final_df], 0)
# final_df<jupyter_output>esg >>> top >>> DataFrame (25, 2) ['query' 'value']
esg >>> rising >>> DataFrame (25, 2) ['query' 'value']
sustainable finance >>> top >>> DataFrame (24, 2) ['query' 'value']
sustainable finance >>> rising >>> DataFrame (21, 2) ['query' 'value']
sustainable investment >>> top >>> DataFrame (14, 2) ['query' 'value']
sustainable investment >>> rising >>> DataFrame (11, 2) ['query' 'value']<jupyter_text>Keyword suggestions<jupyter_code>suggestions_dict = pytrend.suggestions(keyword=kw_list[3])
pd.DataFrame(suggestions_dict)
print(pytrend.suggestions(keyword='sustainable investment'), '\n')
print(pytrend.suggestions(keyword='sustainable investment funds'), '\n')<jupyter_output>[{'mid': '/m/03cz3m_', 'title': 'Principles for Responsible Investment', 'type': 'Topic'}] 

[{'mid': '/g/11c740t89k', 'title': 'Investment Fund for Developing Countries', 'type': 'Company'}]<jupyter_text>Interest by region<jupyter_code># Interest by Region
interest_by_region_df = pytrend.interest_by_region()
print(interest_by_region_df.head())
interest_by_region_df['sustainable finance'].nlargest(10)
interest_by_region_df['sustainable investment'].nlargest(10)
# Interest Over Time
interest_over_time_df = pytrend.interest_over_time()
print(interest_over_time_df.head())
# Get Google Hot Trends data
trending_searches_df = pytrend.trending_searches()
print(trending_searches_df.head())
# Get Google Hot Trends data
today_searches_df = pytrend.today_searches()
print(today_searches_df.head())<jupyter_output><empty_output><jupyter_text>Top charts<jupyter_code># Get Google Top Charts
m = pytrend.top_charts(2020, hl='en-US', tz=360, geo='GLOBAL')
top_charts_global
top_charts_de = pytrend.top_charts(2020, hl='en-US', tz=360, geo='DE')
top_charts_gb = pytrend.top_charts(2020, geo='GB')
top_charts_dk = pytrend.top_charts(2020, geo='DK')
top_charts_dk<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16643454038997216, 'fraction_numerical': 0.013649025069637883, 'mean_word_length': 3.1350417506478547, 'pattern_counts': {'":': 4, '<': 39, '<?xml version=': 0, '>': 82, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '136288', 'license': 'MIT', 'n_tokens_mistral': 4865, 'n_tokens_neox': 4534, 'n_words': 1061, 'path': 'notebooks/0_source_exploration_pytrends.ipynb', 'repo_name': 'philippschmalen/ESG-trending-topics-radar', 'size': '14370'}