<jupyter_start><jupyter_text>Problem Statement  Analye given dataset to rank US states and territories by their 2010 population density.  import necessary libraries<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>Read the given csv's  using pandas<jupyter_code>pop = pd.read_csv('state-population.csv')
areas = pd.read_csv('state-areas.csv')
abbrevs = pd.read_csv('state-abbrevs.csv')<jupyter_output><empty_output><jupyter_text>Merge based on the state/region column of pop and the abbreviation column of abbrevs<jupyter_code>merged = pd.merge(pop, abbrevs, how='outer',left_on='state/region', right_on='abbreviation')
merged = merged.drop('abbreviation', 1) # drop duplicate info
merged.head()<jupyter_output><empty_output><jupyter_text>double-check whether there were any mismatches (use isnul().any())<jupyter_code>merged.isnull().any()<jupyter_output><empty_output><jupyter_text>Analyze the null values accordingly<jupyter_code>#population info is null
merged[merged['population'].isnull()].head()<jupyter_output><empty_output><jupyter_text>It appears that all the null population values are from Puerto Rico prior to the year2000; this is likely due to this data not being available from the original source.<jupyter_code>#some of the new state entries are also null
merged.loc[merged['state'].isnull(), 'state/region'].unique()<jupyter_output><empty_output><jupyter_text>population data includes entries for Puerto Rico(PR) and the United States as a whole (USA), while these entries do not appear in thestate abbreviation key<jupyter_code>merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'
merged.loc[merged['state/region'] == 'USA', 'state'] = 'United States'
merged.isnull().any()<jupyter_output><empty_output><jupyter_text>No more nulls in the state column<jupyter_code>final = pd.merge(merged, areas, on='state', how='left')
final.head()
final.isnull().any()<jupyter_output><empty_output><jupyter_text>There are nulls in the area column<jupyter_code>final['state'][final['area (sq. mi)'].isnull()].unique()
final.dropna(inplace=True)
final.head()
data2010 = final.query("year == 2010 & ages == 'total'")
data2010.head()
data2010.set_index('state', inplace=True)
density = data2010['population'] / data2010['area (sq. mi)']
density.sort_values(ascending=False, inplace=True)
density.head()<jupyter_output><empty_output><jupyter_text>The result is a ranking of US states plus Washington, DC, and Puerto Rico in order oftheir 2010 population density, in residents per square mile.<jupyter_code>density.tail()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12813585488228482, 'fraction_numerical': 0.014280200694712466, 'mean_word_length': 8.391304347826088, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '621552', 'license': 'MIT', 'n_tokens_mistral': 891, 'n_tokens_neox': 826, 'n_words': 206, 'path': 'Chapter_2/Exercise_2.ipynb', 'repo_name': 'nsemble/ML_course', 'size': '2591'}