<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Generate new columns with average block info
Take average values over two time horizons
6 blocks (~1 min) -> represents the current state (short frequency view)
60 blocks (~10 min) -> represents the long term view
Step1:  Merge data with new columns
Step2:  Create a label
What are we predicting? 
A hindsight estimate of what the price should be, given knowledge about previous blocks
Develop a summary statistic about the distribution of prices over previous blocks
Our target
Step3:  Compute the summary statistic mu
given the distribution of mv values, fit a statistical model to the data
use this fit model to compute the 25th percentile of the distribution
Step4:  Compute the label, p, given mu
knowing mu, how do we obtain our hindsight recommendation?
using our definition of mu, we solve an equation to obtain p (price)
p = (mu x gweiPaid_b) / gasUsed_b
this will serve as our label and thus recommendation for how much to pay per unit gas for a transation to successfully commence 
it tells us what price we need to set in order to force mv for that bid to be mu
Step5:  Look our method smoothed the prices out!
Write training set and labels to a csv file for modeling
<ASSISTANT_TASK:>
Python Code:
df['txcnt_second'] = df['tx_count'].values / df['blockTime'].values
df['avg_gasUsed_t_perblock'] = df.groupby('block_id')['gasUsed_t'].transform('mean')
df['avg_price_perblock'] = df.groupby('block_id')['price_gwei'].transform('mean')
def rolling_avg(window_size):
    
    price = df[['block_id', 'avg_price_perblock']].drop_duplicates().sort_values(
                        'block_id', ascending=True)
    
    gasUsed_t = df[['block_id', 'avg_gasUsed_t_perblock']].drop_duplicates().sort_values(
                            'block_id', ascending=True)
    
    txcnt_second = df[['block_id', 'txcnt_second']].drop_duplicates().sort_values(
                                'block_id', ascending=True)
    
    tx_count = df[['block_id', 'tx_count']].drop_duplicates().sort_values(
                            'block_id', ascending=True)
    
    gasUsed_b = df[['block_id', 'gasUsed_b']].drop_duplicates().sort_values(
                            'block_id', ascending=True)
    
    uncle_count = df[['block_id', 'uncle_count']].drop_duplicates().sort_values(
                                'block_id', ascending=True)
    
    difficulty = df[['block_id', 'difficulty']].drop_duplicates().sort_values(
                                'block_id', ascending=True)
    
    blocktime = df[['block_id', 'blockTime']].drop_duplicates().sort_values(
                             'block_id', ascending=True)
    
    # create new pandas dataframe with average values
    rolling_avg = pd.DataFrame()
    
    # calculate rolling averages 
    rolling_avg['avg_blocktime'] = blocktime['blockTime'].rolling(window=window_size).mean()
    rolling_avg['avg_gasUsed_b'] = gasUsed_b['gasUsed_b'].rolling(window=window_size).mean()
    rolling_avg['avg_tx_count'] = tx_count['tx_count'].rolling(window=window_size).mean()
    rolling_avg['avg_uncle_count'] = uncle_count['uncle_count'].rolling(window=window_size).mean()
    rolling_avg['avg_difficulty'] = difficulty['difficulty'].rolling(window=window_size).mean()
    rolling_avg['avg_txcnt_second'] = txcnt_second['txcnt_second'].rolling(window=window_size).mean()
    rolling_avg['avg_gasUsed_t'] = gasUsed_t['avg_gasUsed_t_perblock'].rolling(window=window_size).mean()
    rolling_avg['avg_price'] = price['avg_price_perblock'].rolling(window=window_size).mean()
    
    # insert blockids to merge on
    rolling_avg['blockids'] = df['block_id'].drop_duplicates().sort_values(ascending=True)
    
    return rolling_avg
num_blocks = [6, 60]
for num in num_blocks:
    df_rolling_avg = rolling_avg(num)
    df_rolling_avg.to_csv('./../data/block_avg_{}.csv'.format(num))
df_rolling_avg_6 = rolling_avg(6)
df_rolling_avg_60 = rolling_avg(60)
Explanation: Generate new columns with average block info
Take average values over two time horizons
6 blocks (~1 min) -> represents the current state (short frequency view)
60 blocks (~10 min) -> represents the long term view
End of explanation
merged1 = pd.merge(df, df_rolling_avg_6, left_on='block_id', right_on='blockids')
merged2 = pd.merge(merged1, df_rolling_avg_60, left_on='block_id', right_on='blockids', suffixes=('_6', '_60'))
merged2.columns
for col in merged2.columns:
    print(col, merged2[col].isnull().sum())
