Python data calculation over dataframe

Question

I have a data frame with columns equal to Date, Security and Price.

From this table, for each date, I want to calculate a formula on the price, for every possible combination of three securities.

If I have securities X, Y, Z,...N, I want to calculate 2*Price(x) - Price(y) -Price(z)....and do this for every three possible securities.

Initial dataframe:

Date	Security	Price(P)
1/3/19	X	0.8
1/3/19	Y	0.9
1/3/19	Z	0.95
.
.
.
2/3/19
2/3/19

Results:

Date	Combo	Result
1/3/19	XYZ	2Px - Py - Pz
1/3/19	YZX	2Py - Pz - Px
.
.
.
2/3/19	XYZ	2Px - Py - Pz

user_8228558 · Answer

Here is one way to do it. I consider that all items of 'Security' column are of 1-character length:

result=pd.DataFrame(columns=['Date', 'Combo', 'Result'])

for i in set(df.Date):
    temp=df[df.Date==i]
    vals={i:k for i,k in zip(df['Security'], df['Price(P)'])}
    d={''.join(i):0 for i in itertools.permutations(temp['Security'],3)}
    for k in d:
        d[k]=2*vals[k[0]]-vals[k[1]]-vals[k[2]]
    res=pd.DataFrame({'Date':[i]*len(d), 'Combo':d.keys(), 'Result':d.values()})
    result=pd.concat([result, res])

result.reset_index(drop=True, inplace=True)

Example:

df=pd.DataFrame({'Date':['2019-10-05']*4+['2020-09-10']*3, 'Security':['X', 'Y', 'Z', 'W', 'X', 'Y', 'X'], 'Price(P)':[2, 3, 1, 5, 1.2, 8, 3.9]})

>>> print(df)
         Date Security  Price(P)
0  2019-10-05        X    2.0
1  2019-10-05        Y    3.0
2  2019-10-05        Z    1.0
3  2019-10-05        W    5.0
4  2020-09-10        X    1.2
5  2020-09-10        Y    8.0
6  2020-09-10        X    3.9

After running the above code:

>>>print(result)

          Date Combo  Result
0   2019-10-05   XYZ    -1.2
1   2019-10-05   XYW    -5.2
2   2019-10-05   XZY    -1.2
3   2019-10-05   XZW     1.8
4   2019-10-05   XWY    -5.2
5   2019-10-05   XWZ     1.8
6   2019-10-05   YXZ    11.1
7   2019-10-05   YXW     7.1
8   2019-10-05   YZX    11.1
9   2019-10-05   YZW    10.0
10  2019-10-05   YWX     7.1
11  2019-10-05   YWZ    10.0
12  2019-10-05   ZXY    -9.9
13  2019-10-05   ZXW    -6.9
14  2019-10-05   ZYX    -9.9
15  2019-10-05   ZYW   -11.0
16  2019-10-05   ZWX    -6.9
17  2019-10-05   ZWY   -11.0
18  2019-10-05   WXY    -1.9
19  2019-10-05   WXZ     5.1
20  2019-10-05   WYX    -1.9
21  2019-10-05   WYZ     1.0
22  2019-10-05   WZX     5.1
23  2019-10-05   WZY     1.0
24  2020-09-10   XYX    -4.1
25  2020-09-10   XXY    -4.1
26  2020-09-10   YXX     8.2

In case that 'Security' column has items with more than 1-characters, you would better wrk with tuples. In that case just replace this:

d={''.join(i):0 for i in itertools.permutations(temp['Security'],3)}

by this:

d={i:0 for i in itertools.permutations(temp['Security'],3)}

Python data calculation over dataframe

1 Answers