CODEWITHSUNDEEP
pythonpandasnumpy
Imran
Imran

Reputation: 656

Pandas crosstab with own function

I have a function which takes two inputs and returns a float e.g. my_func('A', 'B') = 0.5.

I have a list of possible inputs: x = ['A', 'B', 'C', 'D', 'E', 'F'].

I want to produce a square matrix (in this case 6 by 6) where the values of each cell is the result of the function with the corresponding row and column values as inputs. Note my_func('A', 'A') = 1 and my_func('A', 'B') = my_func('B', 'A')

I have tried pandas.crosstab(x, x, aggfunc = my_func) but this doesn't work.

Upvotes: 0

Views: 1098

Answers (2)

Max
Max

Reputation: 36

considering the brute force method, it is recommended to use the .loc method, i.e.

for i in x:
    for j in x:
        xt.loc[i,j] = my_func(i,j)

instead of:

for i in x:
    for j in x:
        xt.set_value(i, j, my_func(i, j))

because set_value is deprecated and will be removed in a future release. Also, .at[] or .iat[] acessors may be used.

Upvotes: 0

piRSquared
piRSquared

Reputation: 294318

Option 1
brute force

xt = pd.DataFrame(index=x, columns=x)

for i in x:
    for j in x:
        xt.set_value(i, j, my_func(i, j))

Demo 

def my_func(i, j):
    return ord(i) * ord(j)

x = ['A', 'B', 'C', 'D', 'E', 'F']

xt = pd.DataFrame(index=x, columns=x)

for i in x:
    for j in x:
        xt.set_value(i, j, my_func(i, j))

xt

      A     B     C     D     E     F
A  4225  4290  4355  4420  4485  4550
B  4290  4356  4422  4488  4554  4620
C  4355  4422  4489  4556  4623  4690
D  4420  4488  4556  4624  4692  4760
E  4485  4554  4623  4692  4761  4830
F  4550  4620  4690  4760  4830  4900

Option 2 

idx = pd.MultiIndex.from_product([x, x])
pd.Series(idx.map(lambda x: my_func(*x)), idx).unstack()

      A     B     C     D     E     F
A  4225  4290  4355  4420  4485  4550
B  4290  4356  4422  4488  4554  4620
C  4355  4422  4489  4556  4623  4690
D  4420  4488  4556  4624  4692  4760
E  4485  4554  4623  4692  4761  4830
F  4550  4620  4690  4760  4830  4900

Upvotes: 3

Related Questions