CODEWITHSUNDEEP
pythonpandas
user7133494
user7133494

Reputation:

Python pandas, find the largest pair in 2 dataframe

I have 2 dataframes,

df_1
            AAPL.NSDQ   KO.NYSE  BAC.NYSE   GS.NYSE  
AAPL.NSDQ   1.000000  0.90526 -0.659031 -0.722537
KO.NYSE     0.050526  1.000000  0.042064  0.146106 
BAC.NYSE   -0.659031  0.042064  1.000000  0.944912 
GS.NYSE    -0.722537  0.146106  0.944912  1.000000

df_2
            AAPL.NSDQ   KO.NYSE   BAC.NYSE   GS.NYSE 
AAPL.NSDQ   1.000000  3.116503   5.601350  0.557649  
KO.NYSE     0.320873  1.000000   1.797319  0.178934 
BAC.NYSE    0.178528  0.556384   1.000000  0.099556  
GS.NYSE     1.793243  5.588645  10.044580  1.000000

I want to retrieve a list of pairs, such that the pair's value in df_1 is greater than absolute(0.85) and their value in df_2 is greater than 3. Then print out this list of pairs.

For example, the result will be (AAPL.NSDQ,KO.NYSE), df_1=0.90526 and df_2=3.116503

Thanks

Upvotes: 2

Views: 56

Answers (2)

su79eu7k
su79eu7k

Reputation: 7316

df = pd.concat([df_1[df_1 > 0.85].stack().dropna(), df_2[df_2 > 3].stack().dropna()], axis=1).dropna()
df.columns=['df_1', 'df_2']

print df.index.tolist()

[('AAPL.NSDQ', 'KO.NYSE'), ('GS.NYSE', 'BAC.NYSE')]

print df

                        df_1       df_2
AAPL.NSDQ KO.NYSE   0.905260   3.116503
GS.NYSE   BAC.NYSE  0.944912  10.044580

Upvotes: 1

juanpa.arrivillaga
juanpa.arrivillaga

Reputation: 95948

You can use vectorized boolean operations:

In [10]: pairs = (np.abs(df1) > 0.85) & (df2 > 3)

In [11]: pairs
Out[11]:
          AAPL.NSDQ KO.NYSE BAC.NYSE GS.NYSE
AAPL.NSDQ     False    True    False   False
KO.NYSE       False   False    False   False
BAC.NYSE      False   False    False   False
GS.NYSE       False   False     True   False

Then, with a little help from numpy.where:

In [14]: np.where(pairs.values)
Out[14]: (array([0, 3]), array([1, 2]))

Finally, a simple list-comprehension:

In [16]: [(pairs.index[i], pairs.columns[j]) for i,j in  zip(*np.where(pairs.values))]
Out[16]: [('AAPL.NSDQ', 'KO.NYSE'), ('GS.NYSE', 'BAC.NYSE')]

If you want the values too:

In [20]: [(pairs.index[i], pairs.columns[j], df1.iloc[i,j], df2.iloc[i,j]) for i,j in  zip(*np.where(pairs.values))]
Out[20]:
[('AAPL.NSDQ', 'KO.NYSE', 0.90526000000000006, 3.1165029999999998),
 ('GS.NYSE', 'BAC.NYSE', 0.94491200000000009, 10.04458)]

Or perhaps it would be more neat to define a helper function:

In [21]: def data_tuple(i, j): return (pairs.index[i], pairs.columns[j], df1.iloc[i,j], df2.iloc[i,j])

In [22]: [data_tuple(i,j) for i,j in  zip(*np.where(pairs.values))]
Out[22]:
[('AAPL.NSDQ', 'KO.NYSE', 0.90526000000000006, 3.1165029999999998),
 ('GS.NYSE', 'BAC.NYSE', 0.94491200000000009, 10.04458)]

Upvotes: 0

Related Questions