Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I slice a dataframe by timestamp, when timestamp isn't classified as index?

How can I split my pandas dataframe by using the timestamp on it?

I got the following prices when I call df30m:

               Timestamp    Open    High     Low   Close     Volume
0    2016-05-01 19:30:00  449.80  450.13  449.80  449.90    74.1760
1    2016-05-01 20:00:00  449.90  450.27  449.90  450.07    63.5840
2    2016-05-01 20:30:00  450.12  451.00  450.02  450.51    64.1080
3    2016-05-01 21:00:00  450.51  452.05  450.50  451.22    75.7390
4    2016-05-01 21:30:00  451.21  451.64  450.81  450.87    71.1190
5    2016-05-01 22:00:00  450.87  452.05  450.87  451.07    73.8430
6    2016-05-01 22:30:00  451.09  451.70  450.91  450.91    68.1490
7    2016-05-01 23:00:00  450.91  450.98  449.97  450.61    84.5430
8    2016-05-01 23:30:00  450.61  451.50  450.55  451.45   111.2370
9    2016-05-02 00:00:00  451.47  452.31  450.69  451.19   190.0750
10   2016-05-02 00:30:00  451.20  451.68  450.45  450.82   186.0930
11   2016-05-02 01:00:00  450.83  451.64  450.65  450.73   112.4630
12   2016-05-02 01:30:00  450.73  451.10  450.31  450.56   137.7530
13   2016-05-02 02:00:00  450.56  452.01  449.98  450.27   151.6140
14   2016-05-02 02:30:00  450.27  451.30  450.23  451.11    99.5490
15   2016-05-02 03:00:00  451.29  451.29  450.17  450.33   178.9860
16   2016-05-02 03:30:00  450.44  451.20  450.44  450.75    65.1480
17   2016-05-02 04:00:00  450.79  451.20  450.75  451.00    78.0430
18   2016-05-02 04:30:00  451.00  451.11  450.85  451.11    64.7250
19   2016-05-02 05:00:00  451.11  451.64  451.00  451.12    73.4840
20   2016-05-02 05:30:00  451.12  451.83  450.67  451.33    94.1950
21   2016-05-02 06:00:00  451.35  451.37  450.17  450.18   227.7480
22   2016-05-02 06:30:00  450.18  450.43  450.17  450.17    83.0270
23   2016-05-02 07:00:00  450.17  450.43  448.90  449.41   170.4950
24   2016-05-02 07:30:00  449.38  450.00  448.56  448.56   243.0420
25   2016-05-02 08:00:00  448.67  448.67  446.21  448.00   525.7090
26   2016-05-02 08:30:00  448.12  448.49  445.00  445.00   673.5810
27   2016-05-02 09:00:00  445.00  445.51  440.11  444.20  1392.9049
28   2016-05-02 09:30:00  444.24  444.36  440.11  442.00   438.6860
29   2016-05-02 10:00:00  441.91  443.20  440.05  442.24   400.5850
...                  ...     ...     ...     ...     ...        ...
1651 2016-06-05 05:00:00  578.74  579.00  577.92  578.39    93.6980
1652 2016-06-05 05:30:00  578.40  578.48  574.52  575.26    98.1580
1653 2016-06-05 06:00:00  575.24  576.02  572.47  574.06   126.8620
1654 2016-06-05 06:30:00  574.06  576.35  574.06  576.34   125.4120
1655 2016-06-05 07:00:00  576.34  576.34  574.73  575.83    34.8070
1656 2016-06-05 07:30:00  575.84  576.27  574.91  575.58    74.8180
1657 2016-06-05 08:00:00  575.58  578.57  575.58  578.36   123.2560
1658 2016-06-05 08:30:00  578.23  578.47  576.18  577.25    43.6590
1659 2016-06-05 09:00:00  577.20  578.85  576.70  577.27    95.3900
1660 2016-06-05 09:30:00  577.36  578.18  576.70  576.70    51.0250
1661 2016-06-05 10:00:00  576.70  576.70  574.55  575.39   101.0590
1662 2016-06-05 10:30:00  575.41  576.44  575.18  576.44    86.4340
1663 2016-06-05 11:00:00  576.50  577.89  576.50  577.80   113.0600
1664 2016-06-05 11:30:00  577.80  578.10  576.03  576.98    57.5050
1665 2016-06-05 12:00:00  576.98  577.55  576.59  577.54    56.1070
1666 2016-06-05 12:30:00  577.54  583.00  570.93  572.82   872.8200
1667 2016-06-05 13:00:00  572.94  573.19  569.64  572.50   310.0020
1668 2016-06-05 13:30:00  572.50  574.37  572.50  574.09    59.3410
1669 2016-06-05 14:00:00  574.09  574.19  571.51  572.98   155.4310
1670 2016-06-05 14:30:00  572.98  573.57  572.02  573.47    76.9270
1671 2016-06-05 15:00:00  573.62  575.10  572.97  573.37    59.1430
1672 2016-06-05 15:30:00  573.37  574.39  573.37  574.38    77.3270
1673 2016-06-05 16:00:00  574.39  575.59  574.38  575.59    52.0150
1674 2016-06-05 16:30:00  575.00  575.59  574.50  575.00    66.9300
1675 2016-06-05 17:00:00  575.00  576.83  574.38  576.60    50.2990
1676 2016-06-05 17:30:00  576.60  577.50  575.50  576.86   104.5200
1677 2016-06-05 18:00:00  576.86  577.21  575.44  575.80    55.3270
1678 2016-06-05 18:30:00  575.77  575.80  574.52  574.77    78.7760
1679 2016-06-05 19:00:00  574.73  575.18  572.52  574.47   126.4300
1680 2016-06-05 19:30:00  574.49  574.87  573.80  574.32    10.4930

