Plotly unable to plot from pandas pivot_table/stack

1
votes

My original dataframe contains hourly data from several entities.sample(5) of original dataframe

I have used pd.pivot_table with index=pd.Grouper(freq='M') to obtain the sum of the value for each entity grouped by Month. 

piv=pd.pivot_table(df, values='EnergyValue',index=pd.Grouper(freq='M', key='DDMM'),columns=['entityID'], aggfunc=np.sum)

I now want to plot this using plotly. example of dataframe after pivot_table

I am able to plot the pivot_table directly by adding .plot()

pd.pivot_table(df, values='EnergyValue',index=pd.Grouper(freq='M', key='DDMM'),columns=['entityID'], aggfunc=np.sum).plot()

.plot() directly after pivot_table operation on dataframe

However I want this chart to be in plotly. I have tried to stack the pivot_table and then plot. dataframe after stack operation. I want to plot the values column (red arrow) as y-value and index as x-value

However, I am not able to use the value as y-axis How do I access this y-value? 

stack=piv.stack()
px.line(stack,x='DDMM',y=piv.values,color='entityID')
1
python-3.xpandasindexingpivotplotly
The y argument only accepts names of columns of the data frame argument at the moment. - nicolaskruchten
I see. Thanks for clarifying on px only taking column names. How do I plot this then? Is there a way to make the result of the stack operation (piv.stack()) a dataframe with named columns? - Holden
Your original data frame is already in the correct format for PX to work, you don’t need to do any stacking actually. - nicolaskruchten
You can just use groupby without a pivot table - nicolaskruchten
Thanks a lot Nicolas. I have used groupby and then reset_index on the resulting dataframe. I then passed "DDMM" as the x value to px. This worked. Thanks a lot for your help. - Holden

1 Answers

1
votes

The y argument only accepts string names of columns from the data frame argument.

Your original data frame is already in roughly the right (tidy) format so you don’t need to pivot and stack, you can just use groupby and reset_index.