0

I have a situation where I need to transform my row level data to column level. The unique identifier on which this dataset lays consists of recurrent values. The dataset looks like this :

ID       Rating      Rating_Year
1        A           2016
1        B           2017
1        C           2018
2        D           2017
2        E           2018

Now I need to transpose the column Rating_year as 

Rating_year_2016    Rating_year_2017  Rating_year_2018

and get the final output as :

ID       Rating_Year_2016     Rating_Year_2017   Rating_Year_2018
1        A                    B                    C
2        No Rating            D                    E

This is a categorical value placement problem hence no existing solution worked for me on SO

I have tried the following code that gives me the right answer but I need to store this into a dataframe so that indexing cna be tackled properly going forward

df1=df.set_index(['ID','Rating_Year']).unstack()['Rating']
Django0602
  • 797
  • 7
  • 26
  • Can you add your not working solution? – jezrael Feb 01 '20 at 18:24
  • Do you try `df = df.pivot('ID','Rating_Year','Rating')` ? – jezrael Feb 01 '20 at 18:25
  • Or if not working `df.pivot_table(index='ID',columns='Rating_Year',values='Rating', aggfunc=','.join)` ? – jezrael Feb 01 '20 at 18:26
  • I have added my code. But it is working incorrectly. I need to get the desired output and that too stored in a data frame. – Django0602 Feb 01 '20 at 18:27
  • 1
    `df1=df.set_index(['ID','Rating_Year'])['Rating'].unstack().reset_index()` – jezrael Feb 01 '20 at 18:28
  • I am getting the same answer from the solution that I have provided as well. I just want to convert this to a dataframe so that I have right set of columns that can be read by pandas for further merge operations. – Django0602 Feb 01 '20 at 18:28
  • @jezrael: One more thing, How can I get the column names as shown in the output? I need to have the exact column names in my output – Django0602 Feb 01 '20 at 20:10
  • Use `df1=df.set_index(['ID','Rating_Year'])['Rating'].unstack().add_prefix('Rating_Year_').reset_index()` – jezrael Feb 02 '20 at 05:41

0 Answers0