Python: Accessing cell value in panda and performing mathematical operations

Question

I have an excel file with 4 rows and 4 columns with column labels as 1,2,3,4

               1      2        3       4
 Row   1      12      10       7       6
 Row   2      16      22       9      18
 Row   3      26      10      10       5
 Row   4      24       5      17       1

I wish to access row 3 col 1 integer value and row 4 col 1 integer value to check if adding both cells would equal 50 or not. Any (p,q,r) combo in this case should add (row q, col p) and (row r, col p) values and if the values = 50 then return (p,q,r) as the combo.

In this case, Output should be: (1,3,4).

import pandas as pd

import numpy as np

a = [1,2,3,4]

df = pd.read_excel('example.xlsx', sheetname='Sheet1')

combo = [(p,q,r) for p in a for q in a for r in a 
                  if (df.iloc[q,p] + df.iloc[r,p] = 50)]

print (combo)

Update:

Tried this but with a slight modification i.e. I am doing my calculations between columns of the table:

A combination (1,3,4) should now look for values in the following cells

(1,1) (1,3) (1,4)

(3,1) (3,3) (3,4)

(4,1) (4,3) (4,4)

import numpy as np

import pandas as pd

df = pd.read_excel('example.xlsx', sheetname='Sheet1')

df.index = list(range(1, df.shape[1] + 1))
df.columns = list(range(1, df.shape[1] + 1))

combo = [(p,q,r) for p in df.columns for q in df.columns for r in df.columns 
          if q > p and r > q and r > p
          and df.loc[p,p] - df.loc[p,q] == df.loc[q,p] - df.loc[q,q]]

I am getting the following error:

ValueError: Length mismatch: Expected axis has 3 elements, new values have 4 elements

I'm having a hard time understanding this question. Can you give a (subset of) your data and show the expected output? — rje

Peter Leimbigler Peter Leimbigler · Accepted Answer · 2018-10-18T21:49:50

First, make sure your DataFrame index and columns are both integer indexes, starting from 1 (to be consistent with your input data):

df.index = list(range(1, df.shape[0] + 1))
df.columns = list(range(1, df.shape[1] + 1))

Next, your list comprehension was on the right track:

[(p,q,r) for p in df.index for q in df.index for r in df.columns 
 if df.loc[q,p] + df.loc[r,p] == 50]

# Output
[(1, 3, 4), (1, 4, 3)]

Python: Accessing cell value in panda and performing mathematical operations

1 Answers