CODEWITHSUNDEEP
python
Ivan
Ivan

Reputation: 7746

How to group by date when date is in string datetime format

I have 1 minute sampled data like this:

                     1. open  2. high  3. low  4. close  5. volume
date                                                              
2019-07-22 09:31:00   137.46   137.46  137.46  137.4600   888977.0
2019-07-22 09:32:00   137.46   137.56  137.33  137.5101   138172.0

I want to get the average by day. This statement doesn't work because 'date' index is in datetime format.

avg = data.dropna().groupby('date').mean()

will give me the average by minute of the day, not the entire day.

Upvotes: 1

Views: 282

Answers (1)

Alexandre B.
Alexandre B.

Reputation: 5502

You can access the day with date. That assumes you have a datetime object.

If the index are string, you can convert them with to_datetime:

import pandas as pd

df = pd.DataFrame({"1. open":[137.46, 137.46],
                    "2. high": [137.46, 137.56 ],
                    "3. low": [137.46, 137.33],
                    "4. close": [137.4600, 137.5101],
                    "5. volume": [888977.0, 138172.0]},
                    index=["2019-07-22 09:31:00", "2019-07-22 09:32:00"])
# Convert index to datetime
df.index = pd.to_datetime(df.index, format="%Y-%m-%d %H:%M:%S")
print(df)
#                      1. open  2. high  3. low  4. close  5. volume
# 2019-07-22 09:31:00   137.46   137.46  137.46  137.4600   888977.0
# 2019-07-22 09:32:00   137.46   137.56  137.33  137.5101   138172.0

print(df.groupby(df.index.date).mean())
#             1. open  2. high   3. low   4. close  5. volume
# 2019-07-22   137.46   137.51  137.395  137.48505   513574.5

Upvotes: 1

Related Questions