Taming Pandas

import pandas as pd
import sqlite3

con = sqlite3.connect("AdventureWorksLT.db")
products = pd.read_sql_query("select * from Product", con)
product_categories = pd.read_sql_query("select * from ProductCategory", con)

CREATE TABLE [Product](
...
    [Weight] INTEGER NULL,
...
)

products['Weight'] = pd.to_numeric(products['Weight']).fillna(0)

SELECT *
FROM   Product

products

SELECT ProductID,
       Name,
       ProductNumber
FROM   Product
LIMIT  5

products[['ProductID', 'Name', 'ProductNumber']] \
    .head(5)

SELECT Name,
       ProductNumber,
       StandardCost
FROM   Product
WHERE  StandardCost > 2000
LIMIT  5  

products[['Name', 'ProductNumber', 'StandardCost']] \
   [products.StandardCost > 2000] \
   .head(5)

SELECT NAME,
       ProductNumber,
       StandardCost
FROM   Product
WHERE  StandardCost > 10
       AND StandardCost < 12

products[['Name', 'ProductNumber', 'StandardCost']] \
 [(products.StandardCost > 10) & (products.StandardCost < 12)]

products[['Name', 'ProductNumber', 'StandardCost']] \
 .query('StandardCost > 10 & StandardCost < 12')

SELECT *
FROM   Product
WHERE  Color IN ( 'Red', 'Black', 'Silver' )
       AND StandardCost > 8
       AND StandardCost < 14
LIMIT  5  

colors = ['Red', 'Black', 'Silver']
products[['Name', 'ProductNumber', 'Color', 'StandardCost']] \
    [(products.Color.isin(colors)) \
     & (products.StandardCost > 8) \
     & (products.StandardCost < 14)] 

colors = ['Red', 'Black', 'Silver']
products[['Name', 'ProductNumber', 'Color', 'StandardCost']] \
    .query('Color in @colors and StandardCost > 8 and StandardCost < 14')

SELECT Name,
       ProductNumber,
       Color,
       StandardCost
FROM   Product
WHERE  Color NOT IN ( 'Red', 'Black', 'Blue', 'White',
                      'Silver', 'Yellow' )
LIMIT  5  

colors=['Red', 'Black', 'Blue', 'White', 'Silver', 'Yellow']
products[['Name', 'ProductNumber', 'Color', 'StandardCost']] \
    [(~products.Color.isin(colors))] \
    .head(5)

colors=['Red', 'Black', 'Blue', 'White', 'Silver', 'Yellow']
products[['Name', 'ProductNumber', 'Color', 'StandardCost']] \
    .query('Color not in @colors') \
    .head(5)

SELECT DISTINCT Color
FROM   Product
LIMIT  5  

pd.DataFrame(products['Color'].unique(), columns=['Color']) \
    .head(5)

SELECT Name,
       ProductNumber,
       Color,
       Weight
FROM   Product
WHERE  Color = 'Red'
ORDER  BY Weight
LIMIT  5  

products[['Name', 'ProductNumber', 'Color', 'Weight']] \
    [(products.Color == 'Yellow') & (products.StandardCost > 1000)] \
    .sort_values('Weight', ascending = False) \
    .head(5)

SELECT Count(NAME)
FROM   Product  

products[['Name']] \
    .aggregate(['count']) \
    .T

SELECT Min(StandardCost),
       Max(StandardCost),
       Avg(StandardCost)
FROM   Product  

products[['StandardCost']] \
    .aggregate(['min', 'max', 'average']) \
    .T

SELECT Count(NAME),
       Sum(StandardCost),
       Max(ListPrice)
FROM   Product  

products \
    .agg({'Name':'count', 'StandardCost':'sum', 'ListPrice':'max'})

SELECT Color,
       Count(Color)
FROM   Product
WHERE  Color <> ''
GROUP  BY Color
ORDER  BY Count(*) DESC
LIMIT  5  

products \
    [products.Color != ''] \
    .groupby(['Color']) \
    .size().reset_index(name='Count') \
    .sort_values('Count', ascending=False) \
    .head(5)

SELECT color,
       Count(color)
FROM   Product
WHERE  color <> ''
GROUP  BY color
HAVING Count(color) > 30  

