<jupyter_start><jupyter_text>An analysis of US census data using SQLAlchemy<jupyter_code>from sqlalchemy import create_engine, MetaData

# Define an engine to connect to census.sqlite
engine = create_engine('sqlite:///census.sqlite')

# Initialize MetaData
metadata = MetaData()
from sqlalchemy import Table, Column, String, Integer

# Build a census table
census = Table('census', metadata,
               Column('state', String(50)),
               Column('sex', String(1)),
               Column('age', Integer()),
               Column('pop2000', Integer()),
               Column('pop2008', Integer()))

# Create the table in the database
metadata.create_all(engine)
import csv

values_list = []

# context manager
with open('census.csv.txt', 'r') as file:
    csv_reader = csv.reader(file, delimiter=',')

    # Iterate over the rows in census.csv
    for row in csv_reader:
        # Create a dictionary with the values
        data = {'state': row[0], 'sex': row[1], 'age':row[2], 'pop2000': row[3], 'pop2008': row[4]}
        # Append the dictionary to the values list
        values_list.append(data)
from sqlalchemy import insert

# Build insert statement
stmt = insert(census)

# Use values_list to insert data
connection = engine.connect()
results = connection.execute(stmt, values_list)

# Print rowcount: the number of rows inserted
print(results.rowcount)<jupyter_output>8772<jupyter_text>First query<jupyter_code>from sqlalchemy import select, func

# Calculate weighted average age
stmt = select([census.columns.sex,
               (func.sum(census.columns.pop2008 * census.columns.age) /
                func.sum(census.columns.pop2008)).label('average_age')
               ])

# Group by sex
stmt = stmt.group_by(census.columns.sex)

# Execute the query and store the results
results = connection.execute(stmt).fetchall()

# Print the average age by sex
for result in results:
    print(result.sex, result.average_age)<jupyter_output>F 38
M 35<jupyter_text>So in 2008, females were older than males, on average.  And by a margin of 3 years!  Was this different back in 2000?<jupyter_code># Calculate weighted average age
stmt = select([census.columns.sex,
               (func.sum(census.columns.pop2000 * census.columns.age) /
                func.sum(census.columns.pop2000)).label('average_age')
               ])

# Group by sex
stmt = stmt.group_by(census.columns.sex)

# Execute the query and store the results
results = connection.execute(stmt).fetchall()

# Print the average age by sex
for result in results:
    print(result.sex, result.average_age)<jupyter_output>F 37
M 34<jupyter_text>Second query<jupyter_code># import case, cast and Float from sqlalchemy
from sqlalchemy import case, cast, Float

# Build a query to calculate the percentage of females in 2000
# cast as float
stmt = select([census.columns.state,
    (func.sum(
        case([
            (census.columns.sex == 'F', census.columns.pop2000)
        ], else_=0)) /
     cast(func.sum(census.columns.pop2000), Float) * 100).label('percent_female')
])

# Group By state
stmt = stmt.group_by(census.columns.state)

# Execute the query and store the results
results = connection.execute(stmt).fetchall()

# Print the percentage
for result in results:
    print(result.state, result.percent_female)
import pandas as pd

df = pd.DataFrame(results)
df.columns = ['state','percent_female']
df = df.set_index('state')

print(df[df['percent_female'] < 50])

df = df.sort_values('percent_female', ascending = False)
print(df.head())<jupyter_output>percent_female
state                  
Alaska    49.3014978935
Colorado  49.8476706030
Idaho     49.9897262390
Nevada    49.3673636138
Utah      49.9729527511
Wyoming   49.9459554265
                     percent_female
state                              
District of Columbia  53.1296261417
Rhode Island          52.0734339190
Maryland              51.9357554997
Mississippi           51.9222948179
Massachusetts         51.8430235713<jupyter_text>Using pandas, we find that in 2000 there were 6 states with females in the minority.  Conversely, DC had the largest proportion of females.  Did this change at all in 2008?<jupyter_code># Build a query to calculate the percentage of females in 2008
# cast as float
stmt = select([census.columns.state,
    (func.sum(
        case([
            (census.columns.sex == 'F', census.columns.pop2008)
        ], else_=0)) /
     cast(func.sum(census.columns.pop2008), Float) * 100).label('percent_female')
])

# Group By state
stmt = stmt.group_by(census.columns.state)

# Execute the query and store the results
results = connection.execute(stmt).fetchall()

df_2008 = pd.DataFrame(results)
df_2008.columns = ['state','percent_female']
df_2008 = df_2008.set_index('state')

print(df_2008[df_2008['percent_female'] < 50])

df_2008 = df_2008.sort_values('percent_female', ascending = False)
print(df_2008.head())<jupyter_output>percent_female
state                  
Alaska    49.0188790543
Colorado  49.7519513237
Idaho     49.8036755208
Nevada    49.3842916941
Utah      49.5950264362
Wyoming   49.4834652213
                     percent_female
state                              
District of Columbia  52.8751422119
Maryland              51.7567263258
Rhode Island          51.7432288457
Mississippi           51.7401547724
Delaware              51.6654567711<jupyter_text>The only state present in 2000 results but not in 2008 results is Massachusetts, replaced by Delaware.  Third query<jupyter_code>from sqlalchemy import desc

# Build query to return state name and population difference from 2008 to 2000
stmt = select([census.columns.state,
     (census.columns.pop2008 - census.columns.pop2000).label('pop_change')
])

# Group by State
stmt = stmt.group_by(census.columns.state)

# Order by Population Change in descending order
stmt = stmt.order_by(desc('pop_change'))

# Limit to top 10
# stmt = stmt.limit(10)

# Use connection to execute the statement and fetch
results = connection.execute(stmt).fetchall()

# Print the state and population change for each record
for result in results:
    print(result.state + ': ' + str(result.pop_change))<jupyter_output>California: 105705
Florida: 100984
Texas: 51901
New York: 47098
Pennsylvania: 42387
Arizona: 29509
Ohio: 29392
Illinois: 26221
Michigan: 25126
North Carolina: 24108
New Jersey: 22312
Georgia: 20231
Virginia: 19387
Washington: 16930
Indiana: 15860
South Carolina: 14837
Maryland: 14360
Massachusetts: 14228
Tennessee: 13625
Missouri: 12259
Minnesota: 11743
Wisconsin: 11639
Colorado: 10732
Oregon: 9987
Hawaii: 9229
Alabama: 9215
Kentucky: 8914
Connecticut: 8127
Iowa: 7153
New Mexico: 6875
Louisiana: 6753
Arkansas: 6140
Oklahoma: 6047
Utah: 5943
Kansas: 5527
Mississippi: 5181
Idaho: 4050
Nebraska: 3838
New Hampshire: 3551
West Virginia: 3346
Delaware: 3073
Rhode Island: 2636
Montana: 2633
Maine: 2559
South Dakota: 2486
North Dakota: 1775
District of Columbia: 1371
Alaska: 1334
Vermont: 1194
Wyoming: 1126
Nevada: 531
{'fraction_non_alphanumeric': 0.0949240034413536, 'fraction_numerical': 0.0949240034413536, 'mean_word_length': 3.6875, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 19, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '297044', 'license': 'MIT', 'n_tokens_mistral': 2724, 'n_tokens_neox': 2181, 'n_words': 646, 'path': 'Census%21.ipynb', 'repo_name': 'kadakia/Census', 'size': '6974'}