<jupyter_start><jupyter_text>and then we go to <NAME><jupyter_code>import pandas as pd
import time
import datetime
import matplotlib
import numpy as np
from statsmodels.distributions.empirical_distribution import ECDF
import math
import requests
from bs4 import BeautifulSoup as bs
import pickle as pk
ad = pd.read_csv('datasets/1976-2020-president.csv')

years = list(set(ad['year']))
states = list(set(ad['state']))
states.sort()
years.sort()

dflist = list()
states.remove('DISTRICT OF COLUMBIA')
years.remove(1976)
years.remove(1980)<jupyter_output><empty_output><jupyter_text>some function<jupyter_code>def foofloat(x):
    try:
        return float(x)
    except:
        return np.NaN
def bls_serial(series_id):
  bsobj = bs(requests.post("https://data.bls.gov/pdq/SurveyOutputServlet", {'series_id': series_id}).text)
  return bsobj.findAll('table')[0].findAll('tr')[2].findAll('td')[0].text.strip().upper()
def ecdf_inv(a, q): return sorted(a)[int(len(a) * q)]
def count_elem(l): return {y:list(l).count(y) for y in set(l)}
def dpc(a,b): return (a/b - 1)*100
def alfa(x): return ''.join([c for c in x if c.isalpha() or c==' ']).strip()
def to_percent(older, col_key, shift=0):
    newer = []
    for y in years:
        for s in states:
            try:
                d = older.loc[y-shift, s, 'DEMOCRAT'][col_key]
                r = older.loc[y-shift, s, 'REPUBLICAN'][col_key]
                newer.append([int(y), s, 100*d/(d+r)]) # % share for each state
            except KeyError as e:
                pass
    return pd.DataFrame(data=newer, columns=['year', 'state', col_key, ])
def to_splatter(a, col_name): # from row-column to table-list
    lst = []
    for y in a.columns[2:]:
        for index, row in a.iterrows():
            lst.append([int(y), row['GeoName'].upper(), row[y], ])
    
    return pd.DataFrame(data=lst, columns=['year', 'state', col_name, ])
