<jupyter_start><jupyter_text>Act Report<jupyter_code>import sqlite3
from sqlite3 import Error
import pandas as pd

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by db_file
    :param db_file: database file
    :return: Connection object or None
    """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return conn
database = "../data/master.db"
conn = create_connection(database)<jupyter_output><empty_output><jupyter_text>Distribution of Dog Breeds<jupyter_code>sqlite_select_query = """SELECT prediction AS dog_breed, COUNT(prediction) AS prediction_count 
                         from df_twt_archive_master WHERE breed_predicted == True 
                         GROUP BY prediction ORDER BY prediction_count DESC LIMIT 15"""

df = pd.read_sql_query(sqlite_select_query, conn)
df = df.set_index('dog_breed')
df.plot(kind = 'pie', y='prediction_count', title='Distribution of Dog Breeds',figsize=(9,9))
df.plot(kind = 'bar', y='prediction_count', title='Distribution of Dog Breeds', ylabel='prediction_count',figsize=(9,9))
df<jupyter_output><empty_output><jupyter_text>There is a peak in number for golden retrievers. This number progressively goes down across the top 15 breeds.  Top dog breeds by average favourite count<jupyter_code>sqlite_select_query = """SELECT prediction AS dog_breed, avg(favorite_count) AS avg_favorite_count 
                         from df_twt_archive_master WHERE breed_predicted == True 
                         GROUP BY prediction ORDER BY avg_favorite_count DESC LIMIT 15"""

df = pd.read_sql_query(sqlite_select_query, conn)
df = df.set_index('dog_breed')
df.plot(kind = 'pie', y='avg_favorite_count', title='Top dog breeds by average favorite count', figsize=(9,9))
df.plot(kind = 'bar', y='avg_favorite_count', title='Top dog breeds by average favorite count', ylabel='avg_favorite_count',figsize=(9,9))
df<jupyter_output><empty_output><jupyter_text>There is a peak for the Saluki breed. The distribution looks even across most of the top 15 breeds from African Hound onwards. None of the most common dog breeds are in the top 15 for average favorite count.  Top dog breeds by average retweet count<jupyter_code>sqlite_select_query = """SELECT prediction AS dog_breed, avg(retweet_count) AS avg_retweet_count 
                         from df_twt_archive_master WHERE breed_predicted == True 
                         GROUP BY prediction ORDER BY avg_retweet_count DESC LIMIT 15"""

df = pd.read_sql_query(sqlite_select_query, conn)
df = df.set_index('dog_breed')
df.plot(kind = 'pie', y='avg_retweet_count', title='Top dog breeds by average retweet count', figsize=(9,9))
df.plot(kind = 'bar', y='avg_retweet_count', title='Top dog breeds by average retweet count', ylabel='avg_retweet_count',figsize=(9,9))
df<jupyter_output><empty_output><jupyter_text>Great Pyrenees is the only breed that appears in the top 15 average retweet count but not in the top 15 average favorite count.  Top dog breeds by average rating<jupyter_code>sqlite_select_query = """SELECT prediction AS dog_breed, avg(rating_numerator) AS avg_rating_numerator, avg(favorite_count) AS avg_favorite_count, avg(retweet_count) AS avg_retweet_count
                         from df_twt_archive_master WHERE breed_predicted == True 
                         GROUP BY prediction ORDER BY avg_rating_numerator DESC LIMIT 15"""

df = pd.read_sql_query(sqlite_select_query, conn)
df = df.set_index('dog_breed')
df.plot(kind = 'pie', y='avg_rating_numerator', title='Top dog breeds by average rating', figsize=(9,9))
df.plot(kind = 'bar', y='avg_rating_numerator', title='Top dog breeds by average rating', ylabel='avg_rating_numerator',figsize=(9,9))
df<jupyter_output><empty_output><jupyter_text>Total retweets and favourites over time<jupyter_code>import pandas as pd
sqlite_select_query = """SELECT date, SUM(retweet_count) AS sum_retweet_count, SUM(favorite_count) AS sum_favorite_count
                         from df_twt_archive_master WHERE breed_predicted == True 
                         GROUP BY date ORDER BY date ASC"""

df = pd.read_sql_query(sqlite_select_query, conn)
df = df.set_index('date')
df.plot(kind = 'line', y=['sum_retweet_count', 'sum_favorite_count'], title='Total retweets and favourites over time', ylabel='count',figsize=(15,5))
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09207564160288159, 'fraction_numerical': 0.009004952723998198, 'mean_word_length': 4.624050632911392, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '311024', 'license': 'MIT', 'n_tokens_mistral': 1441, 'n_tokens_neox': 1360, 'n_words': 392, 'path': 'notebooks/act_report.ipynb', 'repo_name': 'stephen-nic/twitter-wrangler', 'size': '4442'}