<jupyter_start><jupyter_text>This notebook uses the plug-in `ipython-sql`<jupyter_code>import sqlite3
print('sqlite3: %s' % sqlite3.sqlite_version)
import pandas as pd
print('pandas: %s' % pd.__version__)
def get_df(table_name):
    """This function returns the table name selected and returns it as a pandas dataframe"""
    table_con = sqlite3.connect("chinook.db")
    table_df = pd.read_sql_query(f"SELECT * FROM {table_name}", table_con)
    table_con.close()
    del(table_con)
    return table_df
# Load the sql magic plug-in
%load_ext sql
# Auto connect to our database file
%sql sqlite:///chinook.db<jupyter_output><empty_output><jupyter_text>Select Data<jupyter_code>%%sql
SELECT * FROM customers
LIMIT 5
get_df("customers").head(5)<jupyter_output><empty_output><jupyter_text>Limit Data<jupyter_code>%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
LIMIT 10
get_df("tracks")[["Name", "Composer", "UnitPrice"]].head(10)<jupyter_output><empty_output><jupyter_text>Special Data<jupyter_code>%%sql
SELECT DISTINCT FirstName
FROM employees
get_df("employees")["FirstName"].unique()<jupyter_output><empty_output><jupyter_text>Counting Data<jupyter_code>%%sql
SELECT COUNT(*) FROM tracks
get_df("tracks").shape[0]
%%sql
SELECT COUNT(FirstName) FROM customers
get_df("customers")["FirstName"].count()
%%sql
SELECT COUNT(DISTINCT FirstName) FROM employees
get_df("employees")["FirstName"].unique().shape[0]<jupyter_output><empty_output><jupyter_text>Filter Data<jupyter_code>%%sql
SELECT * FROM tracks
WHERE UnitPrice > 1.0
LIMIT 10
df = get_df("tracks")
df = df[df["UnitPrice"] > 1.0].head(10)
df
%%sql
SELECT COUNT(*) FROM tracks
WHERE UnitPrice > 1.0 AND UnitPrice < 10
df = get_df("tracks")
df = df[(df["UnitPrice"] > 1.0) & (df["UnitPrice"] < 10)]
df.shape[0]
%%sql
SELECT BillingAddress, BillingCity, Total
FROM invoices
WHERE BillingCity = 'Paris' OR BillingCity = 'Berlin'
LIMIT 5
df = get_df("invoices")
df = df[(df["BillingCity"] == "Paris") | (df["BillingCity"] == "Berlin")]
df = df[["BillingAddress", "BillingCity", "Total"]].head(5)
df
%%sql
SELECT BillingAddress, BillingCity, Total
FROM invoices
WHERE BillingCity != 'Paris' AND BillingCity <> 'Berlin'
--# the != operator is the same as the <> operator
LIMIT 5
df = get_df("invoices")
df = df[(df["BillingCity"] != "Paris") | (df["BillingCity"] != "Berlin")]
df = df[["BillingAddress", "BillingCity", "Total"]].head(5)
df<jupyter_output><empty_output><jupyter_text>Data From a List<jupyter_code>%%sql
SELECT *
FROM invoices
WHERE BillingCity IN ('Berlin', 'Paris', 'New York', 'Boston', 'London')
LIMIT 5
df = get_df("invoices")
df = df[df['BillingCity'].isin(['Berlin', 'Paris', 'New York', 'Boston', 'London'])]
df = df.head(5)
df
%%sql
SELECT *
FROM invoices
WHERE BillingCity NOT IN ('Berlin', 'Paris', 'New York', 'Boston', 'London')
LIMIT 5
df = get_df("invoices")
df = df[~df['BillingCity'].isin(['Berlin', 'Paris', 'New York', 'Boston', 'London'])]
df = df.head(5)
df<jupyter_output><empty_output><jupyter_text>Between Data<jupyter_code>%%sql
SELECT BillingCity, Total
FROM invoices
WHERE Total BETWEEN 5 AND 15
LIMIT 5
df = get_df("invoices")
df = df[df["Total"].between(5, 15)]
df = df[["BillingCity", "Total"]].head(5)
df<jupyter_output><empty_output><jupyter_text>Null Data<jupyter_code>%%sql
SELECT COUNT(*) FROM
invoices
WHERE BillingState IS NULL
get_df("invoices")['BillingState'].isnull().sum()
%%sql
SELECT InvoiceDate, BillingCountry
FROM invoices
WHERE Total IS NOT NULL
LIMIT 10
df = get_df("invoices")
df = df[~df["Total"].isnull()]
df = df[["InvoiceDate", "BillingCountry"]].head(10)
df<jupyter_output><empty_output><jupyter_text>Matching Data<jupyter_code>%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Name LIKE 'B%'
LIMIT 5
df = get_df("tracks")
df = df[df['Name'].str.startswith('B')]
df = df[["Name", "Composer", "UnitPrice"]].head(5)
df
%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Name LIKE '%beautiful%'
df = get_df("tracks")
df = df[df['Name'].str.contains('beautiful', case=False)]
df = df[["Name", "Composer", "UnitPrice"]].head(5)
df
%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
WHERE (Name LIKE 'F%') AND (UnitPrice > 1.0)
df = get_df("tracks")

