Menu
I have a simple question I cant seem to find a strait answer for.
Suppose I have a data frame with date, open, high, low, close and volume.
What I am trying to do is first find the current date which I can do with:
today = pd.datetime.today().date()
My issue comes in selecting the last 20 days of data from the current date.
I need to select the last 20 rows because I need to then find the highest and lowest values in the close colum of this dataset.
Any pointers would help a lot. Iv searched google for a while and keep finding different answers.
Thanks!
816
Method 1: Using tail() method Use pandas. DataFrame. tail(n) to get the last n rows of the DataFrame. It takes one optional argument n (number of rows you want to get from the end).
Drop Last Row of Pandas DataFrame Using iloc[] iloc[] you can drop the rows from DataFrame and use -1 to drop the last row. For example use df. iloc[:-1,:] to select all rows except the last one and then assign it back to the original variable which ideally drops the last row from DataFrame.
Approach: Use the pandas. DataFrame. tail(n) to select the last n rows of the given DataFrame.
Setup
today = pd.datetime.today().date()
df = pd.DataFrame(
np.random.rand(20, 4),
pd.date_range(end=today, periods=20, freq='3D'),
columns=['O', 'H', 'L', 'C'])
df
O H L C
2017-08-01 0.821996 0.894122 0.829814 0.429701
2017-08-04 0.883512 0.668642 0.524440 0.914845
2017-08-07 0.035753 0.231787 0.421547 0.163865
2017-08-10 0.742781 0.293591 0.874033 0.054421
2017-08-13 0.252422 0.632991 0.547044 0.650622
2017-08-16 0.316752 0.190016 0.504701 0.827450
2017-08-19 0.777069 0.533121 0.329742 0.603473
2017-08-22 0.843260 0.546845 0.600270 0.060620
2017-08-25 0.834180 0.395653 0.189499 0.820043
2017-08-28 0.806369 0.850968 0.753335 0.902687
2017-08-31 0.336096 0.145325 0.876519 0.114923
2017-09-03 0.590195 0.946520 0.009151 0.832992
2017-09-06 0.901101 0.616852 0.375829 0.332625
2017-09-09 0.537892 0.852527 0.082807 0.966297
2017-09-12 0.104929 0.803415 0.345942 0.245934
2017-09-15 0.085703 0.743497 0.256762 0.530267
2017-09-18 0.823960 0.397983 0.173706 0.091678
2017-09-21 0.211412 0.980942 0.833802 0.763510
2017-09-24 0.312950 0.850760 0.913519 0.846466
2017-09-27 0.921168 0.568595 0.460656 0.016313
Solution
Use pandas datetime index slicing. Very simple and straightforward and what pandas devs intended for this problem.
Note: This does not care how many rows are within the last 20 days, it just grabs all of them. This is what I think the OP wanted.
df[today - pd.offsets.Day(20):]
O H L C
2017-09-09 0.537892 0.852527 0.082807 0.966297
2017-09-12 0.104929 0.803415 0.345942 0.245934
2017-09-15 0.085703 0.743497 0.256762 0.530267
2017-09-18 0.823960 0.397983 0.173706 0.091678
2017-09-21 0.211412 0.980942 0.833802 0.763510
2017-09-24 0.312950 0.850760 0.913519 0.846466
2017-09-27 0.921168 0.568595 0.460656 0.016313
If you simply want the last 20 lines from a DataFrame you can just use df[-20:]. Instead, if you want to have the date 20 days ago you must make pd.Timedelta(-19, unit='d') + pd.datetime.today().date().
In [1]: index = pd.date_range(start=(pd.Timedelta(-30, unit='d')+pd.datetime.today().date()), periods=31)
In [2]: df = pd.DataFrame(np.random.rand(31, 4), index=index, columns=['O', 'H', 'L', 'C'])
In [3]: df = df.reset_index().rename(columns={'index': 'Date'})
In [4]: df
Out[4]:
Date O H L C
0 2017-08-28 0.616856 0.518961 0.378005 0.716371
1 2017-08-29 0.300977 0.652217 0.713013 0.842369
2 2017-08-30 0.875668 0.232998 0.566047 0.969647
3 2017-08-31 0.273934 0.086575 0.386617 0.390749
4 2017-09-01 0.667561 0.336419 0.648809 0.619215
5 2017-09-02 0.988234 0.563675 0.402908 0.671333
6 2017-09-03 0.111710 0.549302 0.321546 0.201828
7 2017-09-04 0.469041 0.736152 0.345069 0.336593
8 2017-09-05 0.674844 0.276839 0.350289 0.862777
9 2017-09-06 0.128124 0.968918 0.713846 0.415061
10 2017-09-07 0.920488 0.252980 0.573531 0.270999
11 2017-09-08 0.113368 0.781649 0.190273 0.758834
12 2017-09-09 0.414453 0.545572 0.761805 0.586717
13 2017-09-10 0.348459 0.830177 0.779591 0.783887
14 2017-09-11 0.571877 0.230465 0.262744 0.360188
15 2017-09-12 0.844286 0.821388 0.312319 0.473672
16 2017-09-13 0.605548 0.570590 0.457141 0.882498
17 2017-09-14 0.242154 0.066617 0.028913 0.969698
18 2017-09-15 0.725521 0.742362 0.904866 0.890942
19 2017-09-16 0.460858 0.749581 0.429131 0.723394
20 2017-09-17 0.767445 0.452113 0.906294 0.978368
21 2017-09-18 0.342970 0.702579 0.029031 0.743489
22 2017-09-19 0.221478 0.339948 0.403478 0.349097
23 2017-09-20 0.147785 0.633542 0.692545 0.194496
24 2017-09-21 0.656189 0.419257 0.099094 0.708530
25 2017-09-22 0.329901 0.087101 0.683207 0.558431
26 2017-09-23 0.902550 0.155262 0.304506 0.756210
27 2017-09-24 0.072132 0.045242 0.058175 0.755649
28 2017-09-25 0.149873 0.340870 0.198454 0.725051
29 2017-09-26 0.972721 0.505842 0.886602 0.231916
30 2017-09-27 0.511109 0.990975 0.330336 0.898291
In [5]: df[-20:]
Out[5]:
Date O H L C
11 2017-09-08 0.113368 0.781649 0.190273 0.758834
12 2017-09-09 0.414453 0.545572 0.761805 0.586717
13 2017-09-10 0.348459 0.830177 0.779591 0.783887
14 2017-09-11 0.571877 0.230465 0.262744 0.360188
15 2017-09-12 0.844286 0.821388 0.312319 0.473672
16 2017-09-13 0.605548 0.570590 0.457141 0.882498
17 2017-09-14 0.242154 0.066617 0.028913 0.969698
18 2017-09-15 0.725521 0.742362 0.904866 0.890942
19 2017-09-16 0.460858 0.749581 0.429131 0.723394
20 2017-09-17 0.767445 0.452113 0.906294 0.978368
21 2017-09-18 0.342970 0.702579 0.029031 0.743489
22 2017-09-19 0.221478 0.339948 0.403478 0.349097
23 2017-09-20 0.147785 0.633542 0.692545 0.194496
24 2017-09-21 0.656189 0.419257 0.099094 0.708530
25 2017-09-22 0.329901 0.087101 0.683207 0.558431
26 2017-09-23 0.902550 0.155262 0.304506 0.756210
27 2017-09-24 0.072132 0.045242 0.058175 0.755649
28 2017-09-25 0.149873 0.340870 0.198454 0.725051
29 2017-09-26 0.972721 0.505842 0.886602 0.231916
30 2017-09-27 0.511109 0.990975 0.330336 0.898291
In [6]: df[df.Date.isin(pd.date_range(pd.Timedelta(-19, unit='d')+pd.datetime.today().date(), periods=20))]
Out[6]:
Date O H L C
11 2017-09-08 0.113368 0.781649 0.190273 0.758834
12 2017-09-09 0.414453 0.545572 0.761805 0.586717
13 2017-09-10 0.348459 0.830177 0.779591 0.783887
14 2017-09-11 0.571877 0.230465 0.262744 0.360188
15 2017-09-12 0.844286 0.821388 0.312319 0.473672
16 2017-09-13 0.605548 0.570590 0.457141 0.882498
17 2017-09-14 0.242154 0.066617 0.028913 0.969698
18 2017-09-15 0.725521 0.742362 0.904866 0.890942
19 2017-09-16 0.460858 0.749581 0.429131 0.723394
20 2017-09-17 0.767445 0.452113 0.906294 0.978368
21 2017-09-18 0.342970 0.702579 0.029031 0.743489
22 2017-09-19 0.221478 0.339948 0.403478 0.349097
23 2017-09-20 0.147785 0.633542 0.692545 0.194496
24 2017-09-21 0.656189 0.419257 0.099094 0.708530
25 2017-09-22 0.329901 0.087101 0.683207 0.558431
26 2017-09-23 0.902550 0.155262 0.304506 0.756210
27 2017-09-24 0.072132 0.045242 0.058175 0.755649
28 2017-09-25 0.149873 0.340870 0.198454 0.725051
29 2017-09-26 0.972721 0.505842 0.886602 0.231916
30 2017-09-27 0.511109 0.990975 0.330336 0.898291
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
