-
Notifications
You must be signed in to change notification settings - Fork 28
/
Copy pathLesson3-table-join.html
367 lines (254 loc) · 16.1 KB
/
Lesson3-table-join.html
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
<head>
<meta charset="utf-8">
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-88382509-1']);
_gaq.push(['_trackPageview']);
(function() {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Table join — GeoPython - AutoGIS 1 documentation</title>
<link rel="stylesheet" href="_static/css/theme.css" type="text/css" />
<link rel="index" title="Index"
href="genindex.html"/>
<link rel="search" title="Search" href="search.html"/>
<link rel="top" title="GeoPython - AutoGIS 1 documentation" href="index.html"/>
<link rel="next" title="Re-projecting data" href="Lesson3-projections.html"/>
<link rel="prev" title="Geocoding" href="Lesson3-geocoding.html"/>
<script src="_static/js/modernizr.min.js"></script>
</head>
<body class="wy-body-for-nav" role="document">
<div class="wy-grid-for-nav">
<nav data-toggle="wy-nav-shift" class="wy-nav-side">
<div class="wy-side-scroll">
<div class="wy-side-nav-search">
<a href="index.html" class="icon icon-home"> GeoPython - AutoGIS
<img src="_static/logo_hy_geo_135.png" class="logo" />
</a>
<div class="version">
2016 Autumn
</div>
<div role="search">
<form id="rtd-search-form" class="wy-form" action="search.html" method="get">
<input type="text" name="q" placeholder="Search docs" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<p class="caption"><span class="caption-text">Course information</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="course-info.html">General info</a></li>
<li class="toctree-l1"><a class="reference internal" href="Installing_Anacondas_GIS.html">Installing Python + GIS</a></li>
<li class="toctree-l1"><a class="reference internal" href="License-terms.html">License and terms of usage</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 1</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson1-Intro-Python-GIS.html">Introduction to Python GIS</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson1-Geometric-Objects.html">Geometric Objects - Spatial Data Model</a></li>
<li class="toctree-l1"><a class="reference internal" href="Exercise-1.html">Exercise 1</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 2</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson2-overview.html">Lesson 2 Overview</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson2-download-data.html">Download datasets</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson2-overview-pandas-geopandas.html">Pandas and Geopandas -modules</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson2-pandas-intro.html">Introduction to Pandas</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson2-geopandas-basics.html">Introduction to Geopandas</a></li>
<li class="toctree-l1"><a class="reference internal" href="Exercise-2.html">Exercise 2</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 3</span></p>
<ul class="current">
<li class="toctree-l1"><a class="reference internal" href="Lesson3-overview.html">Lesson 3 Overview</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson3-geocoding.html">Geocoding</a></li>
<li class="toctree-l1 current"><a class="current reference internal" href="#">Table join</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson3-projections.html">Re-projecting data</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson3-point-in-polygon.html">Point in Polygon & Intersect</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson3-spatial-join.html">Spatial join</a></li>
<li class="toctree-l1"><a class="reference internal" href="Exercise-3.html">Exercise 3</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 4</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson4-overview.html">Lesson 4 Overview</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson4-download-data.html">Download datasets</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson4-geometric-operations.html">Geometric operations</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson4-reclassify.html">Data reclassification</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson4-nearest-neighbour.html">Nearest Neighbour Analysis</a></li>
<li class="toctree-l1"><a class="reference internal" href="Exercise-4.html">Exercise 4</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 5</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-overview.html">Lesson 5 Overview</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-download-data.html">Download datasets</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-static-maps.html">Static maps</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-interactive-map-bokeh.html">Interactive maps with Bokeh</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-share-on-github.html">Sharing interactive plots on GitHub</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-interactive-map-folium.html">Interactive maps on Leaflet</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson5-World-3D.html">Inspiration: World 3D</a></li>
<li class="toctree-l1"><a class="reference internal" href="Exercise-5.html">Exercise 5</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 6</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson6-overview.html">Lesson 6 Overview</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson6-arcpy.html">Python in ArcGIS</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson6-toolbox.html">ArcGIS Toolbox</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson6-arcpy-script.html">Writing arcpy scripts</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson6-run-the-tool.html">Running the Python script from ArcGIS</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 7</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson7-overview.html">Lesson 7 Overview</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson7-download.html">Download data</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson7-read-raster.html">Reading raster files with GDAL</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson7-read-raster-array.html">Reading raster as a numerical array</a></li>
<li class="toctree-l1"><a class="reference internal" href="Lesson7-gdal-utilities.html">GDAL command line tools</a></li>
<li class="toctree-l1"><a class="reference internal" href="Exercise-7.html">Exercise 7</a></li>
</ul>
<p class="caption"><span class="caption-text">Lesson 8</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Lesson8-network-analysis.html">Network analysis in Python</a></li>
</ul>
<p class="caption"><span class="caption-text">Final Assignment</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="Final-assignment.html">Final assignment</a></li>
</ul>
<p class="caption"><span class="caption-text">Map Challenge 2016</span></p>
<ul>
<li class="toctree-l1"><a class="reference internal" href="map-challenge.html">Map Challenge 2016</a></li>
</ul>
</div>
</div>
</nav>
<section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">
<nav class="wy-nav-top" role="navigation" aria-label="top navigation">
<i data-toggle="wy-nav-top" class="fa fa-bars"></i>
<a href="index.html">GeoPython - AutoGIS</a>
</nav>
<div class="wy-nav-content">
<div class="rst-content">
<div role="navigation" aria-label="breadcrumbs navigation">
<ul class="wy-breadcrumbs">
<li><a href="index.html">Docs</a> »</li>
<li>Table join</li>
<li class="wy-breadcrumbs-aside">
<a href="https://github.com/Automating-GIS-processes/2016/blob/master/source/Lesson3-table-join.rst" class="fa fa-github"> Edit on GitHub</a>
</li>
</ul>
<hr/>
</div>
<div role="main" class="document" itemscope="itemscope" itemtype="http://schema.org/Article">
<div itemprop="articleBody">
<div class="section" id="table-join">
<h1>Table join<a class="headerlink" href="#table-join" title="Permalink to this headline">¶</a></h1>
<p>Table joins are again something that you need to really frequently when
doing GIS analyses. Combining data from different tables based on common
<code class="docutils literal"><span class="pre">key</span></code> attribute can be done easily in Pandas/Geopandas using
<a class="reference external" href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html">.merge()</a>
-function.</p>
<ul class="simple">
<li>Let’s continue with our <a class="reference external" href="Lesson3-geocoding.html#geocoding-in-geopandas">geocoding task</a> and join the <code class="docutils literal"><span class="pre">data</span></code> and <code class="docutils literal"><span class="pre">geo</span></code> DataFrames together based on
common column <code class="docutils literal"><span class="pre">address</span></code>. Parameter <code class="docutils literal"><span class="pre">on</span></code> is used to determine the
common key in the tables. If your key in the first table would be
named differently than in the other one, you can also specify them
separately for each table by using <code class="docutils literal"><span class="pre">left_on</span></code> and <code class="docutils literal"><span class="pre">right_on</span></code>
-parameters.</li>
</ul>
<div class="highlight-ipython"><div class="highlight"><pre><span></span><span class="go"># Join tables by using a key column 'address'</span>
<span class="gp">In [1]: </span><span class="n">join</span> <span class="o">=</span> <span class="n">geo</span><span class="o">.</span><span class="n">merge</span><span class="p">(</span><span class="n">data</span><span class="p">,</span> <span class="n">on</span><span class="o">=</span><span class="s1">'address'</span><span class="p">)</span>
<span class="go"># Let's see what we have</span>
<span class="gp">In [2]: </span><span class="n">join</span><span class="o">.</span><span class="n">head</span><span class="p">()</span>
<span class="gh">Out[2]: </span><span class="go"></span>
<span class="go"> address \</span>
<span class="go">0 Kampinkuja 1, 00100 Helsinki, Finland </span>
<span class="go">1 Kaivokatu 8, 00101 Helsinki, Finland </span>
<span class="go">2 Hermanstads strandsväg 1, 00580 Helsingfors, F... </span>
<span class="go">3 Itäväylä, 00900 Helsinki, Finland </span>
<span class="go">4 Tyynenmerenkatu 9, 00220 Helsinki, Finland </span>
<span class="go"> geometry id </span>
<span class="go">0 POINT (24.9301701 60.1683731) 1001 </span>
<span class="go">1 POINT (24.9418933 60.1698665) 1002 </span>
<span class="go">2 POINT (24.9774004 60.18735880000001) 1003 </span>
<span class="go">3 POINT (25.0919641 60.21448089999999) 1004 </span>
<span class="go">4 POINT (24.9214846 60.1565781) 1005 </span>
</pre></div>
</div>
<ul class="simple">
<li>Let’s also check the data type of our new <code class="docutils literal"><span class="pre">join</span></code> table</li>
</ul>
<div class="highlight-ipython"><div class="highlight"><pre><span></span><span class="gp">In [3]: </span><span class="nb">type</span><span class="p">(</span><span class="n">join</span><span class="p">)</span>
<span class="gh">Out[3]: </span><span class="go">geopandas.geodataframe.GeoDataFrame</span>
</pre></div>
</div>
<p>As a result we have a new GeoDataFrame called <code class="docutils literal"><span class="pre">join</span></code> where we now have
all original columns plus a new column for <code class="docutils literal"><span class="pre">geometry</span></code>.</p>
<ul class="simple">
<li>Now it is easy to save our address points into a Shapefile</li>
</ul>
<div class="code python highlight-default"><div class="highlight"><pre><span></span><span class="c1"># Output file path</span>
<span class="n">outfp</span> <span class="o">=</span> <span class="s2">r"/home/geo/addresses.shp"</span>
<span class="c1"># Save to Shapefile</span>
<span class="n">join</span><span class="o">.</span><span class="n">to_file</span><span class="p">(</span><span class="n">outfp</span><span class="p">)</span>
</pre></div>
</div>
<p>That’s it. Now we have successfully geocoded those addresses into Points
and made a Shapefile out of them.</p>
<div class="admonition-todo admonition" id="index-0">
<p class="first admonition-title">Todo</p>
<p><strong>Task</strong>:</p>
<p class="last">Make a map out of the points. What do you think that the
addresses are representing?</p>
</div>
</div>
</div>
</div>
<footer>
<div class="rst-footer-buttons" role="navigation" aria-label="footer navigation">
<a href="Lesson3-projections.html" class="btn btn-neutral float-right" title="Re-projecting data" accesskey="n">Next <span class="fa fa-arrow-circle-right"></span></a>
<a href="Lesson3-geocoding.html" class="btn btn-neutral" title="Geocoding" accesskey="p"><span class="fa fa-arrow-circle-left"></span> Previous</a>
</div>
<hr/>
<div role="contentinfo">
<p>
© Copyright 2016, Henrikki Tenkanen.
Last updated on Feb 20, 2017.
</p>
</div>
Built with <a href="http://sphinx-doc.org/">Sphinx</a> using a <a href="https://github.com/snide/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>.
</footer>
</div>
</div>
</section>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT:'./',
VERSION:'1',
COLLAPSE_INDEX:false,
FILE_SUFFIX:'.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<script type="text/javascript" src="https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script>
<script type="text/javascript" src="_static/js/theme.js"></script>
<script type="text/javascript">
jQuery(function () {
SphinxRtdTheme.StickyNav.enable();
});
</script>
<li><a href="http://project.invalid/">Project Homepage</a> »</li>
<div class="footer">
<img src="../img/GPLv3_Logo.svg">
</div>
</body>
</html>