products \
    [products.Color!=''] \
    .groupby(['Color']) \
    .filter(lambda g: len(g) > 30) \
    .groupby(['Color']) \
    .size().reset_index(name='Count')

SELECT Name,
       ProductNumber,
       ListPrice
FROM   Product
ORDER  BY ListPrice DESC
LIMIT  3  

products[['Name', 'ProductNumber', 'ListPrice']] \
    .nlargest(3, 'ListPrice')

SELECT Name,
       ProductNumber,
       ListPrice
FROM   Product
ORDER  BY ListPrice DESC
LIMIT  3 offset 3  

products[['Name', 'ProductNumber', 'ListPrice']] \
    .nlargest(3+3, 'ListPrice').tail(3)

SELECT p.Name,
       p.Color,
       pc.Name
FROM   Product p
       JOIN ProductCategory pc
         ON p.ProductCategoryID = pc.ProductCategoryID
WHERE  p.Color <> ''
LIMIT  5  

products \
    .merge(product_categories, on='ProductCategoryID', suffixes=['', '_Category']) \
    [products.Color!=''] \
    [['Name', 'Color', 'Name_Category']] \
 .head(5)

-- bike categories
SELECT ProductCategoryID,
       NAME
FROM   ProductCategory
WHERE  NAME LIKE '% Bikes'
UNION ALL
-- bike parts categories
SELECT ProductCategoryID,
       NAME
FROM   ProductCategory
WHERE  NAME LIKE 'Bike %'  

categories = product_categories
bike_categories = categories[categories.Name.str.endswith(' Bikes')]
bike_parts_categories = categories[categories.Name.str.startswith('Bike ')]

pd.concat([bike_categories, bike_parts_categories])[['ProductCategoryID', 'Name']]

SELECT CategoryName,
       ProductName,
       ListPrice
FROM   (
       SELECT pc.NAME AS CategoryName ,
              p.NAME  AS ProductName ,
              p.ListPrice ,
              Row_number() OVER 
                  (PARTITION BY pc.Name ORDER BY p.ListPrice DESC) AS rn
       FROM   Product p
       JOIN   ProductPategory pc
       ON     p.ProductCategoryID = pc.ProductCategoryID )
WHERE  rn=1
ORDER BY CategoryName 
LIMIT  5 

products_with_categories = products \
    .merge(product_categories, on='ProductCategoryID', suffixes=['', '_Category'])

products_with_categories['rn'] = products_with_categories \
    .sort_values(['Name_Category', 'ListPrice'], ascending=[True, False]) \ 
    .groupby(['Name_Category']) \
    .cumcount() + 1  # count starts from 0 in python, to simulate SQL we add 1

products_with_categories[['Name_Category','Name', 'ListPrice']] \
    [products_with_categories.rn == 1] \
    .sort_values('Name_Category') \
    .head(5)

for i, v in df.iterrows():
    print(row)

for row in df.itertuples(index=False):
    print(row)

INSERT INTO ProductCategory
            (NAME,
             rowguid)
VALUES     ('Category X',
            '12d8351e-9d34-4797-957a-99a3acdf4d31')  

max_pid = product_categories.agg({'ProductCategoryID':'max'})
product_categories.loc[product_categories.index.size] = \ 
    [max_pid, None, 'Category X', '12d8351e-9d34-4797-957a-99a3acdf4d31', None]

INSERT INTO Products
SELECT *
FROM   tmp_Products  

products = products.append(tmp_products)

UPDATE ProductCategory
SET    NAME = 'New Bikes'
WHERE  NAME = 'Bikes'  

product_categories\
    .loc[product_categories.Name=='Bikes', 'Name'] = 'New Bikes'

UPDATE Product
SET    StandardCost = StandardCost * 1.10  

products['StandardCost'] = products['StandardCost']*1.10

DELETE FROM Product
WHERE  Color = 'Grey'  

products = products[products.Color!='Gray']

products.drop(products.Color=='Gray', inplace=True)

ALTER TABLE Product
  ADD COLUMN [Profit] INTEGER;

UPDATE Product
SET    Profit = ListPrice - StandardCost;  

products['Profit'] = products['ListPrice'] - products['StandardCost']

ALTER TABLE Product
  DROP COLUMN [Profit]  

products.drop('Profit', axis='columns')