def var42(df_res, old_colname, new_colname, jump=1):
    new_df = []
    
    df_res = df_res.set_index(['year', 'state'])
    for y in years:
        for s in states:
            try:
                new_df.append([y,s,] + [df_res.at[(y-i,s), old_colname] for i in range(0, 4, jump)])
            except KeyError:
                pass

    new_df = pd.DataFrame(data=new_df, columns = ['year', 'state',] + [f'{new_colname}_{i}' for i in range(4//jump, 0, -1)])
    return new_df
def beadiff(rowcol, colname, diff = True, shift=1):
    difflist = []

    for s in states:
        tmp = rowcol.loc[(slice(None),s),:]
        yyyy = list({i[0] for i in tmp.index})
        yyyy.sort()
        for y in yyyy[shift:]:
            if diff:
                q = dpc(rowcol.at[(y,s), colname], rowcol.at[(int(y)-shift,s),colname])
            else:
                q = rowcol.at[(y,s),colname]
            difflist.append([y, s, q])
            
    return  pd.DataFrame(data=difflist, columns=['year', 'state', colname])
def whore(df, yy=years, ss=states):
    df = df.set_index(['year','state'])
    x = []
    for s in ss:
        for y in yy:
            try:
                df.loc[(y,s),:]
            except:
                x.append((y,s))
    return x
def sell(df, filename=None):
    df = df.sort_values(by=['year', 'state'])
    dflist.append(df)
    if filename is not None:
        df.to_csv(f'datasets-clean/{filename}.csv', index=False)
    return df<jupyter_output><empty_output><jupyter_text>y_hat aka presitdent popular vote<jupyter_code>pop_vote = pd.read_csv('datasets/1976-2020-president.csv')
pop_vote = pop_vote[['year', 'state', 'party_simplified', 'candidatevotes', ]] # drop useless columns
pop_vote = pop_vote.groupby(['year', 'state', 'party_simplified', ]).sum() # drop multiple candidate
pop_vote = to_percent(pop_vote, 'candidatevotes')
pop_vote = pop_vote.rename(columns={"candidatevotes": "y_votes_percent", })
pop_vote = pop_vote.sort_values(by=['year', 'state'])
pop_vote.to_csv('datasets-clean/popular-vote-y.csv', index=False)
dflist.append(pop_vote)
pop_vote<jupyter_output><empty_output><jupyter_text>GDP and friends It was necessary to generate an adjusted series of state GDP because of a change in BEA’s estimation procedure from a Standard Industrial Classification (SIC) basis to a North American Industry Classification System (NAICS) basis in 1997.Data prior to 1997 were adjusted to avoid any erratic shifts in GDP that year.While the change to NAICS basis occurred in 1997, BEA also provides estimates under a SIC basis in that year.~~Our adjustment involved calculating the 1997 ratio of NAICS-based GDP to SIC-based GDP for each state, and multiplying it by SIC-based GDP in all years prior to 1997 to obtain our adjusted series of state-level GDP.~~<jupyter_code>gdp_nom_97 = to_splatter(pd.read_csv('datasets/gdp-nominal-63-97.csv'), 'gdp_nom')
gdp_nom_12 = to_splatter(pd.read_csv('datasets/gdp-nominal-97-20.csv'), 'gdp_nom')
gdp_real_97 = to_splatter(pd.read_csv('datasets/gdp-real-77-97-chain-97.csv'), 'gdp_real')
gdp_real_12 = to_splatter(pd.read_csv('datasets/gdp-real-97-20-chain-12.csv'), 'gdp_real')
gdp_nom_97 = gdp_nom_97.set_index(['year', 'state'])
gdp_nom_12 = gdp_nom_12.set_index(['year', 'state'])
gdp_real_97 = gdp_real_97.set_index(['year', 'state'])
gdp_real_12 = gdp_real_12.set_index(['year', 'state'])
# moving to midterms (retrocompatibility)
k = 2
sx = '_mt' if k-1 else ''<jupyter_output><empty_output><jupyter_text>price indexes<jupyter_code>gdp_def_97 = (gdp_nom_97['gdp_nom'] / gdp_real_97['gdp_real']).to_frame('gdp_def').dropna()
gdp_def_12 = (gdp_nom_12['gdp_nom'] / gdp_real_12['gdp_real']).to_frame('gdp_def').dropna()
# price index FAIR
fair_p = pd.concat([beadiff(gdp_def_12,'gdp_def',shift=4), beadiff(gdp_def_97,'gdp_def',shift=4)])

# fix 97jump
tmp = dpc(gdp_def_12.loc[2000], gdp_def_97.loc[1996])
tmp['year'] = 2000
fair_p = pd.concat([fair_p, tmp.reset_index(), ])

# recolumning + appending
fair_p = var42(fair_p, new_colname='fair_p', old_colname='gdp_def', jump=4)

sell(fair_p)
# OUR price index
def_index = pd.concat([beadiff(gdp_def_12,'gdp_def', shift=k), beadiff(gdp_def_97,'gdp_def',shift=k)])

if k-1:
    tmp = dpc(gdp_def_12.loc[1996+k], gdp_def_97.loc[1996])
    tmp['year'] = 1996+k
    def_index = pd.concat([def_index, tmp.reset_index(), ])

def_index = var42(def_index, new_colname='def'+sx, old_colname='gdp_def', jump=k)

sell(def_index, 'price-deflator')<jupyter_output><empty_output><jupyter_text>population denom<jupyter_code>pop = to_splatter(pd.read_csv('datasets/population-75-21.csv'), col_name='gdp_real')
pop.state = pop.state.apply(alfa)
pop = pop.set_index(['year', 'state'])<jupyter_output><empty_output><jupyter_text>gdp indexes<jupyter_code># re-generate plain series (see despacito)
gdp_real_plain = gdp_real_12.copy()
for y in range(1996, 1977, -1):
    for s in sorted(states):
        a = gdp_real_97.loc[(y,s),'gdp_real']
        b = gdp_real_97.loc[(y+1,s),'gdp_real']
        c = gdp_real_plain.loc[(y+1,s),'gdp_real']

        gdp_real_plain.at[(y,s),'gdp_real'] = a*b/c

gdp_real_plain = gdp_real_plain.reset_index()
# OUR two-lag + fix 97jump
gdp_real_var = pd.concat([beadiff(gdp_real_97,'gdp_real', shift=k), beadiff(gdp_real_12,'gdp_real', shift=k)])

if k-1:
    tmp = gdp_real_plain.set_index(['year','state'])
    tmp = dpc(tmp.loc[1996+k],tmp.loc[1996])
    tmp['year'] = 1996+k
    gdp_real_var = pd.concat([gdp_real_var, tmp.reset_index(), ])
    
gdp_index = var42(gdp_real_var, new_colname='gdp'+sx, old_colname='gdp_real', jump=k)

sell(gdp_index, 'gdp-growth')
if True:
    gdp_real_12 /= pop.loc[gdp_real_12.index]
    gdp_real_97 /= pop.loc[gdp_real_97.index]
# one-lag FAIR
fair_g = pd.concat([beadiff(gdp_real_97,'gdp_real'), beadiff(gdp_real_12,'gdp_real')])
fair_g = var42(fair_g, new_colname='fair_g', old_colname='gdp_real', jump=4)

sell(fair_g)
if True:
    gdp_real_12 *= pop.loc[gdp_real_12.index]
    gdp_real_97 *= pop.loc[gdp_real_97.index]<jupyter_output><empty_output><jupyter_text>z growth index<jupyter_code>tmp = pd.concat([beadiff(gdp_real_97,'gdp_real'), beadiff(gdp_real_12,'gdp_real')])
quantile = ECDF(pd.read_csv('datasets/us-gdp-quarter-47-21-varpc.csv').GDP_PCH)(3.2)

'''for s in states:
    l = tmp[tmp.state == s].gdp_real
    print(f'{s.ljust(max([len(i) for i in states]))} {ecdf_inv(l, quantile):10.2f}', )'''

GDP_THRESHOLD = sorted([ecdf_inv(tmp[tmp.state == s].gdp_real, quantile) for s in states])[0] if False else 3.2
GDP_THRESHOLD
zk = 1
zsx = '_mt' if zk-1 else ''
growth_index = pd.concat([beadiff(gdp_real_97,'gdp_real', shift=zk), beadiff(gdp_real_12,'gdp_real', shift=zk)]) ## NON CONVINTO, brick on 97
growth_index.gdp_real = growth_index.gdp_real.apply(lambda x : 1 if x > GDP_THRESHOLD else 0)
growth_index = var42(growth_index, new_colname='z'+zsx, old_colname='gdp_real', jump=zk)

growth_index['z_mt_2'] = (growth_index.z_4 + growth_index.z_3).apply( lambda x: 1 if x else 0)
growth_index['z_mt_1'] = (growth_index.z_2 + growth_index.z_1).apply( lambda x: 1 if x else 0)

sell(growth_index, 'z-growth')<jupyter_output><empty_output><jupyter_text>incumbent<jupyter_code>incumbent = pd.read_csv('datasets/incumbent-4president-76-20.csv', sep=';')
tmp = pd.DataFrame(data=[[y,s] for y in years for s in states], columns=['year', 'state'])
incumbent = pd.merge(tmp, incumbent, how='inner', left_on='year', right_on='year')

sell(incumbent, 'incumbent-longrep')<jupyter_output><empty_output><jupyter_text>house dummy<jupyter_code>house_vote = pd.read_csv('datasets/1976-2020-house-utf8.csv')
house_vote = house_vote[['year', 'state', 'party', 'candidatevotes', ]]
house_vote = house_vote.groupby(['year', 'state', 'party', ]).sum()
house_vote = to_percent(house_vote, 'candidatevotes', shift=2)
house_vote['candidatevotes'] = house_vote['candidatevotes'].apply(lambda x: -1 if x < 50 else 1)
house_vote = house_vote.rename(columns={'candidatevotes':'house_midterm'})
house_vote = pd.concat([house_vote, pd.read_csv('datasets/midterm-fixed.csv')])
house_vote.state = house_vote.state.apply(lambda x : x.upper())

sell(house_vote, 'midterm')<jupyter_output><empty_output><jupyter_text>personal income<jupyter_code>inc_cap = to_splatter(pd.read_csv('datasets/personal-income-per-capita-72-20.csv'), 'avg_inc')
inc_cap = inc_cap.set_index(['year', 'state'])
#inc_cap.avg_inc = inc_cap.avg_inc.apply(lambda x : math.log(x))
inc_cap = beadiff(inc_cap, 'avg_inc', shift=k)
inc_cap = var42(inc_cap,  'avg_inc', 'avg_inc'+sx, jump=k)

sell(inc_cap, 'income')<jupyter_output><empty_output><jupyter_text>unenployment<jupyter_code>unn = pd.read_csv('datasets/unemployment-76-21-percent.csv')
furi = 'datasets-clean/serial-id-bsl.pkl'

if False:
    sd = dict()
    for s in set([*unn['Series ID'], *nunn['Series ID']]):
        try:
            sd[s] = bls_serial(s)
        except IndexError:
            sd[s] = f'BADASS_{s}'
    pk.dump(sd, open(furi, 'wb'))
else:
    sd = pk.load(open(furi, 'rb'))

unn = unn.rename(columns={
    "Series ID": "state",
    "Year": "year",
    "Value": "unemp",
    "Period": "month",
    })

# transform
unn.unemp = unn.unemp.apply(foofloat).astype(float)
unn.state = unn.state.map(sd)
unn.month = unn.month.apply(lambda x: x.split('M')[-1])
# replace missing value
glue = pd.read_csv('datasets/unemployment-rate-us-country-47-21.csv')
glue['year'] = glue.DATE.apply(lambda x: x.split('-')[0])
glue['month'] = glue.DATE.apply(lambda x: x.split('-')[1])
glue = glue.set_index(['year','month'])

for i in unn.index:
    unn.at[i, "unemp"] = glue.at[(unn.at[i, "year"], unn.at[i, "month"]), 'UNRATE'] if unn.at[i, "unemp"] == np.NaN else unn.at[i, "unemp"]
# delta%
dmp = unn.set_index(['year','month','state'])

unemp_mt = []
unemp_s = []
for y in years:
    for s in states:
        try:
            m2 = dpc(dmp.at[(y, '10', s), 'unemp'], dmp.at[(y-2, '11', s), 'unemp'])
            m1 = dpc(dmp.at[(y-2, '10', s), 'unemp'], dmp.at[(y-3, '01', s), 'unemp'])
            unemp_mt.append([y,s,m1,m2,])

            unemp_s.append([y, s, dmp.loc[[(y, f'{i:02}', s) for i in range(6,11)], 'unemp'].median(), ])
        except KeyError:
            pass
        
unemp_mt = pd.DataFrame(data=unemp_mt, columns=['year', 'state', 'unemp_mt_1', 'unemp_mt_2'])
unemp_s = pd.DataFrame(data=unemp_s, columns=['year', 'state', 'unemp_rn',])

sell(unemp_s)<jupyter_output><empty_output><jupyter_text>merging features<jupyter_code>df = pd.DataFrame(data=[[y,s] for y in years for s in states], columns=['year', 'state'])
for d in dflist:
    df = df.merge(d, how='inner', left_on=['year','state'], right_on=['year','state'])

df = df.sort_values(by=['year', 'state'])

df.to_csv('datasets-clean/xxx-2012-insample-final-dataset.csv', index=False)
df[df.year != 2020].to_csv('datasets-clean/xxx-2020-outofsample-final-dataset.csv', index=False)
for d in dflist:
    w = whore(d)
    if len(w):
        print(d.head())
        print('-+'*30)
        print(w)
        print('-+'*30)
0/0
# chernobyl zone<jupyter_output><empty_output><jupyter_text>exp fail<jupyter_code>tmp = []
for y in years:
    for s in states:
        m2 = unn[unn.year >= y-1][unn.year <= y][unn.state == s].unemp.median()
        m1 = unn[unn.year >= y-3][unn.year <= y-2][unn.state == s].unemp.median()
        tmp.append([y,s,m1,m2])
mid_unem = pd.DataFrame(data=tmp, columns=['year', 'state', 'unemp_12', 'unemp_34'])
dflist.append(mid_unem)
mid_unem
unn = var42(unn, 'unemp', 'unemployment-index', 'unemp')
dflist.append(unn)
unn
gdp_index['gdp_34'] = (gdp_index['gdp_3']+gdp_index['gdp_3'])/2
gdp_index['gdp_12'] = (gdp_index['gdp_1']+gdp_index['gdp_2'])/2

del gdp_index['gdp_1']
del gdp_index['gdp_2']
del gdp_index['gdp_3']
del gdp_index['gdp_4']
gdp_boot = gdp_real.copy()
gdp_exp = gdp_boot.set_index(['year', 'state'])
for s in states:
    print(gdp_exp.loc[(slice(None),s),:].gdp_real.apply(lambda x : math.exp((x - gdp_exp.gdp_real.mean())/ math.sqrt(gdp_exp.gdp_real.var()))))
    break<jupyter_output><empty_output><jupyter_text>us stuff<jupyter_code>gdp_us = pd.read_csv('datasets/gdp-nomina-47-20-chain-12.csv')
gdp_us.DATE = gdp_us.DATE.apply(lambda x: int(str(x).split('-')[0]))
s = gdp_us[gdp_us.DATE > 1975].GDPC1_PC1
s.hist()
ecdf = ECDF(s)
ecdf(2.8)

qnt = 0.67
std_err = np.sqrt(s.var())/2
ecdf_inv(s, qnt), len(s)*(1-qnt), std_err<jupyter_output><empty_output><jupyter_text>house<jupyter_code>'''
house_vote = pd.read_csv('datasets/1976-2020-house-utf8.csv')
house_vote = house_vote[['year', 'state', 'party', 'candidatevotes', ]]
house_vote = house_vote.groupby(['year', 'state', 'party', ]).sum()
house_vote = to_percent(house_vote, 'candidatevotes')
house_vote = house_vote.rename(columns={"candidatevotes": "houserep_votes_percent", })
#'''<jupyter_output><empty_output><jupyter_text>gasoline and friends + approval<jupyter_code>gas = pd.read_csv('datasets/gasoline-93-21.csv')
gas.date = gas.date.apply(lambda x: time.mktime(datetime.datetime.strptime(x,"%m/%d/%Y").timetuple()))
gas.date = gas.date.apply(lambda x: datetime.datetime.fromtimestamp(int(x)))
gas<jupyter_output><empty_output><jupyter_text>houses prices and rent + personal income<jupyter_code>f = open('datasets/house-chain-00.csv', 'rt')
lines = f.readlines()
houses = [[i.strip() for i in l.split(sep = '$') ] for l in lines ]
houses = pd.DataFrame(data=houses, columns=['state', 2000, 1990, 1980, 1970, 1960, 1950, 1940 ])
houses.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17645523362046464, 'fraction_numerical': 0.028974158183241974, 'mean_word_length': 4.829212628375808, 'pattern_counts': {'":': 6, '<': 77, '<?xml version=': 0, '>': 78, 'https://': 1, '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': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '30160', 'license': 'MIT', 'n_tokens_mistral': 6521, 'n_tokens_neox': 6140, 'n_words': 1095, 'path': 'datastroyer.ipynb', 'repo_name': 'ranieri-unimi/bastianin-2022', 'size': '15330'}