andbeonetraveler andbeonetraveler - 5 months ago 27
Python Question

Pandas applymap on multilevel dataframe

I have a square matrix as a dataframe in pandas. It should be symmetric, and nearly is, except for a few missing values that I filled with 0. I want to use the fact that it should be symmetric to fill the missing values, by taking the max of the absolute value over df.ix[x,y] and df.ix[y,x]. I.e.:

df = pd.DataFrame({'b': {'b': 1, 'a': 0,'c':-1}, 'a': {'b': 1, 'a': 1,'c':0},'c':{'c':1,'a':0,'b':0}})

>>> df
a b c
a 1 0 1
b 1 1 0
c 1 -1 1


should become:

>>> df
a b c
a 1 1 1
b 1 1 -1
c 1 -1 1


At first I thought of using a simple applymap with a function something like:

def maxSymmetric(element):
if abs(element) > df.T.ix[element.column,element.row]:
return element
else return df.T.ix[element.column,element.row]


But there doesn't seem to be a way to call the indices of an element within a function inside applymap (see related).

So then I tried making a multilevel dataframe of the original matrix and its transpose:

pd.concat([df,df.T],axis=0,keys=['o','t'])
a b c
o a 1 0 1
b 1 1 0
c 1 -1 1
t a 1 1 1
b 0 1 -1
c 1 0 1


Now I want to extract the correct (nonzero, if available) element from either 'o' or 't', for each element, using a similar function as above. But I'm not very experienced with multiindexing, and I can't figure out how to use applymap here, or if I should be using something else.

Suggestions?

Answer

I think you can first convert df to numpy array, use numpy solution and last create DataFrame with constructor:

a = df.values
print (pd.DataFrame(data=a + a.T - np.diag(a.diagonal()), 
                    columns=df.columns,
                    index=df.index))

   a  b  c
a  1  1  2
b  1  1 -1
c  2 -1  1

EDIT by comment:

print (df + df.T - df[df==df.T].fillna(0))
     a    b    c
a  1.0  1.0  1.0
b  1.0  1.0 -1.0
c  1.0 -1.0  1.0
Comments