Get combination of rows to columns - Pandas

Juanito 0
Viewed 55

I need to calculate all possible coefficients inside each group in a dataset, if I have this dataframe:

ID Country_code  V1   V2
1  US            0.4  1
1  GB            0.6  2
1  AU            0.4  3
2  US            0.5  2
2  CL            0.4  2

I need this as an output:

ID Country_code  coefV1   coefV2
1  US-GB         0.66     0.5
1  US-AU         1        0.33
1  GB-AU         1.5      0.66
2  US-CL         1.25     1

I thought of expanding the dataframe first, something like:

ID Country_code  V1-1   V1-2   V2-1   V2-2
1  US-GB         0.4    0.6    1      2
1  US-AU         0.4    0.4    1      3
1  GB-AU         0.6    0.4    2      3
2  US-CL         0.5    0.4    2      2

But I couldn't do that either.

Any thoughts? Thanks!

pandas numpy python
2 Answers

You can try the following:

import pandas as pd

df = pd.DataFrame({
    'ID': [1, 1, 1, 2, 2],
    'Country': ['US', 'GB', 'AU', 'US', 'CL'],
    'V1': [0.4, 0.6, 0.4, 0.5, 0.4],
    'V2': [1, 2, 3, 2, 2]
})

def f(df):
    dfs=[]
    for c in ['V1', 'V2']:
        d = pd.DataFrame(df[c].values / df[c].values[:, None],
                         index=df['Country'],
                         columns=df['Country'])
        d.columns.name = 'Country2'
        d = d.unstack().reset_index()
        d = d[d['Country'] < d['Country2']]
        d['County Pair'] = d['Country2'] + "/" + d['Country']
        d = d[['County Pair', 0]]
        d = d.set_index('County Pair')
        d.columns = ['Q' + c]
        dfs.append(d)
    return pd.concat(dfs, axis=1)
    
print(df.groupby(by='ID').apply(f))

It gives:

                     QV1       QV2
ID County Pair                    
1  US/GB        0.666667  0.500000
   US/AU        1.000000  0.333333
   GB/AU        1.500000  0.666667
2  US/CL        1.250000  1.000000
bb10

Even though #bbis answer did the job (kudos! and thanks!), I finally did as follows:

def combinateRows(df):
    a, b = map(list, zip(*it.combinations(df.index, 2)))
    d = pd.concat([df.loc[a].reset_index(), df.loc[b].reset_index()],keys=['a', 'b'], axis=1)
    return d.set_index([('a', 'affiliate_id'), ('b', 'affiliate_id')]).rename_axis(['a', 'b'])

df = df.groupby('ID', as_index = False).apply(combinateRows)

df['coefV1'] = df['a V1'] / df['b V1']
df['coefV2'] = df['a V2'] / df['b V2']

Strongly influenced by Pandas: all possible combinations of rows

The upside of this approach is to avoid explicit loops.

Juanito0
Related