Python Row to Time Series Columns

I am analyzing PGA tour data over time. For machine learning purposes I would like the column data to be a representation of a statistic over a couple of weeks. Below is an example of the original data structure.

import pandas as pd
import numpy as np

data = {'Player Name':['Tiger','Tiger','Tiger','Tiger','Tiger','Tiger','Jack',
                       'Jack','Jack','Jack','Jack','Jack','Jack'], 
        'Date':[1, 2, 4, 6, 7, 9, 1, 3, 4, 6, 9, 10, 11],
        'SG Total':[13, 2, 14, 6, 8, 1, 1, 3, 8, 4, 9, 2, 1]}

df_original = pd.DataFrame(data)

I would like to get the data in the following format.

data = {'Player Name':['Tiger','Tiger','Tiger','Jack','Jack',
                   'Jack','Jack'], 
    'Date':[6, 7, 9, 6, 9, 10, 11],
    'SG Total (Date t-3)':[13, 2, 14, 1, 3, 8, 4],
    'SG Total (Date t-2)':[2, 14, 6, 3, 8, 4, 9],
    'SG Total (Date t-1)':[14, 6, 8, 8, 4, 9, 2],
    'SG Total (Date y)':  [6, 8, 1, 4, 9, 2, 1]}
df_correct = pd.DataFrame(data)

In the real data set I am using I have approximately 1000 columns. So the new desired dataset would likely have 4000 columns. As you can see in the desired dataset, I removed the first 3 weeks for each player. I started the date at the 4th week of data for the individual since I am using the previous 3 weeks to fill (t-3), (t-2) & (t-1)

I had originally created a dataset for each week regardless if the player has played, and used this code to created the desired DataFrame.

#%% Creates weekly dataframes & predictions dataframes

#Creates dataframes of each week
dict_of_weeks = {}

for i in range(1,df_numeric_combined['Date'].nunique()+1):
    dict_of_weeks['Week_{}_df'.format(i)] = df_numeric_combined[df_numeric_combined['Date'] == i]
    dict_of_weeks['Week_{}_df'.format(i)].columns += ' (Week ' + str(i) + ')'
    dict_of_weeks['Week_{}_df'.format(i)].rename(columns={'Player Name (Week ' + str(i) + ')' : 'Player Name' , 'Date (Week ' + str(i) + ')' : 'Date'},inplace=True)


#Creating dataframes for prediction of each week
import functools

dict_of_predictions = {}

df_weeks = []

for i in range(4,df_numeric_combined['Date'].nunique()+1):
    dfs = [dict_of_weeks['Week_'+str(i-3)+'_df'], dict_of_weeks['Week_'+str(i-2)+'_df'], dict_of_weeks['Week_'+str(i-1)+'_df'], dict_of_weeks['Week_'+str(i)+'_df']]

    dict_of_predictions['Week_{}_predictions'.format(i)] = functools.reduce(lambda left,right: pd.merge(left,right,on=['Player Name'], how='outer'), dfs)

    cols = []
    count = 1
    for column in dict_of_predictions['Week_{}_predictions'.format(i)].columns:
        if column == 'Date_y':
            cols.append('Date_y_'+ str(count))
            count+=1
            continue
        cols.append(column)

    dict_of_predictions['Week_{}_predictions'.format(i)].columns = cols

    dict_of_predictions['Week_{}_predictions'.format(i)].drop(columns = ['Date_x', 'Date_y_1'],inplace = True)

    dict_of_predictions['Week_{}_predictions'.format(i)].rename(columns={'Date_y_2':'Date'},inplace=True)

    dict_of_predictions['Week_{}_predictions'.format(i)].columns = dict_of_predictions['Week_{}_predictions'.format(i)].columns.str.replace('(Week ' + str(i-3)+ ')', 'Week t-3').str.replace('(Week ' + str(i-2)+ ')', 'Week t-2').str.replace('(Week ' + str(i-1)+ ')', 'Week t-1').str.replace('(Week ' + str(i)+ ')', 'Week y')

    df_weeks.append(dict_of_predictions['Week_{}_predictions'.format(i)])

#Combines predictions dataframes
df = pd.concat(dict_of_predictions.values(), axis=0, join='inner')

Yet this code that I created only works if the player played consecutive weeks because it relies on the week number and minuses 3, 2 and 1.

The end goal is to get the data in the df_correct format.

Thanks!

1 answer

  • answered 2019-08-23 23:53 calestini

    If I understand your requirement correctly, you can use shift in a sorted dataframe with groupby to accomplish the previous week results for each player:

    
    ## Sort first by player and date
    df_corrected = df_original.sort_values(['Player Name','Date'])
    
    your_columns = ['SG Total'] ## name your 4000 columns here
    
    for col in your_columns:
        for s in [3,2,1,0]: ### time lapses
            df_corrected[f'{col} (Date t-{s})'] = df_corrected.groupby('Player Name')[col].shift(s)
    
    df_corrected.drop(your_columns, axis=1, inplace=True)
    

    Which outputs

    Out[12]: 
       Player Name  Date  SG Total (Date t-3)  SG Total (Date t-2)  \
    6         Jack     1                  NaN                  NaN   
    7         Jack     3                  NaN                  NaN   
    8         Jack     4                  NaN                  1.0   
    9         Jack     6                  1.0                  3.0   
    10        Jack     9                  3.0                  8.0   
    11        Jack    10                  8.0                  4.0   
    12        Jack    11                  4.0                  9.0   
    0        Tiger     1                  NaN                  NaN   
    1        Tiger     2                  NaN                  NaN   
    2        Tiger     4                  NaN                 13.0   
    3        Tiger     6                 13.0                  2.0   
    4        Tiger     7                  2.0                 14.0   
    5        Tiger     9                 14.0                  6.0   
    
        SG Total (Date t-1)  SG Total (Date t-0)  
    6                   NaN                    1  
    7                   1.0                    3  
    8                   3.0                    8  
    9                   8.0                    4  
    10                  4.0                    9  
    11                  9.0                    2  
    12                  2.0                    1  
    0                   NaN                   13  
    1                  13.0                    2  
    2                   2.0                   14  
    3                  14.0                    6  
    4                   6.0                    8  
    5                   8.0                    1