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
17118   15925   12/1/2014   ADSM  
16006   11379   12/2/2014   ADSM  
19259   18846   12/3/2014   ADSM  
16386   17068   12/4/2014   ADSM  
7329    23158   12/5/2014   ADSM  
15893   13021   12/6/2014   ADSM  
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
ADSM    48.6    3.2
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 = 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.)

  • \$\begingroup\$ 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. \$\endgroup\$ – 200_success Apr 14 '16 at 18:19
  • \$\begingroup\$ 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. \$\endgroup\$ – mburke05 Apr 14 '16 at 18:29
  • \$\begingroup\$ I've edited the title as a suggestion. Perhaps you could do even better than that. \$\endgroup\$ – 200_success Apr 14 '16 at 18:33

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.