3

I am trying to divide all columns by each column but only once (A/B but not B/A)

From Dividing each column by every other column and creating a new dataframe from the results

and thanks to @COLDSPEED, the following code performs the division of all columns by every column (and adds the corresponding new columns).

I cannot figure out how to avoid the pair duplication.

import pandas as pd
import numpy as np
np.random.seed(42)


df = pd.DataFrame(np.random.randint(0,9,size=(5, 3)), columns=list('ABC'))

ratio_df = pd.concat([df[df.columns.difference([col])].div(df[col], axis=0) \
                   for col in df.columns], axis=1)

print ratio_df

Which outputs:

Original dataframe 

   A  B  C
0  6  3  7
1  4  6  2
2  6  7  4
3  3  7  7
4  2  5  4

Resulting dataframe

          B         C         A         C         A         B
0  0.500000  1.166667  2.000000  2.333333  0.857143  0.428571
1  1.500000  0.500000  0.666667  0.333333  2.000000  3.000000
2  1.166667  0.666667  0.857143  0.571429  1.500000  1.750000
3  2.333333  2.333333  0.428571  1.000000  0.428571  1.000000
4  2.500000  2.000000  0.400000  0.800000  0.500000  1.250000

In row 0, the value for the first column B is B/A or 3/6 = 0.5 and the first column A is A/B or 6/3 = 2

I would like to keep only one result for the pair operation (eg only left column / right column).

        A/B       A/C       B/C
0  2.000000  0.857143  0.428571
1  0.666667  2.000000  3.000000
2  0.857143  1.500000  1.750000
3  0.428571  0.428571  1.000000
4  0.400000  0.500000  1.250000

I was not able to find clues on this matter.

How could I resolve it?

Thanks!

Divakar
  • 218,885
  • 19
  • 262
  • 358
Diego
  • 637
  • 3
  • 10
  • 24

1 Answers1

3

Here's one approach -

idx0,idx1 = np.triu_indices(df.shape[1],1)
df_out = pd.DataFrame(df.iloc[:,idx0].values/df.iloc[:,idx1])
c = df.columns.values
df_out.columns = c[idx0]+'/'+c[idx1]

Sample run -

In [58]: df
Out[58]: 
   A  B  C
0  6  3  7
1  4  6  2
2  6  7  4
3  3  7  7
4  2  5  4

In [59]: df_out
Out[59]: 
        A/B       A/C       B/C
0  2.000000  0.857143  0.428571
1  0.666667  2.000000  3.000000
2  0.857143  1.500000  1.750000
3  0.428571  0.428571  1.000000
4  0.400000  0.500000  1.250000

Alternative way to get idx0 and idx1 -

from itertools import combinations

idx0,idx1 = np.array(list(combinations(range(df.shape[1]),2))).T
Divakar
  • 218,885
  • 19
  • 262
  • 358
  • For some reason my results are different row 0 A/B = 2, A/C = 0 and B/C = 0. Do you think it is a type issue (float)? Thanks – Diego Sep 08 '17 at 17:20
  • This worked df_out = pd.DataFrame(df.iloc[:,idx0].values/df.iloc[:,idx1].astype(float)) – Diego Sep 08 '17 at 17:23
  • @Diego It's because the inputs are all ints. Use : `from __future__ import division` at the top of the script and then use the proposed solution. – Divakar Sep 08 '17 at 17:28