Code

Author

Tony Duan

Code

Sys.setenv(RETICULATE_PYTHON = "/Library/Frameworks/Python.framework/Versions/3.11/bin/python3.11")
library(reticulate)
use_python("/Library/Frameworks/Python.framework/Versions/3.11/bin/python3.11")

Using python 3.11 for siuba pacakege

Code

from platform import python_version
print(python_version())

3.11.9

Code

import os
os.system('pip show pandas')

Code

import os
os.system('pip show siuba')

Code

# set up duckdb database
library(DBI)
library(tidyverse)
library(RSQLite)
library(connections)
library(duckdb)

Code

# set up R data
rm(mtcars) 
mtcars=cbind(model_name = rownames(mtcars), mtcars) |> head(10)
iris=iris|> head(10)

Code

# set up Python data
mtcars=r.mtcars
iris=r.iris

Code

if (file.exists("my-db.duckdb")) {file.remove("my-db.duckdb")
  }
#con <- dbConnect(duckdb(), dbdir = "my-db.duckdb", read_only = TRUE)
con <- dbConnect(duckdb(), dbdir = "my-db.duckdb", read_only = FALSE)

dbWriteTable(con, "iris_table", iris,overwrite = TRUE)
dbWriteTable(con, "mtcars_table", mtcars,overwrite = TRUE)

There are table iris_table and mtcars_table in duckdb my-db.duckdb
There are table iris and mtcars R environment
There are table iris and mtcars Python environment

1 show all table

Code

SHOW ALL TABLES;

2 records
database	schema	name	column_names	column_types	temporary
my-db	main	iris_table	Sepal.Length, Sepal.Width , Petal.Length, Petal.Width , Species	DOUBLE , DOUBLE , DOUBLE , DOUBLE , ENUM(‘setosa’, ‘versicolor’, ‘virginica’)	FALSE
my-db	main	mtcars_table	model_name, mpg , cyl , disp , hp , drat , wt , qsec , vs , am , gear , carb	VARCHAR, DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE	FALSE

Code

dflist <- Filter(is.data.frame, as.list(.GlobalEnv))
names(dflist)

[1] "mtcars" "iris"

Code

import pandas as pd
alldfs = [var for var in dir() if isinstance(eval(var), pd.core.frame.DataFrame)]
print(alldfs)

['iris', 'mtcars']

2 Describe a table

Code

DESCRIBE mtcars_table;

Displaying records 1 - 10
column_name	column_type	null	key	default	extra
model_name	VARCHAR	YES	NA	NA	NA
mpg	DOUBLE	YES	NA	NA	NA
cyl	DOUBLE	YES	NA	NA	NA
disp	DOUBLE	YES	NA	NA	NA
hp	DOUBLE	YES	NA	NA	NA
drat	DOUBLE	YES	NA	NA	NA
wt	DOUBLE	YES	NA	NA	NA
qsec	DOUBLE	YES	NA	NA	NA
vs	DOUBLE	YES	NA	NA	NA
am	DOUBLE	YES	NA	NA	NA

Code

skimr::skim(mtcars)

Data summary
Name	mtcars
Number of rows	10
Number of columns	12
_______________________
Column type frequency:
character	1
numeric	11
________________________
Group variables	None

Variable type: character

skim_variable	n_missing	complete_rate	min	max	empty	n_unique	whitespace
model_name	0	1	7	17	0	10	0

Variable type: numeric

skim_variable	complete_rate	mean	sd	p0	p25	p50	p75	p100	hist
mpg	1	20.37	2.91	14.30	18.82	21.00	22.45	24.40	▂▂▅▇▇
cyl	1	5.80	1.48	4.00	4.50	6.00	6.00	8.00	▅▁▇▁▃
disp	1	208.61	90.37	108.00	150.02	163.80	249.75	360.00	▇▇▅▁▅
hp	1	122.80	51.45	62.00	97.50	110.00	119.75	245.00	▅▇▁▂▂
drat	1	3.54	0.44	2.76	3.16	3.77	3.90	3.92	▁▃▁▁▇
wt	1	3.13	0.41	2.32	2.94	3.20	3.44	3.57	▂▂▂▆▇
qsec	1	18.58	2.14	15.84	17.02	18.45	19.86	22.90	▇▃▃▂▂
vs	1	0.60	0.52	0.00	0.00	1.00	1.00	1.00	▅▁▁▁▇
am	1	0.30	0.48	0.00	0.00	0.00	0.75	1.00	▇▁▁▁▃
gear	1	3.60	0.52	3.00	3.00	4.00	4.00	4.00	▅▁▁▁▇
carb	1	2.50	1.35	1.00	1.25	2.00	4.00	4.00	▆▆▁▁▇

Code

mtcars.describe(include='all')

       model_name        mpg       cyl  ...         am       gear       carb
count          10  10.000000  10.00000  ...  10.000000  10.000000  10.000000
unique         10        NaN       NaN  ...        NaN        NaN        NaN
top     Mazda RX4        NaN       NaN  ...        NaN        NaN        NaN
freq            1        NaN       NaN  ...        NaN        NaN        NaN
mean          NaN  20.370000   5.80000  ...   0.300000   3.600000   2.500000
std           NaN   2.907099   1.47573  ...   0.483046   0.516398   1.354006
min           NaN  14.300000   4.00000  ...   0.000000   3.000000   1.000000
25%           NaN  18.825000   4.50000  ...   0.000000   3.000000   1.250000
50%           NaN  21.000000   6.00000  ...   0.000000   4.000000   2.000000
75%           NaN  22.450000   6.00000  ...   0.750000   4.000000   4.000000
max           NaN  24.400000   8.00000  ...   1.000000   4.000000   4.000000

[11 rows x 12 columns]

3 show column name

Code

PRAGMA table_info(mtcars_table);

Displaying records 1 - 10
cid	name	type	notnull	dflt_value	pk
0	model_name	VARCHAR	FALSE	NA	FALSE
1	mpg	DOUBLE	FALSE	NA	FALSE
2	cyl	DOUBLE	FALSE	NA	FALSE
3	disp	DOUBLE	FALSE	NA	FALSE
4	hp	DOUBLE	FALSE	NA	FALSE
5	drat	DOUBLE	FALSE	NA	FALSE
6	wt	DOUBLE	FALSE	NA	FALSE
7	qsec	DOUBLE	FALSE	NA	FALSE
8	vs	DOUBLE	FALSE	NA	FALSE
9	am	DOUBLE	FALSE	NA	FALSE

Code

names(mtcars)

 [1] "model_name" "mpg"        "cyl"        "disp"       "hp"        
 [6] "drat"       "wt"         "qsec"       "vs"         "am"        
[11] "gear"       "carb"

Code

import pandas as pd
list(mtcars.columns.values)

['model_name', 'mpg', 'cyl', 'disp', 'hp', 'drat', 'wt', 'qsec', 'vs', 'am', 'gear', 'carb']

4 select

4.1 select top 6 and rename

Code

SELECT model_name as model,mpg,cyl from mtcars_table limit 6 ;

6 records
model	mpg	cyl
Mazda RX4	21.0	6
Mazda RX4 Wag	21.0	6
Datsun 710	22.8	4
Hornet 4 Drive	21.4	6
Hornet Sportabout	18.7	8
Valiant	18.1	6

Code

head(mtcars,6) |> select(model=model_name,mpg,cyl)

                              model  mpg cyl
Mazda RX4                 Mazda RX4 21.0   6
Mazda RX4 Wag         Mazda RX4 Wag 21.0   6
Datsun 710               Datsun 710 22.8   4
Hornet 4 Drive       Hornet 4 Drive 21.4   6
Hornet Sportabout Hornet Sportabout 18.7   8
Valiant                     Valiant 18.1   6

Code

mtcars.rename(columns={'model_name': 'model'})

                               model   mpg  cyl   disp  ...   vs   am  gear  carb
