<jupyter_start><jupyter_text>Above from https://data.sba.gov/dataset/ppp-foia/resource/6b62a44b-69ec-436a-9b95-0ea550475543<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import datetime
%matplotlib inline
df_public_150k_plus_210630 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/cfd2e743-8809-49be-90b6-0e22f453be23/download/public_150k_plus_211121.csv')
df_public_up_to_150k_1 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_1_211121.csv')
df_public_up_to_150k_2 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_2_211121.csv')
df_public_up_to_150k_3 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_3_211121.csv')
df_public_up_to_150k_4 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_4_211121.csv')
df_public_up_to_150k_5 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_5_211121.csv')
df_public_up_to_150k_6 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_6_211121.csv')
df_public_up_to_150k_7 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_7_211121.csv')
df_public_up_to_150k_8 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_8_211121.csv')
df_public_up_to_150k_9 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_9_211121.csv')
df_public_up_to_150k_10 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_10_211121.csv')
df_public_up_to_150k_11 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_11_211121.csv')
df_public_up_to_150k_12 = pd.read_csv('https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/3af99a69-abb2-40f8-bcb3-52c6f745ebdf/download/public_up_to_150k_12_211121.csv')
dir()
df_public_150k_plus_210630.info()

df_total = df_public_150k_plus_210630.copy()
df_small_total = df_public_150k_plus_210630.copy()
df_total.info()
df_total = df_total.append(df_public_up_to_150k_1)
df_total = df_total.append(df_public_up_to_150k_2)
df_total = df_total.append(df_public_up_to_150k_3)
df_total = df_total.append(df_public_up_to_150k_4)
df_total = df_total.append(df_public_up_to_150k_5)
df_total = df_total.append(df_public_up_to_150k_6)
df_total = df_total.append(df_public_up_to_150k_7)
df_total = df_total.append(df_public_up_to_150k_8)
df_total = df_total.append(df_public_up_to_150k_9)
df_total = df_total.append(df_public_up_to_150k_10)
df_total = df_total.append(df_public_up_to_150k_11)
df_total = df_total.append(df_public_up_to_150k_12)
df_total.info()

import pickle
with open('NewestSBA_21Nov21', 'wb') as ofile:
    pickle.dump(df_total, ofile)
