In [1]:
import pandas as pd
from datetime import *
import os
import urllib
In [2]:
# this determine the url where the file can be found
url = 'http://www.federalreserve.gov/econresdata/researchdata/feds200628.xls'
In [3]:
# this opens the url
f = urllib.request.urlopen(url)
In [4]:
# the downloads the url and makes a local file. basename will name the file with the last bit of text before the .xls (so feds200628)
with open(os.path.basename(url), "wb") as local_file:
local_file.write(f.read())
In [10]:
#simple Pandas to open a file and make a df
xls_file = pd.ExcelFile(os.path.basename(url))
At this point, you'll hit an error becayse this file is basically .xml in disguise. However, if you simply open the file in Excel, do "save as" and keep it exactly the same, inlcuding still in .xls format, you can return to this script and keep running it as normal without any further changes
In [11]:
xls_df = xls_file.parse('Yields', header = 9).reset_index()
In [12]:
xls_df.rename(columns={'index':'Date'}, inplace=True)
In [13]:
xls_df.Date = pd.to_datetime(xls_df.Date)
In [14]:
xls_df_indexed = xls_df.set_index('Date')
In [15]:
xls_df_indexed[xls_df_indexed.index.is_month_end == True].head()
Out[15]:
In [16]:
csv_df = pd.read_csv('/Users/sarahbeckett-hile/Downloads/feds200628(3).csv', skiprows=9) #will need to customize
In [17]:
csv_df.rename(columns={'Unnamed: 0':'Date'}, inplace=True)
In [18]:
csv_df.Date = pd.to_datetime(csv_df.Date)
In [19]:
csv_df_indexed = csv_df.set_index('Date')
In [20]:
csv_df_indexed[csv_df_indexed.index.is_month_end == True].head()
Out[20]:
In [ ]: