<jupyter_start><jupyter_text>Wake County DataAssessor data come from [here](http://www.wakegov.com/tax/realestate/redatafile/Pages/default.aspx)Land use [codes](http://www.wakegov.com/tax/realestate/Documents/CodeDescriptions.pdf)Lat/lon data on parcels come from [NC OneMap](https://services.nconemap.gov/ncom-parcels-dd.htm). Lat/lon data are merged with Block Group data from US Census [Tiger](https://www2.census.gov/geo/pvs/tiger2010st/37_North_Carolina/37183/)  Load data<jupyter_code>df = pd.read_excel("/".join([data_path, 'realestdata07032020.xlsx']))
print("Initial data size: %d" %df.shape[0])<jupyter_output>Initial data size: 412624<jupyter_text>Clean up data<jupyter_code># Select only residential properties:
df.drop((df.loc[df.TYPE_AND_USE > 10].index),inplace=True)
#df.columns
# drop observations with missing type_and_use
df.drop(df.loc[df.TYPE_AND_USE.isnull()].index,inplace=True)
df.shape[0]
df.columns
df.Parcel_Identification.iloc[:5]
df[['Parcel_Identification','OWNER1','Street_Number', 'Street_Prefix', 'Street_Name','Township']].iloc[0]<jupyter_output><empty_output><jupyter_text>Get property holdings- maybe get rid of City/County owners- some residences don't have units despite being single-residence even multi-family<jupyter_code>cntsbyownr = df.groupby('OWNER1').UNITS.sum()
cntsbyownr.sort_values(ascending=False).head(n=10)
cntsbyownr.sort_values(ascending=False).tail(n=10)
df.loc[df.UNITS==0, ['NUM_of_Rooms','UNITS','TYPE_AND_USE', 'Total_sale_Price','Land_Sale_Date', 'PROPERTY_DESCRIPTION','CITY','DESIGN_STYLE']]
m = df.PROPERTY_DESCRIPTION.isnull().sum()
print("Number of observations without a description: %d" %m)
cond = df.PROPERTY_DESCRIPTION.notnull()
cond2 = df.loc[cond].PROPERTY_DESCRIPTION.str.contains('Downton Square Townhomes BM2019-01749')
df.loc[cond & cond2,['NUM_of_Rooms','UNITS','TYPE_AND_USE', 'Total_sale_Price', 'PROPERTY_DESCRIPTION','CITY','DESIGN_STYLE']]
df.loc[(df.TYPE_AND_USE == 1) & (df.UNITS>1), ['UNITS','PROPERTY_DESCRIPTION','OWNER1','DESIGN_STYLE']]
df[df.OWNER1.str.contains("COLEMAN GROUP INC")]<jupyter_output><empty_output><jupyter_text>Data issuesThe data aren't perfect. - There are plenty of observations with 0 units despite being existing residential units (hand-verified). - Similarly, there are a handful of observations designated as single-family residential but actually containing multiple units because they're condos, apartment complexes, etc (hand-checked a couple of addresses). This lack of use and unit designation is likely not unique to single-family properties. - There are also observations with no sale dates but prices and vice versa though exchange prices are not known for most anyway. - Of course, there are also the name issues. For example: HUANG, TONY TSENG & SUE LIAO  and HUANG, TONY TSENG TE & SUE LIAO are likely the same owners yet they won't be shown as such because of "TE". It's possible to hand fix some of these but not sysmetically.These things cannot be easily reconsiled so presently I'll admin that HHI estimations are approximate at best.  Assess data<jupyter_code>m = df.loc[:,'OWNER1'].unique().shape[0]
n = df.shape[0]
print("Number of unique owners: %d of %d observations or %.02f of total" %(m,n,m/n))
m = df.loc[:,'Mailing_address1'].unique().shape[0]
print("Number of unique mailing addresses: %d and share of observations: %.2f" %(m,m/n))
cntsbyownr = df.groupby('OWNER1').UNITS.sum()
cntsbyownr.sort_values(ascending=False)
m = (cntsbyownr==0).sum()
n = cntsbyownr.shape[0]

print("Number of owners: %d" %n)
print("Number of owners with 0 units: %d" %m)<jupyter_output>Number of owners: 299855
Number of owners with 0 units: 1055<jupyter_text>HHI by city<jupyter_code>findcol('city')
byCity = df.groupby(['PHYSICAL_CITY','OWNER1']).UNITS.sum()
shares = byCity/byCity.groupby(level=0).transform('sum')
(shares**2).groupby(level=0).sum().sort_values()<jupyter_output><empty_output><jupyter_text>Exclude city and county owners<jupyter_code>tooust = df.loc[df.OWNER1.str.contains("CITY OF|COUNTY OF"),'OWNER1'].unique()
tooust = "|".join(tooust)

cond = ~df.OWNER1.str.contains(tooust)
byCity = df.loc[cond].groupby(['PHYSICAL_CITY','OWNER1']).UNITS.sum()
shares = byCity/byCity.groupby(level=0).transform('sum')
(shares**2).groupby(level=0).sum().sort_values()<jupyter_output><empty_output><jupyter_text>HHIs by Zipcode<jupyter_code>byCity = df.loc[cond].groupby(['PHYSICAL_ZIP_CODE','OWNER1']).UNITS.sum()
shares = byCity/byCity.groupby(level=0).transform('sum')
(shares**2).groupby(level=0).sum().sort_values()<jupyter_output><empty_output><jupyter_text>HHI by Neighborhoods<jupyter_code>prs = pd.read_csv("/".join([data_path, 'geocoded_wake_parcels.csv']))
prs.columns
prs.MAILADD.unique().shape[0]
prs.drop_duplicates(subset=['MAILADD','MCITY'],inplace=True)
prs.shape[0]
i = df.shape[0]
print("Shape of data before merger: %d" %i)
t = df.merge(prs, right_on = ['MAILADD', 'MCITY'], left_on = ['Mailing_address1','Mailing_Address2'], how='left')

j = t.shape[0]
print("Shape of data after merger: %d" %j)

k = t.GEOID10.isnull().sum()
print("Observations without matched block group: %d" %k)<jupyter_output>Shape of data before merger: 335199
Shape of data after merger: 335199
Observations without matched block group: 11546<jupyter_text>Looked into some of the mailing addresses that don't have matching parcels and it seems that those addresses simply don't exist in the parcels database. This is partly because in some cases, the address was put into the system incorrectly (ie 1408 Sherron Rd as a city entry) while in other cases we see NEW YORK, NY among the entries.<jupyter_code>t.loc[t.GEOID10.isnull(),'Mailing_Address2'].str.replace(" \d+-\d+",'').unique()<jupyter_output><empty_output><jupyter_text>HHI by Track and Block Group<jupyter_code># exclude cities and counties
tooust = t.loc[t.OWNER1.str.contains("CITY OF|COUNTY OF"),'OWNER1'].unique()
tooust = "|".join(tooust)

cond = ~t.OWNER1.str.contains(tooust)
totals = t.loc[cond].groupby(['GEOID10','OWNER1']).UNITS.sum()
shares = totals/totals.groupby(level=0).transform('sum')
bgHHIs = (shares**2).groupby(level=0).sum().sort_values()
unittotal = totals.groupby(level=0).sum()
numOwners = totals.groupby(level=0).count()
bgHHIs = pd.concat([unittotal,numOwners,bgHHIs],axis=1, keys=['nbr_units','num_owners','HHI'])
totals = t.loc[cond].groupby(['TRACTCE10','OWNER1']).UNITS.sum()
shares = totals/totals.groupby(level=0).transform('sum')
tractHHIs = (shares**2).groupby(level=0).sum().sort_values()
unittotal = totals.groupby(level=0).sum()
numOwners = totals.groupby(level=0).count()
tractHHIs = pd.concat([unittotal,numOwners,tractHHIs],axis=1, keys=['nbr_units','num_owners','HHI'])
fig, axes = plt.subplots(1,2, sharey=True)
fig.set_figwidth(8)
fig.set_figheight(6)

tractHHIs.HHI.plot(kind='box', ax=axes[0])
axes[0].set_title("Tract level", fontsize=18)
axes[0].set_ylabel("HHI", fontsize=16)

bgHHIs.HHI.plot(kind='box', ax=axes[1])
axes[1].set_title("Block Group level", fontsize=18)
axes[1].set_ylabel("HHI", fontsize=16)

plt.tight_layout()<jupyter_output><empty_output><jupyter_text>Merge TIGER data with HHI output<jupyter_code>## TIGER data
bgshp = gpd.read_file("/".join([data_path, 'tl_2010_37183_bg10','tl_2010_37183_bg10.shp']))
tractshp = gpd.read_file("/".join([data_path, 'tl_2010_37183_tract10','tl_2010_37183_tract10.shp']))
# drop superfluous columns
cols2drop = ['NAMELSAD10', 'MTFCC10', 'FUNCSTAT10', 'ALAND10', 'AWATER10',
       'INTPTLAT10', 'INTPTLON10']

for tmp in [bgshp, tractshp]:
    tmp.drop(cols2drop,axis=1,inplace=True)
bgshp.loc[:,'GEOID10'] = bgshp.GEOID10.astype(np.float)

# merge block group shapes to data
bgHHIs = bgHHIs.merge(bgshp, left_index=True,right_on='GEOID10',how='left')
# merger with tiger data
tractshp.loc[:,'TRACTCE10'] = tractshp.TRACTCE10.astype(np.int)
tractHHIs = tractHHIs.merge(tractshp, left_index=True, right_on='TRACTCE10', how='left')
def labelMaker(cuts):
    labs = []
    for i in range(len(cuts)-1):
        #print(i)
        labs.append("%.03f - %.03f" %(cuts[i],cuts[i+1]))
    return(labs)   
for tmp in [tractHHIs, bgHHIs]:
    qcuts = np.array([0,.1,.2,.5,1])#tmp.HHI.quantile(np.arange(0,1.2,.2)).round(3)
    tmp.loc[:,'qcuts'] = pd.cut(tmp.HHI, qcuts, labels = labelMaker(qcuts)).astype('str')
tractHHIs.qcuts.unique()
bgHHIs.qcuts.unique()
for tmp,key in zip([tractHHIs,bgHHIs],['tract','bg']):
    gpd.GeoDataFrame(tmp, crs = bgshp.crs).to_file("/".join([data_path, "wake_cnty_%s_hhi.shp" %key ]))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16159718144450969, 'fraction_numerical': 0.029712272460364064, 'mean_word_length': 7.110476190476191, 'pattern_counts': {'":': 0, '<': 42, '<?xml version=': 0, '>': 44, 'https://': 2, 'lorem ipsum': 0, 'www.': 3, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '116283', 'license': 'MIT', 'n_tokens_mistral': 3367, 'n_tokens_neox': 3170, 'n_words': 624, 'path': 'marketshare Wake County.ipynb', 'repo_name': 'kiwiPhrases/blognotebooks', 'size': '8515'}