df_small_total = df_small_total.append(df_public_up_to_150k_1_210630)
df_small_total['ForgivenessAmount'].sum() 
df_small_total.info()
stop = stop
df_total['ForgivenessAmount'].sum() 
df_public_up_to_150k_13_210630.head()
df_total[df_total['LoanNumber'] == 5375617707]
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_22.csv
df_below_150_22_SBA = pd.read_csv('public_up_to_150k_22.csv')
w = df_below_150_22_SBA
len(w)
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_23.csv
df_below_150_23_SBA = pd.read_csv('public_up_to_150k_23.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_24.csv
df_below_150_24_SBA = pd.read_csv('public_up_to_150k_24.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_25.csv
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_26.csv
df_below_150_26_SBA = pd.read_csv('public_up_to_150k_26.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_27.csv
df_below_150_27_SBA = pd.read_csv('public_up_to_150k_27.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_28.csv
df_below_150_28_SBA = pd.read_csv('public_up_to_150k_28.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_29.csv
df_below_150_29_SBA = pd.read_csv('public_up_to_150k_29.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_30.csv
df_below_150_30_SBA = pd.read_csv('public_up_to_150k_30.csv')
!wget https://data.sba.gov/dataset/8aa276e2-6cab-4f86-aca4-a7dde42adf24/resource/88d9f6f7-b471-4f5b-ae3e-7b9d7b394ad2/download/public_up_to_150k_2595.csv
df_below_150_2595_SBA = pd.read_csv('public_up_to_150k_2595.csv')
df_below_150_SBA_2.head()
df_below_150_SBA_1.head()
df_below_150_SBA_2.head()
df_below_150_3_SBA.head()
df_below_150_4_SBA.head()
df_below_150_5_SBA.head()
df_below_150_6_SBA.head()
df_below_150_7_SBA.head()
df_below_150_8_SBA.head()
df_below_150_9_SBA.head()
df_below_150_10_SBA.head()
df_below_150_SBA_1.info()
result_newest = df_all_high_SBA.append(df_below_150_3_SBA)
result_newest = result_newest.append(df_below_150_4_SBA)
result_newest = result_newest.append(df_below_150_5_SBA)
result_newest = result_newest.append(df_below_150_6_SBA)
result_newest = result_newest.append(df_below_150_7_SBA)
result_newest = result_newest.append(df_below_150_8_SBA)
result_newest = result_newest.append(df_below_150_9_SBA)
result_newest.info()<jupyter_output><empty_output><jupyter_text>Seems links to below 150 SBA loans a bit whacky with 3-9 appearing to contain all the uniques<jupyter_code>import pickle
with open('NewestSBA_22Jun21', 'wb') as ofile:
    pickle.dump(result_newest, ofile)
with open('NewestSBA_22Jun21', 'rb') as ifile:
    xxx = pickle.load(ifile)
xxx.info()
xxxx=zzzzzz
dir()
!df -k
!ls -alt
type(a)
len(a)
len(b)
result_new = a.append([b])
print(result_new.shape)
result_new = result_new.append([c])
result_new = result_new.append([d])
result_new = result_new.append([e])
result_new = result_new.append([f])
result_new = result_new.append([g])
result_new = result_new.append([h])
result_new = result_new.append([i])
result_new = result_new.append([j])
result_new = result_new.append([k])
result_new = result_new.append([l])
result_new = result_new.append([m])
result_new = result_new.append([n])
result.shape
result_new.shape
result.info()
result_new.info()
result_new.ForgivenessAmount.sum()
result_new.ForgivenessDate.value_counts()
yyy = result_new[result_new['BorrowerName'].str.contains('Catholic', case = False, na= False)]
yyy.sort_values(by='ForgivenessAmount', ascending=False)[['BorrowerName','BorrowerAddress','BorrowerCity','CurrentApprovalAmount','ForgivenessAmount']]
yyy.CurrentApprovalAmount.sum()
yyy.ForgivenessAmount.sum()
import pickle
with open('18_Jun_21_Combined_PPP.pkl', 'wb') as ofile:
    result = pickle.dump(result_new, ofile)
with open('18_Jun_21_Combined_PPP.pkl', 'rb') as ifile:
    test_result_new = pickle.load(ifile)
test_result_new.shape
result_new.shape
!df -k
xxx = stop here
import pickle
with open('6_Jun_21_Combined_PPP.pkl', 'rb') as ifile:
    result = pickle.load(ifile)
result.info()
result =  result_prev


!ls -alt *.pkl
xxx.info()
xxx.describe()
xxx.CurrentApprovalAmount.sum()

1747633680
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
xxx = result[result['BorrowerName'].str.contains('Catholic', case = False, na= False)]
xxx[['BorrowerName','CurrentApprovalAmount']]
xxx[['BorrowerName','BorrowerAddress','BorrowerCity','CurrentApprovalAmount']].sor
xxx.sort_values(by='CurrentApprovalAmount', ascending=False)[['BorrowerName','BorrowerAddress','BorrowerCity','CurrentApprovalAmount']]
xxx_no_index = xxx.to_string(index=False)
print(xxx_no_index)
with open('', 'rb') as ifile:
    result_prev = pickle.load(ifile)
result[(result['BorrowerCity'].str.contains('antonio', case = False, na= False)) & (result['BorrowerName'].str.contains(r'MUY ', case = False, na = False))][['BorrowerName','CurrentApprovalAmount', 'BorrowerAddress','BorrowerCity']]
result[(result['BorrowerCity'].str.contains('antonio', case = False, na= False)) & (result['BorrowerName'].str.contains(r'MUY ', case = False, na = False))]['CurrentApprovalAmount'].sum()
result[result['BorrowerCity'].str.contains('antonio', case = False, na= False)]['BorrowerAddress'].value_counts()[:10]
result[(result['BorrowerCity'].str.contains('antonio', case = False, na= False)) & (result['BorrowerAddress'] == r'2161 NW Military Hwy Ste 300')][['BorrowerName', 'CurrentApprovalAmount', 'BorrowerAddress', 'BorrowerCity', 'OriginatingLender', 'DateApproved','LoanNumber']].sort_values('BorrowerName')
result[(result['BorrowerCity'].str.contains('antonio', case = False, na= False)) & (result['BorrowerAddress'] == r'2161 NW Military Hwy Ste 300')]['CurrentApprovalAmount'].sum()
    
result[result['BorrowerName'].str.contains('ramakaya', case= False,na = False)]
result[(result['BorrowerCity'].str.contains('schertz', case = False, na= False)) & (result['BorrowerAddress'].str.contains('17220 INTERSTATE 35'))]
aaa = result[(result['BorrowerCity'].str.contains('antonio', case = False, na= False)) & (result['BorrowerAddress'] == r'2161 NW Military Hwy Ste 300')][['BorrowerName']]
aaa.iloc[:,[0]]
aaa.set_index('BorrowerName', inplace = True)
type(aaa)
for x in aaa.index:
    print(x)
    print(result[result['BorrowerName'] == x][['BorrowerName', 'CurrentApprovalAmount', 'DateApproved', 'BorrowerAddress', 'OriginatingLender', 'OriginatingLenderCity']])
result[(result['OriginatingLender'] == 'City Bank') & (result['OriginatingLenderCity'] == 'LUBBOCK')]
zzz = pd.DataFrame()
zzz = pd.DataFrame()

for x in aaa.index:
    zzz.append(result[result['BorrowerName'] == x][['BorrowerName', 'CurrentApprovalAmount', 'DateApproved', 'BorrowerAddress', 'OriginatingLender', 'OriginatingLenderCity']])
    print(x)
    print(result[result['BorrowerName'] == x][['BorrowerName', 'CurrentApprovalAmount', 'DateApproved', 'BorrowerAddress', 'OriginatingLender', 'OriginatingLenderCity']])
zzz
result[(result['OriginatingLender'] == 'City Bank') & (result['OriginatingLenderCity'] == 'LUBBOCK') & (result['BorrowerCity'] == 'San Antonio')]
aaa
result[(result['BorrowerName'].str.contains('kama', case= False,na = False)) & (result['BorrowerState'] == 'TX')]
result[(result['BorrowerName'].str.contains('Marcosyajoda', case= False,na = False)) & (result['BorrowerState'] == 'TX')]
!wget https://files.zillowstatic.com/research/public_v2/sales_count_now/Metro_sales_count_now_uc_sfrcondo_raw_month.csv?t=1623191914
!ls -alt
!mv Metro_sales_count_now_uc_sfrcondo_raw_month.csv\?t\=1623191914 Metro_sales_count_now_uc_sfrcondo_raw_month.csv
Metro_sales_df = pd.read_csv('Metro_sales_count_now_uc_sfrcondo_raw_month.csv')
len(Metro_sales_df)
Metro_sales_df['RegionName'].value_counts()
Metro_sales_df[Metro_sales_df['RegionName'] == 'San Antonio, TX']
!wget https://files.zillowstatic.com/research/public_v2/med_listings_price_cut_perc/Metro_med_listings_price_cut_perc_uc_sfrcondo_smoothed_week.csv?t=1623191914
Metro_price_cut_df = pd.read_csv('Metro_med_listings_price_cut_perc_uc_sfrcondo_smoothed_week.csv?t=1623191914')
len(Metro_price_cut_df)
Metro_price_cut_df[Metro_price_cut_df['RegionName'].str.contains('san', case = False)]
xxxx=stop_here
type(a.DateApproved)
pd.to_datetime(a.DateApproved[0])
a.loc[:, 'DateApproved_datetime'] = pd.to_datetime(a['DateApproved'])
a.info()
a.tail()
print(set(a.DateApproved_datetime.dt.month))
a.columns
a.dtypes
a.info()
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
import pandas as pd
a.head()
a[a.DateApproved_datetime <'2020-05-01T00:00:00.000000000']
a['CD']
a['CD'].iloc[6]
a.head()
a
z = pd.read_csv('public_150k_plus.csv', index_col='LoanNumber')
z
z.dtypes
z.head()
z.tail(11)
z.columns
a.columns
z.index
a.index
a.describe()
z.describe()
a[:5]
z[0:5]
z.head()
a
a[a.BorrowerState == 'TX'].count()
a.loc[791524]
a.iloc[791524,2]
a[a.BorrowerAddress == '3400 N. Central Expressway Suite 110-243']
a[a.BorrowerName == r'SWH MIMIS CAFE LLC']
a.loc[791578]
a.shape
a.columns
a.count()
a.BorrowerZip.describe()
a.last_valid_index()
z.last_valid_index()
a.iloc[945919]
a.iloc[:3]
z.loc[9547507704]
z.iloc[945918]
pd.show_versions()
a.loc[5, ['DateApproved', 'ProcessingMethod']]
a.iloc[5, [1,3]]
!uptime
a.iloc[range(0,2),[1,2,4]]
a.loc[range(0,2), ['DateApproved', 'SBAOfficeCode', 'BorrowerName']]
a.iloc[1:3,:]
a.iloc[0:2]
a.groupby('BorrowerState')['DateApproved'].count()
a['BorrowerState'].describe()
a['BorrowerState'].unique()
a.iloc[a['BorrowerState' == 'TX']]
a.tail(1)
z.tail(1)
a.iloc[-1]
a.loc[1,'DateApproved']
a.groupby('BorrowerZip')['BorrowerName'].count()
a.groupby('BorrowerZip')['BorrowerName'].nunique()
a.groupby('BorrowerZip').nunique()
a.T.head()
b.count()
900000 - 899872
b_missing_dropped = b.dropna()
b.shape
b_missing_dropped.shape
a.describe()
!uptime
dir()
df_all_high_SBA.info()
df_all_high_SBA[df_all_high_SBA['BorrowerName'].str.contains('TEXAS DISP', na = False)]
df_all_high_SBA[df_all_high_SBA['BorrowerAddress'].str.contains('STARCREST', na = False)]
dir()
result.info()
result_prev.info()
did<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15057755775577558, 'fraction_numerical': 0.1043041804180418, 'mean_word_length': 8.371778350515465, 'pattern_counts': {'":': 0, '<': 10, '<?xml version=': 0, '>': 9, 'https://': 26, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 24, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '307341', 'license': 'MIT', 'n_tokens_mistral': 7198, 'n_tokens_neox': 6435, 'n_words': 613, 'path': 'Updating_On_AWS.ipynb', 'repo_name': 'ldhagen/SBA_PPP_Notebook_Work', 'size': '14544'}