Loading from Quandl and parsing US states abbreviations from Wikipedia

https://youtu.be/3GpvWlVinf0

to install the quandl package use:

  • sudo apt-get install libssl-dev
  • sudo pip install quandl

to parse html files:

  • sudo pip install lxml
  • sudo pip install html5lib
  • sudo pip install BeautifulSoup4

In [1]:
import quandl; 
import pandas as pd;

In [2]:
api_key = open("quandlapikey.txt", "r").read(); #get it from quandl!
df = quandl.get("FMAC/HPI_AK", authtoken=api_key, end_date="2013-06-30");

print(df.head());


                Value
Date                 
1975-01-31  34.400087
1975-02-28  34.911194
1975-03-31  35.434316
1975-04-30  35.980265
1975-05-31  36.576786

In [3]:
fiddy_states = pd.read_html("https://simple.wikipedia.org/wiki/List_of_U.S._states"); #this is the list of tables
print(fiddy_states);


[               0               1               2                  3
0   Abbreviation      State Name         Capital     Became a State
1             AL         Alabama      Montgomery  December 14, 1819
2             AK          Alaska          Juneau    January 3, 1960
3             AZ         Arizona         Phoenix  February 14, 1912
4             AR        Arkansas     Little Rock      June 15, 1836
5             CA      California      Sacramento  September 9, 1850
6             CO        Colorado          Denver     August 1, 1876
7             CT     Connecticut        Hartford    January 9, 1788
8             DE        Delaware           Dover   December 7, 1787
9             FL         Florida     Tallahassee      March 3, 1845
10            GA         Georgia         Atlanta    January 2, 1788
11            HI          Hawaii        Honolulu    August 21, 1959
12            ID           Idaho           Boise       July 3, 1890
13            IL        Illinois     Springfield   December 3, 1818
14            IN         Indiana    Indianapolis  December 11, 1816
15            IA            Iowa      Des Moines  December 28, 1846
16            KS          Kansas          Topeka   January 29, 1861
17            KY        Kentucky       Frankfort       June 1, 1792
18            LA       Louisiana     Baton Rouge     April 30, 1812
19            ME           Maine         Augusta     March 15, 1820
20            MD        Maryland       Annapolis     April 28, 1788
21            MA   Massachusetts          Boston   February 6, 1788
22            MI        Michigan         Lansing   January 26, 1837
23            MN       Minnesota      Saint Paul       May 11, 1858
24            MS     Mississippi         Jackson  December 10, 1817
25            MO        Missouri  Jefferson City    August 10, 1821
26            MT         Montana          Helena   November 8, 1889
27            NE        Nebraska         Lincoln      March 1, 1867
28            NV          Nevada     Carson City   October 31, 1864
29            NH   New Hampshire         Concord      June 21, 1788
30            NJ      New Jersey         Trenton  December 18, 1787
31            NM      New Mexico        Santa Fe    January 6, 1912
32            NY        New York          Albany      July 26, 1788
33            NC  North Carolina         Raleigh  November 21, 1789
34            ND    North Dakota        Bismarck   November 2, 1889
35            OH            Ohio        Columbus      March 1, 1803
36            OK        Oklahoma   Oklahoma City  November 16, 1907
37            OR          Oregon           Salem  February 14, 1859
38            PA    Pennsylvania      Harrisburg  December 12, 1787
39            RI    Rhode Island      Providence       May 19, 1790
40            SC  South Carolina        Columbia       May 23, 1788
41            SD    South Dakota          Pierre   November 2, 1889
42            TN       Tennessee       Nashville       June 1, 1796
43            TX           Texas          Austin  December 29, 1845
44            UT            Utah  Salt Lake City    January 4, 1896
45            VT         Vermont      Montpelier      March 4, 1791
46            VA        Virginia        Richmond      June 25, 1788
47            WA      Washington         Olympia  November 11, 1889
48            WV   West Virginia      Charleston      June 20, 1863
49            WI       Wisconsin         Madison       May 29, 1848
50            WY         Wyoming        Cheyenne      July 10, 1890,                                                   0   \
0  v t e Political divisions of the United States...   
1     v t e Political divisions of the United States   
2                                                NaN   
3                                             States   
4                                                NaN   
5                                   National capital   
6                                                NaN   
7                                      Large islands   
8                                                NaN   
9                                      Small islands   

                                                  1   2       3   \
0     v t e Political divisions of the United States NaN  States   
1                                                NaN NaN     NaN   
2                                                NaN NaN     NaN   
3  Alabama · Alaska · Arizona · Arkansas · Califo... NaN     NaN   
4                                                NaN NaN     NaN   
5                               District of Columbia NaN     NaN   
6                                                NaN NaN     NaN   
7  American Samoa · Guam · Northern Mariana Islan... NaN     NaN   
8                                                NaN NaN     NaN   
9  Baker Island · Howland Island · Jarvis Island ... NaN     NaN   

                                                  4   5                 6   \
0  Alabama · Alaska · Arizona · Arkansas · Califo... NaN  National capital   
1                                                NaN NaN               NaN   
2                                                NaN NaN               NaN   
3                                                NaN NaN               NaN   
4                                                NaN NaN               NaN   
5                                                NaN NaN               NaN   
6                                                NaN NaN               NaN   
7                                                NaN NaN               NaN   
8                                                NaN NaN               NaN   
9                                                NaN NaN               NaN   

                     7   8              9   \
0  District of Columbia NaN  Large islands   
1                   NaN NaN            NaN   
2                   NaN NaN            NaN   
3                   NaN NaN            NaN   
4                   NaN NaN            NaN   
5                   NaN NaN            NaN   
6                   NaN NaN            NaN   
7                   NaN NaN            NaN   
8                   NaN NaN            NaN   
9                   NaN NaN            NaN   

                                                  10  11             12  \
0  American Samoa · Guam · Northern Mariana Islan... NaN  Small islands   
1                                                NaN NaN            NaN   
2                                                NaN NaN            NaN   
3                                                NaN NaN            NaN   
4                                                NaN NaN            NaN   
5                                                NaN NaN            NaN   
6                                                NaN NaN            NaN   
7                                                NaN NaN            NaN   
8                                                NaN NaN            NaN   
9                                                NaN NaN            NaN   

                                                  13  
0  Baker Island · Howland Island · Jarvis Island ...  
1                                                NaN  
2                                                NaN  
3                                                NaN  
4                                                NaN  
5                                                NaN  
6                                                NaN  
7                                                NaN  
8                                                NaN  
9                                                NaN  ,                                                 0  \
0  v t e Political divisions of the United States   
1                                             NaN   
2                                          States   
3                                             NaN   
4                                National capital   
5                                             NaN   
6                                   Large islands   
7                                             NaN   
8                                   Small islands   

                                                   1  
0                                                NaN  
1                                                NaN  
2  Alabama · Alaska · Arizona · Arkansas · Califo...  
3                                                NaN  
4                               District of Columbia  
5                                                NaN  
6  American Samoa · Guam · Northern Mariana Islan...  
7                                                NaN  
8  Baker Island · Howland Island · Jarvis Island ...  ]

