<jupyter_start><jupyter_text>Backing out CPI from Wage Data<NAME>, [<EMAIL>](mailto:<EMAIL>)Use non-adjusted average hourly earnings. Compare the real and nominal series to get it.<jupyter_code>%reset -f
%reload_ext autoreload
%autoreload 2
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt<jupyter_output><empty_output><jupyter_text>Set up the series that we are going to compare to back out the price index:<jupyter_code># Comparison 1: All sectors
# Average weekly earnings of production and nonsupervisory employees, 1982-84 dollars, total private, not seasonally adjusted
nom = 'CEU0500000031'

# Average weekly earnings of production and nonsupervisory employees, total private, not seasonally adjusted
rel = 'CEU0500000030'

sid = [(rel, nom)]

# Comparison 2: Goods-Providing
# Average weekly earnings of production and nonsupervisory employees, goods-producing, not seasonally adjusted
nom = 'CEU0600000030'
# Average weekly earnings of production and nonsupervisory employees, 1982-84 dollars, goods-producing, not seasonally adjusted
rel = 'CEU0600000031'

sid.append((rel, nom))

# Comparison 3: Service-Providing
# Average weekly earnings of production and nonsupervisory employees, private service-providing, not seasonally adjusted
nom = 'CEU0800000030'

# Average weekly earnings of production and nonsupervisory employees, 1982-84 dollars, private service-providing, not seasonally adjusted
rel = 'CEU0800000031'

sid.append((rel, nom))<jupyter_output><empty_output><jupyter_text>Now let's get the data. (Here I download directly from web to demonstrate that this is not an issue with any pre-processing.)<jupyter_code># Import
df_ = pd.read_csv(
      'https://download.bls.gov/pub/time.series/ce/ce.data.0.AllCESSeries',
      sep='\t',
      encoding='ascii',
      header=None,
      skiprows=1,
      low_memory=False
    )
# Rename the columns
df_.columns = ['series_id', 'year', 'period', 'value', 'footnote']
# Get rid of NA rows
df_.dropna(axis=1, how='all', inplace=True)
# Strip white space
df_ = df_.apply(lambda x: x.str.strip() if x.dtype == 'object' else x)
# Isolate the variables of interest
sid_t = tuple([s for sdi_i in sid for s in sdi_i ])
df_ = df_.loc[df_['series_id'].isin(sid_t)]
# Get rid of annual averages
df_.drop(df_.index[df_['period'].isin(['M13', 'A01'])], inplace=True)
# Make dates
df_['date'] = df_.apply(lambda d: pd.Timestamp(d['year'], int(d['period'][1:]),1),axis=1)

df_.head()<jupyter_output><empty_output><jupyter_text>Now we create two versions of the derived index:1. _Version 1_ = real / nominal * 1001. _Version 2_ = nominal / real * 100So we just flip these.<jupyter_code>cpi_v1,cpi_v2 = [], []
print('Import data and check to ensure 1982-84~=100 for both versions:')
for ii,(sid_i,nm) in enumerate(zip(sid, ['All', 'Goods', 'Services'])):
    df = df_.loc[df_['series_id'].isin(sid_i)] #pd.read_sql('SELECT * FROM ces_data WHERE series_id IN {}'.format(sid_i), conn, parse_dates=['date'])
    df = df.pivot(columns='series_id', index='date', values='value').rename(columns={sid_i[0]: 'real', sid_i[1]: 'nominal'})

    # Construct the CPI (of either type)
    df['cpi_v1'] = df['real'] / df['nominal'] * 100
    df['cpi_v2'] = df['nominal'] / df['real'] * 100

    # Check to make sure that the mean of 1982-1984 is 100 (as advertised)
    pp = [df.loc[(df.index >= pd.Timestamp(1982,1,1)) & (df.index < pd.Timestamp(1985,1,1)), 'cpi_v1'].mean(), 
     df.loc[(df.index >= pd.Timestamp(1982,1,1)) & (df.index < pd.Timestamp(1985,1,1)), 'cpi_v2'].mean()]
    print('\t1982-84 Mean Index for {}: {}'.format(nm, pp))

    # Merge to check
    dfi = df[['cpi_v1']].reset_index()
    dfi['name'] = nm
    cpi_v1.append(dfi)
    dfi = df[['cpi_v2']].reset_index()
    dfi['name'] = nm
    cpi_v2.append(dfi)
    
cpi_v1 = pd.concat(cpi_v1).pivot(index='date', values='cpi_v1', columns='name').dropna()
cpi_v2 = pd.concat(cpi_v2).pivot(index='date', values='cpi_v2', columns='name').dropna()<jupyter_output>Import data and check to ensure 1982-84~=100 for both versions:
	1982-84 Mean Index for All: [100.00034869187249, 100.0850012777281]
	1982-84 Mean Index for Goods: [100.08541305274392, 99.99994538356623]
	1982-84 Mean Index for Services: [100.08552539793358, 99.99985699479852]<jupyter_text>From the above we see that both versions of the price index have means that are quite close to the advertised 100. Now plot each of the versions:<jupyter_code>f, axes = plt.subplots(1,2, figsize=(10,5))

cpi_v1.plot(title='Version 1', ax=axes[0])
cpi_v2.plot(title='Version 2', ax=axes[1])<jupyter_output><empty_output><jupyter_text>From this we notice:1. The goods and services indices are identical for both versions (Can be confirmed by looking at head of frame.)1. The "Version 1" and "Version 2" indices appear to be flipped.This can be confirmed by examining the data:<jupyter_code>print('Version 1')
cpi_v1.tail()
print('Version 2')
cpi_v2.tail()<jupyter_output>Version 2<jupyter_text>So it appears:1. The goods and services indices are, in fact, equal for both versions.1. The "All goods" index for version 1 is identical to the goods/services indices for version 2 (and vice versa)._Note_ that the numbers won't be exactly equal to arbitrary precision due to the fact that the original input data was rounded to two decimal places. Of course, we've only examined the tail of the data. As a final check, I look at the absolute maximum difference between the two sets of series:<jupyter_code>print('Maximum absolute difference between "All" (v1) and mean of "Goods"/"Services" (v2): {}'.format(np.max(np.abs(cpi_v1['All'] - cpi_v2[['Goods', 'Services']].mean(axis=1)))))
print('Maximum absolute difference between "All" (v2) and mean of "Goods"/"Services" (v1): {}'.format(np.max(np.abs(cpi_v2['All'] - cpi_v1[['Goods', 'Services']].mean(axis=1)))))<jupyter_output>Maximum absolute difference between "All" (v1) and mean of "Goods"/"Services" (v2): 0.006366744655338152
Maximum absolute difference between "All" (v2) and mean of "Goods"/"Services" (v1): 0.0077194828678557315
{'fraction_non_alphanumeric': 0.1358085808580858, 'fraction_numerical': 0.06303630363036304, 'mean_word_length': 5.393459915611814, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 31, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '59967', 'license': 'Unlicense', 'n_tokens_mistral': 2312, 'n_tokens_neox': 2040, 'n_words': 624, 'path': 'ces_real_data_issue.ipynb', 'repo_name': 'michaelwooley/misc_static_content', 'size': '6102'}