In [87]:
import pandas as pd
import MySQLdb
In [116]:
server = 'ec2-54-149-163-97.us-west-2.compute.amazonaws.com'
In [117]:
db = MySQLdb.connect(server, 'root','test1234', 'world')
In [90]:
SQL_QUERY = '''
SELECT *
FROM country
'''
country_df = pd.read_sql(SQL_QUERY, db)
In [13]:
country_df
Out[13]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
0
ABW
Aruba
North America
Caribbean
193.0
NaN
103000
78.4
828.0
793.0
Aruba
Nonmetropolitan Territory of The Netherlands
Beatrix
129.0
AW
1
AFG
Afghanistan
Asia
Southern and Central Asia
652090.0
1919.0
22720000
45.9
5976.0
NaN
Afganistan/Afqanestan
Islamic Emirate
Mohammad Omar
1.0
AF
2
AGO
Angola
Africa
Central Africa
1246700.0
1975.0
12878000
38.3
6648.0
7984.0
Angola
Republic
José Eduardo dos Santos
56.0
AO
3
AIA
Anguilla
North America
Caribbean
96.0
NaN
8000
76.1
63.2
NaN
Anguilla
Dependent Territory of the UK
Elisabeth II
62.0
AI
4
ALB
Albania
Europe
Southern Europe
28748.0
1912.0
3401200
71.6
3205.0
2500.0
Shqipëria
Republic
Rexhep Mejdani
34.0
AL
5
AND
Andorra
Europe
Southern Europe
468.0
1278.0
78000
83.5
1630.0
NaN
Andorra
Parliamentary Coprincipality
55.0
AD
6
ANT
Netherlands Antilles
North America
Caribbean
800.0
NaN
217000
74.7
1941.0
NaN
Nederlandse Antillen
Nonmetropolitan Territory of The Netherlands
Beatrix
33.0
AN
7
ARE
United Arab Emirates
Asia
Middle East
83600.0
1971.0
2441000
74.1
37966.0
36846.0
Al-Imarat al-´Arabiya al-Muttahida
Emirate Federation
Zayid bin Sultan al-Nahayan
65.0
AE
8
ARG
Argentina
South America
South America
2780400.0
1816.0
37032000
75.1
340238.0
323310.0
Argentina
Federal Republic
Fernando de la Rúa
69.0
AR
9
ARM
Armenia
Asia
Middle East
29800.0
1991.0
3520000
66.4
1813.0
1627.0
Hajastan
Republic
Robert Kotšarjan
126.0
AM
10
ASM
American Samoa
Oceania
Polynesia
199.0
NaN
68000
75.1
334.0
NaN
Amerika Samoa
US Territory
George W. Bush
54.0
AS
11
ATA
Antarctica
Antarctica
Antarctica
13120000.0
NaN
0
NaN
0.0
NaN
–
Co-administrated
NaN
AQ
12
ATF
French Southern territories
Antarctica
Antarctica
7780.0
NaN
0
NaN
0.0
NaN
Terres australes françaises
Nonmetropolitan Territory of France
Jacques Chirac
NaN
TF
13
ATG
Antigua and Barbuda
North America
Caribbean
442.0
1981.0
68000
70.5
612.0
584.0
Antigua and Barbuda
Constitutional Monarchy
Elisabeth II
63.0
AG
14
AUS
Australia
Oceania
Australia and New Zealand
7741220.0
1901.0
18886000
79.8
351182.0
392911.0
Australia
Constitutional Monarchy, Federation
Elisabeth II
135.0
AU
15
AUT
Austria
Europe
Western Europe
83859.0
1918.0
8091800
77.7
211860.0
206025.0
Österreich
Federal Republic
Thomas Klestil
1523.0
AT
16
AZE
Azerbaijan
Asia
Middle East
86600.0
1991.0
7734000
62.9
4127.0
4100.0
Azärbaycan
Federal Republic
Heydär Äliyev
144.0
AZ
17
BDI
Burundi
Africa
Eastern Africa
27834.0
1962.0
6695000
46.2
903.0
982.0
Burundi/Uburundi
Republic
Pierre Buyoya
552.0
BI
18
BEL
Belgium
Europe
Western Europe
30518.0
1830.0
10239000
77.8
249704.0
243948.0
België/Belgique
Constitutional Monarchy, Federation
Albert II
179.0
BE
19
BEN
Benin
Africa
Western Africa
112622.0
1960.0
6097000
50.2
2357.0
2141.0
Bénin
Republic
Mathieu Kérékou
187.0
BJ
20
BFA
Burkina Faso
Africa
Western Africa
274000.0
1960.0
11937000
46.7
2425.0
2201.0
Burkina Faso
Republic
Blaise Compaoré
549.0
BF
21
BGD
Bangladesh
Asia
Southern and Central Asia
143998.0
1971.0
129155000
60.2
32852.0
31966.0
Bangladesh
Republic
Shahabuddin Ahmad
150.0
BD
22
BGR
Bulgaria
Europe
Eastern Europe
110994.0
1908.0
8190900
70.9
12178.0
10169.0
Balgarija
Republic
Petar Stojanov
539.0
BG
23
BHR
Bahrain
Asia
Middle East
694.0
1971.0
617000
73.0
6366.0
6097.0
Al-Bahrayn
Monarchy (Emirate)
Hamad ibn Isa al-Khalifa
149.0
BH
24
BHS
Bahamas
North America
Caribbean
13878.0
1973.0
307000
71.1
3527.0
3347.0
The Bahamas
Constitutional Monarchy
Elisabeth II
148.0
BS
25
BIH
Bosnia and Herzegovina
Europe
Southern Europe
51197.0
1992.0
3972000
71.5
2841.0
NaN
Bosna i Hercegovina
Federal Republic
Ante Jelavic
201.0
BA
26
BLR
Belarus
Europe
Eastern Europe
207600.0
1991.0
10236000
68.0
13714.0
NaN
Belarus
Republic
Aljaksandr Lukašenka
3520.0
BY
27
BLZ
Belize
North America
Central America
22696.0
1981.0
241000
70.9
630.0
616.0
Belize
Constitutional Monarchy
Elisabeth II
185.0
BZ
28
BMU
Bermuda
North America
North America
53.0
NaN
65000
76.9
2328.0
2190.0
Bermuda
Dependent Territory of the UK
Elisabeth II
191.0
BM
29
BOL
Bolivia
South America
South America
1098581.0
1825.0
8329000
63.7
8571.0
7967.0
Bolivia
Republic
Hugo Bánzer Suárez
194.0
BO
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
209
TKL
Tokelau
Oceania
Polynesia
12.0
NaN
2000
NaN
0.0
NaN
Tokelau
Nonmetropolitan Territory of New Zealand
Elisabeth II
3333.0
TK
210
TKM
Turkmenistan
Asia
Southern and Central Asia
488100.0
1991.0
4459000
60.9
4397.0
2000.0
Türkmenostan
Republic
Saparmurad Nijazov
3419.0
TM
211
TMP
East Timor
Asia
Southeast Asia
14874.0
NaN
885000
46.0
0.0
NaN
Timor Timur
Administrated by the UN
José Alexandre Gusmão
1522.0
TP
212
TON
Tonga
Oceania
Polynesia
650.0
1970.0
99000
67.9
146.0
170.0
Tonga
Monarchy
Taufa'ahau Tupou IV
3334.0
TO
213
TTO
Trinidad and Tobago
North America
Caribbean
5130.0
1962.0
1295000
68.0
6232.0
5867.0
Trinidad and Tobago
Republic
Arthur N. R. Robinson
3336.0
TT
214
TUN
Tunisia
Africa
Northern Africa
163610.0
1956.0
9586000
73.7
20026.0
18898.0
Tunis/Tunisie
Republic
Zine al-Abidine Ben Ali
3349.0
TN
215
TUR
Turkey
Asia
Middle East
774815.0
1923.0
66591000
71.0
210721.0
189122.0
Türkiye
Republic
Ahmet Necdet Sezer
3358.0
TR
216
TUV
Tuvalu
Oceania
Polynesia
26.0
1978.0
12000
66.3
6.0
NaN
Tuvalu
Constitutional Monarchy
Elisabeth II
3424.0
TV
217
TWN
Taiwan
Asia
Eastern Asia
36188.0
1945.0
22256000
76.4
256254.0
263451.0
TÂ’ai-wan
Republic
Chen Shui-bian
3263.0
TW
218
TZA
Tanzania
Africa
Eastern Africa
883749.0
1961.0
33517000
52.3
8005.0
7388.0
Tanzania
Republic
Benjamin William Mkapa
3306.0
TZ
219
UGA
Uganda
Africa
Eastern Africa
241038.0
1962.0
21778000
42.9
6313.0
6887.0
Uganda
Republic
Yoweri Museveni
3425.0
UG
220
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991.0
50456000
66.0
42168.0
49677.0
Ukrajina
Republic
Leonid Kutšma
3426.0
UA
221
UMI
United States Minor Outlying Islands
Oceania
Micronesia/Caribbean
16.0
NaN
0
NaN
0.0
NaN
United States Minor Outlying Islands
Dependent Territory of the US
George W. Bush
NaN
UM
222
URY
Uruguay
South America
South America
175016.0
1828.0
3337000
75.2
20831.0
19967.0
Uruguay
Republic
Jorge Batlle Ibáñez
3492.0
UY
223
USA
United States
North America
North America
9363520.0
1776.0
278357000
77.1
8510700.0
8110900.0
United States
Federal Republic
George W. Bush
3813.0
US
224
UZB
Uzbekistan
Asia
Southern and Central Asia
447400.0
1991.0
24318000
63.7
14194.0
21300.0
Uzbekiston
Republic
Islam Karimov
3503.0
UZ
225
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929.0
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538.0
VA
226
VCT
Saint Vincent and the Grenadines
North America
Caribbean
388.0
1979.0
114000
72.3
285.0
NaN
Saint Vincent and the Grenadines
Constitutional Monarchy
Elisabeth II
3066.0
VC
227
VEN
Venezuela
South America
South America
912050.0
1811.0
24170000
73.1
95023.0
88434.0
Venezuela
Federal Republic
Hugo Chávez FrÃas
3539.0
VE
228
VGB
Virgin Islands, British
North America
Caribbean
151.0
NaN
21000
75.4
612.0
573.0
British Virgin Islands
Dependent Territory of the UK
Elisabeth II
537.0
VG
229
VIR
Virgin Islands, U.S.
North America
Caribbean
347.0
NaN
93000
78.1
0.0
NaN
Virgin Islands of the United States
US Territory
George W. Bush
4067.0
VI
230
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945.0
79832000
69.3
21929.0
22834.0
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770.0
VN
231
VUT
Vanuatu
Oceania
Melanesia
12189.0
1980.0
190000
60.6
261.0
246.0
Vanuatu
Republic
John Bani
3537.0
VU
232
WLF
Wallis and Futuna
Oceania
Polynesia
200.0
NaN
15000
NaN
0.0
NaN
Wallis-et-Futuna
Nonmetropolitan Territory of France
Jacques Chirac
3536.0
WF
233
WSM
Samoa
Oceania
Polynesia
2831.0
1962.0
180000
69.2
141.0
157.0
Samoa
Parlementary Monarchy
Malietoa Tanumafili II
3169.0
WS
234
YEM
Yemen
Asia
Middle East
527968.0
1918.0
18112000
59.8
6041.0
5729.0
Al-Yaman
Republic
Ali Abdallah Salih
1780.0
YE
235
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918.0
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792.0
YU
236
ZAF
South Africa
Africa
Southern Africa
1221037.0
1910.0
40377000
51.1
116729.0
129092.0
South Africa
Republic
Thabo Mbeki
716.0
ZA
237
ZMB
Zambia
Africa
Eastern Africa
752618.0
1964.0
9169000
37.2
3377.0
3922.0
Zambia
Republic
Frederick Chiluba
3162.0
ZM
238
ZWE
Zimbabwe
Africa
Eastern Africa
390757.0
1980.0
11669000
37.8
5951.0
8670.0
Zimbabwe
Republic
Robert G. Mugabe
4068.0
ZW
239 rows × 15 columns
In [10]:
'''
주석, docstring => 파이썬 코드를 문서화
'''
a = '''
Multiline string
'''
In [ ]:
# SQL, (structured Query Language; 구조화된 질의 언어 == python programming Language)
In [ ]:
SQL_QUERY = '''
SELECT ______ # 어떤 column, 혹은 어떤 값을 DB 가져올까
FROM _______ # 어떤 Table( Excel, Sheet) 에서 정보를 가져올까
;
'''
In [19]:
# country => name, population, continent
SQL_QUERY = '''
SELECT Name, Population, Continent
FROM country
;
'''
df = pd.read_sql(SQL_QUERY, db)
df.tail()
Out[19]:
Name
Population
Continent
234
Yemen
18112000
Asia
235
Yugoslavia
10640000
Europe
236
South Africa
40377000
Africa
237
Zambia
9169000
Africa
238
Zimbabwe
11669000
Africa
In [22]:
npc_df = country_df[['Name', 'Population', 'Continent']].tail()
Out[22]:
Name
Population
Continent
234
Yemen
18112000
Asia
235
Yugoslavia
10640000
Europe
236
South Africa
40377000
Africa
237
Zambia
9169000
Africa
238
Zimbabwe
11669000
Africa
In [26]:
# NAme => 이름
# Population => 인구수
# Continent => 대륙
npc_df = country_df[['Name', 'Population', 'Continent']].tail()
npc_df.rename(columns = {'Name': '이름', 'Population': '인구수', 'Continent': '대륙'}).tail()
Out[26]:
이름
인구수
대륙
234
Yemen
18112000
Asia
235
Yugoslavia
10640000
Europe
236
South Africa
40377000
Africa
237
Zambia
9169000
Africa
238
Zimbabwe
11669000
Africa
In [29]:
# SELECT _______ FROM ______:
# SELECT ____ (AS) ______ : rename
SQL_QUERY = '''
SELECT Name '이름', Population '인구수', Continent '대륙'
FROM country
;
'''
pd.read_sql(SQL_QUERY, db).tail()
Out[29]:
이름
인구수
대륙
234
Yemen
18112000
Asia
235
Yugoslavia
10640000
Europe
236
South Africa
40377000
Africa
237
Zambia
9169000
Africa
238
Zimbabwe
11669000
Africa
In [32]:
len(country_df)
#country_df.count()
Out[32]:
239
In [35]:
SQL_QUERY = '''
SELECT COUNT(*) 'count'
FROM country
;
'''
pd.read_sql(SQL_QUERY, db).tail()
Out[35]:
count
0
239
In [36]:
import time
# pandas vs sql 어떤게 효율적인가 // 연산에는 최적화 되어있지만 네트워크에 대한 비용이 많이듬
In [46]:
start_time = time.time()
count = len(pd.read_sql('SELECT * FROM country;', db))
print count
end_time = time.time()
execute_time = end_time - start_time
print execute_time
239
0.4359998703
In [59]:
start_time = time.time()
pd.read_sql('SELECT COUNT(*) FROM country;', db)
print count
end_time = time.time()
execute_time = end_time - start_time
print execute_time
239
0.212000131607
In [ ]:
# 조건에 따른 Filtering
In [68]:
# Countinent == Europe, Population > 10,000,000
is_europe = country_df['Continent'] == 'Europe'
is_population = country_df['Population'] > 10000000
country_df[is_europe][is_population].tail()
c:\python27\lib\site-packages\ipykernel\__main__.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
Out[68]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
171
POL
Poland
Europe
Eastern Europe
323250.0
1918.0
38653600
73.2
151697.0
135636.0
Polska
Republic
Aleksander Kwasniewski
2928.0
PL
180
ROM
Romania
Europe
Eastern Europe
238391.0
1878.0
22455500
69.9
38158.0
34843.0
România
Republic
Ion Iliescu
3018.0
RO
181
RUS
Russian Federation
Europe
Eastern Europe
17075400.0
1991.0
146934000
67.2
276608.0
442989.0
Rossija
Federal Republic
Vladimir Putin
3580.0
RU
220
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991.0
50456000
66.0
42168.0
49677.0
Ukrajina
Republic
Leonid Kutšma
3426.0
UA
235
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918.0
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792.0
YU
In [67]:
SQL_QUERY = '''
SELECT *
FROM country
WHERE Continent = 'Europe' AND Population > 10000000
;
'''
pd.read_sql(SQL_QUERY, db).tail()
Out[67]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
11
POL
Poland
Europe
Eastern Europe
323250.0
1918
38653600
73.2
151697.0
135636.0
Polska
Republic
Aleksander Kwasniewski
2928
PL
12
ROM
Romania
Europe
Eastern Europe
238391.0
1878
22455500
69.9
38158.0
34843.0
România
Republic
Ion Iliescu
3018
RO
13
RUS
Russian Federation
Europe
Eastern Europe
17075400.0
1991
146934000
67.2
276608.0
442989.0
Rossija
Federal Republic
Vladimir Putin
3580
RU
14
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991
50456000
66.0
42168.0
49677.0
Ukrajina
Republic
Leonid Kutšma
3426
UA
15
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792
YU
In [ ]:
# Asia나 혹은 Europe 에 속하는 국가
In [80]:
# pandas
is_Europe = country_df['Continent'] == 'Europe'
is_Asia = country_df['Continent'] == 'Asia'
country_df[is_Europe | is_Asia] #series 에서는 or and 이 안먹음
Out[80]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
1
AFG
Afghanistan
Asia
Southern and Central Asia
652090.0
1919.0
22720000
45.9
5976.0
NaN
Afganistan/Afqanestan
Islamic Emirate
Mohammad Omar
1.0
AF
4
ALB
Albania
Europe
Southern Europe
28748.0
1912.0
3401200
71.6
3205.0
2500.0
Shqipëria
Republic
Rexhep Mejdani
34.0
AL
5
AND
Andorra
Europe
Southern Europe
468.0
1278.0
78000
83.5
1630.0
NaN
Andorra
Parliamentary Coprincipality
55.0
AD
7
ARE
United Arab Emirates
Asia
Middle East
83600.0
1971.0
2441000
74.1
37966.0
36846.0
Al-Imarat al-´Arabiya al-Muttahida
Emirate Federation
Zayid bin Sultan al-Nahayan
65.0
AE
9
ARM
Armenia
Asia
Middle East
29800.0
1991.0
3520000
66.4
1813.0
1627.0
Hajastan
Republic
Robert Kotšarjan
126.0
AM
15
AUT
Austria
Europe
Western Europe
83859.0
1918.0
8091800
77.7
211860.0
206025.0
Österreich
Federal Republic
Thomas Klestil
1523.0
AT
16
AZE
Azerbaijan
Asia
Middle East
86600.0
1991.0
7734000
62.9
4127.0
4100.0
Azärbaycan
Federal Republic
Heydär Äliyev
144.0
AZ
18
BEL
Belgium
Europe
Western Europe
30518.0
1830.0
10239000
77.8
249704.0
243948.0
België/Belgique
Constitutional Monarchy, Federation
Albert II
179.0
BE
21
BGD
Bangladesh
Asia
Southern and Central Asia
143998.0
1971.0
129155000
60.2
32852.0
31966.0
Bangladesh
Republic
Shahabuddin Ahmad
150.0
BD
22
BGR
Bulgaria
Europe
Eastern Europe
110994.0
1908.0
8190900
70.9
12178.0
10169.0
Balgarija
Republic
Petar Stojanov
539.0
BG
23
BHR
Bahrain
Asia
Middle East
694.0
1971.0
617000
73.0
6366.0
6097.0
Al-Bahrayn
Monarchy (Emirate)
Hamad ibn Isa al-Khalifa
149.0
BH
25
BIH
Bosnia and Herzegovina
Europe
Southern Europe
51197.0
1992.0
3972000
71.5
2841.0
NaN
Bosna i Hercegovina
Federal Republic
Ante Jelavic
201.0
BA
26
BLR
Belarus
Europe
Eastern Europe
207600.0
1991.0
10236000
68.0
13714.0
NaN
Belarus
Republic
Aljaksandr Lukašenka
3520.0
BY
32
BRN
Brunei
Asia
Southeast Asia
5765.0
1984.0
328000
73.6
11705.0
12460.0
Brunei Darussalam
Monarchy (Sultanate)
Haji Hassan al-Bolkiah
538.0
BN
33
BTN
Bhutan
Asia
Southern and Central Asia
47000.0
1910.0
2124000
52.4
372.0
383.0
Druk-Yul
Monarchy
Jigme Singye Wangchuk
192.0
BT
39
CHE
Switzerland
Europe
Western Europe
41284.0
1499.0
7160400
79.6
264478.0
256092.0
Schweiz/Suisse/Svizzera/Svizra
Federation
Adolf Ogi
3248.0
CH
41
CHN
China
Asia
Eastern Asia
9572900.0
-1523.0
1277558000
71.4
982268.0
917719.0
Zhongquo
People'sRepublic
Jiang Zemin
1891.0
CN
54
CYP
Cyprus
Asia
Middle East
9251.0
1960.0
754700
76.7
9333.0
8246.0
Kýpros/Kibris
Republic
Glafkos Klerides
2430.0
CY
55
CZE
Czech Republic
Europe
Eastern Europe
78866.0
1993.0
10278100
74.5
55017.0
52037.0
¸esko
Republic
Václav Havel
3339.0
CZ
56
DEU
Germany
Europe
Western Europe
357022.0
1955.0
82164700
77.4
2133367.0
2102826.0
Deutschland
Federal Republic
Johannes Rau
3068.0
DE
59
DNK
Denmark
Europe
Nordic Countries
43094.0
800.0
5330000
76.5
174099.0
169264.0
Danmark
Constitutional Monarchy
Margrethe II
3315.0
DK
66
ESP
Spain
Europe
Southern Europe
505992.0
1492.0
39441700
78.8
553233.0
532031.0
España
Constitutional Monarchy
Juan Carlos I
653.0
ES
67
EST
Estonia
Europe
Baltic Countries
45227.0
1991.0
1439200
69.5
5328.0
3371.0
Eesti
Republic
Lennart Meri
3791.0
EE
69
FIN
Finland
Europe
Nordic Countries
338145.0
1917.0
5171300
77.4
121914.0
119833.0
Suomi
Republic
Tarja Halonen
3236.0
FI
72
FRA
France
Europe
Western Europe
551500.0
843.0
59225700
78.8
1424285.0
1392448.0
France
Republic
Jacques Chirac
2974.0
FR
73
FRO
Faroe Islands
Europe
Nordic Countries
1399.0
NaN
43000
78.4
0.0
NaN
Føroyar
Part of Denmark
Margrethe II
901.0
FO
76
GBR
United Kingdom
Europe
British Islands
242900.0
1066.0
59623400
77.7
1378330.0
1296830.0
United Kingdom
Constitutional Monarchy
Elisabeth II
456.0
GB
77
GEO
Georgia
Asia
Middle East
69700.0
1991.0
4968000
64.5
6064.0
5924.0
Sakartvelo
Republic
Eduard Ševardnadze
905.0
GE
79
GIB
Gibraltar
Europe
Southern Europe
6.0
NaN
25000
79.0
258.0
NaN
Gibraltar
Dependent Territory of the UK
Elisabeth II
915.0
GI
85
GRC
Greece
Europe
Southern Europe
131626.0
1830.0
10545700
78.4
120724.0
119946.0
Elláda
Republic
Kostis Stefanopoulos
2401.0
GR
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
163
OMN
Oman
Asia
Middle East
309500.0
1951.0
2542000
71.8
16904.0
16153.0
´Uman
Monarchy (Sultanate)
Qabus ibn Sa´id
2821.0
OM
164
PAK
Pakistan
Asia
Southern and Central Asia
796095.0
1947.0
156483000
61.1
61289.0
58549.0
Pakistan
Republic
Mohammad Rafiq Tarar
2831.0
PK
168
PHL
Philippines
Asia
Southeast Asia
300000.0
1946.0
75967000
67.5
65107.0
82239.0
Pilipinas
Republic
Gloria Macapagal-Arroyo
766.0
PH
171
POL
Poland
Europe
Eastern Europe
323250.0
1918.0
38653600
73.2
151697.0
135636.0
Polska
Republic
Aleksander Kwasniewski
2928.0
PL
173
PRK
North Korea
Asia
Eastern Asia
120538.0
1948.0
24039000
70.7
5332.0
NaN
Choson Minjujuui In´min Konghwaguk (Bukhan)
Socialistic Republic
Kim Jong-il
2318.0
KP
174
PRT
Portugal
Europe
Southern Europe
91982.0
1143.0
9997600
75.8
105954.0
102133.0
Portugal
Republic
Jorge Sampãio
2914.0
PT
176
PSE
Palestine
Asia
Middle East
6257.0
NaN
3101000
71.4
4173.0
NaN
Filastin
Autonomous Area
Yasser (Yasir) Arafat
4074.0
PS
178
QAT
Qatar
Asia
Middle East
11000.0
1971.0
599000
72.4
9472.0
8920.0
Qatar
Monarchy
Hamad ibn Khalifa al-Thani
2973.0
QA
180
ROM
Romania
Europe
Eastern Europe
238391.0
1878.0
22455500
69.9
38158.0
34843.0
România
Republic
Ion Iliescu
3018.0
RO
181
RUS
Russian Federation
Europe
Eastern Europe
17075400.0
1991.0
146934000
67.2
276608.0
442989.0
Rossija
Federal Republic
Vladimir Putin
3580.0
RU
183
SAU
Saudi Arabia
Asia
Middle East
2149690.0
1932.0
21607000
67.8
137635.0
146171.0
Al-´Arabiya as-Sa´udiya
Monarchy
Fahd ibn Abdul-Aziz al-Sa´ud
3173.0
SA
186
SGP
Singapore
Asia
Southeast Asia
618.0
1965.0
3567000
80.1
86503.0
96318.0
Singapore/Singapura/Xinjiapo/Singapur
Republic
Sellapan Rama Nathan
3208.0
SG
189
SJM
Svalbard and Jan Mayen
Europe
Nordic Countries
62422.0
NaN
3200
NaN
0.0
NaN
Svalbard og Jan Mayen
Dependent Territory of Norway
Harald V
938.0
SJ
193
SMR
San Marino
Europe
Southern Europe
61.0
885.0
27000
81.1
510.0
NaN
San Marino
Republic
None
3171.0
SM
198
SVK
Slovakia
Europe
Eastern Europe
49012.0
1993.0
5398700
73.7
20594.0
19452.0
Slovensko
Republic
Rudolf Schuster
3209.0
SK
199
SVN
Slovenia
Europe
Southern Europe
20256.0
1991.0
1987800
74.9
19756.0
18202.0
Slovenija
Republic
Milan Kucan
3212.0
SI
200
SWE
Sweden
Europe
Nordic Countries
449964.0
836.0
8861400
79.6
226492.0
227757.0
Sverige
Constitutional Monarchy
Carl XVI Gustaf
3048.0
SE
203
SYR
Syria
Asia
Middle East
185180.0
1941.0
16125000
68.5
65984.0
64926.0
Suriya
Republic
Bashar al-Assad
3250.0
SY
207
THA
Thailand
Asia
Southeast Asia
513115.0
1350.0
61399000
68.6
116416.0
153907.0
Prathet Thai
Constitutional Monarchy
Bhumibol Adulyadej
3320.0
TH
208
TJK
Tajikistan
Asia
Southern and Central Asia
143100.0
1991.0
6188000
64.1
1990.0
1056.0
Toçikiston
Republic
Emomali Rahmonov
3261.0
TJ
210
TKM
Turkmenistan
Asia
Southern and Central Asia
488100.0
1991.0
4459000
60.9
4397.0
2000.0
Türkmenostan
Republic
Saparmurad Nijazov
3419.0
TM
211
TMP
East Timor
Asia
Southeast Asia
14874.0
NaN
885000
46.0
0.0
NaN
Timor Timur
Administrated by the UN
José Alexandre Gusmão
1522.0
TP
215
TUR
Turkey
Asia
Middle East
774815.0
1923.0
66591000
71.0
210721.0
189122.0
Türkiye
Republic
Ahmet Necdet Sezer
3358.0
TR
217
TWN
Taiwan
Asia
Eastern Asia
36188.0
1945.0
22256000
76.4
256254.0
263451.0
TÂ’ai-wan
Republic
Chen Shui-bian
3263.0
TW
220
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991.0
50456000
66.0
42168.0
49677.0
Ukrajina
Republic
Leonid Kutšma
3426.0
UA
224
UZB
Uzbekistan
Asia
Southern and Central Asia
447400.0
1991.0
24318000
63.7
14194.0
21300.0
Uzbekiston
Republic
Islam Karimov
3503.0
UZ
225
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929.0
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538.0
VA
230
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945.0
79832000
69.3
21929.0
22834.0
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770.0
VN
234
YEM
Yemen
Asia
Middle East
527968.0
1918.0
18112000
59.8
6041.0
5729.0
Al-Yaman
Republic
Ali Abdallah Salih
1780.0
YE
235
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918.0
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792.0
YU
97 rows × 15 columns
In [83]:
# pandas 이건생각못함
is_asia_or_europe = country_df["Continent"].isin(['Asia', 'Europe'])
country_df[is_asia_or_europe]
Out[83]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
1
AFG
Afghanistan
Asia
Southern and Central Asia
652090.0
1919.0
22720000
45.9
5976.0
NaN
Afganistan/Afqanestan
Islamic Emirate
Mohammad Omar
1.0
AF
4
ALB
Albania
Europe
Southern Europe
28748.0
1912.0
3401200
71.6
3205.0
2500.0
Shqipëria
Republic
Rexhep Mejdani
34.0
AL
5
AND
Andorra
Europe
Southern Europe
468.0
1278.0
78000
83.5
1630.0
NaN
Andorra
Parliamentary Coprincipality
55.0
AD
7
ARE
United Arab Emirates
Asia
Middle East
83600.0
1971.0
2441000
74.1
37966.0
36846.0
Al-Imarat al-´Arabiya al-Muttahida
Emirate Federation
Zayid bin Sultan al-Nahayan
65.0
AE
9
ARM
Armenia
Asia
Middle East
29800.0
1991.0
3520000
66.4
1813.0
1627.0
Hajastan
Republic
Robert Kotšarjan
126.0
AM
15
AUT
Austria
Europe
Western Europe
83859.0
1918.0
8091800
77.7
211860.0
206025.0
Österreich
Federal Republic
Thomas Klestil
1523.0
AT
16
AZE
Azerbaijan
Asia
Middle East
86600.0
1991.0
7734000
62.9
4127.0
4100.0
Azärbaycan
Federal Republic
Heydär Äliyev
144.0
AZ
18
BEL
Belgium
Europe
Western Europe
30518.0
1830.0
10239000
77.8
249704.0
243948.0
België/Belgique
Constitutional Monarchy, Federation
Albert II
179.0
BE
21
BGD
Bangladesh
Asia
Southern and Central Asia
143998.0
1971.0
129155000
60.2
32852.0
31966.0
Bangladesh
Republic
Shahabuddin Ahmad
150.0
BD
22
BGR
Bulgaria
Europe
Eastern Europe
110994.0
1908.0
8190900
70.9
12178.0
10169.0
Balgarija
Republic
Petar Stojanov
539.0
BG
23
BHR
Bahrain
Asia
Middle East
694.0
1971.0
617000
73.0
6366.0
6097.0
Al-Bahrayn
Monarchy (Emirate)
Hamad ibn Isa al-Khalifa
149.0
BH
25
BIH
Bosnia and Herzegovina
Europe
Southern Europe
51197.0
1992.0
3972000
71.5
2841.0
NaN
Bosna i Hercegovina
Federal Republic
Ante Jelavic
201.0
BA
26
BLR
Belarus
Europe
Eastern Europe
207600.0
1991.0
10236000
68.0
13714.0
NaN
Belarus
Republic
Aljaksandr Lukašenka
3520.0
BY
32
BRN
Brunei
Asia
Southeast Asia
5765.0
1984.0
328000
73.6
11705.0
12460.0
Brunei Darussalam
Monarchy (Sultanate)
Haji Hassan al-Bolkiah
538.0
BN
33
BTN
Bhutan
Asia
Southern and Central Asia
47000.0
1910.0
2124000
52.4
372.0
383.0
Druk-Yul
Monarchy
Jigme Singye Wangchuk
192.0
BT
39
CHE
Switzerland
Europe
Western Europe
41284.0
1499.0
7160400
79.6
264478.0
256092.0
Schweiz/Suisse/Svizzera/Svizra
Federation
Adolf Ogi
3248.0
CH
41
CHN
China
Asia
Eastern Asia
9572900.0
-1523.0
1277558000
71.4
982268.0
917719.0
Zhongquo
People'sRepublic
Jiang Zemin
1891.0
CN
54
CYP
Cyprus
Asia
Middle East
9251.0
1960.0
754700
76.7
9333.0
8246.0
Kýpros/Kibris
Republic
Glafkos Klerides
2430.0
CY
55
CZE
Czech Republic
Europe
Eastern Europe
78866.0
1993.0
10278100
74.5
55017.0
52037.0
¸esko
Republic
Václav Havel
3339.0
CZ
56
DEU
Germany
Europe
Western Europe
357022.0
1955.0
82164700
77.4
2133367.0
2102826.0
Deutschland
Federal Republic
Johannes Rau
3068.0
DE
59
DNK
Denmark
Europe
Nordic Countries
43094.0
800.0
5330000
76.5
174099.0
169264.0
Danmark
Constitutional Monarchy
Margrethe II
3315.0
DK
66
ESP
Spain
Europe
Southern Europe
505992.0
1492.0
39441700
78.8
553233.0
532031.0
España
Constitutional Monarchy
Juan Carlos I
653.0
ES
67
EST
Estonia
Europe
Baltic Countries
45227.0
1991.0
1439200
69.5
5328.0
3371.0
Eesti
Republic
Lennart Meri
3791.0
EE
69
FIN
Finland
Europe
Nordic Countries
338145.0
1917.0
5171300
77.4
121914.0
119833.0
Suomi
Republic
Tarja Halonen
3236.0
FI
72
FRA
France
Europe
Western Europe
551500.0
843.0
59225700
78.8
1424285.0
1392448.0
France
Republic
Jacques Chirac
2974.0
FR
73
FRO
Faroe Islands
Europe
Nordic Countries
1399.0
NaN
43000
78.4
0.0
NaN
Føroyar
Part of Denmark
Margrethe II
901.0
FO
76
GBR
United Kingdom
Europe
British Islands
242900.0
1066.0
59623400
77.7
1378330.0
1296830.0
United Kingdom
Constitutional Monarchy
Elisabeth II
456.0
GB
77
GEO
Georgia
Asia
Middle East
69700.0
1991.0
4968000
64.5
6064.0
5924.0
Sakartvelo
Republic
Eduard Ševardnadze
905.0
GE
79
GIB
Gibraltar
Europe
Southern Europe
6.0
NaN
25000
79.0
258.0
NaN
Gibraltar
Dependent Territory of the UK
Elisabeth II
915.0
GI
85
GRC
Greece
Europe
Southern Europe
131626.0
1830.0
10545700
78.4
120724.0
119946.0
Elláda
Republic
Kostis Stefanopoulos
2401.0
GR
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
163
OMN
Oman
Asia
Middle East
309500.0
1951.0
2542000
71.8
16904.0
16153.0
´Uman
Monarchy (Sultanate)
Qabus ibn Sa´id
2821.0
OM
164
PAK
Pakistan
Asia
Southern and Central Asia
796095.0
1947.0
156483000
61.1
61289.0
58549.0
Pakistan
Republic
Mohammad Rafiq Tarar
2831.0
PK
168
PHL
Philippines
Asia
Southeast Asia
300000.0
1946.0
75967000
67.5
65107.0
82239.0
Pilipinas
Republic
Gloria Macapagal-Arroyo
766.0
PH
171
POL
Poland
Europe
Eastern Europe
323250.0
1918.0
38653600
73.2
151697.0
135636.0
Polska
Republic
Aleksander Kwasniewski
2928.0
PL
173
PRK
North Korea
Asia
Eastern Asia
120538.0
1948.0
24039000
70.7
5332.0
NaN
Choson Minjujuui In´min Konghwaguk (Bukhan)
Socialistic Republic
Kim Jong-il
2318.0
KP
174
PRT
Portugal
Europe
Southern Europe
91982.0
1143.0
9997600
75.8
105954.0
102133.0
Portugal
Republic
Jorge Sampãio
2914.0
PT
176
PSE
Palestine
Asia
Middle East
6257.0
NaN
3101000
71.4
4173.0
NaN
Filastin
Autonomous Area
Yasser (Yasir) Arafat
4074.0
PS
178
QAT
Qatar
Asia
Middle East
11000.0
1971.0
599000
72.4
9472.0
8920.0
Qatar
Monarchy
Hamad ibn Khalifa al-Thani
2973.0
QA
180
ROM
Romania
Europe
Eastern Europe
238391.0
1878.0
22455500
69.9
38158.0
34843.0
România
Republic
Ion Iliescu
3018.0
RO
181
RUS
Russian Federation
Europe
Eastern Europe
17075400.0
1991.0
146934000
67.2
276608.0
442989.0
Rossija
Federal Republic
Vladimir Putin
3580.0
RU
183
SAU
Saudi Arabia
Asia
Middle East
2149690.0
1932.0
21607000
67.8
137635.0
146171.0
Al-´Arabiya as-Sa´udiya
Monarchy
Fahd ibn Abdul-Aziz al-Sa´ud
3173.0
SA
186
SGP
Singapore
Asia
Southeast Asia
618.0
1965.0
3567000
80.1
86503.0
96318.0
Singapore/Singapura/Xinjiapo/Singapur
Republic
Sellapan Rama Nathan
3208.0
SG
189
SJM
Svalbard and Jan Mayen
Europe
Nordic Countries
62422.0
NaN
3200
NaN
0.0
NaN
Svalbard og Jan Mayen
Dependent Territory of Norway
Harald V
938.0
SJ
193
SMR
San Marino
Europe
Southern Europe
61.0
885.0
27000
81.1
510.0
NaN
San Marino
Republic
None
3171.0
SM
198
SVK
Slovakia
Europe
Eastern Europe
49012.0
1993.0
5398700
73.7
20594.0
19452.0
Slovensko
Republic
Rudolf Schuster
3209.0
SK
199
SVN
Slovenia
Europe
Southern Europe
20256.0
1991.0
1987800
74.9
19756.0
18202.0
Slovenija
Republic
Milan Kucan
3212.0
SI
200
SWE
Sweden
Europe
Nordic Countries
449964.0
836.0
8861400
79.6
226492.0
227757.0
Sverige
Constitutional Monarchy
Carl XVI Gustaf
3048.0
SE
203
SYR
Syria
Asia
Middle East
185180.0
1941.0
16125000
68.5
65984.0
64926.0
Suriya
Republic
Bashar al-Assad
3250.0
SY
207
THA
Thailand
Asia
Southeast Asia
513115.0
1350.0
61399000
68.6
116416.0
153907.0
Prathet Thai
Constitutional Monarchy
Bhumibol Adulyadej
3320.0
TH
208
TJK
Tajikistan
Asia
Southern and Central Asia
143100.0
1991.0
6188000
64.1
1990.0
1056.0
Toçikiston
Republic
Emomali Rahmonov
3261.0
TJ
210
TKM
Turkmenistan
Asia
Southern and Central Asia
488100.0
1991.0
4459000
60.9
4397.0
2000.0
Türkmenostan
Republic
Saparmurad Nijazov
3419.0
TM
211
TMP
East Timor
Asia
Southeast Asia
14874.0
NaN
885000
46.0
0.0
NaN
Timor Timur
Administrated by the UN
José Alexandre Gusmão
1522.0
TP
215
TUR
Turkey
Asia
Middle East
774815.0
1923.0
66591000
71.0
210721.0
189122.0
Türkiye
Republic
Ahmet Necdet Sezer
3358.0
TR
217
TWN
Taiwan
Asia
Eastern Asia
36188.0
1945.0
22256000
76.4
256254.0
263451.0
TÂ’ai-wan
Republic
Chen Shui-bian
3263.0
TW
220
UKR
Ukraine
Europe
Eastern Europe
603700.0
1991.0
50456000
66.0
42168.0
49677.0
Ukrajina
Republic
Leonid Kutšma
3426.0
UA
224
UZB
Uzbekistan
Asia
Southern and Central Asia
447400.0
1991.0
24318000
63.7
14194.0
21300.0
Uzbekiston
Republic
Islam Karimov
3503.0
UZ
225
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929.0
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538.0
VA
230
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945.0
79832000
69.3
21929.0
22834.0
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770.0
VN
234
YEM
Yemen
Asia
Middle East
527968.0
1918.0
18112000
59.8
6041.0
5729.0
Al-Yaman
Republic
Ali Abdallah Salih
1780.0
YE
235
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918.0
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792.0
YU
97 rows × 15 columns
In [76]:
SQL_QUERY = '''
SELECT *
FROM country
Where
Continent = 'Europe'
OR Continent = 'Asia'
'''
pd.read_sql(SQL_QUERY, db).tail()
Out[76]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
92
UZB
Uzbekistan
Asia
Southern and Central Asia
447400.0
1991.0
24318000
63.7
14194.0
21300.0
Uzbekiston
Republic
Islam Karimov
3503
UZ
93
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929.0
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538
VA
94
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945.0
79832000
69.3
21929.0
22834.0
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770
VN
95
YEM
Yemen
Asia
Middle East
527968.0
1918.0
18112000
59.8
6041.0
5729.0
Al-Yaman
Republic
Ali Abdallah Salih
1780
YE
96
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918.0
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792
YU
In [92]:
# 이방법이 더 효율적
SQL_QUERY = '''
SELECT *
FROM country
Where
Continent in ('Asia', 'Europe')
'''
pd.read_sql(SQL_QUERY, db).tail()
Out[92]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
92
UZB
Uzbekistan
Asia
Southern and Central Asia
447400.0
1991.0
24318000
63.7
14194.0
21300.0
Uzbekiston
Republic
Islam Karimov
3503
UZ
93
VAT
Holy See (Vatican City State)
Europe
Southern Europe
0.4
1929.0
1000
NaN
9.0
NaN
Santa Sede/Città del Vaticano
Independent Church State
Johannes Paavali II
3538
VA
94
VNM
Vietnam
Asia
Southeast Asia
331689.0
1945.0
79832000
69.3
21929.0
22834.0
Viêt Nam
Socialistic Republic
Trân Duc Luong
3770
VN
95
YEM
Yemen
Asia
Middle East
527968.0
1918.0
18112000
59.8
6041.0
5729.0
Al-Yaman
Republic
Ali Abdallah Salih
1780
YE
96
YUG
Yugoslavia
Europe
Southern Europe
102173.0
1918.0
10640000
72.4
17000.0
NaN
Jugoslavija
Federal Republic
Vojislav Koštunica
1792
YU
In [ ]:
# sorting
In [102]:
country_df.sort_values('Population', ascending = False)[['Name','Population']].head()
Out[102]:
Name
Population
41
China
1277558000
99
India
1013662000
223
United States
278357000
98
Indonesia
212107000
30
Brazil
170115000
In [96]:
SQL_QUERY = '''
SELECT Name, Population
FROM country
ORDER BY Population DESC
'''
pd.read_sql(SQL_QUERY, db).head()
Out[96]:
Name
Population
0
China
1277558000
1
India
1013662000
2
United States
278357000
3
Indonesia
212107000
4
Brazil
170115000
In [ ]:
In [ ]:
# text mining
# governmentForm에 'Republic' 이라는 텍스트가 포함된 열
In [111]:
df = country_df
In [113]:
# 1. pandas
df[df['GovernmentForm'].str.contains('Republic')].head()
Out[113]:
Code
Name
Continent
Region
SurfaceArea
IndepYear
Population
LifeExpectancy
GNP
GNPOld
LocalName
GovernmentForm
HeadOfState
Capital
Code2
2
AGO
Angola
Africa
Central Africa
1246700.0
1975.0
12878000
38.3
6648.0
7984.0
Angola
Republic
José Eduardo dos Santos
56.0
AO
4
ALB
Albania
Europe
Southern Europe
28748.0
1912.0
3401200
71.6
3205.0
2500.0
Shqipëria
Republic
Rexhep Mejdani
34.0
AL
8
ARG
Argentina
South America
South America
2780400.0
1816.0
37032000
75.1
340238.0
323310.0
Argentina
Federal Republic
Fernando de la Rúa
69.0
AR
9
ARM
Armenia
Asia
Middle East
29800.0
1991.0
3520000
66.4
1813.0
1627.0
Hajastan
Republic
Robert Kotšarjan
126.0
AM
15
AUT
Austria
Europe
Western Europe
83859.0
1918.0
8091800
77.7
211860.0
206025.0
Österreich
Federal Republic
Thomas Klestil
1523.0
AT
In [ ]:
# 2. sql - containg, startswith, endswith
In [119]:
SQL_QUERY = '''
SELECT Name, GovernmentForm
From country
WHERE
GovernmentForm LIKE '%Republic%'
;
'''
# 정규표현식과 비슷한것 사용가능
pd.read_sql(SQL_QUERY, db).head()
Out[119]:
Name
GovernmentForm
0
Angola
Republic
1
Albania
Republic
2
Argentina
Federal Republic
3
Armenia
Republic
4
Austria
Federal Republic
In [ ]:
# JOIN (pandas - merge)
In [123]:
city_df = pd.read_sql("SELECT * FROM city;" , db)
In [126]:
country_df.columns
Out[126]:
Index([u'Code', u'Name', u'Continent', u'Region', u'SurfaceArea', u'IndepYear',
u'Population', u'LifeExpectancy', u'GNP', u'GNPOld', u'LocalName',
u'GovernmentForm', u'HeadOfState', u'Capital', u'Code2'],
dtype='object')
In [124]:
city_df.columns
Out[124]:
Index([u'ID', u'Name', u'CountryCode', u'District', u'Population'], dtype='object')
In [138]:
city_df.merge(country_df, left_on = 'CountryCode', right_on= 'Code')[['Name_x','Name_y']]
Out[138]:
Name_x
Name_y
0
Kabul
Afghanistan
1
Qandahar
Afghanistan
2
Herat
Afghanistan
3
Mazar-e-Sharif
Afghanistan
4
Amsterdam
Netherlands
5
Rotterdam
Netherlands
6
Haag
Netherlands
7
Utrecht
Netherlands
8
Eindhoven
Netherlands
9
Tilburg
Netherlands
10
Groningen
Netherlands
11
Breda
Netherlands
12
Apeldoorn
Netherlands
13
Nijmegen
Netherlands
14
Enschede
Netherlands
15
Haarlem
Netherlands
16
Almere
Netherlands
17
Arnhem
Netherlands
18
Zaanstad
Netherlands
19
´s-Hertogenbosch
Netherlands
20
Amersfoort
Netherlands
21
Maastricht
Netherlands
22
Dordrecht
Netherlands
23
Leiden
Netherlands
24
Haarlemmermeer
Netherlands
25
Zoetermeer
Netherlands
26
Emmen
Netherlands
27
Zwolle
Netherlands
28
Ede
Netherlands
29
Delft
Netherlands
...
...
...
4049
Roanoke
United States
4050
Billings
United States
4051
Compton
United States
4052
Gainesville
United States
4053
Fairfield
United States
4054
Arden-Arcade
United States
4055
San Mateo
United States
4056
Visalia
United States
4057
Boulder
United States
4058
Cary
United States
4059
Santa Monica
United States
4060
Fall River
United States
4061
Kenosha
United States
4062
Elgin
United States
4063
Odessa
United States
4064
Carson
United States
4065
Charleston
United States
4066
Charlotte Amalie
Virgin Islands, U.S.
4067
Harare
Zimbabwe
4068
Bulawayo
Zimbabwe
4069
Chitungwiza
Zimbabwe
4070
Mount Darwin
Zimbabwe
4071
Mutare
Zimbabwe
4072
Gweru
Zimbabwe
4073
Gaza
Palestine
4074
Khan Yunis
Palestine
4075
Hebron
Palestine
4076
Jabaliya
Palestine
4077
Nablus
Palestine
4078
Rafah
Palestine
4079 rows × 2 columns
In [136]:
SQL_QUERY = '''
SELECT country.Name "country Name", city.Name "city Name"
FROM country, city
WHERE country.Code = city.CountryCode
;
'''
pd.read_sql(SQL_QUERY, db).head()
Out[136]:
country Name
city Name
0
Aruba
Oranjestad
1
Afghanistan
Kabul
2
Afghanistan
Qandahar
3
Afghanistan
Herat
4
Afghanistan
Mazar-e-Sharif
Content source: GD-park/python_basic
Similar notebooks: