CODEWITHSUNDEEP
pythonpandasdataframe
MadHatter
MadHatter

Reputation: 680

Getting certain elements from a Pandas Dataframe

I have a square 2-dimensional pandas dataframe, and I'd like to extract the elements which satisfy both the following conditions:

I'd like to print: value extracted, column label, row label.

Upvotes: 1

Views: 55

Answers (2)

jezrael
jezrael

Reputation: 863056

First replace values below 0 with diagonal to NaN and then reshape by stack:

np.random.seed(100)
df = pd.DataFrame(np.random.randint(10, size=(5,5)), columns=list('ABCDE')) - 3
print (df)
   A  B  C  D  E
0  5  5  0  4  4
1 -3  1 -1  2 -1
2 -1 -1 -2 -3  5
3  1 -3  6  3 -1
4  1 -2  2  0  1

df = df.where(df > 0)
np.fill_diagonal(df.values, np.nan)

df = df.stack().reset_index()
df.columns=['idx','col','val']
print (df)
   idx col  val
0    0   B  5.0
1    0   D  4.0
2    0   E  4.0
3    1   D  2.0
4    2   E  5.0
5    3   A  1.0
6    3   C  6.0
7    4   A  1.0
8    4   C  2.0

Explanation:

stack create MultiIndex Series:

print (df.stack())
0  B    5.0
   D    4.0
   E    4.0
1  D    2.0
2  E    5.0
3  A    1.0
   C    6.0
4  A    1.0
   C    2.0
dtype: float64

And then reset_index() create columns from MultiIndex:

print (df.stack().reset_index())

   level_0 level_1    0
0        0       B  5.0
1        0       D  4.0
2        0       E  4.0
3        1       D  2.0
4        2       E  5.0
5        3       A  1.0
6        3       C  6.0
7        4       A  1.0
8        4       C  2.0

Upvotes: 2

jpp
jpp

Reputation: 164743

You can do this with NumPy by replacing unwanted numbers with NaN:

import numpy as np

df = pd.DataFrame(np.random.randint(-5, 6, (5, 5)))
arr = df.values.astype(float)

np.fill_diagonal(arr, np.nan)  # exclude diagonal
arr[arr <= 0] = np.nan         # filter for > 0

print(arr)

[[nan  2.  4. nan nan]
 [nan nan nan nan  3.]
 [nan nan nan nan nan]
 [ 2. nan  3. nan  4.]
 [nan  4.  1. nan nan]]

nan_filter = ~np.isnan(arr)

# aggregate indices with values
res = np.hstack((np.argwhere(nan_filter), arr[nan_filter][:, None]))

print(res)

[[0. 1. 2.]
 [0. 2. 4.]
 [1. 4. 3.]
 [3. 0. 2.]
 [3. 2. 3.]
 [3. 4. 4.]
 [4. 1. 4.]
 [4. 2. 1.]]

Upvotes: 2

Related Questions