<jupyter_start><jupyter_text>Extracting and Comparing Year Transit Facility Rebuilt From Notes  Loading in Data In this exercise I will need the regex (re) package, pandas, and natural language toolkit.<jupyter_code>import re
import pandas as pd
import nltk<jupyter_output><empty_output><jupyter_text>I am pulling in a published dataset that my team would like to work with. Each record holds data on a single transit agency facility, reported as of 2019.<jupyter_code>response='https://www.transit.dot.gov/sites/fta.dot.gov/files/2020-10/2019%20Facility%20Inventory.xlsx'
response
df=pd.read_excel(response)<jupyter_output><empty_output><jupyter_text>There is a column for Year Built, as well as when a condition assessment was performed, and any notes that the reporting transit agency felt were relevant to include. I only care about these particular records (along with the name of the agency) and I only care about records that have notes provided.<jupyter_code>al=df[['Agency Name','Year Built','Condition Assessment Date','Notes']]
Notes_only=al[al.Notes.notnull()] #filtering out all non-null notes
Notes_only<jupyter_output><empty_output><jupyter_text>Using Natural Language Processing to Extract Year Now I will use regular expression (regex) tokenization to find the right string. I want to find a four digit year, which can be in either the 1900s or the 2000s. The first command below will limit the Notes_Year<jupyter_code>Notes_year=Notes_only[Notes_only['Notes'].str.contains('1[0-9]{3}','20[0-9]{2}')] #497 have rows that are probably years
Notes_year['Notes']
Notes_year.head(1)<jupyter_output><empty_output><jupyter_text>This does not not show the entire notes field (is truncated). I'd like to quickly verify that they all have years, to verify my code above worked. I can use the option context function to display the max column width using the following.<jupyter_code>with pd.option_context('display.max_colwidth', -1): display(Notes_year) #https://stackoverflow.com/questions/25351968/how-to-display-full-non-truncated-dataframe-information-in-html-when-convertin<jupyter_output><empty_output><jupyter_text>Performing the (Imperfect) Comparison Scanning through some of the notes above, it looks like the reported values in Year Built are very accurate compared to the notes. At this point, it isn't clear whether it is valuable to actually extract the year from the notes column to perform a logical comparison.<jupyter_code>pattern = '([0-9]{4})'

Notes_year['result'] = Notes_year.loc[:,'Notes'].str.extract(pattern)
Notes_year['result']=to_numeric(Notes_year['result'])
Notes_year<jupyter_output>/Users/Joe/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  This is separate from the ipykernel package so we can avoid doing imports until
/Users/Joe/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  after removing the cwd from sys.path.<jupyter_text>So I can use an equivalence operator to add a new column, 'agree', which is based on true/false.<jupyter_code>Notes_year['agree']=(Notes_year['result']==Notes_year['Year Built'])
Notes_year.value_counts('agree')<jupyter_output>/Users/Joe/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  """Entry point for launching an IPython kernel.<jupyter_text>I can see from the above that there are only 39 results that agree. This seems like too many based on what I saw before. Upon further investigation I find out there must be a constraint in the system limiting the Year Built to 1900 and afterwards.<jupyter_code>Notes_year[Notes_year['result']>1900].value_counts('agree')<jupyter_output><empty_output><jupyter_text>Conclusion Now we see a much more balanced dataset. Let's check this set to make sure this is worthwhile.<jupyter_code>Notes_year[Notes_year['result']>1900][Notes_year['result']<2021][Notes_year['agree']==False]<jupyter_output>/Users/Joe/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
  """Entry point for launching an IPython kernel.
/Users/Joe/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
  """Entry point for launching an IPython kernel.
{'fraction_non_alphanumeric': 0.09272939346811819, 'fraction_numerical': 0.017301710730948678, 'mean_word_length': 6.135922330097087, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 36, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '537986', 'license': 'Unlicense', 'n_tokens_mistral': 1671, 'n_tokens_neox': 1552, 'n_words': 517, 'path': 'NLP wPublic Transit Data.ipynb', 'repo_name': 'beatzbyJWE/beatzbyJWE.github.io', 'size': '5144'}