# Analysis of TV station preferences for various demographic groups

I have a notebook here which details my code and may be more legible.

I worked on a project that flattens a table that initially looked like this (this is table1):

18+fem  18+male date    station
59688   36299   12/1/2014   AEN
61738   67912   12/2/2014   AEN
61115   30988   12/3/2014   AEN
50751   29726   12/4/2014   AEN
73939   47329   12/5/2014   AEN
147652  54407   12/6/2014   AEN


Into the table that is in the linked notebook which looks like this (this is table2):

        65+fem  65+male
AEN    -20.4   -22.4


By section of code here is what I am doing:

1: Importing dependencies, importing files, joining them together to create table1 view.

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)


2: Creating a temp dataframe, this will house the values that I calculate in the next step and become our heat map table.

heat_df = pd.DataFrame(index=df.station.unique(), columns=list(df.columns[:12]))


Finally, this is where performance really becomes brutally slow (~10-15seconds.)

3: Creating table2

#for each station in our table1

for station in df.station.unique():

#13 is the number of columns of our demographics (18+fem, 18+male, etc.; of which there are 13 total), hence xrange(12), I think this could or should have been accomplished by using something better referencing the columns by name rather than by index.
for i in xrange(12):

#create a temp dataframe. there is a pre and a post section. i am going to compare the post to the pre in the below maths.
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))]

#this is where things get funky. i am time adjusting the dataframe so that the days of the week line up (12/2/2014 is a tuesday, and 12/1/2015 is a tuesday, so i create a new index that is the number of days since each of those respective starting points.) i call this column 'delta'.
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))

#i zero in on the column(which represents one demographic) i'm looking for; i.
post_join = post.set_index('delta')
post_join = post_join[[i]]
pre_join = pre.set_index('delta')
pre_join = pre_join[[i]]

#create a temp dataframe called 'calc' that is just the delta as an index, and the specific demographic
#i then create a column 't' which is used to calculate the % change +/- year over year, adjusted by the delta column.
calc = pd.concat([pre_join, post_join], axis=1)
calc['t'] =((calc[[1]] - calc[[0]])/((calc[[0]])))*100

#the single point calc.t.mean() is then finally entered for each demographic, and station combination.
heat_df.set_value(station, df.columns[i], calc.t.mean())


I'm hoping to get input on my general code, any mistakes or redundancies I might be committing to error. Performance improvements. And just general theory improvements (i.e. how would you adjust this problem if you needed the same sort of transformation of the data.)

• For the benefit of reviewers, could you briefly summarize what the transformation is from table1 to table2, and make that your question title? See How to Ask. – 200_success Apr 14 '16 at 18:19
• Im honestly not sure how to describe what the transformation is which is why I spent so much time trying to flesh the code nuances out so that they made sense. Do you have suggestions? I think maybe it's like a shifted pivot transformation. But not sure. Sorry if that doesn't help. – mburke05 Apr 14 '16 at 18:29
• I've edited the title as a suggestion. Perhaps you could do even better than that. – 200_success Apr 14 '16 at 18:33