CODEWITHSUNDEEP
pythonpandasnumpy
user308827
user308827

Reputation: 21961

Getting non-diagonal elements in dataframe

Following pandas DataFrame diagonal, I can get diagonal elements using np.diag. How can I get the non-diagonal elements in a dataframe (assuming that dataframe is size n x n)

Upvotes: 7

Views: 3953

Answers (3)

Matt
Matt

Reputation: 2329

EDITED answer to equal to SomeGuy response as the initial answer was wrong when diagonal elements were equal to 0.

xf = pd.DataFrame(np.random.rand(5,5))
xf.mask(np.eye(5, dtype = bool))

INCORRECT INITIAL ANSWER WHEN DIAGONAL ELEMENTS ARE EQUAL TO 0

First construct mask of True and False values and then apply on your square matrix / dataframe.

xf = pd.DataFrame(np.random.rand(5,5))
diag = np.diag(np.diag(xf))
xf.mask(diag != 0)

Upvotes: 0

piRSquared
piRSquared

Reputation: 294228

I'll use @Matt's same dataframe xf

xf = pd.DataFrame(np.random.rand(5, 5))

However, I'll point out that if the diagonal happens to be equal to zero, Using np.diag(np.diag(xf)) != 0 will break down.

The way to guarantee that you are masking the diagonal is to evaluate if the row indices are not equal to the column indices.

Option 1
numpy.indices

Conveniently, numpy provides those as well via the np.indices function.

Observe what they look like

rows, cols = np.indices((5, 5))

print(rows)

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

print(cols)

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

And where they are equal... The diagonal.

print((cols == rows).astype(int))

[[1 0 0 0 0]
 [0 1 0 0 0]
 [0 0 1 0 0]
 [0 0 0 1 0]
 [0 0 0 0 1]]

So with these, we can mask where they are equal with

xf.mask(np.equal(*np.indices(xf.shape)))

          0         1         2         3         4
0       NaN  0.605436  0.573386  0.978588  0.160986
1  0.295911       NaN  0.509203  0.692233  0.717464
2  0.275767  0.966976       NaN  0.883339  0.143704
3  0.628941  0.668836  0.468928       NaN  0.309901
4  0.286933  0.523243  0.693754  0.253426       NaN

We can make is a bit faster with

pd.DataFrame(
    np.where(np.equal(*np.indices(xf.shape)), np.nan, xf.values),
    xf.index, xf.columns
)

Option 2
numpy.arange with slice assignment 

v = xf.values.copy()
i = j = np.arange(np.min(v.shape))
v[i, j] = np.nan
pd.DataFrame(v, xf.index, xf.columns)

          0         1         2         3         4
0       NaN  0.605436  0.573386  0.978588  0.160986
1  0.295911       NaN  0.509203  0.692233  0.717464
2  0.275767  0.966976       NaN  0.883339  0.143704
3  0.628941  0.668836  0.468928       NaN  0.309901
4  0.286933  0.523243  0.693754  0.253426       NaN

%%timeit 
v = xf.values.copy()
i = j = np.arange(np.min(v.shape))
v[i, j] = np.nan
pd.DataFrame(v, xf.index, xf.columns)


%timeit pd.DataFrame(np.where(np.eye(np.min(xf.shape)), np.nan, xf.values), xf.index, xf.columns)
%timeit pd.DataFrame(np.where(np.equal(*np.indices(xf.shape)), np.nan, xf.values), xf.index, xf.columns)
%timeit xf.mask(np.equal(*np.indices(xf.shape)))
%timeit xf.mask(np.diag(np.diag(xf.values)) != 0)
%timeit xf.mask(np.eye(np.min(xf.shape), dtype=bool)

10000 loops, best of 3: 74.5 µs per loop
10000 loops, best of 3: 85.7 µs per loop
10000 loops, best of 3: 77 µs per loop
1000 loops, best of 3: 519 µs per loop
1000 loops, best of 3: 517 µs per loop
1000 loops, best of 3: 528 µs per loop

Upvotes: 7

Some Guy
Some Guy

Reputation: 1797

Use a mask generated with np.eye like:

xf = pd.DataFrame(np.random.rand(5,5))
xf.mask(np.eye(5, dtype = bool))

Upvotes: 11

Related Questions