미루는 것이
아끼는 것이다

!pip install polars

import polars as pl

df = pl.DataFrame(
    {
        'Model': ['Camry','Corolla','RAV4',
                  'Mustang','F-150','Escape',
                  'Golf','Tiguan'],
        'Year': [1982,1966,1994,1964,1975,2000,…
        'Engine_Min':[2.5,1.8,2.0,2.3,2.7,1.5,1…
        'Engine_Max':[3.5,2.0,2.5,5.0,5.0,2.5,2…
        'AWD':[False,False,True,False,True,True…
        'Company': ['Toyota','Toyota','Toyota',…
                    'Ford','Ford','Volkswagen',…
    }
)
df

df.rows()
[('Camry', 1982, 2.5, 3.5, False, 'Toyota'),
 ('Corolla', 1966, 1.8, 2.0, False, 'Toyota'),
 ('RAV4', 1994, 2.0, 2.5, True, 'Toyota'),
 ('Mustang', 1964, 2.3, 5.0, False, 'Ford'),
 ('F-150', 1975, 2.7, 5.0, True, 'Ford'),
 ('Escape', 2000, 1.5, 2.5, True, 'Ford'),
 ('Golf', 1974, 1.0, 2.0, True, 'Volkswagen'),
 ('Tiguan', 2007, 1.4, 2.0, True, 'Volkswagen')]

# 각 열의 데이터 유형을 온전한 이름으로 본다
df.dtypes
[String, Int64, Float64, Float64, Boolean, String]

# 열 이름을 얻는다
df.columns
# ['Model', 'Year', 'Engine_Min', 'Engine_Max', 'AWD', 'Company']

df.select(
    'Model'
)

df.select(
    ['Model','Company']        # 또는 'Model','Company'
)

df.select(
    pl.col(pl.String)
)

df.select(
    pl.col(['Year','Model','Engine_Max'])
      .sort_by(['Engine_Max','Year'],descending =…
)

df.select(
    [pl.col(pl.String), 'Year']
)

df.row(0)
# ('Camry', 1982, 2.5, 3.5, False, 'Toyota')

df[1:3]     # 둘째와 셋째 행을 돌려준다

df.filter(
    pl.col('Company') == 'Toyota'
)

# ① Toyota 또는 Ford
df.filter(
    (pl.col('Company') == 'Toyota') |
    (pl.col('Company') == 'Ford')
)

# ② 여러 상표를 맞출 때는 is_in()이 더 간편하다
df.filter(
    (pl.col('Company').is_in(['Toyota','Ford']))
)

# ③ Toyota이면서 1980년 이후에 나온 것
df.filter(
    (pl.col('Company') == 'Toyota') &
    (pl.col('Year') > 1980)
)

# ④ Toyota가 아닌 것 — 부정 연산자
df.filter(
    ~(pl.col('Company') == 'Toyota')
)

# ⑤ != 연산자로도 같은 일을 한다
df.filter(
    (pl.col('Company') != 'Toyota')
)

df.filter(
    pl.col('Company') == 'Toyota'
).select(
    'Model'
)

# 여러 열을 고르려면 열 이름을 목록으로 담는다
df.filter(
    pl.col('Company') == 'Toyota'
).select(
    ['Model','Year']
)

ctx = pl.SQLContext(cars = df)
ctx.execute("SELECT * FROM cars", eager=True)

ctx.execute('''
    SELECT Company,
           AVG(Engine_Min) AS avg_engine_min,
           AVG(Engine_Max) AS avg_engine_max
    FROM cars
    GROUP BY Company;
''', eager=True)

import pandas as pd

df = pd.read_csv('flights.csv')
df

df = pd.read_csv('flights.csv')
df = df[(df['MONTH'] == 5) &
        (df['ORIGIN_AIRPORT'] == 'SFO') &
        (df['DESTINATION_AIRPORT'] == 'SEA')]
df

import polars as pl

# 지연 — LazyFrame을 돌려준다
q = pl.scan_csv('flights.csv')
type(q)
# polars.lazyframe.frame.LazyFrame

# 즉시 — DataFrame을 돌려준다
df = pl.read_csv('flights.csv')
type(df)
# polars.dataframe.frame.DataFrame

q = pl.scan_csv('flights.csv')
q = q.select(['MONTH', 'ORIGIN_AIRPORT','DESTINATION_AIRPORT'])
q = q.filter(
    (pl.col('MONTH') == 5) &
    (pl.col('ORIGIN_AIRPORT') == 'SFO') &
    (pl.col('DESTINATION_AIRPORT') == 'SEA'))

q = (
    pl.scan_csv('flights.csv')
    .select(['MONTH', 'ORIGIN_AIRPORT','DESTINATION_AIRPORT'])
    .filter(
        (pl.col('MONTH') == 5) &
        (pl.col('ORIGIN_AIRPORT') == 'SFO') &
        (pl.col('DESTINATION_AIRPORT') == 'SEA'))
)

# 최적화된 계획. 먼저 CSV를 훑고(그래프 위쪽) 이어서 여과한다(아래쪽)
q.show_graph(optimized=True)

# 최적화되지 않은 계획. CSV를 훑어 31개 열을 모두 적재한 뒤 여과를 하나씩 수행한다
q.show_graph(optimized=False)

q.collect()

df = (
    pl.read_csv('flights.csv')
    .select(['MONTH', 'ORIGIN_AIRPORT','DESTINATION_AIRPORT'])
    .filter(
        (pl.col('MONTH') == 5) &
        (pl.col('ORIGIN_AIRPORT') == 'SFO') &
        (pl.col('DESTINATION_AIRPORT') == 'SEA')
    )
)
df

q = (
    pl.read_csv('flights.csv')
    .lazy()
    .select(['MONTH', 'ORIGIN_AIRPORT','DESTINATION_AIRPORT'])
    .filter(
        (pl.col('MONTH') == 5) &
        (pl.col('ORIGIN_AIRPORT') == 'SFO') &
        (pl.col('DESTINATION_AIRPORT') == 'SEA')
    )
)
df = q.collect()
display(df)

pip install pyarrow

import duckdb

result = duckdb.sql('''
    SELECT *
    FROM df
''')
result

result.pl()      # Polars DataFrame으로
result.df()      # pandas DataFrame으로

result.describe()          # 기초 통계

result.order('Year')        # 연도 오름차순
result.order('Year DESC')   # 연도 내림차순

result.apply('min', 'Year')  # Year 열의 최솟값

duckdb.sql('''
    SELECT Company, Model
    FROM df
    ORDER BY Company, Model
''').pl()

# SQL 쪽
duckdb.sql('''
    SELECT Company, count(Model) as count
    FROM df
    GROUP BY Company
''').pl()

# Polars 쪽
result.pl().select(
    pl.col('Company').value_counts()
).unnest('Company')

import duckdb

conn = duckdb.connect()

conn.execute('''
    CREATE TABLE customers
    (customer_id INTEGER PRIMARY KEY, name STRING)
''')

conn.execute('''
    CREATE TABLE products
    (product_id INTEGER PRIMARY KEY, product_name STRING)
''')

conn.execute('''
    CREATE TABLE sales
    (customer_id INTEGER, product_id INTEGER, qty INTEGER,
     PRIMARY KEY(customer_id,product_id))
''')

customers_relation = conn.table('customers')

# 세 행을 넣는다
customers_relation.insert([1, 'Alice'])
customers_relation.insert([2, 'Bob'])
customers_relation.insert([3, 'Charlie'])

products_relation = conn.table('products')
products_relation.insert([10, 'Paperclips'])
products_relation.insert([20, 'Staple'])
products_relation.insert([30, 'Notebook'])

sales_relation = conn.table("sales")
sales_relation.insert([1,20,1])
sales_relation.insert([1,10,2])
sales_relation.insert([2,30,7])
sales_relation.insert([3,10,3])
sales_relation.insert([3,20,2])

result = customers_relation.join(
    sales_relation,
    condition = "customer_id",
    how = "inner"
).join(
    products_relation,
    condition = "product_id",
    how = "inner"
)

# 관계형 API 쪽
result.filter('customer_id = 1')

# SQL 쪽 — result를 테이블처럼 참조한다
conn.execute('''
    SELECT *
    FROM result
    WHERE customer_id = 1
''').pl()

result.aggregate('customer_id, MAX(name) AS Name…
                 'SUM(qty) as "Total Qty"',
                 'customer_id')

SELECT customer_id as 'Customer ID', MAX(name) AS Name,
       sum(qty) as 'Total Qty'
FROM result
GROUP BY customer_id

result.project('name, qty, product_name')

result.limit(3)        # 처음 세 행
result.limit(3,2)      # 세 행, 위치 2(셋째 행)에서 시작