# These two ways are the same, but the first one is smaller to write
df = df[(df['Name'].str.contains('^F', case=False)) & (df['UnitPrice'] > 1.0)]
# df = df[(df['Name'].str.lower().str.startswith('f')) & (df['UnitPrice'] > 1.0)]

df = df[["Name", "Composer", "UnitPrice"]]
df<jupyter_output><empty_output><jupyter_text>Aggregate Data<jupyter_code>%%sql
SELECT SUM(Total), MAX(Total), MIN(Total), AVG(Total)
FROM invoices
get_df("invoices")["Total"].agg(['sum', 'max','min','mean'])
%%sql
SELECT AVG(Total), BillingCity
FROM invoices
WHERE BillingCity = 'Paris'
df = get_df("invoices")
df = pd.DataFrame({
    "avgTotal": [df[df['BillingCity'] == "Paris"]['Total'].mean()],
    "BillingCity": ["Paris"],
})
df
%%sql
SELECT SUM(Total) / COUNT(Total)
FROM invoices
df = get_df("invoices")
df = pd.DataFrame({
    "mean": [df["Total"].sum() / df["Total"].count()],
})
df
%%sql
SELECT Name, Milliseconds / 1000.0 / 60.0
FROM tracks
LIMIT 10
df = get_df("tracks")[["Name", "Milliseconds"]]
df["AvgDurr"] = df["Milliseconds"] / 1000.0 / 60.0
df = df[["Name","AvgDurr"]].head(10)
df
%%sql
SELECT 
    SUM(Total) as sum_total,
    MAX(Total) as max_total,
    MIN(Total) as min_total,
    SUM(Total) / COUNT(Total) as mean_total
FROM invoices
df = get_df("invoices")
df = pd.DataFrame({
    "sum_total": [df["Total"].sum()],
    "max_total": [df["Total"].max()],
    "min_total": [df["Total"].min()],
    "mean_total": [df["Total"].sum() / df["Total"].count()],
})
df<jupyter_output><empty_output><jupyter_text>Order Data<jupyter_code>%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Composer <> 'None'
ORDER BY Composer
LIMIT 10
df = get_df("tracks")
df = df[~df["Composer"].isnull()]
df = df.sort_values("Composer")
df = df[["Name", "Composer", "UnitPrice"]].head(10)
df
%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Composer <> 'None'
ORDER BY UnitPrice, Composer, Name
LIMIT 10
df = get_df("tracks")
df = df[~df["Composer"].isnull()]
df = df.sort_values(["UnitPrice", "Composer", "Name"])
df = df[["Name", "Composer", "UnitPrice"]].head(10)
df
%%sql
SELECT Name, Composer, UnitPrice
FROM tracks
WHERE Composer != 'None'
ORDER BY UnitPrice DESC, Composer DESC, Name DESC
LIMIT 10
df = get_df("tracks")
df = df[~df["Composer"].isnull()]
df = df.sort_values(["UnitPrice", "Composer", "Name"], ascending=False)
df = df[["Name", "Composer", "UnitPrice"]].head(10)
df
%%sql
SELECT GenreID, COUNT(*) as genre_count
FROM tracks
GROUP BY GenreId
LIMIT 10
get_df("tracks").groupby('GenreId')['GenreId'].count().head(10)<jupyter_output><empty_output><jupyter_text>Put it all together<jupyter_code>%%sql
SELECT 
    GenreId,
    AlbumId,
    COUNT(*) as genre_count,
    AVG(Milliseconds) / 1000.0 / 60.0 as avg_duration,
    AVG(UnitPrice) as avg_price
FROM tracks
GROUP BY GenreId, AlbumId
LIMIT 10
df_group = get_df("tracks").groupby(['GenreId', 'AlbumId'])
df = pd.concat(
    [
        df_group['AlbumId'].count(),
        df_group['Milliseconds'].mean() / 1000.0 / 60.0,
        df_group['UnitPrice'].mean(),
    ],
    axis=1,
    join="inner",
)
df = df.head(10)
df
%%sql
SELECT GenreId
FROM tracks
GROUP BY GenreId
HAVING COUNT(GenreId) > 100
get_df("tracks").groupby('GenreId').filter(lambda group: len(group) > 100)['GenreId'].unique()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18112347499664835, 'fraction_numerical': 0.01622201367475533, 'mean_word_length': 6.378832838773492, 'pattern_counts': {'":': 7, '<': 59, '<?xml version=': 0, '>': 66, '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': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '486377', 'license': 'MIT', 'n_tokens_mistral': 2925, 'n_tokens_neox': 2760, 'n_words': 651, 'path': 'Pandas2Sql.ipynb', 'repo_name': 'uno-sebastian/Pandas2Sql', 'size': '7459'}