Creating new matrix from dataframe and matrix in pandas
Question: I have a dataframe `df` which looks like this:

    
    
        id1  id2  weights
    0   a    2a   144.0
    1   a    2b   52.5
    2   a    2c   2.0
    3   a    2d   1.0
    4   a    2e   1.0
    5   b    2a   2.0
    6   b    2e   1.0
    7   b    2f   1.0
    8   b    2b   1.0
    9   b    2c   0.008
    

And a similarity matrix `mat` between the elements of the `id2` column:

    
    
        2a    2b   2c   2d   2e   2f
    2a  1     0.5  0.7  0.2  0.1  0.3
    2b  0.5   1    0.6  0.4  0.3  0.4
    2c  0.7   0.6  1    0.1  0.4  0.2
    2d  0.2   0.4  0.1  1    0.8  0.7
    2e  0.1   0.3  0.4  0.8  1    0.8
    2f  0.3   0.4  0.2  0.7  0.8  1
    

Now I would like to create a similarity matrix between the elements of `id1`
and the elements from `id2`. For that I consider the elements of `id1` as
barycentres of the corresponding elements of `id2` ind my dataframe `df` (with
the corresponding `weights`).

My first attempt to do that is with loops (aouch):

    
    
    ids = df.id1.unique()
    output = pd.DataFrame(columns = mat.columns,index = ids)
    for id in ids:
        df_slice = df.loc[df.id1 == id]
        to_normalize = df_slice.weights.sum()
        temp = mat.loc[df_slice.id2]
        for art in df_slice.id2:
            temp.loc[art] *= df_slice.ix[df_slice.id2 == art,'weights'].values[0]
            temp.loc[art] /= (1.*to_normalize)
        output.loc[id] = temp.sum()
    

But of course this is way not pythonic, and takes ages (`timeit` for these
small matrix showed `21.3ms` not computable for a 10k-rows `df` and 3k by 3k
`mat`). What would be a more clean/efficient way to do it?

Desired output:

    
    
        2a          2b          2c          2d          2e          2f
    a   0.857606    0.630424    0.672319    0.258354    0.163342    0.329676
    b   0.580192    0.540096    0.520767    0.459425    0.459904    0.559425
    

And is there a way to compute another similarity matrix between the elements
of `id1` (from this data)?

Thank you in advance.


Answer: The following clocks in at 6-7ms (vs. around 30ms that your approach takes on
my machine).

    
    
    import io
    
    import pandas as pd
    
    
    raw_df = io.StringIO("""\
      id1  id2  weights
    0   a    2a   144.0
    1   a    2b   52.5
    2   a    2c   2.0
    3   a    2d   1.0
    4   a    2e   1.0
    5   b    2a   2.0
    6   b    2e   1.0
    7   b    2f   1.0
    8   b    2b   1.0
    9   b    2c   0.008
    """)
    df = pd.read_csv(raw_df, delim_whitespace=True)
    
    raw_mat = io.StringIO("""\
        2a    2b   2c   2d   2e   2f
    2a  1     0.5  0.7  0.2  0.1  0.3
    2b  0.5   1    0.6  0.4  0.3  0.4
    2c  0.7   0.6  1    0.1  0.4  0.2
    2d  0.2   0.4  0.1  1    0.8  0.7
    2e  0.1   0.3  0.4  0.8  1    0.8
    2f  0.3   0.4  0.2  0.7  0.8  1
    """)
    mat = pd.read_csv(raw_mat, delim_whitespace=True)
    
    
    df['norm'] = df.groupby('id1')['weights'].transform('sum')
    
    m = pd.merge(df, mat, left_on='id2', right_index=True)
    m[mat.index] = m[mat.index].multiply(m['weights'] / m['norm'], axis=0)
    
    output = m.groupby('id1')[mat.index].sum()
    output.columns.name = 'id2'
    print(output)    
    

Output:

    
    
    id2        2a        2b        2c        2d        2e        2f
    id1                                                            
    a    0.857606  0.630424  0.672319  0.258354  0.163342  0.329676
    b    0.580192  0.540096  0.520767  0.459425  0.459904  0.559425
    

