Extracting from the ACS

Here is some code I wrote for creating Census graphics from the American Community Survey data. It could be generalized, but it works for now.

import pandas as pd

path = '/media/hd2/census/acs-summary-file/'

# much faster to load the one ny state sheet as csv
geo = pd.read_csv(path+'5_year_Mini_Geo_NY.csv', index_col='Logical Record Number')

# useful fields for querying
geo['summary_level']=geo['Geography ID'].str[0:3]
geo['state_id']=geo['Geography ID'].str[7:9]
geo['county_id']=geo['Geography ID'].str[9:12]
geo['county_sub']=geo['Geography ID'].str[12:18]
geo['geoid']=geo['Geography ID'].str[7:18]

# load headers
header = pd.read_excel(path+'ACS_5yr_Seq_Table_Number_Lookup.xlsx')

# create a column with census variable headers
header['COL_NAME'] = header['Table ID'] + '_' + header['Line Number'].apply(lambda a: "{0:.0f}".format(a).zfill(3))

# segment id, along with ACS year and state
segId = 33
year = 2019
state = 'ny'
geolevel=''

# create a list of headers for segment file
segHead = ['FILEID','FILETYPE','STUSAB','CHARITER','SEQUENCE','LOGRECNO'] \
    + header.query('`Sequence Number` == '+str(segId)).dropna(subset=['Line Number'])['COL_NAME'].to_list()
    
seg = pd.read_csv(path+'e'+str(year)+'5'+state+(str(segId).zfill(4))+'000.txt'+geolevel+'.zip',header=None, names=segHead, index_col=5)

seg = geo.join(seg)

# make the pretty graphs

import matplotlib.pyplot as plt
from datetime import date

plt.rcParams['font.family'] = 'Nimbus Sans' #'Overpass Mono'
plt.rcParams['font.size'] = 14

plt.rcParams['figure.figsize'] = [10, 10]

colors = plt.get_cmap('plasma', 11).colors

counties_fips=seg['county_id'].unique()

for county_fips in counties_fips:
    if not county_fips: continue
    print('County FIPS: '+county_fips)
    
    towns=seg[((seg['county_id'] == county_fips) & (seg['summary_level'] == '060'))].sort_values(by='Geography Name', ascending=False)
    
    fig, ax = plt.subplots()
    
    # clean up these variables
    for var in ['B09005_001','B09005_002','B09005_003','B09005_004','B09005_005']:
        towns[var]=pd.to_numeric(towns[var],errors='coerce')
        towns=towns.dropna(axis=0, subset=[var])

    
    # iterate through each line so we can see if big enough to add label
    for i, key in enumerate(towns.index):  
    
		# suppress towns with few lines
        town = towns.loc[key]
        if town['B09005_001'] < 5: continue
        

        # plot bars
        left = 0
        
        # choose colors that reinforce gender stereotypes
        for var, label, color in zip(['B09005_002','B09005_003','B09005_004','B09005_005'], 
                             ['Married','Cohabitating','Men','Women'],
                             ['SeaGreen','IndianRed','DarkBlue','Orchid']):
            
            per = (town[var]/town['B09005_001'])*100
            
            if i != 0:
                p1=ax.barh(town['Geography Name'].split(',')[0], per, 
                          color=color, left=left)            
            else:
                p1=ax.barh(town['Geography Name'].split(',')[0], per, 
                          color=color, label=label, left=left)

			# label bars above 10%
            if per > 10:
                ax.bar_label(p1, label_type='center',weight=1000, size=14, backgroundcolor='#ffffff66', fmt='%.1f%%')

            left += per

    county = towns.iloc[0]['Geography Name'].split(', ')[1]
    
    plt.figtext(.5, 1.08, county+' Household Type for Families with Children', horizontalalignment='center', 
                fontsize=22, fontweight=1000) 
    
    plt.figtext(.5, 1.03, 'While the traditional married couple is the most common way '+\
                'of raising children across the state,\n single mothers are more common especially in the cities. Single men households remain relatively rate.', 
                horizontalalignment='center', 
                fontsize=12, fontstyle='italic', fontweight=100) 
    
    plt.figtext(1, -0.01, '2019 5-yr American Community Survey, Table No. B09005, Seq. 33', wrap=True, horizontalalignment='right', fontstyle='italic', fontsize=14, color='#00000088')
    plt.figtext(0, -0.01, 'Andy Arthur, '+date.today().strftime('%m/%-d/%y'), wrap=True, horizontalalignment='left', fontstyle='italic', fontsize=14, color='#00000088')

    plt.margins(0,0.02)
    ax.xaxis.set_ticks_position('bottom')
    ax.yaxis.set_ticks_position('none')
    #ax.xaxis.grid(False)
    ax.spines.top.set_visible(False)
    ax.spines.bottom.set_visible(False)
    ax.spines.left.set_visible(False)
    ax.spines.right.set_visible(False)
    
    fig.patch.set_facecolor('White')

    plt.tight_layout()
    
    #ax.xaxis.set_ticks([0, 50, 100])
    
    plt.legend(bbox_to_anchor=(0., 1, 1., .102), loc='lower center',
          ncol=4, mode="expand", borderaxespad=0., edgecolor='white')
    
    #plt.show()
    #break
    plt.savefig('/tmp/household/'+county+'_child_household.png',dpi=150,bbox_inches='tight')
    

