All possible combinations of columns in dataframe -pandas/python

I'm trying to take one dataframe and create another, with all possible combinations of the columns and the difference between the corresponding values, i.e on 11-apr column AB should be (B-A)= 0 etc.

e.g, starting with

        Dt              A           B           C          D
        11-apr          1           1           1          1
        10-apr          2           3           1          2

how do I get a new frame that looks like this:

desired result

I have come across the below post, but have not been able to transpose this to work for columns.

Aggregate all dataframe row pair combinations using pandas


You can use:

from itertools import combinations
df = df.set_index('Dt')

cc = list(combinations(df.columns,2))
df = pd.concat([df[c[1]].sub(df[c[0]]) for c in cc], axis=1, keys=cc)
df.columns = df.columns.map(''.join)
print (df)
        AB  AC  AD  BC  BD  CD
Dt                            
11-apr   0   0   0   0   0   0
10-apr   1  -1   0  -2  -1   1

Make sure your index is Dt

df = df.set_index('Dt')

Using numpys np.tril_indices and slicing See below for explanation of np.triu_indices

v = df.values

i, j = np.tril_indices(len(df.columns), -1)

We can create a pd.MultiIndex for the columns. This makes it more generalizable for column names that are longer than one character.

pd.DataFrame(
    v[:, i] - v[:, j],
    df.index,
    [df.columns[j], df.columns[i]]
)

        A     B  A  B  C
        B  C  C  D  D  D
Dt                      
11-apr  0  0  0  0  0  0
10-apr  1 -1 -2  0 -1  1

But we can also do

pd.DataFrame(
    v[:, i] - v[:, j],
    df.index,
    df.columns[j] + df.columns[i]
)

        AB  AC  BC  AD  BD  CD
Dt                            
11-apr   0   0   0   0   0   0
10-apr   1  -1  -2   0  -1   1

np.tril_indices explained

This is a numpy function that returns two arrays that when used together, provide the locations of a lower triangle of a square matrix. This is handy when doing manipulations of all combinations of things as this lower triangle represents all combinations of one axis of a matrix with the other.

Consider the dataframe d for illustration

d = pd.DataFrame(np.array(list('abcdefghijklmnopqrstuvwxy')).reshape(-1, 5))
d

   0  1  2  3  4
0  a  b  c  d  e
1  f  g  h  i  j
2  k  l  m  n  o
3  p  q  r  s  t
4  u  v  w  x  y

The triangle indices, when looked at like coordinate pairs, looks like this

i, j = np.tril_indices(5, -1)
list(zip(i, j))

[(1, 0),
 (2, 0),
 (2, 1),
 (3, 0),
 (3, 1),
 (3, 2),
 (4, 0),
 (4, 1),
 (4, 2),
 (4, 3)]

I can manipulate ds values with i and j

d.values[i, j] = 'z'
d

   0  1  2  3  4
0  a  b  c  d  e
1  z  g  h  i  j
2  z  z  m  n  o
3  z  z  z  s  t
4  z  z  z  z  y

And you can see it targeted just that lower triangle

naive time test

enter image description here