0

I am having following data frame

INCIDENT DATE TITLE ABS/FORECAST DIRECTORY 098 DIRECTORY 076 DIRECTORY 027
entity 1 26/4 prior absolute nAn 2389 2842
entity 2 26/4 prior forecasted 2876 9200 6674
entity 2 26/4 prior absolute nAn 8356 nAn
entity 1 26/5 prior absolute 1190 4544 7110
entity 2 26/5 prior absolute 9182 8128 2726
entity 3 26/6 prior forecasted nAn 6373 nAn
entity 3 26/6 prior absolute nAn nAn 7737

which is converted into long using melt

  `df2 = pd.melt(df, id_vars=["INCIDENT", "TITLE", "ABS/FORECAST", "DATE"], 
              var_name="KPI", value_name="Value")`

which looks like this

INCIDENT DATE TITLE ABS/FORECAST KPI Value
entity 1 26/4 prior absolute DIRECTORY 098 nAn
entity 2 26/4 prior forecasted DIRECTORY 098 2876
entity 2 26/4 prior absolute DIRECTORY 098 nAn
entity 1 26/5 prior absolute DIRECTORY 098 1190
entity 2 26/5 prior absolute DIRECTORY 098 9182
entity 3 26/6 prior forecasted DIRECTORY 098 nAn
entity 3 26/6 prior absolute DIRECTORY 098 nAn
entity 1 26/4 prior absolute DIRECTORY 076 2389
entity 2 26/4 prior forecasted DIRECTORY 076 9200
entity 2 26/4 prior absolute DIRECTORY 076 8356
entity 1 26/5 prior absolute DIRECTORY 076 4544
entity 2 26/5 prior absolute DIRECTORY 076 8128
entity 3 26/6 prior forecasted DIRECTORY 076 6373
entity 3 26/6 prior absolute DIRECTORY 076 nAn
entity 1 26/4 prior absolute DIRECTORY 027 2842
entity 2 26/4 prior forecasted DIRECTORY 027 6674
entity 2 26/4 prior absolute DIRECTORY 027 nAn
entity 1 26/5 prior absolute DIRECTORY 027 7110
entity 2 26/5 prior absolute DIRECTORY 027 2726
entity 3 26/6 prior forecasted DIRECTORY 027 nAn
entity 3 26/6 prior absolute DIRECTORY 027 7737

now this needs to h=get converted into wide format where the directory has single unique row entry. I tried to use PIVOT FUNCTION, but due to the duplicity it throws error as: 'Incident' is not in list

df3= df2.pivot(index='KPI',columns=["Incident", "DATE", "ABS/FORECAST"], values= 'Value')
ASSOND
  • 37
  • 8
  • pivot1=pd.pivot_table(df,index='KPI',columns=['Incident',TITLE','Segment','ABS/FORECAST','Date',],values='value') pivot1 – ASSOND Apr 27 '22 at 11:44

1 Answers1

1
df.melt(['INCIDENT','DATE', 'TITLE'], var_name = 'DIRECTORY LIST')

   INCIDENT  DATE  TITLE DIRECTORY LIST value
0  entity 1  26/4  prior  DIRECTORY 098   nAn
1  entity 2  26/5  prior  DIRECTORY 098  9182
2  entity 3  26/6  prior  DIRECTORY 098   nAn
3  entity 1  26/4  prior  DIRECTORY 076  2389
4  entity 2  26/5  prior  DIRECTORY 076  8128
5  entity 3  26/6  prior  DIRECTORY 076  6373
6  entity 1  26/4  prior  DIRECTORY 027  2842
7  entity 2  26/5  prior  DIRECTORY 027  2726
8  entity 3  26/6  prior  DIRECTORY 027   nAn
Onyambu
  • 67,392
  • 3
  • 24
  • 53
  • Aleary converted using melt but i need in Wide format with directory as single row entry which can be done using pivot but its throwing error – ASSOND Apr 27 '22 at 10:59
  • @ASSOND this question has been marked as duplicate. Only this solution is feasible. No other solution can be added. So no need of edditing your post. If you have a question, you better ask it on another new question – Onyambu Apr 27 '22 at 11:08