Next Time – October 5, 2021

Today’s sunrise was at 6:57 am. The next time the sun will rise earlier then today πŸŒ„ is in 33 days on Sunday, November 7.

The average high for today is 66 degrees. 🌑 The next time it will be on average warmer then today is in 209 days on Monday, May 2, 2022 when the average temperature will be 67 degrees.

The highest point for the sun today will be 42.5° from the horizon at 12:44 pm. 🌞 The next time the sun will be higher in the sky mid-day is in 154 days on Tuesday, March 8.

Today has 11 hours and 18 minutes of daylight. ⏳ The next time the day will be longer then today is in 154 days on Tuesday, March 8.

Today’s sunset will be at 6:29 pm. The next time the sun will set later then today πŸŒ† is in 159 days on Sunday, March 13, 2022.

The average low for today is 45 degrees. 🌑 The next night it will be on average warmer then tonight is in 215 days on Sunday, May 8, 2022 when the average temperature will be 46 degrees.

Weather Update – October 5, 2021

Drizzly and wet today, not sure about Columbus Day Weekend πŸ‚

That’s the best way to describe the forecast. I still might consider taking off Friday, I am going to check the calendar at work and see if there is coverage and I may ask for it off. The weekend doesn’t look real sunny, but it also doesn’t look like a washout and it’s been a few weeks since I’ve had a chance to get out of town.

Today.
Feels like …
October 17th.

Scattered Showers

Patchy drizzle with scattered showers before 4pm, then patchy drizzle after 4pm. Cloudy.

Light north wind. Chance of precipitation is 40%. New precipitation amounts of less than a tenth of an inch possible.

and

61 degrees , 56 max dew point, 6:29
sunset.
Tonight.
Feels like …
September 17th.

Isolated Showers then Mostly Cloudy

Isolated showers before 10pm. Mostly cloudy.

Calm wind. Chance of precipitation is 20%.

and

52 degrees , 6:58
sunrise.
Wednesday.
Feels like …
September 24th.

Mostly Cloudy

Mostly cloudy.

Calm wind becoming north around 5 mph in the afternoon.

and

71 degrees , 58 max dew point, 6:28
sunset.
Wednesday Night.
Feels like …
September 17th.

Mostly Cloudy

Mostly cloudy.

Calm wind.

and

52 degrees , 6:59
sunrise.
Thursday.
Feels like …
September 14th.

Partly Sunny

Partly sunny.

Calm wind.

and

75 degrees , 60 max dew point, 6:26
sunset.
Thursday Night.
Feels like …
September 9th.

Partly Cloudy

Partly cloudy.

 

and

55 degrees , 7:00
sunrise.
Friday.
Feels like …
September 21st.

Partly Sunny

Partly sunny.

 

and

72 degrees , 59 max dew point, 6:24
sunset.
Friday Night.
Feels like …
September 14th.

Mostly Cloudy

Mostly cloudy.

 

and

53 degrees , 7:02
sunrise.
Saturday.
Feels like …
October 3rd.

Mostly Cloudy

Mostly cloudy.

 

and

67 degrees , 55 max dew point, 6:22
sunset.
Saturday Night.
Feels like …
September 22nd.

Mostly Cloudy

Mostly cloudy.

 

and

50 degrees , 7:03
sunrise.
Sunday.
Feels like …
September 30th.

Mostly Cloudy

Mostly cloudy.

 

and

68 degrees , 56 max dew point, 6:21
sunset.
Sunday Night.
Feels like …
September 12th.

Mostly Cloudy

Mostly cloudy.

 

and

54 degrees , 7:04
sunrise.
Columbus Day.
Feels like …
September 24th.

Mostly Cloudy

Mostly cloudy.

 

and

71 degrees , 60 max dew point, 6:19
sunset.

Good Morning – October 5, 2021

Good morning! Happy Tuesday. 🚍

A few rain drops here and there and 57 degrees at the Elm Avenue Park and Ride. β˜” Calm wind. The dew point is 55 degrees. The skies will clear tomorrow around 6 pm.

I was worried that the bus would be really late ⌚ because I saw the the 7:55 bus pull away around 8:15 as I was heading down to the park and ride but things were on time. I increased the automatic refill of my bus card πŸ’³ to $20 because I’m riding the bus more now and listening to the refill alerts is obnoxious.

Didn’t rain much while I was walking down the street to the bus 🚍 but it picked up going downtown. Apparently one of the state workers buses broke down, so we stopped by there on the way downtown. Maybe that’s why things were running late for the earlier bus. Not sure but we’re making good progress downtown now.

It’s good with the rain though, 🌧 I might get off at the Concourse and walk to the office in the tunnel, as I am afraid it’s going to be too wet to walk on the plaza.

I am continuing to look at tires for Big Red, ⚫ it’s honestly not the option I necessarily want to choose but I just need to face it because it needs to get done if I want to go on vacation and get on with life. They are doing the alignment on my truck next week, so the next step is the tires. The easy option is to just order them through Mavis Tire, which I can schedule online for the BFGs. The alternative is the off-road shop, although based on their quote for the Nitto Terra Gabblers, I am leaning against them. I also looked at Tire Rack — they can ship them to an installer, but then I have to make the appointment with the installer. I could live with the price of the BFGs. I just want to make up my mind now, as I want to ensure I can get the appointment two weeks out, so I can leave the following week for West Virginia.