In [178]:
import glob
import pandas as pd
import os
In [179]:
allFiles = glob.glob(os.getcwd() + "/*.csv")
In [181]:
df_list = []
for files in allFiles:
    filename = os.path.split(files)[-1].split('.csv')[0]
    df = pd.read_csv(files, names=["date","station", "impressions"], encoding="utf-8-sig")
    df['new_index'] = df[['date', 'station']].apply(lambda x: '_'.join(map(str, x)), axis=1)
    df = df[['new_index','impressions']]
    df.set_index('new_index', inplace=True)
    df.rename(columns={'impressions' : filename}, inplace=True)
    df_list.append(df)
In [182]:
df = pd.concat([frame for frame in df_list], axis=1)
df = df.reset_index()
df['date'] = df['new_index'].apply(lambda x: x.split('_')[0])
df['date'] = pd.to_datetime(df['date'], format="%Y-%m-%d")
df['station'] = df['new_index'].apply(lambda x: x.split('_')[1])
del df['new_index']
df.sort_values(by=['station', 'date'], inplace=True)
In [183]:
heat_df = pd.DataFrame(index=df.station.unique(), columns=list(df.columns[:12]))
In [204]:
from datetime import datetime
for station in df.station.unique():
    for i in xrange(12):
        pre = df[(df.station == station) & 
                 (df.date >= datetime(year=2014, day=2, month=12)) &
                 (df.date <= datetime(year=2015, day=22, month=3))]
        post = df[(df.station == station) & 
                 (df.date >= datetime(year=2015, day=1, month=12)) &
                 (df.date <= datetime(year=2016, day=20, month=3))]
        pre['delta'] = pre['date'].apply(lambda x: x - datetime(year=2014, day=2, month=12))
        post['delta'] = post['date'].apply(lambda x: x - datetime(year=2015, day=1, month=12))
        post_join = post.set_index('delta')
        post_join = post_join[[i]]
        pre_join = pre.set_index('delta')
        pre_join = pre_join[[i]]
        calc = pd.concat([pre_join, post_join], axis=1)
        calc['t'] =((calc[[1]] - calc[[0]])/((calc[[0]])))*100
        heat_df.set_value(station, df.columns[i], calc.t.mean())
C:\Users\mburke\AppData\Local\Continuum\Anaconda64\lib\site-packages\ipykernel\__main__.py:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
C:\Users\mburke\AppData\Local\Continuum\Anaconda64\lib\site-packages\ipykernel\__main__.py:11: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
In [283]:
import numpy as np
import seaborn as sns
cm = sns.light_palette("green", as_cmap=True)
heat_df['aud'] = 0
df['total_adults'] = df.iloc[:,0]  + df.iloc[:,1]
for j in heat_df.index:
    heat_df.set_value(j, 'aud', df[df.station == j]['total_adults'].mean())
with pd.option_context('display.precision', 4):
    html = heat_df.dropna()[[12,2,4,6,8,10,0,3,5,7,9,11,1]].sort_values(by='aud', ascending=False).style.background_gradient(cmap=cm)
In [284]:
html
Out[284]:
aud 18-24fem 25-34fem 35-49fem 50-64fem 65+fem 18+fem 18-24male 25-34male 35-49male 50-64male 65+male 18+male
FOXNC 1.11e+06 146.9 74.08 58.65 20.59 33.44 31.65 215.6 48.04 40.24 22.66 22.22 24.65
USA 9.083e+05 -11.85 -23.27 -18.08 -3.77 -2.424 -12.86 -0.6469 -13.38 -2.943 0.3399 10.08 -3.257
TNT 8.482e+05 1.684 16.36 -0.7269 -7.483 -3.444 -4.269 24.44 -5.646 -8.301 -3.579 -12.98 -7.924
ESPN 8.478e+05 -0.7392 -0.4674 25.65 20.65 4.196 8.196 -9.522 -18.23 -6.238 3.062 -3.875 -7.713
HGTV 8.104e+05 35.41 10.73 7.649 12.48 24.97 14.59 39.87 16.89 8.423 24.18 37.03 19.89
ADSM 7.888e+05 -12.97 2.454 -1.77 26.29 48.68 -3.492 -10.14 4.08 9.098 2.141 3.276 -1.199
HIST 6.646e+05 1.445 -10.78 -8.827 -4.588 -3.82 -8.179 -19.71 -22.37 -17.27 -9.466 -8.161 -13.8
ID 6.547e+05 0.006687 -21.94 17 17.59 11.24 9.894 53.7 -15.75 11.96 10.64 1.764 4.341
TBSC 6.373e+05 -23.25 -2.796 4.37 5.127 6.356 -1.975 -2.8 -10.2 -5.299 -6.176 -1.209 -7.325
DISC 6.209e+05 23.05 6.147 9.986 5.156 32.62 7.189 15.88 3.474 -0.883 7.228 5.421 2.344
FOOD 5.531e+05 -11.48 8.867 -1.667 11.74 9.762 4.413 18.09 9.141 -0.131 3.501 14.88 3.707
AMC 5.511e+05 0.5985 -0.4053 2.981 8.967 15.4 -0.865 -10.01 -14.74 -5.709 5.103 9.761 -5.968
HALL 5.419e+05 102.4 33.11 -1.049 12.86 0.2256 5.237 229.1 62.89 37.16 45.91 24.43 32.53
CNN 5.141e+05 208.1 58.65 42.05 37.53 71.05 52.93 341.8 72.05 74.87 56.04 85.41 71.48
FX 5.104e+05 -6.09 14.08 7.946 2.639 12.18 0.839 -11.84 -5.656 -0.4983 -1.585 0.8628 -6.782
AEN 5.036e+05 1.82 -0.3238 -23.05 -17.34 -20.46 -18.33 -6.92 -2.687 -24.15 -21.43 -22.4 -21.8
NAN 4.941e+05 29.61 0.6572 22.69 22.72 25.41 15.1 -10.95 5.716 76.69 52.86 49.41 22.56
NICK 4.726e+05 12.76 21.71 12.71 16.51 43.51 15.06 -3.316 5.119 3.082 18.35 14.16 0.873
LIF 4.54e+05 -1.445 -6.647 -4.043 -3.499 11.63 -3.695 54.48 30.55 16.3 10.83 11.14 11.01
TVL 4.225e+05 -25.13 -31.48 -19.66 7.834 7.893 -5.978 1.503 1.511 -5.087 5.524 0.1638 -1.184
FAM 4.19e+05 -22.34 1.013 -2.601 1.36 4.926 -7.622 8.299 13.88 21.94 33.66 33.4 15.15
TLC 4.167e+05 -28.37 -28.19 -5.988 -4.901 19.88 -7.944 -6.067 12.05 35.5 67.9 54.67 32.62
SPIKE 4.119e+05 15.41 25.03 13.3 16.03 20.37 9.387 0.3307 -9.068 -5.07 5.454 25.09 -2.856
DSNY 3.892e+05 -2.96 10.91 -11.84 -1.053 0.3818 -3.403 -2.643 8.314 -5.693 41.74 -6.334 1.244
TOON 3.892e+05 -6.848 11.3 2.258 -4.249 21.79 -0.6639 -28.89 -0.3822 5.071 -4.643 6.502 -12.41
MSNBC 3.694e+05 434.8 94.11 74.7 65.32 88.67 65.77 242.6 148.8 50.67 50.28 46.01 42.4
SYFY 3.684e+05 32 13.75 -2.149 7.86 15.26 3.112 -0.3778 -5.692 -9.601 1.173 4.126 -5.62
CMDY 3.156e+05 -16.9 -6.933 8.167 10.88 31.06 -3.823 -35.4 -19.94 -12.4 4.762 0.2617 -20.08
APL 3.069e+05 4.38 2.901 12.74 8.65 31.46 8.761 -12.23 -5.643 -8.137 -1.65 4.665 -6.705
GSN 3.068e+05 30.63 4.215 29.3 -0.9647 -6.213 -1.963 34.72 3.827 24.42 23.87 8.68 10.24
LMN 2.979e+05 12.14 -6.96 2.668 -6.157 21.06 -1.777 116.4 26.62 40.61 8.527 27.29 14.57
BRVO 2.913e+05 -38.4 -9.265 2.29 -0.1932 9.824 -6.498 52.04 22.06 20.55 32.21 40 19.54
HLN 2.824e+05 4.949 9.537 -7.762 18.24 -1.828 0.3641 134.9 17.63 5.457 21.73 32.39 16.03
WE 2.681e+05 83.14 8.444 21.19 62.17 142.7 41.5 306.7 100.9 66.81 109.1 175.6 75.64
WGNA 2.585e+05 19.02 28.68 -6.788 19.17 0.2849 0.4576 36.69 34.98 32.44 -3.399 -0.1596 -2.125
NGC 2.567e+05 3.205 -11.31 -1.48 11.4 33 1.338 -20.78 -8.809 -14.63 12.42 16.1 -3.881
BET 2.562e+05 25.34 25.45 6.495 -0.898 20.1 6.475 62.78 35.77 18.24 28.11 39.52 22.51
ENT 2.541e+05 -7.942 11.35 10.99 10.9 54.53 7.307 32.12 12.2 19.86 19.33 12.48 6.44
ESPN2 2.512e+05 38.17 20.38 37.29 23.6 23.17 16.78 -6.723 -6.747 -1.471 1.596 -10.11 -7.118
MTV 2.491e+05 -20.15 13.46 26.1 32.03 67.82 4.094 -13.48 -2.859 29.31 63.4 62.58 4.599
TRAV 2.43e+05 10.88 10.41 21.17 28.58 40.6 19.29 7.303 -3.561 1.157 6.735 31.68 4.41
VH1 2.222e+05 25.24 63.33 24.09 25.91 36.84 26.99 65.93 30.47 12.57 35.87 62.46 22.78
TRU 2.175e+05 42.95 17.1 5.153 3.805 5.797 3.951 41.66 7.99 -4.625 -15.01 -19.92 -8.383
H2 2.157e+05 29.53 -13.84 -9.931 -8.096 -5.97 -14.24 -31.32 -7.277 -3.94 -11.6 13.62 -7.815
INSP 2.152e+05 4302 507.3 85.48 8.234 35.23 29.78 1952 1899 278 67.67 49.24 63.64
OWN 2.13e+05 60.86 -9.717 18.65 0.6176 -2.641 -0.3057 149.7 35.91 24.27 52.3 36.38 27.26
DISJR 2.056e+05 -21.62 6.285 21.35 -18.22 38.37 -1.303 63.24 28.81 5.962 81.87 21.49 16.3
TWC 1.879e+05 116.5 22.21 -27.24 -4.731 -5.836 -10.85 50.45 0.9838 -14.59 5.049 4.961 -0.8262
SCI 1.818e+05 7.68 7.386 3.339 7.795 51.52 4.893 -5.806 13.22 6.685 -6.583 -5.767 -5.424
FXX 1.666e+05 -2.332 10.67 20.97 49.07 89.3 13.6 -3.704 4.924 10.59 30.47 59.84 6.933
CNBC 1.601e+05 152.7 56.61 29.57 34.81 21.68 22.84 151.9 23.82 9.978 19.94 11.24 10.36
NFLN 1.499e+05 287.9 110.1 104.3 75.18 133.1 73.57 37.5 17.58 36.26 36.38 57.08 26.26
SPD 1.432e+05 434.9 140.1 43.84 45.43 77.07 31.82 137.2 60.81 6.509 24.59 30.1 14.69
OXYG 1.356e+05 -21.86 -11.02 -3.788 16.2 53.65 -6.141 1.334 -2.52 5.365 30.6 47.56 -2.016
CMT 1.355e+05 1.134 22.03 5.339 26.76 25.44 6.054 6.717 14.65 -12.39 30.84 3.806 -0.1202
NGWD 1.329e+05 95.09 -0.3533 -16.07 -3.368 30.43 0.2035 99.8 66.25 4.021 -0.3418 12.16 5.09
NBCSN 1.316e+05 244.5 36.61 39.21 49.8 97.93 32.19 93.98 47.03 18.71 29.57 31.42 18.07
BBCA 1.193e+05 76.23 46.74 22.55 16.98 70.86 15.64 26.61 30.47 9.265 23.33 67.74 11.81
TV1 1.162e+05 23.33 -57.27 -4.408 16.61 81.45 -7.988 4.857 -4.639 -10.44 20.01 70.7 -4.454
MTV2 1.15e+05 -16.48 -16.94 30.1 40.98 111.4 -1.454 -33.27 -18.67 10.06 72.6 117.1 -13.45
NKJR 1.149e+05 105.4 50.1 75.62 7.712 16.02 40.79 69.37 72.57 26.55 130.2 7.991 37.28
VEL 1.147e+05 95.94 76.15 86.64 65.25 58.35 44.35 33.4 44.1 39.15 18.2 4.502 15.3
DAM 1.064e+05 83.21 -4.13 -1.491 18.2 28.56 2.632 67.3 10.69 -14.42 -2.205 7.084 -11.43
IFC 9.954e+04 108.1 27.13 30.99 38.45 33.1 16.55 49.06 2.79 6.55 20.95 19.98 6.58
GOLF 9.598e+04 690.1 586.7 49.55 83.09 23.78 37.41 340.1 120.1 14.6 -12.94 17.94 3.465
SUND 9.111e+04 1567 205.6 95.38 109.2 88.8 81.48 1595 276.3 146.3 71.64 50.35 68.48
RLZC 8.748e+04 645.3 243.1 74.18 60.28 56.77 54.8 451.2 189.8 16.24 -1.758 -3.483 0.3924
BOOM 8.65e+04 -13.39 14.1 -9.759 28.86 19.85 -7.285 46.14 -10.29 -14.07 52.92 -12.14 -5.586
TVGN 8.38e+04 81.65 59.62 13.97 -3.283 -10.37 -2.942 224.3 188.3 36.92 5.321 5.037 11.61
BIO 8.344e+04 316.6 42.19 35.61 7.436 4.455 13.38 166.9 13.64 10.31 -11.13 -8.288 -10.02
STYL 8.267e+04 760.8 134.2 41.44 97.65 182.7 61.94 64.88 44.52 6.728 43.09 206.2 22.32
DXD 7.901e+04 -39.53 34.1 5.082 29.69 61.37 -2.76 7.491 17.46 -6.718 15.62 50.53 -5.038
FXM 7.729e+04 107.3 97.27 37.59 17.14 66.57 18.35 224.8 139.2 24.76 18.37 40.74 14.46
CC 7.703e+04 71.36 21.15 15.5 24.45 31.39 15.28 74.72 75.27 14.69 10.11 43.62 12.62
NKTNS 6.279e+04 26.87 41.86 62.32 22.46 57.73 22.63 16.07 5.313 9.689 20.35 21.97 -6.473
TNNK 6.135e+04 -33.54 50.12 74.98 59.8 142.9 0.4515 -15.22 -20.37 49.79 48.82 186.9 -9.769
GALA 6.025e+04 13.29 28.63 6.331 -21.21 29.73 -2.258 69.34 31.63 18.12 -1.983 60.01 13.11
HUB 5.206e+04 -6.644 1.922 -15.45 3.746 14.21 -13.81 27.17 35.9 -17.83 12.7 -43.34 -23.32
ESPNU 5.069e+04 389.8 46.59 25.18 57.51 35.89 7.972 60.68 -0.6039 -18.37 8.566 -0.1102 -9.055
ENN 5.018e+04 64.78 7.674 1.254 16.77 -3.748 -17.49 7.823 -15.6 -6.316 -8.498 -3.492 -11.75
NBAT 4.8e+04 661.8 104.4 41.99 37.59 21.23 24.68 -0.6859 39 41.43 1.055 45.75 13.49
DSE 4.435e+04 39.3 43.56 -4.431 30.23 270.9 9.294 51.31 -3.972 14.42 -9.218 168.8 -2.332
CNTRC 4.35e+04 -10.33 86.76 4.145 8.95 0.1326 -8.641 644.8 327.6 65.71 2.67 87.6 22.13
UDN 4.188e+04 610.1 139.9 39.15 198.7 237 42 148 44.87 71.93 65.14 731.3 49.86
FUSE 2.55e+04 103.8 91.23 54.68 60.66 312.6 36.77 124.5 16.79 45.88 114.8 185.7 30.31
FOXD 2.421e+04 -1.878 111.4 54.92 169.2 161.8 25.12 67.58 6.096 52.9 17.31 146.3 11.4
MUN2 2.153e+04 -31.38 19.2 12.42 94.6 214.9 -0.9105 4.256 32.45 21.15 25.08 146.5 7.119