analyze.py 7.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215
  1. import pandas as pd
  2. import numpy as np
  3. from bokeh.plotting import figure
  4. from bokeh.models import HoverTool
  5. binning_age_max = 110
  6. binning_career_max = 70
  7. def save_fig(fig, filename):
  8. from bokeh.embed import components
  9. script, div = components(fig)
  10. with open(f'output/{filename}.js', 'w') as f:
  11. f.write(script)
  12. with open(f'output/{filename}.html', 'w') as f:
  13. f.write(div)
  14. def percentile_from_pdf(pdf, bin_centers, percentile=0.5):
  15. cdf = 0
  16. for pdf_val, bin_low, bin_high in zip(pdf, bin_centers[:-1], bin_centers[1:]):
  17. if cdf+pdf_val > percentile:
  18. return bin_low + (percentile - cdf)*(bin_high-bin_low) / pdf_val
  19. cdf += pdf_val
  20. raise ValueError(f"couldn't find percentile: {percentile}, cdf: {cdf}" )
  21. def pdf_stats(pdf, bins):
  22. from collections import namedtuple
  23. Stats = namedtuple('Stats', ['hist', 'mean', 'median', 'quart_high', 'quart_low'])
  24. bin_centers = (bins[:-1] + bins[1:])/2
  25. mean = np.average(bin_centers, weights=pdf)
  26. median = percentile_from_pdf(pdf, bin_centers)
  27. quart_low = percentile_from_pdf(pdf, bin_centers, 0.25)
  28. quart_high = percentile_from_pdf(pdf, bin_centers, 0.75)
  29. return Stats((pdf, bins), mean, median, quart_high, quart_low)
  30. def get_congress(year, parties=None, states=None, positions=None):
  31. query = f'''\
  32. SELECT yob, career_length FROM Member
  33. WHERE congress={year}'''
  34. if positions:
  35. query += ' AND position IN (' + ', '.join(f'"{position}"' for position in positions) + ')'
  36. if parties:
  37. query += ' AND party IN (' + ', '.join(f'"{party}"' for party in parties) + ')'
  38. if states:
  39. query += ' AND state IN (' + ', '.join(f'"{state}"' for state in states) + ')'
  40. data = pd.read_sql_query(query, 'sqlite:///us_congress_members.sqlite3')
  41. data['age'] = year - data.yob
  42. return data
  43. def get_stats_congress(year, age_or_term, parties=None, states=None, positions=None):
  44. data = get_congress(year, parties, states, positions)
  45. if len(data) == 0:
  46. return None
  47. if age_or_term == "Age":
  48. pdf, bins = np.histogram(data.age, bins=binning_age_max, range=(0, binning_age_max), density=True)
  49. else:
  50. pdf, bins = np.histogram(data.career_length, bins=binning_career_max, range=(0, binning_career_max),
  51. density=True)
  52. return pdf_stats(pdf, bins)
  53. def get_stats_genpop(year, sex=None, states=None):
  54. query = f'''\
  55. SELECT perwt, age FROM person
  56. WHERE year={year}
  57. '''
  58. if sex:
  59. query += f' AND sex={sex}'
  60. if states:
  61. query += f' AND statefip IN (' + ', '.join(f'{state}' for state in states) + ')'
  62. data = pd.read_sql_query(query, 'sqlite:///usa_00001.sqlite3')
  63. pdf, bins = np.histogram(data.age, bins=binning_age_max, range=(0, binning_age_max), weights=data.perwt,
  64. density=True)
  65. return pdf_stats(pdf, bins)
  66. def plot_yearly_stats(figname):
  67. hover = HoverTool(tooltips=[(f'Age', "@y{00.0}")],
  68. mode='vline')
  69. fig = figure(tools=[hover, 'pan', 'wheel_zoom', 'save', 'reset'],
  70. sizing_mode='scale_width', plot_width=700, plot_height=450,
  71. toolbar_location="right")
  72. genpop_stats = {}
  73. congress_stats = {}
  74. for year in [1850, 1860, 1870, 1880, 1900, 1910, 1920, 1930, 1940, 1950, 1960, 1970, 1980, 1990, 2000, 2010, 2016]:
  75. genpop_stats[year] = get_stats_genpop(year)
  76. for year in range(1850, 2017):
  77. congress_stats[year] = get_stats_congress(year, 'Age', positions=['Senator', 'Representative'])
  78. congress_years = []
  79. congress_medians = []
  80. congress_quart_highs = []
  81. congress_quart_lows = []
  82. for year, year_stats in congress_stats.items():
  83. congress_years.append(year)
  84. congress_medians.append(year_stats.median)
  85. congress_quart_highs.append(year_stats.quart_high)
  86. congress_quart_lows.append(year_stats.quart_low)
  87. genpop_years = []
  88. genpop_medians = []
  89. genpop_quart_highs = []
  90. genpop_quart_lows = []
  91. for year, year_stats in genpop_stats.items():
  92. genpop_years.append(year)
  93. genpop_medians.append(year_stats.median)
  94. genpop_quart_highs.append(year_stats.quart_high)
  95. genpop_quart_lows.append(year_stats.quart_low)
  96. def do_plot(years, medians, quart_highs, quart_lows, color, label):
  97. fig.patch(years + years[::-1], quart_highs + quart_lows[::-1], fill_color=color, alpha=0.3)
  98. fig.line(years, medians, line_color=color, line_width=2, legend=label, level='overlay')
  99. do_plot(genpop_years, genpop_medians, genpop_quart_highs, genpop_quart_lows, 'blue', 'U.S. Population')
  100. do_plot(congress_years, congress_medians, congress_quart_highs, congress_quart_lows, 'red', 'Congress')
  101. fig.legend.location = 'bottom_right'
  102. fig.xaxis.axis_label = 'Year'
  103. fig.yaxis.axis_label = 'Age'
  104. fig.y_range.start = 0
  105. save_fig(fig, figname)
  106. def plot_partisan_stats(age_or_term, figname):
  107. hover = HoverTool(tooltips=[(f'{age_or_term}', "@y{00.0}")],
  108. mode='vline')
  109. fig = figure(tools=[hover, 'pan', 'wheel_zoom', 'save', 'reset'],
  110. sizing_mode='scale_width', plot_width=700, plot_height=450,
  111. toolbar_location="right")
  112. parties = [('Republican', 'red'), ('Democrat', 'blue')]
  113. for (party, color) in parties:
  114. stats = {}
  115. for year in range(1850, 2017):
  116. stat = get_stats_congress(year, age_or_term, parties=[party], positions=['Senator', 'Representative'])
  117. if stat:
  118. stats[year] = stat
  119. years = []
  120. medians = []
  121. quart_highs = []
  122. quart_lows = []
  123. for year, year_stats in stats.items():
  124. years.append(year)
  125. medians.append(year_stats.median)
  126. quart_highs.append(year_stats.quart_high)
  127. quart_lows.append(year_stats.quart_low)
  128. fig.patch(years + years[::-1], quart_highs + quart_lows[::-1], fill_color=color, alpha=0.3)
  129. fig.line(years, medians, line_color=color, line_width=2, legend=party, level='overlay')
  130. fig.legend.location = 'bottom_right'
  131. fig.xaxis.axis_label = 'Year'
  132. fig.yaxis.axis_label = age_or_term
  133. fig.y_range.start = 0
  134. save_fig(fig, figname)
  135. def plot_chamber_stats(age_or_term, figname):
  136. from bokeh.models import HoverTool
  137. hover = HoverTool(tooltips=[(f'{age_or_term}', "@y{00.0}")],
  138. mode='vline')
  139. fig = figure(tools=[hover, 'pan', 'wheel_zoom', 'save', 'reset'],
  140. sizing_mode='scale_width', plot_width=700, plot_height=450,
  141. toolbar_location="right")
  142. chambers = [('Senator', 'Senate', 'red'), ('Representative', 'House', 'blue')]
  143. for (position, chamber, color) in chambers:
  144. stats = {}
  145. for year in range(1850, 2017):
  146. stat = get_stats_congress(year, age_or_term, positions=[position])
  147. if stat is not None:
  148. stats[year] = stat
  149. years = []
  150. medians = []
  151. quart_highs = []
  152. quart_lows = []
  153. for year, year_stats in stats.items():
  154. years.append(year)
  155. medians.append(year_stats.median)
  156. quart_highs.append(year_stats.quart_high)
  157. quart_lows.append(year_stats.quart_low)
  158. fig.patch(years + years[::-1], quart_highs + quart_lows[::-1], fill_color=color, alpha=0.3)
  159. fig.line(years, medians, line_color=color, line_width=2, legend=chamber, level='overlay')
  160. fig.legend.location = 'bottom_right'
  161. fig.xaxis.axis_label = 'Year'
  162. fig.yaxis.axis_label = age_or_term
  163. fig.y_range.start = 0
  164. save_fig(fig, figname)
  165. if __name__ == '__main__':
  166. plot_yearly_stats('congress_ages')
  167. plot_chamber_stats('Age', 'chamber_age')
  168. plot_chamber_stats('Career Length', 'chamber_career_length')
  169. plot_partisan_stats('Age', 'partisan_age')
  170. plot_partisan_stats('Career Length', 'partisan_career_length')