I have a list of animals with their counts:
import numpy as np
import pandas as pd
from random import randint
table = np.zeros((5,1), dtype=int)
for i in range(5):
table[i]=randint(10, 20)
df1 = pd.DataFrame(columns=['Animal', 'Count'])
df1['Animal'] = animal_list
df1['Count'] = table
df1
And I have a matrix of how many times they appear together:
table = np.zeros((5,5), dtype=int)
animal_list = ['Monkey', 'Tiger', 'Cat', 'Dog', 'Lion']
for i in range(5):
for j in range(5):
table[i][j]=randint(0, 9)
df2 = pd.DataFrame(table, columns=animal_list, index=animal_list)
df2
I want to find the animals' association strength, which is defined like so - if Lion
and Cat
appear together 5 times, and Lion
's count is 10 and Cat
's count is 15, then Lion -> Cat
association strength is 5/10=0.5
, and Cat -> Lion
association strength is 5/15=0.33
.
I do it like so:
assoc_df = pd.DataFrame(columns=['Animal 1', 'Animal 2', 'Association Strength'])
for row_word in df2:
for col_word in df2:
if row_word!=col_word:
assoc_df = assoc_df.append({'Animal 1': row_word, 'Animal 2': col_word,
'Association Strength': df2[col_word][row_word]/df1[df1.Animal==row_word]['Count'].values[0]}, ignore_index=True)
assoc_df
The problem is, if I have a large number (say, 1000) of animals to loop on, it takes hours to finish computing the association strength table.
So, how to do I better optimize this last code block, the creation/generation process of this association table?
P.S.: In most practical use cases, df2
is a symmetric matrix, as "X
appears together with Y
" generally also means the same as "Y
appears together with X
". So, I am ok with solutions that assume that df2
is symmetric, and cut down the running time by half. In the above example, df2
is not a symmetric matrix, which is the more general case, and applicable for situations where we want to express meanings such as "X
appears after Y
" and "Y
appears after X
", which may not be the same.
if row_word!=col_word
). But the first 2 blocks don't cause much problem - they finish executing in the order of minutes, while the last block (calculating the association strengths) takes hours to finish. So, the last block is what I really want optimized. \$\endgroup\$