0

I would like to reshape data from long to wide. Especially columns [SAP Version, FY, Period, value]

What I have tried so far:

df.reset_index() df = df.set_index(['Project No_left','Name','SBU Name','Reporting Level 1','Reporting Level 2','Reporting Level 3']) Current DF:

Project No_left Name| Reporting Level 1| Reporting Level 2| Reporting Level 3| SAP Version| FY| Period| value| 0-01214 Project A| IP| NI| FS| 0| 2020| Oct| 1939.92| 0-01214 Project A| IP| NI| FS| 63| 2020| Oct| 1939.92| 0-01214 Project A| IP| NI| FS| 64| 2020| Oct| 1939.92| 0-01214 Project A| IP| NI| FS| 65| 2020| Oct| 1939.92| 0-01214 Project A| IP| NI| FS| 0| 2020| Nov| 9052.96| 0-01214 Project A| IP| NI| FS| 63| 2020| Nov| 9052.96| 0-01032 Project B| IP| AR| PO| 0| 2021| Jul| 27842.5| 0-01032 Project B| IP| AR| PO| 0| 2021| Jul| 30000| 0-01032 Project B| IP| AR| PO| 63| 2021| Jul| 672500| 0-01032 Project B| IP| AR| PO| 64| 2021| Jul| 27842.5| 0-01032 Project B| IP| AR| PO| 64| 2021| Jul| 50000| 0-01032 Project B| IP| AR| PO| 0| 2021| Aug| 27842.5| 0-01032 Project B| IP| AR| PO| 0| 2021| Aug| 50000| 0-01032 Project B| IP| AR| PO| 63| 2021| Aug| 297500| 0-01032 Project B| IP| AR| PO| 64| 2021| Aug| 27842.5| Expected DF:

+------------------+------------+--------------------+--------------------+--------------------+-------+-------+-------+-------+-------+-------+-------+-------+ | Project No_left| | Name| | Reporting Level 1 | Reporting Level 2| | Reporting Level 3| | 0| | 63| | 64| | 65| | 0| | 63| | 64| | 65| | +------------------+------------+--------------------+--------------------+--------------------+-------+-------+-------+-------+-------+-------+-------+-------+ | Project No_left| | Name| | Reporting Level 1 | Reporting Level 2| | Reporting Level 3| | 2020| | 2020| | 2020| | 2020| | 2021| | 2021| | 2021| | 2021| | | Project No_left| | Name| | Reporting Level 1| | Reporting Level 2| | Reporting Level 3| | Jul| | Aug| | Oct| | Nov| | Jul| | Aug| | Oct| | Nov| | | 0-01214| | Project A| | IP| | NI| | FS| | | | | | | | | | | 0-01032| | Project B| | IP| | AR| | PO| | | | | | | | | | +------------------+------------+--------------------+--------------------+--------------------+-------+-------+-------+-------+-------+-------+-------+-------+ can someone also tell me how to create a table to paste, i have tried this https://senseful.github.io/text-table/ but doesn't work properly :(

Pasting a picture as well to explain the outcome better. Thanksenter image description here

user13670066
  • 45
  • 8
  • @jezrael - Thanks for the link but I couldnt find an answer that matches my requirement – user13670066 Jun 18 '20 at 07:44
  • Use `pivot_table`, index and columns by list by your columns names. – jezrael Jun 18 '20 at 07:45
  • tried this, doesnt work - pd.pivot_table(df,index=['Project No_left','Name','SBU Name','Reporting Level 1','Reporting Level 2','Reporting Level 3'],values=["value"],aggfunc=np.sum) Where do i specify the columns that i want to transform to wide like 'SAP Version', 'FY','Period' ? – user13670066 Jun 18 '20 at 07:52
  • pd.pivot_table(df,index=['Project No_left','Name','SBU Name','Reporting Level 1','Reporting Level 2','Reporting Level 3'],columns = ['SAP Version', 'Period','FY'], values=["value"],aggfunc=np.sum) – user13670066 Jun 18 '20 at 07:54

0 Answers0