Date offset to normalized date columns

Question

For the following data frame:

df = pd.DataFrame({'Name': {0: "A", 1: "A", 2:"B", 3: "B", 4: "A"}, 
       'Spec1': {0: '1', 1: '1', 2:'5',
                3: '5', 4: '1'},
       'Spec2': {0: '2a', 1: '2a', 2:np.nan,
                3: np.nan, 4: '2a'},
       'Date': {0: '10/1/2007', 1: '11/1/2007', 2:'7/1/2009',
                3: '8/1/2009', 4: '12/1/2007'},
       'Value': {0: 5, 1: 10, 2: 3,
                3: 4, 4: 8}            
              }, columns=['Name', 'Spec1', 'Spec2','Date','Value'])
df['Date']=pd.to_datetime(df['Date'])


    Name    Spec1   Spec2   Date    Value
0       A       1       2a  2007-10-01  5
1       A       1       2a  2007-11-01  10
2       B       5       NaN 2009-07-01  3
3       B       5       NaN 2009-08-01  4
4       A       1       2a  2007-12-01  8

I would like to convert a dataframe to:

    Name    Spec1   Spec2   Date1   Date2 Date3
0   A       1       2a      5       10    8
1   B       5       NaN     3       4     NaN

I think I could expand it, but I'm not sure how to handle the transfer of dates from the actual date to the columns "First Date Instance, Second Date Instance".

Answer 1

You just need cumcountto create a new key pair here

df['key']=df.groupby('Name').cumcount()+1

df.set_index(['Name','Spec1','Spec2','key']).Value.unstack().add_prefix('Date').reset_index()
Out[11]: 
key Name  Spec1 Spec2  Date1  Date2  Date3
0      A      1    2a    5.0   10.0    8.0
1      B      5   NaN    3.0    4.0    NaN