Mazda RX4                  Mazda RX4  21.0  6.0  160.0  ...  0.0  1.0   4.0   4.0
Mazda RX4 Wag          Mazda RX4 Wag  21.0  6.0  160.0  ...  0.0  1.0   4.0   4.0
Datsun 710                Datsun 710  22.8  4.0  108.0  ...  1.0  1.0   4.0   1.0
Hornet 4 Drive        Hornet 4 Drive  21.4  6.0  258.0  ...  1.0  0.0   3.0   1.0
Hornet Sportabout  Hornet Sportabout  18.7  8.0  360.0  ...  0.0  0.0   3.0   2.0
Valiant                      Valiant  18.1  6.0  225.0  ...  1.0  0.0   3.0   1.0
Duster 360                Duster 360  14.3  8.0  360.0  ...  0.0  0.0   3.0   4.0
Merc 240D                  Merc 240D  24.4  4.0  146.7  ...  1.0  0.0   4.0   2.0
Merc 230                    Merc 230  22.8  4.0  140.8  ...  1.0  0.0   4.0   2.0
Merc 280                    Merc 280  19.2  6.0  167.6  ...  1.0  0.0   4.0   4.0

[10 rows x 12 columns]

Code

from siuba import *
mtcars >> rename(model=_.model_name) >> select(_.model, _.mpg, _.cyl) >> head(6)

                               model   mpg  cyl
Mazda RX4                  Mazda RX4  21.0  6.0
Mazda RX4 Wag          Mazda RX4 Wag  21.0  6.0
Datsun 710                Datsun 710  22.8  4.0
Hornet 4 Drive        Hornet 4 Drive  21.4  6.0
Hornet Sportabout  Hornet Sportabout  18.7  8.0
Valiant                      Valiant  18.1  6.0

4.2 select distinct

all distinct record of mpg and cyl

Code

SELECT distinct mpg,cyl from mtcars_table;

8 records
mpg	cyl
18.7	8
21.0	6
21.4	6
24.4	4
19.2	6
22.8	4
18.1	6
14.3	8

Code

mtcars |> distinct(mpg,cyl)

                   mpg cyl
Mazda RX4         21.0   6
Datsun 710        22.8   4
Hornet 4 Drive    21.4   6
Hornet Sportabout 18.7   8
Valiant           18.1   6
Duster 360        14.3   8
Merc 240D         24.4   4
Merc 280          19.2   6

Code

df=mtcars[["mpg", "cyl"]]
print(df.drop_duplicates())

                    mpg  cyl
Mazda RX4          21.0  6.0
Datsun 710         22.8  4.0
Hornet 4 Drive     21.4  6.0
Hornet Sportabout  18.7  8.0
Valiant            18.1  6.0
Duster 360         14.3  8.0
Merc 240D          24.4  4.0
Merc 280           19.2  6.0

Code

mtcars >> distinct(_.mpg, _.cyl)

    mpg  cyl
0  21.0  6.0
1  22.8  4.0
2  21.4  6.0
3  18.7  8.0
4  18.1  6.0
5  14.3  8.0
6  24.4  4.0
7  19.2  6.0

5 check row number and column number

Code

/*Gives number of rows*/
SELECT count(*) as row_number from mtcars_table;

1 records
row_number
10

Code

/*Gives number of columns*/
select count(*) as column_number from (DESCRIBE mtcars_table);

1 records
column_number
12

Code

# Gives number of rows
nrow(mtcars)

[1] 10

Code

 # Gives number of columns
ncol(mtcars)

[1] 12

Code

# Gives number of rows
mtcars.shape[0]

Code

# Gives number of columns
mtcars.shape[1]

6 create column

Code

SELECT *, mpg+1 as new_mpg from mtcars_table;

Displaying records 1 - 10
model_name	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb	new_mpg
Mazda RX4	21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4	22.0
Mazda RX4 Wag	21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4	22.0
Datsun 710	22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1	23.8
Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1	22.4
Hornet Sportabout	18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2	19.7
Valiant	18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1	19.1
Duster 360	14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4	15.3
Merc 240D	24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2	25.4
Merc 230	22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2	23.8
Merc 280	19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4	20.2

Code

mtcars |> mutate(new_mpg=mpg+1)

                         model_name  mpg cyl  disp  hp drat    wt  qsec vs am
Mazda RX4                 Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1
Mazda RX4 Wag         Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1
Datsun 710               Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1
Hornet 4 Drive       Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0
Hornet Sportabout Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0
Valiant                     Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1  0
Duster 360               Duster 360 14.3   8 360.0 245 3.21 3.570 15.84  0  0
Merc 240D                 Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0
Merc 230                   Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0
Merc 280                   Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1  0
                  gear carb new_mpg
Mazda RX4            4    4    22.0
Mazda RX4 Wag        4    4    22.0
Datsun 710           4    1    23.8
Hornet 4 Drive       3    1    22.4
Hornet Sportabout    3    2    19.7
Valiant              3    1    19.1
Duster 360           3    4    15.3
Merc 240D            4    2    25.4
Merc 230             4    2    23.8
Merc 280             4    4    20.2

Code

mtcars["new_mpg"]=mtcars["mpg"]+1
mtcars

                          model_name   mpg  cyl  ...  gear  carb  new_mpg
Mazda RX4                  Mazda RX4  21.0  6.0  ...   4.0   4.0     22.0
Mazda RX4 Wag          Mazda RX4 Wag  21.0  6.0  ...   4.0   4.0     22.0
Datsun 710                Datsun 710  22.8  4.0  ...   4.0   1.0     23.8
Hornet 4 Drive        Hornet 4 Drive  21.4  6.0  ...   3.0   1.0     22.4
Hornet Sportabout  Hornet Sportabout  18.7  8.0  ...   3.0   2.0     19.7
Valiant                      Valiant  18.1  6.0  ...   3.0   1.0     19.1
Duster 360                Duster 360  14.3  8.0  ...   3.0   4.0     15.3
Merc 240D                  Merc 240D  24.4  4.0  ...   4.0   2.0     25.4
Merc 230                    Merc 230  22.8  4.0  ...   4.0   2.0     23.8
Merc 280                    Merc 280  19.2  6.0  ...   4.0   4.0     20.2

[10 rows x 13 columns]

Code

mtcars >> mutate(new_mpg=_.mpg+1)

                          model_name   mpg  cyl  ...  gear  carb  new_mpg
Mazda RX4                  Mazda RX4  21.0  6.0  ...   4.0   4.0     22.0
Mazda RX4 Wag          Mazda RX4 Wag  21.0  6.0  ...   4.0   4.0     22.0
Datsun 710                Datsun 710  22.8  4.0  ...   4.0   1.0     23.8
Hornet 4 Drive        Hornet 4 Drive  21.4  6.0  ...   3.0   1.0     22.4
Hornet Sportabout  Hornet Sportabout  18.7  8.0  ...   3.0   2.0     19.7
Valiant                      Valiant  18.1  6.0  ...   3.0   1.0     19.1
Duster 360                Duster 360  14.3  8.0  ...   3.0   4.0     15.3
Merc 240D                  Merc 240D  24.4  4.0  ...   4.0   2.0     25.4
Merc 230                    Merc 230  22.8  4.0  ...   4.0   2.0     23.8
Merc 280                    Merc 280  19.2  6.0  ...   4.0   4.0     20.2

[10 rows x 13 columns]

7 Filter

Code

/* and */
SELECT * from mtcars_table where mpg=21 and cyl=6;

2 records
model_name	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb
Mazda RX4	21	6	160	110	3.9	2.620	16.46	0	1	4	4
Mazda RX4 Wag	21	6	160	110	3.9	2.875	17.02	0	1	4	4

Code

/* or */
SELECT * from mtcars_table where mpg=21 or cyl=6;

5 records
model_name	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb
Mazda RX4	21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4
Mazda RX4 Wag	21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4
Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1
Valiant	18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1
Merc 280	19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4

Code

# and
mtcars |> filter(mpg==21&cyl==6)

                 model_name mpg cyl disp  hp drat    wt  qsec vs am gear carb
Mazda RX4         Mazda RX4  21   6  160 110  3.9 2.620 16.46  0  1    4    4
Mazda RX4 Wag Mazda RX4 Wag  21   6  160 110  3.9 2.875 17.02  0  1    4    4

Code

# or
mtcars |> filter(mpg==21|cyl==6)

                   model_name  mpg cyl  disp  hp drat    wt  qsec vs am gear
Mazda RX4           Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4
Mazda RX4 Wag   Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4
Hornet 4 Drive Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3
Valiant               Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3
Merc 280             Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4
               carb
Mazda RX4         4
Mazda RX4 Wag     4
Hornet 4 Drive    1
Valiant           1
Merc 280          4

Code

# and
mtcars.query('mpg==21 and cyl==6')

                  model_name   mpg  cyl   disp  ...   am  gear  carb  new_mpg
Mazda RX4          Mazda RX4  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0
Mazda RX4 Wag  Mazda RX4 Wag  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0

[2 rows x 13 columns]

Code

# or
mtcars.query('mpg==21 or cyl==6')

                    model_name   mpg  cyl   disp  ...   am  gear  carb  new_mpg
Mazda RX4            Mazda RX4  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0
Mazda RX4 Wag    Mazda RX4 Wag  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0
Hornet 4 Drive  Hornet 4 Drive  21.4  6.0  258.0  ...  0.0   3.0   1.0     22.4
Valiant                Valiant  18.1  6.0  225.0  ...  0.0   3.0   1.0     19.1
Merc 280              Merc 280  19.2  6.0  167.6  ...  0.0   4.0   4.0     20.2

[5 rows x 13 columns]

Code

# and
mtcars >> filter(_.mpg==21,_.cyl==6)

                  model_name   mpg  cyl   disp  ...   am  gear  carb  new_mpg
Mazda RX4          Mazda RX4  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0
Mazda RX4 Wag  Mazda RX4 Wag  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0

[2 rows x 13 columns]

Code

# or
mtcars >> filter((_.mpg==21) |(_.cyl==6))

                    model_name   mpg  cyl   disp  ...   am  gear  carb  new_mpg
Mazda RX4            Mazda RX4  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0
Mazda RX4 Wag    Mazda RX4 Wag  21.0  6.0  160.0  ...  1.0   4.0   4.0     22.0
Hornet 4 Drive  Hornet 4 Drive  21.4  6.0  258.0  ...  0.0   3.0   1.0     22.4
Valiant                Valiant  18.1  6.0  225.0  ...  0.0   3.0   1.0     19.1
Merc 280              Merc 280  19.2  6.0  167.6  ...  0.0   4.0   4.0     20.2

[5 rows x 13 columns]

8 order

Code

SELECT model_name as model,mpg,cyl from mtcars_table order by mpg desc limit 3;

3 records
model	mpg	cyl
Merc 240D	24.4	4
Datsun 710	22.8	4
Merc 230	22.8	4

Code

mtcars |> select(model=model_name,mpg,cyl) |> arrange(desc(mpg)) |> head(3)

                model  mpg cyl
Merc 240D   Merc 240D 24.4   4
Datsun 710 Datsun 710 22.8   4
Merc 230     Merc 230 22.8   4

Code

mtcars[["model_name","mpg","cyl"]].sort_values(by='mpg',ascending=False).head(3)

            model_name   mpg  cyl
Merc 240D    Merc 240D  24.4  4.0
Datsun 710  Datsun 710  22.8  4.0
Merc 230      Merc 230  22.8  4.0

Code

mtcars >> select(_.model_name,_.mpg,_.cyl) >> arrange(-_.mpg) >> head(3)

            model_name   mpg  cyl
Merc 240D    Merc 240D  24.4  4.0
Datsun 710  Datsun 710  22.8  4.0
Merc 230      Merc 230  22.8  4.0

9 group by

Code

SELECT model_name, sum(mpg) as total_mpg,mean(cyl) as cyl_mean from mtcars_table
group by 1 limit 5
;

5 records
model_name	total_mpg	cyl_mean
Mazda RX4 Wag	21.0	6
Hornet 4 Drive	21.4	6
Valiant	18.1	6
Merc 240D	24.4	4
Datsun 710	22.8	4

Code

mtcars |> group_by(model_name) |> summarise(total_mpg=sum(mpg),cyl_mean=mean(cyl)) |> head(5)

# A tibble: 5 × 3
  model_name        total_mpg cyl_mean
  <chr>                 <dbl>    <dbl>
1 Datsun 710             22.8        4
2 Duster 360             14.3        8
3 Hornet 4 Drive         21.4        6
4 Hornet Sportabout      18.7        8
5 Mazda RX4              21          6

Code

mtcars.groupby('model_name').agg({'mpg': 'sum', 'cyl': 'mean'}).head(5)

                    mpg  cyl
model_name                  
Datsun 710         22.8  4.0
Duster 360         14.3  8.0
Hornet 4 Drive     21.4  6.0
Hornet Sportabout  18.7  8.0
Mazda RX4          21.0  6.0

Code

mtcars >> group_by(_.model_name) >> summarize(total_mpg=_.mpg.sum(),cyl_mean=_.cyl.mean()) >> head(5)

          model_name  total_mpg  cyl_mean
0         Datsun 710       22.8       4.0
1         Duster 360       14.3       8.0
2     Hornet 4 Drive       21.4       6.0
3  Hornet Sportabout       18.7       8.0
4          Mazda RX4       21.0       6.0

10 create table

10.1 CREATE OR REPLACE

allows a new table to be created or for an existing table to be overwritten by the new table. This is shorthand for dropping the existing table and then creating the new one.

Code

CREATE OR REPLACE temp table mtcars_table_group as
SELECT model_name, sum(mpg) as total_mpg,mean(cyl) as cyl_mean from mtcars_table
group by 1
;

10.2 CREATE TABLE IF NOT EXISTS

will only proceed with the creation of the table if it does not already exist. If the table already exists, no action will be taken and the existing table will remain in the database.

Code

CREATE TABLE IF NOT EXISTS  new_mtcars_table_group as
SELECT model_name, sum(mpg) as total_mpg,mean(cyl) as cyl_mean from mtcars_table
group by 1
;

11 unique

11.1 check unique

Code

SELECT count(*),count(distinct model_name) from mtcars_table
;

1 records
count_star()	count(DISTINCT model_name)
10	10

12 join

Code

SELECT  * from mtcars_table_group t1 ;

Displaying records 1 - 10
model_name	total_mpg	cyl_mean
Valiant	18.1	6
Merc 240D	24.4	4
Datsun 710	22.8	4
Duster 360	14.3	8
Merc 280	19.2	6
Hornet Sportabout	18.7	8
Merc 230	22.8	4
Mazda RX4	21.0	6
Mazda RX4 Wag	21.0	6
Hornet 4 Drive	21.4	6

12.1 left join

Code

SELECT t1.model_name,t1.mpg ,t1.cyl,t2.* from mtcars_table t1 
left join mtcars_table_group t2 on t1.model_name = t2.model_name
limit 5
;

5 records
model_name	mpg	cyl	model_name	total_mpg	cyl_mean
Mazda RX4	21.0	6	Mazda RX4	21.0	6
Mazda RX4 Wag	21.0	6	Mazda RX4 Wag	21.0	6
Datsun 710	22.8	4	Datsun 710	22.8	4
Hornet 4 Drive	21.4	6	Hornet 4 Drive	21.4	6
Hornet Sportabout	18.7	8	Hornet Sportabout	18.7	8

Code

mtcars |> left_join(mtcars, by = join_by(model_name == model_name)) |> head(5)

         model_name mpg.x cyl.x disp.x hp.x drat.x  wt.x qsec.x vs.x am.x
1         Mazda RX4  21.0     6    160  110   3.90 2.620  16.46    0    1
2     Mazda RX4 Wag  21.0     6    160  110   3.90 2.875  17.02    0    1
3        Datsun 710  22.8     4    108   93   3.85 2.320  18.61    1    1
4    Hornet 4 Drive  21.4     6    258  110   3.08 3.215  19.44    1    0
5 Hornet Sportabout  18.7     8    360  175   3.15 3.440  17.02    0    0
  gear.x carb.x mpg.y cyl.y disp.y hp.y drat.y  wt.y qsec.y vs.y am.y gear.y
