0

I have data as such

        time  close
date   
6/1/20 00:00 4375.5
6/1/20 00:15 4374.0
6/1/20 00:30 4376.5
...

I used df.pivot(columns='time', values='close') to output this (which worked as I wanted)

        00:00  00:15  00:30
date   
6/1/20 4375.5 4374.0  4376.5
...

I ran the pct change across timeframes df.pct_change(axis=1)

        00:00  00:15  00:30
date   
6/1/20   NaN  .00312 .00123  #these are just not real calcs, just putting in nums for example
...

Now I want to melt the df, but I'm having trouble doing so. I want the dataframe to go back to the original layout

        time  pct_change
date   
6/1/20 00:00  NaN
6/1/20 00:15 .00312
6/1/20 00:30 .00123
...

The reason I want to do this is because plotly.express.density_heatmap() cannot read the data as in the non-melted form. I'm using streamlit and wanted to insert a chart with plotly but ultimatly the chart just needs to look the same as df.style.background_gradient(cmap='green')

from plotly.express as px

#this code doesnt work, but this is how ideally want to input it. 
fig = px.density_heatmap(df, x='time', y='date', z='pct_change')

Thank you to anyone who provides guidance!

imbes
  • 71
  • 1
  • 6
  • Does this answer your question? [Doing the opposite of pivot in pandas Python](https://stackoverflow.com/questions/48135994/doing-the-opposite-of-pivot-in-pandas-python) – Ynjxsjmh May 24 '22 at 17:04

2 Answers2

1

you are missing a date, you can try this way

df2=df.pivot(index='date', columns=['time'], values='close').pct_change(axis=1)
df2

time    00:00   00:15   00:30
date            
6/1/20  NaN     -0.000343   0.000572

df2.unstack().reset_index()

    time    date    0
0   00:00   6/1/20  NaN
1   00:15   6/1/20  -0.000343
2   00:30   6/1/20  0.000572
Naveed
  • 11,495
  • 2
  • 14
  • 21
1

This is all going to be much easier if you just have a proper datetime index:

df.reset_index(inplace=True)
df['datetime'] = pd.to_datetime(df['date'] + ' ' + df['time'])
df = df.set_index('datetime').drop(['date', 'time'], axis=1)
print(df.pct_change())

Output:

                        close
datetime
2020-06-01 00:00:00       NaN
2020-06-01 00:15:00 -0.000343
2020-06-01 00:30:00  0.000572
BeRT2me
  • 12,699
  • 2
  • 13
  • 31