merged2.dropna(inplace=True)
Explanation: Merge data with new columns
End of explanation
merged2['mv'] = merged2.gweiShare / merged2.gasShare
merged2['mv'].isnull().sum()
merged2['mv'].describe()
Explanation: Create a label
What are we predicting? 
A hindsight estimate of what the price should be, given knowledge about previous blocks
Develop a summary statistic about the distribution of prices over previous blocks
Our target: the 25th percentile of the distribution (gweiShare / gasShare)
Definitions
gasUsed_t -> the amount of gas consumed on a transation
gasUsed_b -> the amount of gas consumed in an entire block
gweiPaid -> the total amount paid (Gwei) for a transaction (= gasUsed_t x price_gwei)
gweiPaid_b -> the total amount paid in a block 
gweiShare -> the fraction of gwei paid w.r.t. the entire block
gasShare -> the fraction of gas consumed w.r.t. the entire block
Define "miner value" – mv
the fraction of prices per block and gas per block
mv = gweiShare / gasShare 
local parameter (per transaction)
Define mu
mu is a summary statistic of mv (global parameter)
a measure of how likely a transaction is to be "picked up" by a miner for completion (risk factor)
our target/goal is for mu to be the 25th percentile of mv (gweiShare / gasShare)
mu = percentile(mv, 25) over the entire distribution of mv values
we can tune this parameter to increase or decrease the desired percentile 
it is a pre-emptive statistical calculation based on our hindsight knowledge
The "price" predicted with hindsight
knowing mu, how do we obtain our hindsight recommendation?
using our definition of mu, we solve an equation to obtain p (price)
p = (mu x gweiPaid_b) / gasUsed_b
this will serve as our label and thus recommendation for how much to pay per unit gas for a transation to successfully commence 
it tells us what price we need to set in order to force mv for that bid to be mu
Calculate miner value (mv) for every datapoint in our dataset
price / gas or gweiShare / gasShare
End of explanation
alpha = .25
mu= merged2.mv.quantile(alpha)
merged2.mv.apply(np.log10).hist(bins=100)
plt.xlim([-2,2])
ylims=plt.gca().get_ylim()
plt.vlines(np.log10(mu), ylims[0], ylims[1], 'r' )
merged2.mv.hist(bins=np.arange(0,10,.20))
ylims=plt.gca().get_ylim()
plt.vlines(mu, ylims[0], ylims[1], 'r' )
merged2.mv.hist(bins=np.arange(0,10,.20), color = 'k', alpha=0.5, histtype='stepfilled', 
                label='Miner Values')
ylims=plt.gca().get_ylim()
plt.vlines(mu, ylims[0], ylims[1], 'r', linestyle='--')
plt.title('Distribution of miner values', fontsize=18)
plt.legend()
plt.tight_layout()
plt.savefig('./../images/mv_dist.png', dpi=300)
Explanation: Compute the summary statistic mu
given the distribution of mv values, fit a statistical model to the data
use this fit model to compute the 25th percentile of the distribution
End of explanation
mu
merged2['p_label'] = mu* (merged2.gweiPaid_b / merged2.gasUsed_b)
merged2['p_label'].hist(bins=np.arange(0,50,2), color = 'b', alpha=0.7, histtype='stepfilled',
                        label='New Label')
merged2['price_gwei'].hist(bins=np.arange(0,50,.5), color = 'r', alpha=0.7, 
                           histtype='stepfilled', label='Price')
plt.title('Constructed Label', fontsize=18)
plt.legend()
plt.tight_layout()
merged2['p_label2'] = mu*merged2.gweiPaid_b/(merged2.gasUsed_b+merged2.gasUsed_t*(1-mu))
merged2.p_label2.describe()
merged2['p_label2'].hist(bins=np.arange(0,50,2), color = 'b', alpha=0.7, histtype='stepfilled',
                         label='New Label')
merged2['price_gwei'].hist(bins=np.arange(0,50,.5), color = 'r', alpha=0.7, histtype='stepfilled',
                           label='Price')
plt.title('Constructed Label', fontsize=16)
plt.legend()
plt.tight_layout()
plt.savefig('./../images/label.png', dpi=300)
Explanation: Compute the label, p, given mu
knowing mu, how do we obtain our hindsight recommendation?
using our definition of mu, we solve an equation to obtain p (price)
p = (mu x gweiPaid_b) / gasUsed_b
this will serve as our label and thus recommendation for how much to pay per unit gas for a transation to successfully commence 
it tells us what price we need to set in order to force mv for that bid to be mu
End of explanation
merged2.columns
# select candidate features for modeling
sel_cols = ['gasLimit_t',
           'gasUsed_t',
           'newContract',
           'blockTime',
           'difficulty',
           'gasLimit_b',
           'gasUsed_b',
           'reward',
           'size',
           'type',
           'totalFee',
           'amount_gwei',
           'gasShare',
           'gweiPaid',
           'gweiPaid_b',
           'gweiShare',
           'free_t',
           'day',
           'hour',
           'dayofweek',
           'txcnt_second',
           'avg_blocktime_6',
           'avg_gasUsed_b_6',
           'avg_tx_count_6', 
           'avg_uncle_count_6',
           'avg_difficulty_6',
           'avg_txcnt_second_6',
           'avg_gasUsed_t_6',
           'avg_price_6', 
           'avg_blocktime_60',
           'avg_gasUsed_b_60',
           'avg_tx_count_60',
           'avg_uncle_count_60',
           'avg_difficulty_60',
           'avg_txcnt_second_60',
           'avg_gasUsed_t_60',
           'avg_price_60',
           'mv']
features = merged2[sel_cols]
features.to_csv('./../data/training.csv')
labels = merged2['p_label2']
labels.to_csv('./../data/labels.csv')
Explanation: Look our method smoothed the prices out!
Write training set and labels to a csv file for modeling
End of explanation
<END_TASK>