1      4      4  21.0     6    160  110   3.90 2.620  16.46    0    1      4
2      4      4  21.0     6    160  110   3.90 2.875  17.02    0    1      4
3      4      1  22.8     4    108   93   3.85 2.320  18.61    1    1      4
4      3      1  21.4     6    258  110   3.08 3.215  19.44    1    0      3
5      3      2  18.7     8    360  175   3.15 3.440  17.02    0    0      3
  carb.y
1      4
2      4
3      1
4      1
5      2

Code

pd.merge(mtcars, mtcars, left_on='model_name', right_on='model_name', how='left').head(5)

          model_name  mpg_x  cyl_x  disp_x  ...  am_y  gear_y  carb_y  new_mpg_y
0          Mazda RX4   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
1      Mazda RX4 Wag   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
2         Datsun 710   22.8    4.0   108.0  ...   1.0     4.0     1.0       23.8
3     Hornet 4 Drive   21.4    6.0   258.0  ...   0.0     3.0     1.0       22.4
4  Hornet Sportabout   18.7    8.0   360.0  ...   0.0     3.0     2.0       19.7

[5 rows x 25 columns]

Code

mtcars >> left_join(_,mtcars, on = {"model_name": "model_name"}) >> head(5)

          model_name  mpg_x  cyl_x  disp_x  ...  am_y  gear_y  carb_y  new_mpg_y
0          Mazda RX4   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
1      Mazda RX4 Wag   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
2         Datsun 710   22.8    4.0   108.0  ...   1.0     4.0     1.0       23.8
3     Hornet 4 Drive   21.4    6.0   258.0  ...   0.0     3.0     1.0       22.4
4  Hornet Sportabout   18.7    8.0   360.0  ...   0.0     3.0     2.0       19.7

[5 rows x 25 columns]

12.2 inter join

Code

SELECT t1.model_name,t1.mpg ,t1.cyl,t2.* from mtcars_table t1 
inner join (select * from mtcars_table_group limit 5) t2 on t1.model_name = t2.model_name
;

5 records
model_name	mpg	cyl	model_name	total_mpg	cyl_mean
Datsun 710	22.8	4	Datsun 710	22.8	4
Valiant	18.1	6	Valiant	18.1	6
Duster 360	14.3	8	Duster 360	14.3	8
Merc 240D	24.4	4	Merc 240D	24.4	4
Merc 280	19.2	6	Merc 280	19.2	6

Code

mtcars |> inner_join(mtcars, by = join_by(model_name == model_name)) |> head(5)

         model_name mpg.x cyl.x disp.x hp.x drat.x  wt.x qsec.x vs.x am.x
1         Mazda RX4  21.0     6    160  110   3.90 2.620  16.46    0    1
2     Mazda RX4 Wag  21.0     6    160  110   3.90 2.875  17.02    0    1
3        Datsun 710  22.8     4    108   93   3.85 2.320  18.61    1    1
4    Hornet 4 Drive  21.4     6    258  110   3.08 3.215  19.44    1    0
5 Hornet Sportabout  18.7     8    360  175   3.15 3.440  17.02    0    0
  gear.x carb.x mpg.y cyl.y disp.y hp.y drat.y  wt.y qsec.y vs.y am.y gear.y
1      4      4  21.0     6    160  110   3.90 2.620  16.46    0    1      4
2      4      4  21.0     6    160  110   3.90 2.875  17.02    0    1      4
3      4      1  22.8     4    108   93   3.85 2.320  18.61    1    1      4
4      3      1  21.4     6    258  110   3.08 3.215  19.44    1    0      3
5      3      2  18.7     8    360  175   3.15 3.440  17.02    0    0      3
  carb.y
1      4
2      4
3      1
4      1
5      2

Code

pd.merge(mtcars, mtcars, left_on='model_name', right_on='model_name', how='inner').head(5)

          model_name  mpg_x  cyl_x  disp_x  ...  am_y  gear_y  carb_y  new_mpg_y
0          Mazda RX4   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
1      Mazda RX4 Wag   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
2         Datsun 710   22.8    4.0   108.0  ...   1.0     4.0     1.0       23.8
3     Hornet 4 Drive   21.4    6.0   258.0  ...   0.0     3.0     1.0       22.4
4  Hornet Sportabout   18.7    8.0   360.0  ...   0.0     3.0     2.0       19.7

[5 rows x 25 columns]

Code

mtcars >> inner_join(_,mtcars, on = {"model_name": "model_name"}) >> head(5)

          model_name  mpg_x  cyl_x  disp_x  ...  am_y  gear_y  carb_y  new_mpg_y
0          Mazda RX4   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
1      Mazda RX4 Wag   21.0    6.0   160.0  ...   1.0     4.0     4.0       22.0
2         Datsun 710   22.8    4.0   108.0  ...   1.0     4.0     1.0       23.8
3     Hornet 4 Drive   21.4    6.0   258.0  ...   0.0     3.0     1.0       22.4
4  Hornet Sportabout   18.7    8.0   360.0  ...   0.0     3.0     2.0       19.7

[5 rows x 25 columns]

13 append rows

13.1 append without duplicate elimination `union all`

Code

SELECT count(*) from mtcars_table
;

1 records
count_star()
10

Code

CREATE temp table double_mtcars_table as
SELECT * from mtcars_table 
union all
SELECT * from mtcars_table 
;

Code

SELECT count(*) from double_mtcars_table
;

1 records
count_star()
20

Code

mtcars %>% bind_rows(mtcars)

                              model_name  mpg cyl  disp  hp drat    wt  qsec vs
Mazda RX4...1                  Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0
Mazda RX4 Wag...2          Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0
Datsun 710...3                Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1
Hornet 4 Drive...4        Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1
Hornet Sportabout...5  Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0
Valiant...6                      Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1
Duster 360...7                Duster 360 14.3   8 360.0 245 3.21 3.570 15.84  0
Merc 240D...8                  Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1
Merc 230...9                    Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1
Merc 280...10                   Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1
Mazda RX4...11                 Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0
Mazda RX4 Wag...12         Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0
Datsun 710...13               Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1
Hornet 4 Drive...14       Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1
Hornet Sportabout...15 Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0
Valiant...16                     Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1
Duster 360...17               Duster 360 14.3   8 360.0 245 3.21 3.570 15.84  0
Merc 240D...18                 Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1
Merc 230...19                   Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1
Merc 280...20                   Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1
                       am gear carb
Mazda RX4...1           1    4    4
Mazda RX4 Wag...2       1    4    4
Datsun 710...3          1    4    1
Hornet 4 Drive...4      0    3    1
Hornet Sportabout...5   0    3    2
Valiant...6             0    3    1
Duster 360...7          0    3    4
Merc 240D...8           0    4    2
Merc 230...9            0    4    2
Merc 280...10           0    4    4
Mazda RX4...11          1    4    4
Mazda RX4 Wag...12      1    4    4
Datsun 710...13         1    4    1
Hornet 4 Drive...14     0    3    1
Hornet Sportabout...15  0    3    2
Valiant...16            0    3    1
Duster 360...17         0    3    4
Merc 240D...18          0    4    2
Merc 230...19           0    4    2
Merc 280...20           0    4    4

Code

pd.concat([mtcars,mtcars],ignore_index=True)

           model_name   mpg  cyl   disp     hp  ...   vs   am  gear  carb  new_mpg
0           Mazda RX4  21.0  6.0  160.0  110.0  ...  0.0  1.0   4.0   4.0     22.0
1       Mazda RX4 Wag  21.0  6.0  160.0  110.0  ...  0.0  1.0   4.0   4.0     22.0
2          Datsun 710  22.8  4.0  108.0   93.0  ...  1.0  1.0   4.0   1.0     23.8
3      Hornet 4 Drive  21.4  6.0  258.0  110.0  ...  1.0  0.0   3.0   1.0     22.4
4   Hornet Sportabout  18.7  8.0  360.0  175.0  ...  0.0  0.0   3.0   2.0     19.7
5             Valiant  18.1  6.0  225.0  105.0  ...  1.0  0.0   3.0   1.0     19.1
6          Duster 360  14.3  8.0  360.0  245.0  ...  0.0  0.0   3.0   4.0     15.3
7           Merc 240D  24.4  4.0  146.7   62.0  ...  1.0  0.0   4.0   2.0     25.4
8            Merc 230  22.8  4.0  140.8   95.0  ...  1.0  0.0   4.0   2.0     23.8
9            Merc 280  19.2  6.0  167.6  123.0  ...  1.0  0.0   4.0   4.0     20.2
10          Mazda RX4  21.0  6.0  160.0  110.0  ...  0.0  1.0   4.0   4.0     22.0
11      Mazda RX4 Wag  21.0  6.0  160.0  110.0  ...  0.0  1.0   4.0   4.0     22.0
12         Datsun 710  22.8  4.0  108.0   93.0  ...  1.0  1.0   4.0   1.0     23.8
13     Hornet 4 Drive  21.4  6.0  258.0  110.0  ...  1.0  0.0   3.0   1.0     22.4
14  Hornet Sportabout  18.7  8.0  360.0  175.0  ...  0.0  0.0   3.0   2.0     19.7
15            Valiant  18.1  6.0  225.0  105.0  ...  1.0  0.0   3.0   1.0     19.1
16         Duster 360  14.3  8.0  360.0  245.0  ...  0.0  0.0   3.0   4.0     15.3
17          Merc 240D  24.4  4.0  146.7   62.0  ...  1.0  0.0   4.0   2.0     25.4
18           Merc 230  22.8  4.0  140.8   95.0  ...  1.0  0.0   4.0   2.0     23.8
19           Merc 280  19.2  6.0  167.6  123.0  ...  1.0  0.0   4.0   4.0     20.2

[20 rows x 13 columns]

13.2 append with duplicate elimination `union`

Code

CREATE OR REPLACE temp table double_mtcars_table as
SELECT * from mtcars_table 
union
SELECT * from mtcars_table 
;

Code

SELECT count(*) from double_mtcars_table
;

1 records
count_star()
10

Code

select count(*) from (
SELECT distinct * from double_mtcars_table
)
;

1 records
count_star()
10

Code

mtcars %>% bind_rows(mtcars) |> distinct()

                         model_name  mpg cyl  disp  hp drat    wt  qsec vs am
Mazda RX4                 Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1
Mazda RX4 Wag         Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1
Datsun 710               Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1
Hornet 4 Drive       Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0
Hornet Sportabout Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0
Valiant                     Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1  0
Duster 360               Duster 360 14.3   8 360.0 245 3.21 3.570 15.84  0  0
Merc 240D                 Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0
Merc 230                   Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0
Merc 280                   Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1  0
                  gear carb
Mazda RX4            4    4
Mazda RX4 Wag        4    4
Datsun 710           4    1
Hornet 4 Drive       3    1
Hornet Sportabout    3    2
Valiant              3    1
Duster 360           3    4
Merc 240D            4    2
Merc 230             4    2
Merc 280             4    4

Code

pd.concat([mtcars,mtcars],ignore_index=True).drop_duplicates()

          model_name   mpg  cyl   disp     hp  ...   vs   am  gear  carb  new_mpg
0          Mazda RX4  21.0  6.0  160.0  110.0  ...  0.0  1.0   4.0   4.0     22.0
1      Mazda RX4 Wag  21.0  6.0  160.0  110.0  ...  0.0  1.0   4.0   4.0     22.0
2         Datsun 710  22.8  4.0  108.0   93.0  ...  1.0  1.0   4.0   1.0     23.8
3     Hornet 4 Drive  21.4  6.0  258.0  110.0  ...  1.0  0.0   3.0   1.0     22.4
4  Hornet Sportabout  18.7  8.0  360.0  175.0  ...  0.0  0.0   3.0   2.0     19.7
5            Valiant  18.1  6.0  225.0  105.0  ...  1.0  0.0   3.0   1.0     19.1
6         Duster 360  14.3  8.0  360.0  245.0  ...  0.0  0.0   3.0   4.0     15.3
7          Merc 240D  24.4  4.0  146.7   62.0  ...  1.0  0.0   4.0   2.0     25.4
8           Merc 230  22.8  4.0  140.8   95.0  ...  1.0  0.0   4.0   2.0     23.8
9           Merc 280  19.2  6.0  167.6  123.0  ...  1.0  0.0   4.0   4.0     20.2

[10 rows x 13 columns]

14 delete rows

Code

DELETE FROM mtcars_table WHERE model_name = 'Mazda RX4';
;

Code

select count(*) from mtcars_table
;

1 records
count_star()
9

Code

mtcars |> filter(model_name != 'Mazda RX4')

                         model_name  mpg cyl  disp  hp drat    wt  qsec vs am
Mazda RX4 Wag         Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1
Datsun 710               Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1
Hornet 4 Drive       Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0
Hornet Sportabout Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0
Valiant                     Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1  0
Duster 360               Duster 360 14.3   8 360.0 245 3.21 3.570 15.84  0  0
Merc 240D                 Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0
Merc 230                   Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0
Merc 280                   Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1  0
                  gear carb
Mazda RX4 Wag        4    4
Datsun 710           4    1
Hornet 4 Drive       3    1
Hornet Sportabout    3    2
Valiant              3    1
Duster 360           3    4
Merc 240D            4    2
Merc 230             4    2
Merc 280             4    4

Code

mtcars[mtcars['model_name'] != 'Mazda RX4']

                          model_name   mpg  cyl  ...  gear  carb  new_mpg
Mazda RX4 Wag          Mazda RX4 Wag  21.0  6.0  ...   4.0   4.0     22.0
Datsun 710                Datsun 710  22.8  4.0  ...   4.0   1.0     23.8
Hornet 4 Drive        Hornet 4 Drive  21.4  6.0  ...   3.0   1.0     22.4
Hornet Sportabout  Hornet Sportabout  18.7  8.0  ...   3.0   2.0     19.7
Valiant                      Valiant  18.1  6.0  ...   3.0   1.0     19.1
Duster 360                Duster 360  14.3  8.0  ...   3.0   4.0     15.3
Merc 240D                  Merc 240D  24.4  4.0  ...   4.0   2.0     25.4
Merc 230                    Merc 230  22.8  4.0  ...   4.0   2.0     23.8
Merc 280                    Merc 280  19.2  6.0  ...   4.0   4.0     20.2

[9 rows x 13 columns]

Code

mtcars >> filter(_.model_name != 'Mazda RX4')

                          model_name   mpg  cyl  ...  gear  carb  new_mpg
Mazda RX4 Wag          Mazda RX4 Wag  21.0  6.0  ...   4.0   4.0     22.0
Datsun 710                Datsun 710  22.8  4.0  ...   4.0   1.0     23.8
Hornet 4 Drive        Hornet 4 Drive  21.4  6.0  ...   3.0   1.0     22.4
Hornet Sportabout  Hornet Sportabout  18.7  8.0  ...   3.0   2.0     19.7
Valiant                      Valiant  18.1  6.0  ...   3.0   1.0     19.1
Duster 360                Duster 360  14.3  8.0  ...   3.0   4.0     15.3
Merc 240D                  Merc 240D  24.4  4.0  ...   4.0   2.0     25.4
Merc 230                    Merc 230  22.8  4.0  ...   4.0   2.0     23.8
Merc 280                    Merc 280  19.2  6.0  ...   4.0   4.0     20.2

[9 rows x 13 columns]

15 update rows

Code

select model_name,mpg  from mtcars_table where model_name= 'Mazda RX4'
;

0 records
model_name	mpg

Code

UPDATE mtcars_table
SET mpg = 999
WHERE model_name= 'Mazda RX4'
;

Code

select model_name,mpg from mtcars_table where model_name= 'Mazda RX4'
;

0 records
model_name	mpg

Code

mtcars |> filter(model_name == 'Mazda RX4 Wag') |> mutate(mpg=999)

                 model_name mpg cyl disp  hp drat    wt  qsec vs am gear carb
Mazda RX4 Wag Mazda RX4 Wag 999   6  160 110  3.9 2.875 17.02  0  1    4    4

Code

mtcars.loc[mtcars['model_name'] == 'Mazda RX4 Wag', 'mpg'] = 999
mtcars

                          model_name    mpg  cyl  ...  gear  carb  new_mpg
Mazda RX4                  Mazda RX4   21.0  6.0  ...   4.0   4.0     22.0
Mazda RX4 Wag          Mazda RX4 Wag  999.0  6.0  ...   4.0   4.0     22.0
Datsun 710                Datsun 710   22.8  4.0  ...   4.0   1.0     23.8
Hornet 4 Drive        Hornet 4 Drive   21.4  6.0  ...   3.0   1.0     22.4
Hornet Sportabout  Hornet Sportabout   18.7  8.0  ...   3.0   2.0     19.7
Valiant                      Valiant   18.1  6.0  ...   3.0   1.0     19.1
Duster 360                Duster 360   14.3  8.0  ...   3.0   4.0     15.3
Merc 240D                  Merc 240D   24.4  4.0  ...   4.0   2.0     25.4
Merc 230                    Merc 230   22.8  4.0  ...   4.0   2.0     23.8
Merc 280                    Merc 280   19.2  6.0  ...   4.0   4.0     20.2

[10 rows x 13 columns]

Code

mtcars >> filter(_.model_name == 'Mazda RX4 Wag') >> mutate(mpg=999)

                  model_name  mpg  cyl   disp  ...   am  gear  carb  new_mpg
Mazda RX4 Wag  Mazda RX4 Wag  999  6.0  160.0  ...  1.0   4.0   4.0     22.0

[1 rows x 13 columns]

16 drop table

Before drop

Code

SHOW ALL TABLES;

5 records
database	schema	name	column_names	column_types	temporary
my-db	main	iris_table	Sepal.Length, Sepal.Width , Petal.Length, Petal.Width , Species	DOUBLE , DOUBLE , DOUBLE , DOUBLE , ENUM(‘setosa’, ‘versicolor’, ‘virginica’)	FALSE
my-db	main	mtcars_table	model_name, mpg , cyl , disp , hp , drat , wt , qsec , vs , am , gear , carb	VARCHAR, DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE	FALSE
my-db	main	new_mtcars_table_group	model_name, total_mpg , cyl_mean	VARCHAR, DOUBLE , DOUBLE	FALSE
temp	main	double_mtcars_table	model_name, mpg , cyl , disp , hp , drat , wt , qsec , vs , am , gear , carb	VARCHAR, DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE	TRUE
temp	main	mtcars_table_group	model_name, total_mpg , cyl_mean	VARCHAR, DOUBLE , DOUBLE	TRUE

dropping

Code

DROP table IF EXISTS mtcars_table_group;

after drop

Code

SHOW ALL TABLES;

4 records
database	schema	name	column_names	column_types	temporary
my-db	main	iris_table	Sepal.Length, Sepal.Width , Petal.Length, Petal.Width , Species	DOUBLE , DOUBLE , DOUBLE , DOUBLE , ENUM(‘setosa’, ‘versicolor’, ‘virginica’)	FALSE
my-db	main	mtcars_table	model_name, mpg , cyl , disp , hp , drat , wt , qsec , vs , am , gear , carb	VARCHAR, DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE	FALSE
my-db	main	new_mtcars_table_group	model_name, total_mpg , cyl_mean	VARCHAR, DOUBLE , DOUBLE	FALSE
temp	main	double_mtcars_table	model_name, mpg , cyl , disp , hp , drat , wt , qsec , vs , am , gear , carb	VARCHAR, DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE , DOUBLE	TRUE

17 PIVOT

long to wide

Code

DROP table IF EXISTS cities;

CREATE temp TABLE  cities (
    country VARCHAR, name VARCHAR, year INTEGER, population INTEGER
);
INSERT INTO cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

Code

SELECT *
FROM cities;

9 records
country	name	year	population
NL	Amsterdam	2000	1005
NL	Amsterdam	2010	1065
NL	Amsterdam	2020	1158
US	Seattle	2000	564
US	Seattle	2010	608
US	Seattle	2020	738
US	New York City	2000	8015
US	New York City	2010	8175
US	New York City	2020	8772

17.1 PIVOT on one column

Code

PIVOT cities
ON year
USING sum(population)
GROUP BY country;

2 records
country	2000	2010	2020
US	8579	8783	9510
NL	1005	1065	1158

17.2 PIVOT on two column

Code

PIVOT cities
ON country, name
USING sum(population);

3 records
year	NL_Amsterdam	NL_New York City	NL_Seattle	US_Amsterdam	US_New York City	US_Seattle
2000	1005	NA	NA	NA	8015	564
2010	1065	NA	NA	NA	8175	608
2020	1158	NA	NA	NA	8772	738

18 unpivot

wide to long

Code

UNPIVOT
(
PIVOT cities
ON year
USING sum(population)
GROUP BY country
)
ON COLUMNS(* EXCLUDE (country))
INTO
    NAME year
    VALUE population;
;

6 records
country	year	population
NL	2000	1005
NL	2010	1065
NL	2020	1158
US	2000	8579
US	2010	8783
US	2020	9510

19 EXPLAIN

Code

EXPLAIN 
SELECT t1.model_name,t1.mpg ,t1.cyl,t2.* from mtcars_table t1 
inner join (select * from mtcars_table limit 5) t2 on t1.model_name = t2.model_name
;

Code

EXPLAIN ANALYZE 
SELECT t1.model_name,t1.mpg ,t1.cyl,t2.* from mtcars_table t1 
inner join (select * from mtcars_table limit 5) t2 on t1.model_name = t2.model_name
;

Code

dbDisconnect(con, shutdown=TRUE)

20 Display beautiful table

in R and Python using GT package

R
Python

Code

library(gt)
mtcars |> gt()

model_name	mpg	cyl	disp	hp	drat	wt	qsec	vs	am	gear	carb
Mazda RX4	21.0	6	160.0	110	3.90	2.620	16.46	0	1	4	4
Mazda RX4 Wag	21.0	6	160.0	110	3.90	2.875	17.02	0	1	4	4
Datsun 710	22.8	4	108.0	93	3.85	2.320	18.61	1	1	4	1
Hornet 4 Drive	21.4	6	258.0	110	3.08	3.215	19.44	1	0	3	1
Hornet Sportabout	18.7	8	360.0	175	3.15	3.440	17.02	0	0	3	2
Valiant	18.1	6	225.0	105	2.76	3.460	20.22	1	0	3	1
Duster 360	14.3	8	360.0	245	3.21	3.570	15.84	0	0	3	4
Merc 240D	24.4	4	146.7	62	3.69	3.190	20.00	1	0	4	2
Merc 230	22.8	4	140.8	95	3.92	3.150	22.90	1	0	4	2
Merc 280	19.2	6	167.6	123	3.92	3.440	18.30	1	0	4	4

Code

from great_tables import GT
GT(mtcars)

