1

I have the following dataframe and I want to create a new dataframe with one column being unique strikes (no repetition) and one index (TimeUntilExpiration-unique values) with the Mid values being the values that corresponds to a unique combination of TimeUntilExpiration and Strike.

 TimeUntilExpiration  Strike    Mid

0               0.010959   22.75  2.325
1               0.010959   23.00  2.200
2               0.010959   23.25  2.100
3               0.010959   23.50  1.975
4               0.010959   23.75  1.875
..                   ...     ...    ...
307             2.043836   25.00  1.455
308             2.043836   26.00  1.015
309             2.043836   27.00  2.795
310             2.043836   28.00  0.395
311             2.043836   29.00  0.205

Tried so many ways and mapping but nothing seemed to work. Appreciate any help:)

Stochastic vol
  • 13
  • 3
  • You can modify the data because your question is not readable. – wordinone Jun 03 '22 at 22:16
  • in your example data it looks like the Strikes are already unique? Is this the final output you're trying to make? If so can you paste your input? – mitoRibo Jun 03 '22 at 22:40
  • I modified the data apologies. As for the strikes, they look unique because the data is huge and this is only a small representation. I had a huge dataset, created this dataframe based on 3 columns, and now I want to adjust this dataframe and create a new one based on one unique column (Strikes), one unique index(TimeUntilExpiration) and the corresponding 'Mid' values for every unique strike and TimeUntilExpiraton – Stochastic vol Jun 03 '22 at 22:46
  • Do you want to [pivot](https://stackoverflow.com/q/47152691/15239951) your dataframe? – Corralien Jun 03 '22 at 22:46
  • You didn't update your example, those are still unique values :) – Drakax Jun 03 '22 at 22:51
  • Thanks everyone. it was just a pivot table:) – Stochastic vol Jun 03 '22 at 23:01

2 Answers2

0

Maybe you want to pivot your dataframe?

>>> df.pivot_table('Mid', 'TimeUntilExpiration', 'Strike')

Strike               22.75  23.00  23.25  23.50  23.75  25.00  26.00  27.00  28.00  29.00
TimeUntilExpiration                                                                      
0.010959             2.325    2.2    2.1  1.975  1.875    NaN    NaN    NaN    NaN    NaN
2.043836               NaN    NaN    NaN    NaN    NaN  1.455  1.015  2.795  0.395  0.205
Corralien
  • 109,409
  • 8
  • 28
  • 52
0
pd.melt(df, id_vars=["TimeUntilExpiration",'Strike'], value_vars=["Mid"], value_name='Mid values').drop('variable', axis=1)

Result:

index TimeUntilExpiration Strike Mid values
0 0.010959 22.75 2.325
1 0.010959 23.0 2.2
2 0.010959 23.25 2.1
3 0.010959 23.5 1.975
4 0.010959 23.75 1.875
5 2.043836 25.0 1.455
6 2.043836 26.0 1.015
7 2.043836 27.0 2.795
8 2.043836 28.0 0.395
9 2.043836 29.0 0.205
Drakax
  • 1,305
  • 3
  • 9