CODEWITHSUNDEEP
pythonpandas
Adil El Abbassi
Adil El Abbassi

Reputation: 11

pandas finding the indices of a common value between two columns in pandas

I have a dataframe looks like this: df1

Date and time   Price1  PrePrice
17.9.2018 9:47  1200.6  1204.8
17.9.2018 9:47  1200.6  1203.8
17.9.2018 9:47  1200.6  1202.1
17.9.2018 9:47  1200.6  1204.8
17.9.2018 9:47  1200.6  1204.8
17.9.2018 9:47  1200.6  1204.8
17.9.2018 9:47  1202.1  1204.8
17.9.2018 23:30 1200.7  1204.8
17.9.2018 23:31 1200.7  1204.8
17.9.2018 23:32 1200.6  1204.8
17.9.2018 23:33 1200.6  1204.8
17.9.2018 23:36 1200.7  1204.8
17.9.2018 23:47 1200.7  1204.8
17.9.2018 23:48 1200.6  1202.1
17.9.2018 23:50 1202.1  1200.9
17.9.2018 23:52 1203.8  1200.8
17.9.2018 23:55 1204.8  1200.7

I would like to get the common values between the two columns Price1,PrePrice like this :(1204.8; 17.9.2018 9:47; 17.9.2018 23:55) It tried this method but it's very slow:

c = [(i, j)  for i, x in enumerate(a) for j, y in enumerate(b) if x == y]

Upvotes: 1

Views: 703

Answers (2)

Adil El Abbassi
Adil El Abbassi

Reputation: 1

df = pd.read_csv("C:/Users/Adil/Desktop/test.csv",delimiter=';', decimal=',',parse_dates=["Date and time"],index_col="Date and time") finally the above code is working. but still far from the desired result :(1204.8; 17.9.2018 9:47; 17.9.2018 23:55)

Upvotes: 0

Josh Friedlander
Josh Friedlander

Reputation: 11657

If you want places where they are equal on the same row, this is vanilla Pandas:

df1[df1.Price1 == df1.PrePrice]

(There are none in your example.)

If you want all shared values you can use set notation:

c = set(df1.Price1).intersection(df1.PrePrice)
print(c)
> {1200.7, 1202.1, 1203.8, 1204.8}

Given those times, you can filter for Date and Time with Price1:

df1[df1.Price1.isin(c)][['Date and time', 'Price1']]

    Date and time   Price1
6   17.9.2018 9:47      1202.1
7   17.9.2018 23:30     1200.7
8   17.9.2018 23:31     1200.7
11  17.9.2018 23:36     1200.7
12  17.9.2018 23:47     1200.7
14  17.9.2018 23:50     1202.1
15  17.9.2018 23:52     1203.8
16  17.9.2018 23:55     1204.8

Upvotes: 2

Related Questions