<jupyter_start><jupyter_text>US Vacancy Rate  National Econcometric Data CollectionGoal: Organize your data to streamline the next steps of yourcapstone<jupyter_code>#Import pandas, matplotlib.pyplot, and seaborn
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import os
import numpy as np
#change directory to get FRED data
path= '/Users/josephfrasca/Coding_Stuff/Springboard/Capstone_2/data/raw/fredgraph'
os.chdir(path)
os.getcwd()
os.listdir()<jupyter_output><empty_output><jupyter_text>Data Loading<jupyter_code># load FRED data - current US national data
df_yr = pd.read_csv('Annual.csv')
df_m = pd.read_csv('Monthly.csv')
df_q = pd.read_csv('Quarterly.csv')<jupyter_output><empty_output><jupyter_text>Data Definition - Before Merge○ Goal: Gain an understanding of your data features to inform thenext steps of your project.○ Time estimate: 1-2 hours■ Column names■ Data types■ Description of the columns■ Counts and percents unique values■ Ranges of values- Hint: here are some useful questions to ask yourself during thisprocess:- Do your column names correspond to what those columnsstore?- Check the data types of your columns. Are they sensible?- Calculate summary statistics for each of your columns, suchas mean, median, mode, standard deviation, range, andnumber of unique values. What does this tell you about yourdata? What do you now need to investigate?<jupyter_code>#see info/describe/unique value counts to get a summary of the data
df_yr.info()
df_yr.describe()
df_yr.nunique()
df_yr['MEHOINUSA672N'].value_counts(normalize=True)*100
df_m.info()
df_m.describe()
df_m['TLRESCONS'].value_counts(normalize=True)*100
df_q.info()
df_q['RRVRUSQ156N'].value_counts(normalize=True)*100
df_q.describe()
#Call the head/tail method to explore FRED data
#SPPOPGROWUSA has data from 1961-01-01 to 2019-01-01
#MEHOINUSA672N has data from 1984-01-01 to 2018-01-01
df_yr
'''
UNRATE data from 1948-01-01 to 2020-07-01
INTDSRUSM193N from 1950-01-01 to 2020-07-01
CUUR0000SEHA from 1914-12-01 to 2020-07-01
CSUSHPINSA from 1987-01-01 to 2020-06-01
HOUST from 1959-01-01 to 2020-07-01
WPUIP2311001 from 1986-06-01 to 2020-07-01
TLRESCONS from 2002-01-01 to 2020-06-01
'''
df_m.tail()
#RRVRUSQ156N is from 1956-01-01 to 2020-04-01
df_q.tail()<jupyter_output><empty_output><jupyter_text>Data Cleaning○ Goal: Clean up the data in order to prepare it for the next steps ofyour project.○ Time estimate: 1-2 hours ■ NA or missing values■ Duplicates- Hint: don’t forget about the following awesome Python functions for datacleaning, which make life a whole lot easier:- loc[] - filter your data by label- iloc[] - filter your data by indexes- apply() - execute a function across an axis of a DataFrame- drop() - drop columns from a DataFrame- is_unique() - check if a column is a unique identifier- Series methods, such as str.contains(), which can be used to check ifa certain substring occurs in a string of a Series, and str.extract(),which can be used to extract capture groups with a certain regex (orregular expression ) pattern- numPy methods like .where(), to clean columns. Recall that suchmethods have the structure: np.where(condition, then, else)- DataFrame methods to check for null values, such asdf.isnull().values.any()<jupyter_code>#convert DATE columns to datetime object
df_q['DATE']= pd.to_datetime(df_q['DATE'])
df_q.dtypes
df_m['DATE']= pd.to_datetime(df_m['DATE'])
df_m.dtypes
df_yr['DATE']= pd.to_datetime(df_yr['DATE'])
df_yr.dtypes<jupyter_output><empty_output><jupyter_text>Data Joining<jupyter_code>#merge yearly df with quarterly: df2
df2 = pd.merge_ordered(df_yr, df_q, fill_method="ffill")
df2
#merge df2 with monthly df: df3
df3 = pd.merge_ordered(df2, df_m, fill_method="ffill")
df3.iloc[853:]
df3.dtypes
#replace '.'s with NaN
df3 = df3.replace(".", np.nan)
#convert dtype objects to floats as needed
df3 = df3.astype({"MEHOINUSA672N": float, "UNRATE": float, "INTDSRUSM193N": float, "CUUR0000SEHA": float, "CSUSHPINSA": float, "HOUST": float, "WPUIP2311001": float, "TLRESCONS": float})
df3.dtypes
#explore combined datas in df3 - stored in data definition table
df3.info()
df3.describe().T
df3.nunique()
df3['RRVRUSQ156N'].value_counts(normalize=True)*100
#change column headers to make more user friendly
df3.rename(columns = {'SPPOPGROWUSA':'uspop_growth', 'MEHOINUSA672N': 'med_hIncome', 'UNRATE': 'unemplt_rate', 'INTDSRUSM193N': 'int_rate', 'CUUR0000SEHA': 'cpi_rent', 'CSUSHPINSA': 'homePrice_index', 'HOUST': 'newHouse_starts', 'WPUIP2311001': 'ppi_resConstruct', 'RRVRUSQ156N': 'rentl_vacnyRate', 'TLRESCONS': 'resConstruct_spending'}, inplace = True)
df3.T
#deal with NAN
#determine the outlier for NaNs
nonnull_counts = df3.apply(lambda x: x.count(), axis=1)
nonnull_counts = pd.DataFrame(nonnull_counts)
nonnull_counts
nonnull_counts.describe()
#plot nonnull counts, seems like index 400-1100 is ballpark 25-75% 
nonnull_counts.plot()
plt.xlabel('index number')
plt.ylabel('non-null counts')
plt.show()
nonnull_counts.plot.hist()
plt.xlabel('non-null counts')
#to get within 1 standard deviation of non null data, need to use rows 421-1266 inclusive
nonnull_counts.iloc[420:]
#the years outside 1 standard deviation of NaN values are pre 1950 and post 6/2020
df3.iloc[421:1267].T
#vacany rate data starts at year 1956, so we can confidently drop rows without rental vacancy rate and get the majority of the data
#create another data frame that drops rows with no vacany rate data
missing_vacancy = df3['rentl_vacnyRate'].isnull()
df3_1956 = df3[missing_vacancy == False]
df3_1956.T
#check if there are any any NaN values left in Rental vacany rate. 
df3_1956['rentl_vacnyRate'].isnull().values.any()
#calculate percent NaN for df3_1956
sum(df3_1956.isna().sum())/sum(df3_1956.count())*100
#calculate percent NaN per column
df3_1956.isna().sum()/len(df3_1956)*100
#calculate percent not NaN per column
df3_1956.count()/len(df3_1956)*100
df3_1956.describe().T
#compare df3 to df3_1956
df3.describe().T
#examine distribution of values, don't seem to be any extreme outliers
df3_1956.hist(figsize=(15,10))
plt.subplots_adjust(hspace=0.5);<jupyter_output><empty_output><jupyter_text>Save Data<jupyter_code>df3_1956.to_csv(r'/Users/josephfrasca/Coding_Stuff/Springboard/Capstone_2/data/interim/df3_1956', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10843373493975904, 'fraction_numerical': 0.06467977171845275, 'mean_word_length': 6.1207674943566595, 'pattern_counts': {'":': 8, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '212420', 'license': 'FTL', 'n_tokens_mistral': 2476, 'n_tokens_neox': 2169, 'n_words': 675, 'path': 'notebooks/1.0-jjf-initial-data-wrangling.ipynb', 'repo_name': 'jjfrasca/Capstone_2', 'size': '6308'}