1
$\begingroup$

I have data structured like so:

date        time    station     dem      impressions program
2/1/2016     12:00  station1    women18+ 6.85        malcom in the middle
2/1/2016     12:01  station1    men18+   1.225       movie (rpt)

This is my first real exposure to working with "big data" (the data is contained in a roughly 1bn row 50 column table) and as such have to be more selective/creative with my queries in the essence of time.

I was hoping to get an idea of how different demographic groups had changed over time, how station performance, day parts, days of the week had changed or shifted over time, etc.

When looking back in time I wasn't sure statistically how to approach these changes, or what a good way of comparing year over year changes that was more savvy than the simple difference in impressions for a group.

An example is the following table I created, which is a small subset of the

Aud = Audience (Column 1), which is total average impressions for the station.

18-24f = The average difference in daily impressions year over year for day-of-week-aligned days for 18-24 year old females (e.g. 12/1/2015 Tuesday - 12/2/2014 Tuesday; the average of all the number in this set.)

         aud    18-24f  25-34f  35-49f  50-64f  65+f    18+f    18-24m  25-34m  35-49m  50-64m  65+m    18+m
FOXNC   1.11m   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.08k   -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.482k  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.478k  -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.104k  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.888k  -12.97  2.454   -1.77   26.29   48.68   -3.492  -10.14  4.08    9.098   2.141   3.276   -1.199

Here is an example of the code for the above very simple math I was referring to:

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())

This is an example of what the time adjusted series looks like, and I'm just taking the average of the "t" column to get the average change in demographics, for a give station.

The index column here represents the number of days since the anchor date which is the same day of week (in this case 12/2/2014, 12/1/2015.)

FOXD    
delta   65+m;'14 65+m;'15 % difference      
0 days  50521   56379   11.595178
1 days  44075   53034   20.326716
2 days  33001   42361   28.362777
3 days  42140   36434   -13.540579
4 days  60595   50407   -16.813268
5 days  71703   39488   -44.928385
6 days  43573   52548   20.597618
7 days  34001   51940   52.760213
8 days  42140   39855   -5.422402
9 days  37500   30121   -19.677333
10 days 43820   37777   -13.790507

I'm hoping to get ideas on

a) How I should compare time series points statistically that is more "correct" than my current method.

b) What are some other ideas to explore this sort of a data set in order to measure shifting dimensions over time

c) Is there anything else methodologically you would do when deconstructing a problem like this which in the future might help when approaching similar problems

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.