How to create multiple year columns in a new dataframe, from the original single column datetime dat-CodePudding

Source df has sdate datetime64 and svalue float64 columns as:

sdate      svalue
1980-01-01 5
1980-01-02 7
1980-01-05 2

1981-01-01 6
1981-01-02 3

1982-01-01 4
1982-01-02 2
1982-01-06 9

1983-01-06 8

How to create multiple year columns in a new dataset as:

dayofyear 1980 1981 1982 1983
1         5    6    4    nan
2         7    3    2    nan
3         nan  nan  nan  nan
4         nan  nan  nan  nan
5         2    nan  nan  nan
6         nan  nan  9    8

I tried something like df_new = df.pivot(index=df.sdate.dt.dayofyear, columns=df.sdate.dt.year, values='svalue')

CodePudding user response：

Use DataFrame.assign for new columns and then pivoting:

df_new = df.assign(d = df.sdate.dt.dayofyear, y = df.sdate.dt.year).pivot('d','y','svalue')

print (df_new)
y  1980  1981  1982  1983
d                        
1   5.0   6.0   4.0   NaN
2   7.0   3.0   2.0   NaN
5   2.0   NaN   NaN   NaN
6   NaN   NaN   9.0   8.0