How to make item item matrix with counting values in cells

  • A+
Category:Languages

I have a dataframe like this

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

I want to get

  a b c a 2 1 0 b 1 2 1 c 0 1 2 

where a,b,c are column names, and I get the values counting '1' in all columns when the filter is '1' in another column. For ample, when df.a == 1, we count a = 2, b =1, c = 0 etc

I made a loop to solve

matrix = [] for name, values in df.iteritems():     matrix.append(pd.DataFrame( df.groupby(name, as_index=False).apply(lambda x: x[x == 1].count())).values.tolist()[1]) pd.DataFrame(matrix) 

But I think that there is a simpler solution, isn't it?


You appear to want the matrix product, so leverage DataFrame.dot:

df.T.dot(df)    a  b  c a  2  1  0 b  1  2  1 c  0  1  2 

Alternatively, if you want the same level of performance without the overhead of pandas, you could compute the product with np.dot:

v = df.values pd.DataFrame(v.T.dot(v), index=df.columns, columns=df.columns) 

Or, if you want to get cute,

(lambda a, c: pd.DataFrame(a.T.dot(a), c, c))(df.values, df.columns) 

   a  b  c a  2  1  0 b  1  2  1 c  0  1  2 

—piRSquared

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: