<jupyter_start><jupyter_text>pandas ~~vs~~and SQL pandas != SQLSQL most often is OLTP with concurrency and scalepandas is data analytics, easy and fast * can't compare and doesn't compete* move data from/to https://github.com/Nozdi/first-steps-with-pandas-workshop<jupyter_code># pip install pandas, jupyter, ipython-sql, sqlalchemy, psycopg2

%load_ext sql

# createuser -s -P pandas
# createdb -O pandas pandas
%%sql postgresql://pandas:pandas@localhost/pandas
        select version();
import numpy as np
import pandas as pd
print('pandas: ' + pd.__version__)
strengths = pd.Series([400, 200, 300, 400, 500])
strengths
%sql SELECT unnest(array[400, 200, 300, 400, 500]) AS strength
names = pd.Series(["Batman", "Robin", "Spiderman", "Robocop", "Terminator"])
result = %sql SELECT a.name FROM (VALUES ('Batman'), ('Robin'), ('Spiderman'), ('Robocop'), ('Terminator')) AS a(name)
heroes = pd.DataFrame({
    'hero': names,
    'strength': strengths
})
heroes
%%sql
CREATE TABLE IF NOT EXISTS heroes (name text, strength int);
TRUNCATE heroes;
INSERT INTO heroes VALUES ('Batman', 400), ('Robin', 200), ('Spiderman', 300), ('Robocop', 400), ('Terminator', 500)
  RETURNING *;
another_heroes = pd.DataFrame([
    pd.Series(["Wonder Woman", 10, 3], index=["hero", "strength", "cookies"]),
    pd.Series(["Xena", 20, 0], index=["hero", "strength", "cookies"])
])
result = %sql CREATE TABLE IF NOT EXISTS another_heroes AS SELECT *, (random() * 10)::int AS cookies FROM heroes LIMIT 2;
pd.read_json('data/cached_python.json')
%%sql
CREATE TABLE IF NOT EXISTS cached_python (doc json);
COPY cached_python FROM '/var/lib/postgres/tmp/cached_python.json';

CREATE TABLE IF NOT EXISTS daily_stats AS
  SELECT to_timestamp(v.key::bigint / 1000) AS ts,
    v.value as daily_views,
    m.value as month,
    p.value as project,
    r.value as rank,
    t.value as title
    FROM cached_python
    CROSS JOIN LATERAL json_each(doc->'daily_views') v
    JOIN LATERAL json_each(doc->'month') m ON m.key = v.key
    JOIN LATERAL json_each(doc->'project') p ON p.key = v.key
    JOIN LATERAL json_each(doc->'rank') r ON r.key = v.key
    JOIN LATERAL json_each(doc->'title') t ON t.key = v.key;
SELECT * FROM daily_stats;
movies = pd.read_csv('data/movies.csv')
movies.head()
%%sql
CREATE TABLE IF NOT EXISTS movies (
    movie_title varchar,
    title_year numeric(4,0),
    budget numeric(14,2),
    gross numeric(14,2),
    genres varchar,
    language varchar,
    country varchar,
    movie_facebook_likes int,
    imdb_score numeric(2,1),
    num_voted_users int,
    num_critic_for_reviews numeric(6,0),
    num_user_for_reviews numeric(6,0),
    director_name varchar,
    director_facebook_likes numeric(9,0),
    actor_1_name varchar,
    actor_1_facebook_likes numeric(9,0),
    actor_2_name varchar,
    actor_2_facebook_likes numeric(9,0),
    actor_3_name varchar,
    actor_3_facebook_likes numeric(9,0),
    cast_total_facebook_likes numeric(9,0),
    color varchar,
    duration numeric(4,0),
    plot_keywords varchar,
    content_rating varchar,
    aspect_ratio numeric (4,2),
    facenumber_in_poster numeric(2,0),
    movie_imdb_link varchar
);

