Working PANDAS and American Community Survey Summary File

import pandas as pd

path = '/home/andy/Desktop/acs-summary-file/'

# list of geography
geo = pd.read_excel(path+'5_year_Mini_Geo.xlsx', sheet_name='ny',index_col='Logical Record Number')

# 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 = 135
year = 2019
state = 'ny'

# 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()

# read the segment file, including column names above    
seg = pd.read_csv(path+'e'+str(year)+'5'+state+(str(segId).zfill(4))+'000.txt',header=None, names=segHead, index_col=5)

# join the segment file to geography using Logical Record number
seg = geo.join(seg)

# calculate percentage of households with internet subscriptions -- codes from ACS_5yr_Seq_Table_Number_Lookup.xlsx
seg['Internet Subscription']=seg['B28011_002']/seg['B28011_001']*100

# output the percentage of households by county with internet subscriptions
seg[seg['Geography ID'].str.startswith('050')][['Geography Name','Internet Subscription']]

# alternatively you can display human readable columns automatically
seg.rename(dict(zip(header['COL_NAME'],header['Table Title'])),axis=1)

import pandas as pd
import censusdata as cd

# attributes to load
cdcol=['B28011_001','B28011_002']

cdf = cd.download('acs5', 2019,
           cd.censusgeo([('state', '36'),
                         ('county','*')]),
          cdcol)


# seperate out the geoid and geography name
geoid=[]
geoname=[]

for index in cdf.index.tolist():
    geopart=''
    for part in index.geo:
        geopart = geopart + part[1]
    geoid.append(geopart)
    geoname.append(index.name)

cdf['geoid']=geoid
cdf['geoname']=geoname

# calculate percentage with internet subscriptions
cdf['Internet Subscription']=cdf['B28011_002']/cdf['B28011_001']*100

# output a similar table as above
cdf