CODEWITHSUNDEEP
pythonpandas
nipy
nipy

Reputation: 5488

Next day or next row index in Pandas Data frame

I am trying to find a way to get the next day (next row in this case) in a Pandas dataframe. I thought this would be easy to find but Im struggling.

Starting Data:

    ts = pd.DataFrame(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))
ts.columns = ['Val']
ts['Week'] = ts.index.week
ts

             Val       Week
2000-01-01  -0.639345   52
2000-01-02  1.294537    52
2000-01-03  1.181486    1
2000-01-04  -0.011694   1
2000-01-05  -0.224887   1
2000-01-06  -0.493120   1
2000-01-07  1.439436    1
2000-01-08  1.017722    1
2000-01-09  1.125153    1
2000-01-10  0.209741    2

Subset of the data:

tsSig = ts[ts.Val>1.5].drop_duplicates(subset='Week')

tsSig.head()


              Val   Week
2000-01-24  2.215559    4
2000-02-09  1.561941    6
2000-02-24  1.645916    8
2000-03-16  1.745079    11
2000-04-10  1.570023    15

I now want to use the index from my tsSig subset to find the next day in tsand then create a new column ts['Val_Dayplus1'] showing values from the 25th(-0.309811), 10th(-1.644814) etc.

I am trying things like ts.loc[tsSig.index].shift(1) to get next day but this is obviously not correct....

Desired output:

                 Val  Val_Dayplus1  Week
2000-01-24  2.215559  -0.309811      4
2000-02-09  1.561941  -1.644814      6
2000-02-24  1.645916   -0.187440     8

(for all rows in tsSig.index)

Edit:

This appears to give me what I need in terms of shifting the date index on tsSig.index. I would like to hear of any other ways to do this as well.

ts.loc[tsSig.index + pd.DateOffset(days=1)]
 tsSig['Val_Dayplus1'] = ts['Val'].ix[tsSig.index + pd.DateOffset(days=1)].values

Upvotes: 0

Views: 4694

Answers (2)

nipy
nipy

Reputation: 5488

I managed to work this one out so sharing the answer:

    ts.loc[tsSig.index + pd.DateOffset(days=1)]
    tsSig['Val_Dayplus1'] = ts['Val'].ix[tsSig.index + pd.DateOffset(days=1)].values

tsSig

                Val Week  Val_Dayplus1
2000-02-15  1.551125    7   -0.102154
2000-02-24  1.525402    8   -0.009776
2000-03-11  1.801845    10  0.832837
2000-03-22  1.546953    12  0.377510
2000-04-17  1.568720    16  -0.258558
2000-06-04  1.646147    22  0.853044

Upvotes: 3

Michael Silverstein
Michael Silverstein

Reputation: 1843

I'm not sure if I completely understand your question, but in general, you can index through a pandas data frame by using df.iloc[ROWS,COLS]. So, in your case, for an index i in a for loop, you could index ts.iloc[i+1,:], to get all of the info from the next row in the ts dataframe.

Upvotes: 0

Related Questions