In [6]:
for abbreviation in fiddy_states[0][0][1:]: #element[0][0][0] is "Abbreviation"
    print("FMAC/HPI_" + abbreviation);


FMAC/HPI_AL
FMAC/HPI_AK
FMAC/HPI_AZ
FMAC/HPI_AR
FMAC/HPI_CA
FMAC/HPI_CO
FMAC/HPI_CT
FMAC/HPI_DE
FMAC/HPI_FL
FMAC/HPI_GA
FMAC/HPI_HI
FMAC/HPI_ID
FMAC/HPI_IL
FMAC/HPI_IN
FMAC/HPI_IA
FMAC/HPI_KS
FMAC/HPI_KY
FMAC/HPI_LA
FMAC/HPI_ME
FMAC/HPI_MD
FMAC/HPI_MA
FMAC/HPI_MI
FMAC/HPI_MN
FMAC/HPI_MS
FMAC/HPI_MO
FMAC/HPI_MT
FMAC/HPI_NE
FMAC/HPI_NV
FMAC/HPI_NH
FMAC/HPI_NJ
FMAC/HPI_NM
FMAC/HPI_NY
FMAC/HPI_NC
FMAC/HPI_ND
FMAC/HPI_OH
FMAC/HPI_OK
FMAC/HPI_OR
FMAC/HPI_PA
FMAC/HPI_RI
FMAC/HPI_SC
FMAC/HPI_SD
FMAC/HPI_TN
FMAC/HPI_TX
FMAC/HPI_UT
FMAC/HPI_VT
FMAC/HPI_VA
FMAC/HPI_WA
FMAC/HPI_WV
FMAC/HPI_WI
FMAC/HPI_WY

In [ ]: