CODEWITHSUNDEEP
pythonpandasdataframe
61938388
61938388

Reputation: 73

pandas how to filter out the next row of the row corresponding to the specified column value

I have two dataframes like this：

import numpy as np
import pandas as pd

dataA = [["2005-1-20", 5], ["2005-1-26", 8], ["2005-1-29", 7],
    ["2005-2-11",4], ["2005-2-18", 2],
    ["2005-2-21", 2], ["2005-3-11",3], ["2005-3-25", 4],
    ["2005-6-1",4], ["2005-6-15",5],
    ["2005-9-2", 12], ["2005-9-13",13], ["2005-10-11",14],
    ["2005-11-2",14], ["2005-11-17",15]]

dataB = [["2005-1-26", 5], ["2005-3-11",2]]

dfa= pd.DataFrame(data = dataA, columns=["date", "Vala"])

dfb= pd.DataFrame(data = dataB, columns=["date", "Valb"])

dfa                    dfb                                 dfc

       date   Vala     date    Valb                      date      Vala
0   2005-1-20   5  
1   2005-1-26   8 --- 2005-1-26  5 
2   2005-1-29   7 ----------------------------> i want   2005-1-29  7
3   2005-2-11   4
4   2005-2-18   2
5   2005-2-21   2
6   2005-3-11   3 --- 2005-3-11  2
7   2005-3-25   4 ----------------------------> i want  2005-3-25   4
8   2005-6-1    4
9   2005-6-15   5
10  2005-9-2    12
11  2005-9-13   13
12  2005-10-11  14
13  2005-11-2   14
14  2005-11-17  15

base on two datafram dfa and dfb, i want to get dfc from dfa, dfc is composed of the next row of the row corresponding to the date of dfb and dfa ， how to do it?

Upvotes: 0

Views: 159

Answers (1)

Pygirl
Pygirl

Reputation: 13349

1st convert the dates into datetime. Try to get the index where the date is in the list of date i.e dfb.date and in order to get the next row just shift it by 1 and fill na with False.

try:

dfa.date=pd.to_datetime(dfa.date)
dfb.date=pd.to_datetime(dfb.date)
dfc = dfa[dfa.date.isin(dfb.date).shift(1).fillna(False)]

dfc:

    date    Vala
2   2005-01-29  7
7   2005-03-25  4

Upvotes: 1

Related Questions