In [75]:
## Python packages - you may have to pip install sqlalchemy, sqlalchemy_utils, and psycopg2.
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
import psycopg2
import pandas as pd
import re
In [38]:
#In Python: Define a database name (we're using a dataset on births, so I call it
# birth_db), and your username for your computer (CHANGE IT BELOW).
dbname = 'zipcode_profiles'
username = 'akuepper'
pswd = 'FLP@nd'
In [39]:
## 'engine' is a connection to a database
## Here, we're using postgres, but sqlalchemy can connect to other things too.
engine = create_engine('postgresql://%s:%s@localhost/%s'%(username,pswd,dbname))
print(engine.url)
# Replace localhost with IP address if accessing a remote server
postgresql://akuepper:FLP%40nd@localhost/zipcode_profiles
In [48]:
## create a database (if it doesn't exist)
if not database_exists(engine.url):
create_database(engine.url)
print(database_exists(engine.url))
True
In [131]:
# load a database from CSV
zipcode_data = pd.DataFrame.from_csv('../project/data/census/DEC10.csv', header=0)
/Users/akuepper/anaconda/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2825: DtypeWarning: Columns (1,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,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,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,143,144,145,146,147,148,149,150,151,152,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,214,215,216,217,218,219,220,221,222,223,224,225,226,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,262,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,288,289,290,291,292,293,294,295,296,297,298,299,300,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,337,340,342,344,345,346,347,348,349,350,351,352,353,354,355,356,357,359,362,363,364,365,367,369,370,371,373) have mixed types. Specify dtype option on import or set low_memory=False.
if self.run_code(code, result):
In [132]:
columnnames = list(zipcode_data.columns.values)
list(zipcode_data.columns.values)
Out[132]:
['GEO.id2',
'GEO.display-label',
'HD01_S001',
'HD02_S001',
'HD01_S002',
'HD02_S002',
'HD01_S003',
'HD02_S003',
'HD01_S004',
'HD02_S004',
'HD01_S005',
'HD02_S005',
'HD01_S006',
'HD02_S006',
'HD01_S007',
'HD02_S007',
'HD01_S008',
'HD02_S008',
'HD01_S009',
'HD02_S009',
'HD01_S010',
'HD02_S010',
'HD01_S011',
'HD02_S011',
'HD01_S012',
'HD02_S012',
'HD01_S013',
'HD02_S013',
'HD01_S014',
'HD02_S014',
'HD01_S015',
'HD02_S015',
'HD01_S016',
'HD02_S016',
'HD01_S017',
'HD02_S017',
'HD01_S018',
'HD02_S018',
'HD01_S019',
'HD02_S019',
'HD01_S020',
'HD02_S020',
'HD01_S021',
'HD02_S021',
'HD01_S022',
'HD02_S022',
'HD01_S023',
'HD02_S023',
'HD01_S024',
'HD02_S024',
'HD01_S025',
'HD02_S025',
'HD01_S026',
'HD02_S026',
'HD01_S027',
'HD02_S027',
'HD01_S028',
'HD02_S028',
'HD01_S029',
'HD02_S029',
'HD01_S030',
'HD02_S030',
'HD01_S031',
'HD02_S031',
'HD01_S032',
'HD02_S032',
'HD01_S033',
'HD02_S033',
'HD01_S034',
'HD02_S034',
'HD01_S035',
'HD02_S035',
'HD01_S036',
'HD02_S036',
'HD01_S037',
'HD02_S037',
'HD01_S038',
'HD02_S038',
'HD01_S039',
'HD02_S039',
'HD01_S040',
'HD02_S040',
'HD01_S041',
'HD02_S041',
'HD01_S042',
'HD02_S042',
'HD01_S043',
'HD02_S043',
'HD01_S044',
'HD02_S044',
'HD01_S045',
'HD02_S045',
'HD01_S046',
'HD02_S046',
'HD01_S047',
'HD02_S047',
'HD01_S048',
'HD02_S048',
'HD01_S049',
'HD02_S049',
'HD01_S050',
'HD02_S050',
'HD01_S051',
'HD02_S051',
'HD01_S052',
'HD02_S052',
'HD01_S053',
'HD02_S053',
'HD01_S054',
'HD02_S054',
'HD01_S055',
'HD02_S055',
'HD01_S056',
'HD02_S056',
'HD01_S057',
'HD02_S057',
'HD01_S058',
'HD02_S058',
'HD01_S059',
'HD02_S059',
'HD01_S060',
'HD02_S060',
'HD01_S061',
'HD02_S061',
'HD01_S062',
'HD02_S062',
'HD01_S063',
'HD02_S063',
'HD01_S064',
'HD02_S064',
'HD01_S065',
'HD02_S065',
'HD01_S066',
'HD02_S066',
'HD01_S067',
'HD02_S067',
'HD01_S068',
'HD02_S068',
'HD01_S069',
'HD02_S069',
'HD01_S070',
'HD02_S070',
'HD01_S071',
'HD02_S071',
'HD01_S072',
'HD02_S072',
'HD01_S073',
'HD02_S073',
'HD01_S074',
'HD02_S074',
'HD01_S075',
'HD02_S075',
'HD01_S076',
'HD02_S076',
'HD01_S077',
'HD02_S077',
'HD01_S078',
'HD02_S078',
'HD01_S079',
'HD02_S079',
'HD01_S080',
'HD02_S080',
'HD01_S081',
'HD02_S081',
'HD01_S082',
'HD02_S082',
'HD01_S083',
'HD02_S083',
'HD01_S084',
'HD02_S084',
'HD01_S085',
'HD02_S085',
'HD01_S086',
'HD02_S086',
'HD01_S087',
'HD02_S087',
'HD01_S088',
'HD02_S088',
'HD01_S089',
'HD02_S089',
'HD01_S090',
'HD02_S090',
'HD01_S091',
'HD02_S091',
'HD01_S092',
'HD02_S092',
'HD01_S093',
'HD02_S093',
'HD01_S094',
'HD02_S094',
'HD01_S095',
'HD02_S095',
'HD01_S096',
'HD02_S096',
'HD01_S097',
'HD02_S097',
'HD01_S098',
'HD02_S098',
'HD01_S099',
'HD02_S099',
'HD01_S100',
'HD02_S100',
'HD01_S101',
'HD02_S101',
'HD01_S102',
'HD02_S102',
'HD01_S103',
'HD02_S103',
'HD01_S104',
'HD02_S104',
'HD01_S105',
'HD02_S105',
'HD01_S106',
'HD02_S106',
'HD01_S107',
'HD02_S107',
'HD01_S108',
'HD02_S108',
'HD01_S109',
'HD02_S109',
'HD01_S110',
'HD02_S110',
'HD01_S111',
'HD02_S111',
'HD01_S112',
'HD02_S112',
'HD01_S113',
'HD02_S113',
'HD01_S114',
'HD02_S114',
'HD01_S115',
'HD02_S115',
'HD01_S116',
'HD02_S116',
'HD01_S117',
'HD02_S117',
'HD01_S118',
'HD02_S118',
'HD01_S119',
'HD02_S119',
'HD01_S120',
'HD02_S120',
'HD01_S121',
'HD02_S121',
'HD01_S122',
'HD02_S122',
'HD01_S123',
'HD02_S123',
'HD01_S124',
'HD02_S124',
'HD01_S125',
'HD02_S125',
'HD01_S126',
'HD02_S126',
'HD01_S127',
'HD02_S127',
'HD01_S128',
'HD02_S128',
'HD01_S129',
'HD02_S129',
'HD01_S130',
'HD02_S130',
'HD01_S131',
'HD02_S131',
'HD01_S132',
'HD02_S132',
'HD01_S133',
'HD02_S133',
'HD01_S134',
'HD02_S134',
'HD01_S135',
'HD02_S135',
'HD01_S136',
'HD02_S136',
'HD01_S137',
'HD02_S137',
'HD01_S138',
'HD02_S138',
'HD01_S139',
'HD02_S139',
'HD01_S140',
'HD02_S140',
'HD01_S141',
'HD02_S141',
'HD01_S142',
'HD02_S142',
'HD01_S143',
'HD02_S143',
'HD01_S144',
'HD02_S144',
'HD01_S145',
'HD02_S145',
'HD01_S146',
'HD02_S146',
'HD01_S147',
'HD02_S147',
'HD01_S148',
'HD02_S148',
'HD01_S149',
'HD02_S149',
'HD01_S150',
'HD02_S150',
'HD01_S151',
'HD02_S151',
'HD01_S152',
'HD02_S152',
'HD01_S153',
'HD02_S153',
'HD01_S154',
'HD02_S154',
'HD01_S155',
'HD02_S155',
'HD01_S156',
'HD02_S156',
'HD01_S157',
'HD02_S157',
'HD01_S158',
'HD02_S158',
'HD01_S159',
'HD02_S159',
'HD01_S160',
'HD02_S160',
'HD01_S161',
'HD02_S161',
'HD01_S162',
'HD02_S162',
'HD01_S163',
'HD02_S163',
'HD01_S164',
'HD02_S164',
'HD01_S165',
'HD02_S165',
'HD01_S166',
'HD02_S166',
'HD01_S167',
'HD02_S167',
'HD01_S168',
'HD02_S168',
'HD01_S169',
'HD02_S169',
'HD01_S170',
'HD02_S170',
'HD01_S171',
'HD02_S171',
'HD01_S172',
'HD02_S172',
'HD01_S173',
'HD02_S173',
'HD01_S174',
'HD02_S174',
'HD01_S175',
'HD02_S175',
'HD01_S176',
'HD02_S176',
'HD01_S177',
'HD02_S177',
'HD01_S178',
'HD02_S178',
'HD01_S179',
'HD02_S179',
'HD01_S180',
'HD02_S180',
'HD01_S181',
'HD02_S181',
'HD01_S182',
'HD02_S182',
'HD01_S183',
'HD02_S183',
'HD01_S184',
'HD02_S184',
'HD01_S185',
'HD02_S185',
'HD01_S186',
'HD02_S186']
In [133]:
zipcode_data.index.name = "geoid"
zipcode_data.index
Out[133]:
Index(['Id', '0500000US01001', '0500000US01003', '0500000US01005',
'0500000US01007', '0500000US01009', '0500000US01011', '0500000US01013',
'0500000US01015', '0500000US01017',
...
'0500000US56027', '0500000US56029', '0500000US56031', '0500000US56033',
'0500000US56035', '0500000US56037', '0500000US56039', '0500000US56041',
'0500000US56043', '0500000US56045'],
dtype='object', name='geoid', length=3144)
In [134]:
for columnname in columnnames:
columnname_wo_specialcharacters = re.sub('[ \-\_\+\=\`\~\{\}\;\:\,\.\<\>\?\/\!\@\#\$\%\^\&\*\(\)\[\]]', '', columnname)
print(columnname_wo_specialcharacters.lower())
zipcode_data.rename(columns={columnname: columnname_wo_specialcharacters.lower()}, inplace=True)
geoid2
geodisplaylabel
hd01s001
hd02s001
hd01s002
hd02s002
hd01s003
hd02s003
hd01s004
hd02s004
hd01s005
hd02s005
hd01s006
hd02s006
hd01s007
hd02s007
hd01s008
hd02s008
hd01s009
hd02s009
hd01s010
hd02s010
hd01s011
hd02s011
hd01s012
hd02s012
hd01s013
hd02s013
hd01s014
hd02s014
hd01s015
hd02s015
hd01s016
hd02s016
hd01s017
hd02s017
hd01s018
hd02s018
hd01s019
hd02s019
hd01s020
hd02s020
hd01s021
hd02s021
hd01s022
hd02s022
hd01s023
hd02s023
hd01s024
hd02s024
hd01s025
hd02s025
hd01s026
hd02s026
hd01s027
hd02s027
hd01s028
hd02s028
hd01s029
hd02s029
hd01s030
hd02s030
hd01s031
hd02s031
hd01s032
hd02s032
hd01s033
hd02s033
hd01s034
hd02s034
hd01s035
hd02s035
hd01s036
hd02s036
hd01s037
hd02s037
hd01s038
hd02s038
hd01s039
hd02s039
hd01s040
hd02s040
hd01s041
hd02s041
hd01s042
hd02s042
hd01s043
hd02s043
hd01s044
hd02s044
hd01s045
hd02s045
hd01s046
hd02s046
hd01s047
hd02s047
hd01s048
hd02s048
hd01s049
hd02s049
hd01s050
hd02s050
hd01s051
hd02s051
hd01s052
hd02s052
hd01s053
hd02s053
hd01s054
hd02s054
hd01s055
hd02s055
hd01s056
hd02s056
hd01s057
hd02s057
hd01s058
hd02s058
hd01s059
hd02s059
hd01s060
hd02s060
hd01s061
hd02s061
hd01s062
hd02s062
hd01s063
hd02s063
hd01s064
hd02s064
hd01s065
hd02s065
hd01s066
hd02s066
hd01s067
hd02s067
hd01s068
hd02s068
hd01s069
hd02s069
hd01s070
hd02s070
hd01s071
hd02s071
hd01s072
hd02s072
hd01s073
hd02s073
hd01s074
hd02s074
hd01s075
hd02s075
hd01s076
hd02s076
hd01s077
hd02s077
hd01s078
hd02s078
hd01s079
hd02s079
hd01s080
hd02s080
hd01s081
hd02s081
hd01s082
hd02s082
hd01s083
hd02s083
hd01s084
hd02s084
hd01s085
hd02s085
hd01s086
hd02s086
hd01s087
hd02s087
hd01s088
hd02s088
hd01s089
hd02s089
hd01s090
hd02s090
hd01s091
hd02s091
hd01s092
hd02s092
hd01s093
hd02s093
hd01s094
hd02s094
hd01s095
hd02s095
hd01s096
hd02s096
hd01s097
hd02s097
hd01s098
hd02s098
hd01s099
hd02s099
hd01s100
hd02s100
hd01s101
hd02s101
hd01s102
hd02s102
hd01s103
hd02s103
hd01s104
hd02s104
hd01s105
hd02s105
hd01s106
hd02s106
hd01s107
hd02s107
hd01s108
hd02s108
hd01s109
hd02s109
hd01s110
hd02s110
hd01s111
hd02s111
hd01s112
hd02s112
hd01s113
hd02s113
hd01s114
hd02s114
hd01s115
hd02s115
hd01s116
hd02s116
hd01s117
hd02s117
hd01s118
hd02s118
hd01s119
hd02s119
hd01s120
hd02s120
hd01s121
hd02s121
hd01s122
hd02s122
hd01s123
hd02s123
hd01s124
hd02s124
hd01s125
hd02s125
hd01s126
hd02s126
hd01s127
hd02s127
hd01s128
hd02s128
hd01s129
hd02s129
hd01s130
hd02s130
hd01s131
hd02s131
hd01s132
hd02s132
hd01s133
hd02s133
hd01s134
hd02s134
hd01s135
hd02s135
hd01s136
hd02s136
hd01s137
hd02s137
hd01s138
hd02s138
hd01s139
hd02s139
hd01s140
hd02s140
hd01s141
hd02s141
hd01s142
hd02s142
hd01s143
hd02s143
hd01s144
hd02s144
hd01s145
hd02s145
hd01s146
hd02s146
hd01s147
hd02s147
hd01s148
hd02s148
hd01s149
hd02s149
hd01s150
hd02s150
hd01s151
hd02s151
hd01s152
hd02s152
hd01s153
hd02s153
hd01s154
hd02s154
hd01s155
hd02s155
hd01s156
hd02s156
hd01s157
hd02s157
hd01s158
hd02s158
hd01s159
hd02s159
hd01s160
hd02s160
hd01s161
hd02s161
hd01s162
hd02s162
hd01s163
hd02s163
hd01s164
hd02s164
hd01s165
hd02s165
hd01s166
hd02s166
hd01s167
hd02s167
hd01s168
hd02s168
hd01s169
hd02s169
hd01s170
hd02s170
hd01s171
hd02s171
hd01s172
hd02s172
hd01s173
hd02s173
hd01s174
hd02s174
hd01s175
hd02s175
hd01s176
hd02s176
hd01s177
hd02s177
hd01s178
hd02s178
hd01s179
hd02s179
hd01s180
hd02s180
hd01s181
hd02s181
hd01s182
hd02s182
hd01s183
hd02s183
hd01s184
hd02s184
hd01s185
hd02s185
hd01s186
hd02s186
---------------------------------------------------------------------------
NameError Traceback (most recent call last)
<ipython-input-134-3d38ce9d8bff> in <module>()
4 zipcode_data.rename(columns={columnname: columnname_wo_specialcharacters.lower()}, inplace=True)
5
----> 6 Index.rename(name, inplace=False)
NameError: name 'Index' is not defined
In [66]:
test = "I am a test stringf *&#T:;@$()"
In [95]:
test = re.sub('[!@#:$;%^&\*()\[\]]', '', test)
print(test)
I am a test stringf T
In [135]:
zipcode_data.columns.values
Out[135]:
array(['geoid2', 'geodisplaylabel', 'hd01s001', 'hd02s001', 'hd01s002',
'hd02s002', 'hd01s003', 'hd02s003', 'hd01s004', 'hd02s004',
'hd01s005', 'hd02s005', 'hd01s006', 'hd02s006', 'hd01s007',
'hd02s007', 'hd01s008', 'hd02s008', 'hd01s009', 'hd02s009',
'hd01s010', 'hd02s010', 'hd01s011', 'hd02s011', 'hd01s012',
'hd02s012', 'hd01s013', 'hd02s013', 'hd01s014', 'hd02s014',
'hd01s015', 'hd02s015', 'hd01s016', 'hd02s016', 'hd01s017',
'hd02s017', 'hd01s018', 'hd02s018', 'hd01s019', 'hd02s019',
'hd01s020', 'hd02s020', 'hd01s021', 'hd02s021', 'hd01s022',
'hd02s022', 'hd01s023', 'hd02s023', 'hd01s024', 'hd02s024',
'hd01s025', 'hd02s025', 'hd01s026', 'hd02s026', 'hd01s027',
'hd02s027', 'hd01s028', 'hd02s028', 'hd01s029', 'hd02s029',
'hd01s030', 'hd02s030', 'hd01s031', 'hd02s031', 'hd01s032',
'hd02s032', 'hd01s033', 'hd02s033', 'hd01s034', 'hd02s034',
'hd01s035', 'hd02s035', 'hd01s036', 'hd02s036', 'hd01s037',
'hd02s037', 'hd01s038', 'hd02s038', 'hd01s039', 'hd02s039',
'hd01s040', 'hd02s040', 'hd01s041', 'hd02s041', 'hd01s042',
'hd02s042', 'hd01s043', 'hd02s043', 'hd01s044', 'hd02s044',
'hd01s045', 'hd02s045', 'hd01s046', 'hd02s046', 'hd01s047',
'hd02s047', 'hd01s048', 'hd02s048', 'hd01s049', 'hd02s049',
'hd01s050', 'hd02s050', 'hd01s051', 'hd02s051', 'hd01s052',
'hd02s052', 'hd01s053', 'hd02s053', 'hd01s054', 'hd02s054',
'hd01s055', 'hd02s055', 'hd01s056', 'hd02s056', 'hd01s057',
'hd02s057', 'hd01s058', 'hd02s058', 'hd01s059', 'hd02s059',
'hd01s060', 'hd02s060', 'hd01s061', 'hd02s061', 'hd01s062',
'hd02s062', 'hd01s063', 'hd02s063', 'hd01s064', 'hd02s064',
'hd01s065', 'hd02s065', 'hd01s066', 'hd02s066', 'hd01s067',
'hd02s067', 'hd01s068', 'hd02s068', 'hd01s069', 'hd02s069',
'hd01s070', 'hd02s070', 'hd01s071', 'hd02s071', 'hd01s072',
'hd02s072', 'hd01s073', 'hd02s073', 'hd01s074', 'hd02s074',
'hd01s075', 'hd02s075', 'hd01s076', 'hd02s076', 'hd01s077',
'hd02s077', 'hd01s078', 'hd02s078', 'hd01s079', 'hd02s079',
'hd01s080', 'hd02s080', 'hd01s081', 'hd02s081', 'hd01s082',
'hd02s082', 'hd01s083', 'hd02s083', 'hd01s084', 'hd02s084',
'hd01s085', 'hd02s085', 'hd01s086', 'hd02s086', 'hd01s087',
'hd02s087', 'hd01s088', 'hd02s088', 'hd01s089', 'hd02s089',
'hd01s090', 'hd02s090', 'hd01s091', 'hd02s091', 'hd01s092',
'hd02s092', 'hd01s093', 'hd02s093', 'hd01s094', 'hd02s094',
'hd01s095', 'hd02s095', 'hd01s096', 'hd02s096', 'hd01s097',
'hd02s097', 'hd01s098', 'hd02s098', 'hd01s099', 'hd02s099',
'hd01s100', 'hd02s100', 'hd01s101', 'hd02s101', 'hd01s102',
'hd02s102', 'hd01s103', 'hd02s103', 'hd01s104', 'hd02s104',
'hd01s105', 'hd02s105', 'hd01s106', 'hd02s106', 'hd01s107',
'hd02s107', 'hd01s108', 'hd02s108', 'hd01s109', 'hd02s109',
'hd01s110', 'hd02s110', 'hd01s111', 'hd02s111', 'hd01s112',
'hd02s112', 'hd01s113', 'hd02s113', 'hd01s114', 'hd02s114',
'hd01s115', 'hd02s115', 'hd01s116', 'hd02s116', 'hd01s117',
'hd02s117', 'hd01s118', 'hd02s118', 'hd01s119', 'hd02s119',
'hd01s120', 'hd02s120', 'hd01s121', 'hd02s121', 'hd01s122',
'hd02s122', 'hd01s123', 'hd02s123', 'hd01s124', 'hd02s124',
'hd01s125', 'hd02s125', 'hd01s126', 'hd02s126', 'hd01s127',
'hd02s127', 'hd01s128', 'hd02s128', 'hd01s129', 'hd02s129',
'hd01s130', 'hd02s130', 'hd01s131', 'hd02s131', 'hd01s132',
'hd02s132', 'hd01s133', 'hd02s133', 'hd01s134', 'hd02s134',
'hd01s135', 'hd02s135', 'hd01s136', 'hd02s136', 'hd01s137',
'hd02s137', 'hd01s138', 'hd02s138', 'hd01s139', 'hd02s139',
'hd01s140', 'hd02s140', 'hd01s141', 'hd02s141', 'hd01s142',
'hd02s142', 'hd01s143', 'hd02s143', 'hd01s144', 'hd02s144',
'hd01s145', 'hd02s145', 'hd01s146', 'hd02s146', 'hd01s147',
'hd02s147', 'hd01s148', 'hd02s148', 'hd01s149', 'hd02s149',
'hd01s150', 'hd02s150', 'hd01s151', 'hd02s151', 'hd01s152',
'hd02s152', 'hd01s153', 'hd02s153', 'hd01s154', 'hd02s154',
'hd01s155', 'hd02s155', 'hd01s156', 'hd02s156', 'hd01s157',
'hd02s157', 'hd01s158', 'hd02s158', 'hd01s159', 'hd02s159',
'hd01s160', 'hd02s160', 'hd01s161', 'hd02s161', 'hd01s162',
'hd02s162', 'hd01s163', 'hd02s163', 'hd01s164', 'hd02s164',
'hd01s165', 'hd02s165', 'hd01s166', 'hd02s166', 'hd01s167',
'hd02s167', 'hd01s168', 'hd02s168', 'hd01s169', 'hd02s169',
'hd01s170', 'hd02s170', 'hd01s171', 'hd02s171', 'hd01s172',
'hd02s172', 'hd01s173', 'hd02s173', 'hd01s174', 'hd02s174',
'hd01s175', 'hd02s175', 'hd01s176', 'hd02s176', 'hd01s177',
'hd02s177', 'hd01s178', 'hd02s178', 'hd01s179', 'hd02s179',
'hd01s180', 'hd02s180', 'hd01s181', 'hd02s181', 'hd01s182',
'hd02s182', 'hd01s183', 'hd02s183', 'hd01s184', 'hd02s184',
'hd01s185', 'hd02s185', 'hd01s186', 'hd02s186'], dtype=object)
In [136]:
## insert data into database from Python (proof of concept - this won't be useful for big data, of course)
## df is any pandas dataframe
zipcode_data.to_sql('zipcode_data_table', engine, if_exists='replace')
In [140]:
# connect:
con = None
con = psycopg2.connect(database = dbname, user = username, host='localhost', password=pswd)
# query:
sql_query = """
SELECT * FROM zipcode_data_table WHERE geoid2='1001';
"""
birth_data_from_sql = pd.read_sql_query(sql_query,con)
birth_data_from_sql.head()
Out[140]:
geoid
geoid2
geodisplaylabel
hd01s001
hd02s001
hd01s002
hd02s002
hd01s003
hd02s003
hd01s004
...
hd01s182
hd02s182
hd01s183
hd02s183
hd01s184
hd02s184
hd01s185
hd02s185
hd01s186
hd02s186
0
0500000US01001
1001
Autauga County, Alabama
54571
100
3579
6.6
3991
7.3
4290
...
40942
( X )
2.69
( X )
4973
24.6
13174
( X )
2.65
( X )
1 rows × 375 columns
In [14]:
print(column_names)
[]
In [ ]:
Content source: ahwkuepper/stdme
Similar notebooks: