1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
|
from __future__ import absolute_import, print_function, division
# join()
########
import petl as etl
table1 = [['id', 'colour'],
[1, 'blue'],
[2, 'red'],
[3, 'purple']]
table2 = [['id', 'shape'],
[1, 'circle'],
[3, 'square'],
[4, 'ellipse']]
table3 = etl.join(table1, table2, key='id')
table3
# if no key is given, a natural join is tried
table4 = etl.join(table1, table2)
table4
# note behaviour if the key is not unique in either or both tables
table5 = [['id', 'colour'],
[1, 'blue'],
[1, 'red'],
[2, 'purple']]
table6 = [['id', 'shape'],
[1, 'circle'],
[1, 'square'],
[2, 'ellipse']]
table7 = etl.join(table5, table6, key='id')
table7
# compound keys are supported
table8 = [['id', 'time', 'height'],
[1, 1, 12.3],
[1, 2, 34.5],
[2, 1, 56.7]]
table9 = [['id', 'time', 'weight'],
[1, 2, 4.5],
[2, 1, 6.7],
[2, 2, 8.9]]
table10 = etl.join(table8, table9, key=['id', 'time'])
table10
# leftjoin()
############
import petl as etl
table1 = [['id', 'colour'],
[1, 'blue'],
[2, 'red'],
[3, 'purple']]
table2 = [['id', 'shape'],
[1, 'circle'],
[3, 'square'],
[4, 'ellipse']]
table3 = etl.leftjoin(table1, table2, key='id')
table3
# rightjoin()
#############
import petl as etl
table1 = [['id', 'colour'],
[1, 'blue'],
[2, 'red'],
[3, 'purple']]
table2 = [['id', 'shape'],
[1, 'circle'],
[3, 'square'],
[4, 'ellipse']]
table3 = etl.rightjoin(table1, table2, key='id')
table3
# outerjoin()
#############
import petl as etl
table1 = [['id', 'colour'],
[1, 'blue'],
[2, 'red'],
[3, 'purple']]
table2 = [['id', 'shape'],
[1, 'circle'],
[3, 'square'],
[4, 'ellipse']]
table3 = etl.outerjoin(table1, table2, key='id')
table3
# crossjoin()
#############
import petl as etl
table1 = [['id', 'colour'],
[1, 'blue'],
[2, 'red']]
table2 = [['id', 'shape'],
[1, 'circle'],
[3, 'square']]
table3 = etl.crossjoin(table1, table2)
table3
# antijoin()
############
import petl as etl
table1 = [['id', 'colour'],
[0, 'black'],
[1, 'blue'],
[2, 'red'],
[4, 'yellow'],
[5, 'white']]
table2 = [['id', 'shape'],
[1, 'circle'],
[3, 'square']]
table3 = etl.antijoin(table1, table2, key='id')
table3
# lookupjoin()
##############
import petl as etl
table1 = [['id', 'color', 'cost'],
[1, 'blue', 12],
[2, 'red', 8],
[3, 'purple', 4]]
table2 = [['id', 'shape', 'size'],
[1, 'circle', 'big'],
[1, 'circle', 'small'],
[2, 'square', 'tiny'],
[2, 'square', 'big'],
[3, 'ellipse', 'small'],
[3, 'ellipse', 'tiny']]
table3 = etl.lookupjoin(table1, table2, key='id')
table3
# unjoin()
##########
import petl as etl
# join key is present in the table
table1 = (('foo', 'bar', 'baz'),
('A', 1, 'apple'),
('B', 1, 'apple'),
('C', 2, 'orange'))
table2, table3 = etl.unjoin(table1, 'baz', key='bar')
table2
table3
# an integer join key can also be reconstructed
table4 = (('foo', 'bar'),
('A', 'apple'),
('B', 'apple'),
('C', 'orange'))
table5, table6 = etl.unjoin(table4, 'bar')
table5
table6
|
