import glob
import pandas as pd
import os
allFiles = glob.glob(os.getcwd() + "/*.csv")
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)
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)
heat_df = pd.DataFrame(index=df.station.unique(), columns=list(df.columns[:12]))
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
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)
html
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 |