COPY movies FROM '/var/lib/postgres/tmp/movies.csv' csv header;
heroes.to_json(orient='records')
%sql SELECT array_to_json(array_agg(row_to_json(a))) FROM (SELECT * FROM heroes LIMIT 5) a
heroes.to_csv('data/heroes.csv', index=False)
%sql COPY heroes TO '/var/lib/postgres/tmp/heroes.csv' csv header;
heroes[heroes['strength'] > 400]
%sql SELECT * FROM heroes WHERE strength > 400
try:
    heroes[200 < heroes['strength'] < 400]
except ValueError:
    print("This cool Python syntax ain't work :(")
    
heroes[
    (heroes['strength'] > 200) & 
    (heroes['strength'] < 400)
]
%sql SELECT * FROM heroes WHERE strength BETWEEN 201 AND 399
heroes[
    heroes['hero'].isin(['Batman', 'Robin'])
].sort_values('strength', ascending=False)
%sql SELECT * FROM heroes WHERE name IN ('Batman', 'Robin') ORDER BY strength DESC
heroes
heroes.T
tuples = list(zip(*[['bar', 'bar', 'baz', 'baz',
                      'foo', 'foo', 'qux', 'qux'],
                     ['one', 'two', 'one', 'two',
                      'one', 'two', 'one', 'two']]))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=['A', 'B'])
df
df.stack()
heroes['health'] = 100
heroes['height'] = [180, 170, 175, 190, 185]
heroes['is_hungry'] = pd.Series([True, False, False, True, True])
heroes.head()
%%sql
ALTER TABLE heroes ADD COLUMN health int;
ALTER TABLE heroes ADD COLUMN height int;
UPDATE heroes SET health = 100;
UPDATE heroes SET height = h.column2
FROM (VALUES ('Batman', 180), ('Robin', 170), ('Spiderman', 175), ('Robocop', 190), ('Terminator', 185)) h
WHERE name = h.column1 RETURNING heroes.*;
heroes['strength'] / heroes['height']
heroes['strength_per_cm'] = heroes['strength'] / heroes['height']
%%sql
ALTER TABLE heroes ADD COLUMN strength_per_cm double precision;
UPDATE heroes SET strength_per_cm = strength::double precision / height
  RETURNING *;
heroes['code'] = heroes['hero'].map(lambda name: name[:2])
heroes['effective_strength'] = heroes.apply(
    lambda row: (not row['is_hungry']) * row['strength'],
    axis=1
)
heroes[['health', 'strength']] = heroes[['health', 'strength']].applymap(
    lambda x: x + 100
)
heroes
movies['first_genre'] = movies['genres'].str.split('|').str[0]
movies[['first_genre', 'genres']].head()
%sql SELECT (string_to_array(genres, '|'))[1], genres FROM movies LIMIT 5
movies.groupby('title_year')['gross'].max().tail(10)
%%sql
SELECT * FROM (
  SELECT title_year, max(gross)
  FROM movies
  GROUP BY title_year
) a
WHERE title_year IS NOT NULL
ORDER BY title_year DESC
LIMIT 10
spiderman_meals = pd.DataFrame([
        dict(time='2016-10-15 10:00', calories=300),
        dict(time='2016-10-15 13:00', calories=900),
        dict(time='2016-10-15 15:00', calories=1200),
        dict(time='2016-10-15 21:00', calories=700),
        dict(time='2016-10-16 07:00', calories=1600),
        dict(time='2016-10-16 13:00', calories=600),
        dict(time='2016-10-16 16:00', calories=900),
        dict(time='2016-10-16 20:00', calories=500),
        dict(time='2016-10-16 21:00', calories=300),
        dict(time='2016-10-17 08:00', calories=900),
    ])
spiderman_meals['time'] = pd.to_datetime(spiderman_meals['time'])
spiderman_meals = spiderman_meals.set_index('time')
spiderman_meals
%%sql
CREATE TABLE IF NOT EXISTS spiderman_meals AS
  SELECT * FROM (
  VALUES
    ('2016-10-15 10:00'::timestamp, 300),
    ('2016-10-15 13:00'::timestamp, 900),
    ('2016-10-15 15:00'::timestamp, 1200),
    ('2016-10-15 21:00'::timestamp, 700),
    ('2016-10-16 07:00'::timestamp, 1600),
    ('2016-10-16 13:00'::timestamp, 600),
    ('2016-10-16 16:00'::timestamp, 900),
    ('2016-10-16 20:00'::timestamp, 500),
    ('2016-10-16 21:00'::timestamp, 300),
    ('2016-10-17 08:00'::timestamp, 900)
  ) AS t (ts, calories);
