<jupyter_start><jupyter_text>Special Features of the CensusDataframeUse the ``publicdata.parse_app_url`` to create a new Census URL.<jupyter_code>from publicdata import parse_app_url
import pandas as pd
u = parse_app_url('census://2016/5/CA/county/B01001')
df = u.dataframe<jupyter_output><empty_output><jupyter_text>DisplayThe ``.table`` method has acessors for displaying information about the table, and most importantly, has a pretty display in Jupyter.<jupyter_code>df.table<jupyter_output><empty_output><jupyter_text>The ``.titles`` property returns the dataframe with colum names that include the Census column titles, which make it much easier to find the columns you want to use. Combine with ``.head().T`` for an easier-to-read format.<jupyter_code>df.titles.head().T<jupyter_output><empty_output><jupyter_text>The codes for the column are the first word in the titles. The margin columns are suffixed with '_m90' and are not converted with titles. The ``.search_columns`` function returs columns that contain a string or strings, or match a regular expression<jupyter_code>pd.set_option('display.max_colwidth', -1)
df.search_columns('Total').head()<jupyter_output><empty_output><jupyter_text>Or, use a regular expression<jupyter_code>import re
df.search_columns(re.compile("Total Pop.*(Female|Male):$")).head()<jupyter_output><empty_output><jupyter_text>You can also have more than one term -- they are OR'd -- and mix strings and regexes<jupyter_code>import re
df.search_columns('14', re.compile("Total Pop.*(Female|Male):$")).head()<jupyter_output><empty_output><jupyter_text>Math with MarginsThe dataframe understands the margins and can perform the correct math for summation and ratios.  SummationThe ``.sum_m`` and ``add_sum_m`` return  summation columns and add them to the dataset, respectively.  First, lets get a subset of the dataframe.<jupyter_code>total_cols = list(df.search_columns(re.compile("Total Pop.*(Female|Male):$")).code)

sdf = df[total_cols].copy()
print(total_cols)
sdf.head()<jupyter_output>['B01001_002', 'B01001_026']<jupyter_text>Note that we only request 2 columns, but the indexing also returned the margin columsn, so we got 4. Now we can add the two together. We'll get 2 columns, one for the sum, and one for the margin.<jupyter_code>x = pd.DataFrame()

x['total'], x['total_margin'] = df.sum_m('B01001_002', 'B01001_026')

x.head()<jupyter_output><empty_output><jupyter_text>The ``.add_sum_m`` does all of the hard work of adding the columns to the dataset, but requires the first parameter to be the name of the resulting column.<jupyter_code>x = sdf.add_sum_m('total', 'B01001_002', 'B01001_026')
sdf.head() # or x, since the add_sum_m returns the dataframe<jupyter_output><empty_output><jupyter_text>Caution with Sums!There are some interesting problems with sums. Lets look at the sum of Male and Female compared to the total columns<jupyter_code>search = df.search_columns(re.compile("Total Pop.*(Female|Male|Total):$"))
search
sdf = df[list(search.code)].copy()

sdf.add_sum_m('total', 'B01001_002', 'B01001_026')

sdf['total_ratio'] = sdf.B01001_001 / sdf.total

sdf[['B01001_001','total', 'total_ratio']].head()<jupyter_output><empty_output><jupyter_text>Note that the ratio is not 1, meaning that the sum of males and females is not the same as the reported total. The clue as to why is in the margins column for the total, `B01001_001_m90`: it is mostly 0. A value of 0 indicates that these values were taken from a controlled source, usually administrative data. Since the number was not pproduced with samploy, there is no sample error. The male and female values were calculated from samples, so they are different and have an error.The moral is to always check if the value you want to calculate already exists.   Ratios and Proportions The ``.ratio`` and ``.proportion`` methods are used for divding numbers.* For ratio: The numerator should not be a subset of the denominator, such as the ratio of males to females. * For proportion:  The numerator should be a subset of the denominator,  So, if you are dividing  males by females, use the ``.ratio`` method. If you are dividing males by the total population, use the ``.proportion`` method. ( The underlying reason for the difference has to do with whether the resulting value can be greater than 1 )<jupyter_code>sdf.titles.head().T
sdf['mf_ratio'], sdf['mf_ratio_m'] = sdf.ratio( 'B01001_002', 'B01001_026') # male/female
sdf[['mf_ratio','mf_ratio_m']] .head()
sdf['male_prop'], sdf['male_prop_m90'] = sdf.proportion( 'B01001_002', 'B01001_001') # male/total
sdf.head()<jupyter_output><empty_output><jupyter_text>Error Margin ManipulationThe ``.add_rse`` add a relative standard error column<jupyter_code>sdf.add_rse('B01001_001').head()
There are acessors on the estimate colums that will return alternate margins, including: 

* .m90: 90% margins
* .m95: 95% margins
* .m99: 99% margins
* .se: Standard error
* .rse: Relative standard error
sdf.B01001_026.se.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10692200279273888, 'fraction_numerical': 0.034709754637941355, 'mean_word_length': 6.162857142857143, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 48, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '221561', 'license': 'MIT', 'n_tokens_mistral': 1747, 'n_tokens_neox': 1588, 'n_words': 552, 'path': 'notebooks/Special Features of Census Dataframe.ipynb', 'repo_name': 'CivicKnowledge/publicdata', 'size': '5013'}