CODEWITHSUNDEEP
pythonpandasdataframe
MTT
MTT

Reputation: 379

Transforming dataframe (transposing)

I have a dataframe that looks like this:

df=pd.DataFrame(columns=["AC_YEAR","Physics"],data=[[2010,8],[2010,9],[2011,7],[2010,3],[2011,4]])

And I want to transform it into:

df2=pd.DataFrame(columns=[2010,2011],data=[[8,7],[9,4],[3,]])

Some of the options I have tried (without luck) are:

df2 = pd.DataFrame(columns=[])
for year in [2010,2011]:
    pd.concat([df2,(df[df["AC_YEAR"]==year].iloc[:,1])], axis=1, join="inner", ignore_index=True)

Any help? Thanks!

Upvotes: 1

Views: 62

Answers (1)

jezrael
jezrael

Reputation: 862406

You need create new index values by cumcount or apply and Series contructor and last reshape by unstack:

df2 = df.set_index([df.groupby('AC_YEAR').cumcount(), 'AC_YEAR'])['Physics'].unstack()

Alternative solution:

df2 = df.groupby('AC_YEAR')['Physics'].apply(lambda x: pd.Series(x.values)).unstack(0)

print (df2)
AC_YEAR  2010  2011
0         8.0   7.0
1         9.0   4.0
2         3.0   NaN

Upvotes: 2

Related Questions