Playing with my Apple Health Data but the csv is harder to wrangle than I have encountered-CodePudding

My pandas dataframe comes out as:

                                      BURNED CALORIES      
Date     00 - 01 01 - 02 02 - 03 .... 23 - 24
1/13/19  17.6    11.53   3.24         28.6
1/14/19  1.5     1.46    2.41         27.44

The top row is the hourly breakdown but the only column is Burned Calories over the 23-24 hour column. I feel as though I need to pivot it but I can't pull the Date column as an actual column. Any suggestions? Ideally, I would like to create a timeseries and play with hourly data.

This is what I would like to get to:

Date Hour Burned Calories

Thank you!!

DataFrame

CodePudding user response：

IIUC, is that what you're looking for?

df2=df.melt(id_vars='Date', var_name='variable')
df2.rename(columns={'variable':'hours', 'value':'calories burned'}, inplace=True)
df2

    Date    hours   calories burned
0   1/13/19 00-01   17.6
1   1/14/19 00-01   1.5
2   1/13/19 01-02   11.53
3   1/14/19 01-02   1.46
4   1/13/19 02-03   3.24
5   1/14/19 02-03   2.41
6   1/13/19 23-24   28.6
7   1/14/19 23-24   27.44

CodePudding user response：

So, I had to set the header to 1

Then run the melt operation and it was a success. Thank you again!

Now to convert my 23 - 24 to hours but I think I will split by delimiter then convert to time.

'''df2=burnedcalories2018.melt(id_vars='Date', var_name='variable') df2.rename(columns={'variable':'hours', 'value':'calories burned'}, inplace=True) df2['Date'] = pd.to_datetime(df2['Date']) df2.sort_values(by='Date', ascending=False)'''