GT(_tbl_data=                          model_name    mpg  cyl  ...  gear  carb  new_mpg
Mazda RX4                  Mazda RX4   21.0  6.0  ...   4.0   4.0     22.0
Mazda RX4 Wag          Mazda RX4 Wag  999.0  6.0  ...   4.0   4.0     22.0
Datsun 710                Datsun 710   22.8  4.0  ...   4.0   1.0     23.8
Hornet 4 Drive        Hornet 4 Drive   21.4  6.0  ...   3.0   1.0     22.4
Hornet Sportabout  Hornet Sportabout   18.7  8.0  ...   3.0   2.0     19.7
Valiant                      Valiant   18.1  6.0  ...   3.0   1.0     19.1
Duster 360                Duster 360   14.3  8.0  ...   3.0   4.0     15.3
Merc 240D                  Merc 240D   24.4  4.0  ...   4.0   2.0     25.4
Merc 230                    Merc 230   22.8  4.0  ...   4.0   2.0     23.8
Merc 280                    Merc 280   19.2  6.0  ...   4.0   4.0     20.2

[10 rows x 13 columns], _body=<great_tables._gt_data.Body object at 0x16672a890>, _boxhead=Boxhead([ColInfo(var='model_name', type=<ColInfoTypeEnum.default: 1>, column_label='model_name', column_align='left', column_width=None), ColInfo(var='mpg', type=<ColInfoTypeEnum.default: 1>, column_label='mpg', column_align='right', column_width=None), ColInfo(var='cyl', type=<ColInfoTypeEnum.default: 1>, column_label='cyl', column_align='right', column_width=None), ColInfo(var='disp', type=<ColInfoTypeEnum.default: 1>, column_label='disp', column_align='right', column_width=None), ColInfo(var='hp', type=<ColInfoTypeEnum.default: 1>, column_label='hp', column_align='right', column_width=None), ColInfo(var='drat', type=<ColInfoTypeEnum.default: 1>, column_label='drat', column_align='right', column_width=None), ColInfo(var='wt', type=<ColInfoTypeEnum.default: 1>, column_label='wt', column_align='right', column_width=None), ColInfo(var='qsec', type=<ColInfoTypeEnum.default: 1>, column_label='qsec', column_align='right', column_width=None), ColInfo(var='vs', type=<ColInfoTypeEnum.default: 1>, column_label='vs', column_align='right', column_width=None), ColInfo(var='am', type=<ColInfoTypeEnum.default: 1>, column_label='am', column_align='right', column_width=None), ColInfo(var='gear', type=<ColInfoTypeEnum.default: 1>, column_label='gear', column_align='right', column_width=None), ColInfo(var='carb', type=<ColInfoTypeEnum.default: 1>, column_label='carb', column_align='right', column_width=None), ColInfo(var='new_mpg', type=<ColInfoTypeEnum.default: 1>, column_label='new_mpg', column_align='right', column_width=None)]), _stub=<great_tables._gt_data.Stub object at 0x167b9d050>, _spanners=Spanners([]), _heading=Heading(title=None, subtitle=None, preheader=None), _stubhead=None, _source_notes=[], _footnotes=[], _styles=[], _locale=<great_tables._gt_data.Locale object at 0x167b90450>, _formats=[], _substitutions=[], _options=Options(table_id=OptionsInfo(scss=False, category='table', type='value', value=None), table_caption=OptionsInfo(scss=False, category='table', type='value', value=None), table_width=OptionsInfo(scss=True, category='table', type='px', value='auto'), table_layout=OptionsInfo(scss=True, category='table', type='value', value='fixed'), table_margin_left=OptionsInfo(scss=True, category='table', type='px', value='auto'), table_margin_right=OptionsInfo(scss=True, category='table', type='px', value='auto'), table_background_color=OptionsInfo(scss=True, category='table', type='value', value='#FFFFFF'), table_additional_css=OptionsInfo(scss=False, category='table', type='values', value=[]), table_font_names=OptionsInfo(scss=False, category='table', type='values', value=['-apple-system', 'BlinkMacSystemFont', 'Segoe UI', 'Roboto', 'Oxygen', 'Ubuntu', 'Cantarell', 'Helvetica Neue', 'Fira Sans', 'Droid Sans', 'Arial', 'sans-serif']), table_font_size=OptionsInfo(scss=True, category='table', type='px', value='16px'), table_font_weight=OptionsInfo(scss=True, category='table', type='value', value='normal'), table_font_style=OptionsInfo(scss=True, category='table', type='value', value='normal'), table_font_color=OptionsInfo(scss=True, category='table', type='value', value='#333333'), table_font_color_light=OptionsInfo(scss=True, category='table', type='value', value='#FFFFFF'), table_border_top_include=OptionsInfo(scss=False, category='table', type='boolean', value=True), table_border_top_style=OptionsInfo(scss=True, category='table', type='value', value='solid'), table_border_top_width=OptionsInfo(scss=True, category='table', type='px', value='2px'), table_border_top_color=OptionsInfo(scss=True, category='table', type='value', value='#A8A8A8'), table_border_right_style=OptionsInfo(scss=True, category='table', type='value', value='none'), table_border_right_width=OptionsInfo(scss=True, category='table', type='px', value='2px'), table_border_right_color=OptionsInfo(scss=True, category='table', type='value', value='#D3D3D3'), table_border_bottom_include=OptionsInfo(scss=False, category='table', type='boolean', value=True), table_border_bottom_style=OptionsInfo(scss=True, category='table', type='value', value='solid'), table_border_bottom_width=OptionsInfo(scss=True, category='table', type='px', value='2px'), table_border_bottom_color=OptionsInfo(scss=True, category='table', type='value', value='#A8A8A8'), table_border_left_style=OptionsInfo(scss=True, category='table', type='value', value='none'), table_border_left_width=OptionsInfo(scss=True, category='table', type='px', value='2px'), table_border_left_color=OptionsInfo(scss=True, category='table', type='value', value='#D3D3D3'), heading_background_color=OptionsInfo(scss=True, category='heading', type='value', value=None), heading_align=OptionsInfo(scss=True, category='heading', type='value', value='center'), heading_title_font_size=OptionsInfo(scss=True, category='heading', type='px', value='125%'), heading_title_font_weight=OptionsInfo(scss=True, category='heading', type='value', value='initial'), heading_subtitle_font_size=OptionsInfo(scss=True, category='heading', type='px', value='85%'), heading_subtitle_font_weight=OptionsInfo(scss=True, category='heading', type='value', value='initial'), heading_padding=OptionsInfo(scss=True, category='heading', type='px', value='4px'), heading_padding_horizontal=OptionsInfo(scss=True, category='heading', type='px', value='5px'), heading_border_bottom_style=OptionsInfo(scss=True, category='heading', type='value', value='solid'), heading_border_bottom_width=OptionsInfo(scss=True, category='heading', type='px', value='2px'), heading_border_bottom_color=OptionsInfo(scss=True, category='heading', type='value', value='#D3D3D3'), heading_border_lr_style=OptionsInfo(scss=True, category='heading', type='value', value='none'), heading_border_lr_width=OptionsInfo(scss=True, category='heading', type='px', value='1px'), heading_border_lr_color=OptionsInfo(scss=True, category='heading', type='value', value='#D3D3D3'), column_labels_background_color=OptionsInfo(scss=True, category='column_labels', type='value', value=None), column_labels_font_size=OptionsInfo(scss=True, category='column_labels', type='px', value='100%'), column_labels_font_weight=OptionsInfo(scss=True, category='column_labels', type='value', value='normal'), column_labels_text_transform=OptionsInfo(scss=True, category='column_labels', type='value', value='inherit'), column_labels_padding=OptionsInfo(scss=True, category='column_labels', type='px', value='5px'), column_labels_padding_horizontal=OptionsInfo(scss=True, category='column_labels', type='px', value='5px'), column_labels_vlines_style=OptionsInfo(scss=True, category='table_body', type='value', value='none'), column_labels_vlines_width=OptionsInfo(scss=True, category='table_body', type='px', value='1px'), column_labels_vlines_color=OptionsInfo(scss=True, category='table_body', type='value', value='#D3D3D3'), column_labels_border_top_style=OptionsInfo(scss=True, category='column_labels', type='value', value='solid'), column_labels_border_top_width=OptionsInfo(scss=True, category='column_labels', type='px', value='2px'), column_labels_border_top_color=OptionsInfo(scss=True, category='column_labels', type='value', value='#D3D3D3'), column_labels_border_bottom_style=OptionsInfo(scss=True, category='column_labels', type='value', value='solid'), column_labels_border_bottom_width=OptionsInfo(scss=True, category='column_labels', type='px', value='2px'), column_labels_border_bottom_color=OptionsInfo(scss=True, category='column_labels', type='value', value='#D3D3D3'), column_labels_border_lr_style=OptionsInfo(scss=True, category='column_labels', type='value', value='none'), column_labels_border_lr_width=OptionsInfo(scss=True, category='column_labels', type='px', value='1px'), column_labels_border_lr_color=OptionsInfo(scss=True, category='column_labels', type='value', value='#D3D3D3'), column_labels_hidden=OptionsInfo(scss=False, category='column_labels', type='boolean', value=False), row_group_background_color=OptionsInfo(scss=True, category='row_group', type='value', value=None), row_group_font_size=OptionsInfo(scss=True, category='row_group', type='px', value='100%'), row_group_font_weight=OptionsInfo(scss=True, category='row_group', type='value', value='initial'), row_group_text_transform=OptionsInfo(scss=True, category='row_group', type='value', value='inherit'), row_group_padding=OptionsInfo(scss=True, category='row_group', type='px', value='8px'), row_group_padding_horizontal=OptionsInfo(scss=True, category='row_group', type='px', value='5px'), row_group_border_top_style=OptionsInfo(scss=True, category='row_group', type='value', value='solid'), row_group_border_top_width=OptionsInfo(scss=True, category='row_group', type='px', value='2px'), row_group_border_top_color=OptionsInfo(scss=True, category='row_group', type='value', value='#D3D3D3'), row_group_border_right_style=OptionsInfo(scss=True, category='row_group', type='value', value='none'), row_group_border_right_width=OptionsInfo(scss=True, category='row_group', type='px', value='1px'), row_group_border_right_color=OptionsInfo(scss=True, category='row_group', type='value', value='#D3D3D3'), row_group_border_bottom_style=OptionsInfo(scss=True, category='row_group', type='value', value='solid'), row_group_border_bottom_width=OptionsInfo(scss=True, category='row_group', type='px', value='2px'), row_group_border_bottom_color=OptionsInfo(scss=True, category='row_group', type='value', value='#D3D3D3'), row_group_border_left_style=OptionsInfo(scss=True, category='row_group', type='value', value='none'), row_group_border_left_width=OptionsInfo(scss=True, category='row_group', type='px', value='1px'), row_group_border_left_color=OptionsInfo(scss=True, category='row_group', type='value', value='#D3D3D3'), row_group_as_column=OptionsInfo(scss=False, category='row_group', type='boolean', value=False), table_body_hlines_style=OptionsInfo(scss=True, category='table_body', type='value', value='solid'), table_body_hlines_width=OptionsInfo(scss=True, category='table_body', type='px', value='1px'), table_body_hlines_color=OptionsInfo(scss=True, category='table_body', type='value', value='#D3D3D3'), table_body_vlines_style=OptionsInfo(scss=True, category='table_body', type='value', value='none'), table_body_vlines_width=OptionsInfo(scss=True, category='table_body', type='px', value='1px'), table_body_vlines_color=OptionsInfo(scss=True, category='table_body', type='value', value='#D3D3D3'), table_body_border_top_style=OptionsInfo(scss=True, category='table_body', type='value', value='solid'), table_body_border_top_width=OptionsInfo(scss=True, category='table_body', type='px', value='2px'), table_body_border_top_color=OptionsInfo(scss=True, category='table_body', type='value', value='#D3D3D3'), table_body_border_bottom_style=OptionsInfo(scss=True, category='table_body', type='value', value='solid'), table_body_border_bottom_width=OptionsInfo(scss=True, category='table_body', type='px', value='2px'), table_body_border_bottom_color=OptionsInfo(scss=True, category='table_body', type='value', value='#D3D3D3'), data_row_padding=OptionsInfo(scss=True, category='data_row', type='px', value='8px'), data_row_padding_horizontal=OptionsInfo(scss=True, category='data_row', type='px', value='5px'), stub_background_color=OptionsInfo(scss=True, category='stub', type='value', value=None), stub_font_size=OptionsInfo(scss=True, category='stub', type='px', value='100%'), stub_font_weight=OptionsInfo(scss=True, category='stub', type='value', value='initial'), stub_text_transform=OptionsInfo(scss=True, category='stub', type='value', value='inherit'), stub_border_style=OptionsInfo(scss=True, category='stub', type='value', value='solid'), stub_border_width=OptionsInfo(scss=True, category='stub', type='px', value='2px'), stub_border_color=OptionsInfo(scss=True, category='stub', type='value', value='#D3D3D3'), stub_row_group_background_color=OptionsInfo(scss=True, category='stub', type='value', value=None), stub_row_group_font_size=OptionsInfo(scss=True, category='stub', type='px', value='100%'), stub_row_group_font_weight=OptionsInfo(scss=True, category='stub', type='value', value='initial'), stub_row_group_text_transform=OptionsInfo(scss=True, category='stub', type='value', value='inherit'), stub_row_group_border_style=OptionsInfo(scss=True, category='stub', type='value', value='solid'), stub_row_group_border_width=OptionsInfo(scss=True, category='stub', type='px', value='2px'), stub_row_group_border_color=OptionsInfo(scss=True, category='stub', type='value', value='#D3D3D3'), source_notes_padding=OptionsInfo(scss=True, category='source_notes', type='px', value='4px'), source_notes_padding_horizontal=OptionsInfo(scss=True, category='source_notes', type='px', value='5px'), source_notes_background_color=OptionsInfo(scss=True, category='source_notes', type='value', value=None), source_notes_font_size=OptionsInfo(scss=True, category='source_notes', type='px', value='90%'), source_notes_border_bottom_style=OptionsInfo(scss=True, category='source_notes', type='value', value='none'), source_notes_border_bottom_width=OptionsInfo(scss=True, category='source_notes', type='px', value='2px'), source_notes_border_bottom_color=OptionsInfo(scss=True, category='source_notes', type='value', value='#D3D3D3'), source_notes_border_lr_style=OptionsInfo(scss=True, category='source_notes', type='value', value='none'), source_notes_border_lr_width=OptionsInfo(scss=True, category='source_notes', type='px', value='2px'), source_notes_border_lr_color=OptionsInfo(scss=True, category='source_notes', type='value', value='#D3D3D3'), source_notes_multiline=OptionsInfo(scss=False, category='source_notes', type='boolean', value=True), source_notes_sep=OptionsInfo(scss=False, category='source_notes', type='value', value=' '), row_striping_background_color=OptionsInfo(scss=True, category='row', type='value', value='rgba(128,128,128,0.05)'), row_striping_include_stub=OptionsInfo(scss=False, category='row', type='boolean', value=False), row_striping_include_table_body=OptionsInfo(scss=False, category='row', type='boolean', value=False), container_width=OptionsInfo(scss=False, category='container', type='px', value='auto'), container_height=OptionsInfo(scss=False, category='container', type='px', value='auto'), container_padding_x=OptionsInfo(scss=False, category='container', type='px', value='0px'), container_padding_y=OptionsInfo(scss=False, category='container', type='px', value='10px'), container_overflow_x=OptionsInfo(scss=False, category='container', type='overflow', value='auto'), container_overflow_y=OptionsInfo(scss=False, category='container', type='overflow', value='auto'), quarto_disable_processing=OptionsInfo(scss=False, category='quarto', type='logical', value=False), quarto_use_bootstrap=OptionsInfo(scss=False, category='quarto', type='logical', value=False)), _has_built=False)

21 reference:

https://duckdb.org/docs/stable/clients/r.html

https://duckdb.org/docs/stable/sql/statements

1 show all table

2 Describe a table

3 show column name

4 select

4.1 select top 6 and rename

4.2 select distinct

5 check row number and column number

6 create column

7 Filter

8 order

9 group by

10 create table

10.1 CREATE OR REPLACE

10.2 CREATE TABLE IF NOT EXISTS

11 unique

11.1 check unique

12 join

12.1 left join

12.2 inter join

13 append rows

13.1 append without duplicate elimination union all

13.2 append with duplicate elimination union

14 delete rows

15 update rows

16 drop table

17 PIVOT

17.1 PIVOT on one column

17.2 PIVOT on two column

18 unpivot

19 EXPLAIN

20 Display beautiful table

21 reference:

13.1 append without duplicate elimination `union all`

13.2 append with duplicate elimination `union`