As you can see, it contains the last 35 days grouped by intervals of 30 min.

I wanna manipulate this price history in different time windows.

So, as a beginner example, I would like to fetch only the info from the last 1 day.

How can I filter this dataframe to show the info from the last 1 day?

This is what I've tried:

import datetime

d0 = datetime.datetime.today()
d1 = datetime.datetime.today() - datetime.timedelta(days=1)

print d0
>>> 2016-06-05 17:10:37.633824

print d1
>>> 2016-06-04 17:10:37.633967  

df_1d = df30m['Timestamp'] > d1

print df_1d

This returns me a pandas series filled with True or False

0    False
1    False
2    False
3    False
4    False
...
1676    True
1677    True
1678    True
1679    True
1680    True

Also I've tried to use the between_time() module.

df_1d = df30m.between_time(d0, d1)

But I got the following error message:

TypeError: Index must be DatetimeIndex

Please, can anyone show me a pythonic way to slice my dataframe?

like image 721
dot.Py Avatar asked Jun 05 '16 20:06

dot.Py


People also ask

How to add timestamp to the index of data?

If you have set the "Timestamp" column as the index , then you can simply use Additionally. If you have also time in your index, you can use it like this df.loc ['2009-05-01 00:00:00':'2009-03-01 23:00:00']. IIUC, a simple slicing?

How to slice columns in pandas Dataframe?

How to Slice Columns in pandas DataFrame 1 Quick Examples of Column-Slices of Pandas DataFrame#N#If you are in a hurry, below are some quick examples of how to... 2 Pandas DataFrame.iloc [] – Column Slices by Index or Position#N#By using pandas.DataFrame.iloc [] you can slice... 3 Complete Example To Take Column-Slices From DataFrame More ...

How to take column-slices from a Dataframe?

Like slices by column labels, you can also slice a DataFrame by a range of positions. To get the last column use df.iloc [:,-1:] and to get just first column df.iloc [:,:1] 4. Complete Example To Take Column-Slices From DataFrame

What is data frame in a data frame?

A data frame consists of data, which is arranged in rows and columns, and row and column labels. You can easily select, slice or take a subset of the data in several different ways, for example by using labels, by index location, by value and so on.


1 Answers

You can use loc to index your data. Do you know if your timestamps at datetime.datetime formats or Pandas Timestamps?

df30m.loc[(df30m.Timestamp <= d0) & (df30m.Timestamp >= d1)]

You can set the index to the Timestamp column and then index as follows:

df.set_index('Timestamp', inplace=True)
df[d1:d0]
like image 159
Alexander Avatar answered Sep 26 '22 14:09

Alexander