In [1]:
import pandas as pd
import numpy as np
In [2]:
data_pddf = pd.read_excel('./input_files/positions.xlsx')
In [3]:
data_pddf.columns
Out[3]:
In [4]:
positions_pddf = data_pddf[['Member', 'CC', 'Delta']]
positions_pddf.head()
Out[4]:
In [5]:
udl_values_df = data_pddf[['CC', 'UDL value']]
udl_values_df = udl_values_df.drop_duplicates('CC').set_index('CC').sort_index()
udl_values_df.head()
Out[5]:
In [6]:
udl_pddf = data_pddf[['UDL', 'Nu', 'Coef']]
udl_pddf = udl_pddf.set_index('UDL').sort_index()
udl_pddf = udl_pddf.dropna()
udl_pddf.head()
Out[6]:
In [7]:
udl_pddf['UDL value'] = udl_values_df['UDL value']
udl_pddf.head()
Out[7]:
In [8]:
members_id = list(set(positions_pddf['Member'].values))
members_id.sort(key=lambda s: int(s[2:]))
assets_id = list(set(udl_values_df.index.values))
assets_id.sort()
In [9]:
df_positions_matrix = pd.DataFrame(index=assets_id, columns=members_id)
for member in members_id:
tmp_pos = positions_pddf.loc[positions_pddf['Member'] == member]
tmp_pos = tmp_pos[['CC', 'Delta']].set_index('CC')['Delta']
df_positions_matrix[member] = tmp_pos
df_positions_matrix[np.isnan(df_positions_matrix)] = 0.
df_positions_matrix = df_positions_matrix.transpose()
df_positions_matrix.head()
Out[9]:
In [10]:
import os
dir_ = './transformed_input'
if not os.path.exists(dir_):
os.makedirs(dir_)
In [11]:
udl_pddf.to_csv(os.path.join(dir_, 'UDL.csv'))
df_positions_matrix.to_csv(os.path.join(dir_, 'positions.csv'))