All possible combinations of columns in dataframe -pandas/python
itertools.combinations
will help you:
import itertools
pd.DataFrame({'{}{}'.format(a, b): df[a] - df[b] for a, b in itertools.combinations(df.columns, 2)})
Which results in:
AB AC AD BC BD CD
Dt
11-apr 0 0 0 0 0 0
10-apr -1 1 0 2 1 -1
Itertools
module should help you to create the required combinations/permutations.
from itertools import combinations
# Creating a new pd.DataFrame
new_df = pd.DataFrame(index=df.index)
# list of columns
columns = df.columns
# Create all combinations of length 2 . eg. AB, BC, etc.
for combination in combinations(columns, 2):
combination_string = "".join(combination)
new_df[combination_string] = df[combination[1]]-df[combination[0]]
print new_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 numpy
s 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 d
s 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
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