SELECT * FROM spiderman_meals;
spiderman_meals["2016-10-15"]
%sql SELECT * FROM spiderman_meals WHERE date_trunc('day', ts) = '2016-10-15'
spiderman_meals.resample('1D').sum()
%sql SELECT date_trunc('day', ts), sum(calories) FROM spiderman_meals GROUP BY 1 ORDER BY 1
spiderman_meals.resample('1H').mean().head()
%%sql
SELECT t, m.calories
FROM (SELECT min(ts), max(ts) FROM spiderman_meals) s
CROSS JOIN LATERAL generate_series(s.min, s.max, '1 hour'::interval) t
LEFT JOIN spiderman_meals m ON m.ts = t
LIMIT 5

spiderman_meals.resample('1H').ffill().head(10)
%%sql
SELECT t, first_value(calories) OVER (PARTITION BY value_partition ORDER BY t)
FROM (
  SELECT t, m.calories, sum(m.calories) OVER (ORDER BY t) AS value_partition
  FROM (SELECT min(ts), max(ts) FROM spiderman_meals) s
  CROSS JOIN LATERAL generate_series(s.min, s.max, '1 hour'::interval) t
  LEFT JOIN spiderman_meals m ON m.ts = t
) a
LIMIT 10
spiderman_meals.resample('1D').first()
%%sql
SELECT DISTINCT
  date_trunc('day', m.ts),
  first_value(m.calories) OVER (PARTITION BY date_trunc('day', m.ts) ORDER BY m.ts)
FROM spiderman_meals m
ORDER BY 1
movies.describe()
%sql SELECT * FROM describe('movies')
%%sql
CREATE TYPE attr_stats AS (
  attname name,
  count int,
  mean double precision,
  std double precision,
  min double precision,
  p025 double precision,
  p050 double precision,
  p075 double precision,
  max double precision
);
%%sql
CREATE FUNCTION describe(_table text) RETURNS SETOF attr_stats AS $$
DECLARE
  _query text;
  _result attr_stats;
BEGIN
  SELECT INTO _query 'SELECT (b.a).* FROM (SELECT UNNEST(ARRAY[' || array_to_string(array_agg(format('
      (''%s'',
      COUNT(%s),
      AVG(%s::double precision),
      STDDEV_SAMP(%s::double precision),
      MIN(%s::double precision),
      (PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY %s))::double precision,
      (PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY %s))::double precision,
      (PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY %s))::double precision,
      MAX(%s::double precision))::attr_stats',
    a.attname, a.attname, a.attname, a.attname, a.attname, a.attname, a.attname, a.attname, a.attname)), ', ') || ']) a FROM ' || _table || ') b'
  FROM pg_attribute a
  JOIN pg_type t ON t.oid = a.atttypid
  WHERE a.attrelid = _table::regclass AND t.typcategory = 'N' AND t.typname != 'oid';

  RAISE NOTICE 'Executing following query: %', _query;

  FOR _result IN EXECUTE _query LOOP
    RETURN NEXT _result;
  END LOOP;

  RETURN;
END;
$$ LANGUAGE PLPGSQL STRICT;
%sql SELECT * FROM describe('movies')<jupyter_output>16 rows affected.
{'fraction_non_alphanumeric': 0.15231718799037555, 'fraction_numerical': 0.05502667643058897, 'mean_word_length': 4.469107551487414, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 12, 'https://': 1, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '593907', 'license': 'MIT', 'n_tokens_mistral': 3879, 'n_tokens_neox': 3372, 'n_words': 912, 'path': 'Pandas and SQL.ipynb', 'repo_name': 'nineinchnick/pystok-pandas', 'size': '9559'}