Más Pandas#
Filtros
agregaciones y agrupaciones
tablas dinámicas
uso de pivotui
Filtrar un DataFrame#
Para el siguiente ejemplo utilizaremos el archivo data/bonos_empresa_carga_inicial.xlsx.
import pandas as pd
bonos_empresa = pd.read_excel('data/bonos_empresa_carga_inicial.xlsx')
Vemos los primeros registros del archivo.
bonos_empresa.tail()
| nemotecnico | fecha_emision | tasa_emision | tasa_descuento | unidad_monetaria | base_tasa_descuento | numero_flujos | meses_entre_vencimiento | tipo_intereses | numero_amortizaciones | plazo_agnos | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1755 | USTDH30914 | 2014-09-01 | 3.15 | 3.1728 | CLF | 365 | 50 | 6 | C | 1 | 25 |
| 1756 | USTDW20320 | 2020-03-01 | 3.50 | 3.5278 | CLF | 365 | 31 | 6 | C | 1 | 16 |
| 1757 | USTDW60320 | 2020-03-01 | 3.50 | 3.5274 | CLF | 365 | 33 | 6 | C | 1 | 17 |
| 1758 | USTDW70320 | 2020-03-01 | 3.55 | 3.5784 | CLF | 365 | 35 | 6 | C | 1 | 18 |
| 1759 | USTDW80320 | 2020-03-01 | 3.55 | 3.5786 | CLF | 365 | 37 | 6 | C | 1 | 19 |
Filtrar las columnas nemotecnico y unidad_monetaria.
bonos_empresa[['nemotecnico', 'unidad_monetaria']]
| nemotecnico | unidad_monetaria | |
|---|---|---|
| 0 | BAARA-A | CLF |
| 1 | BAARA-B | CLF |
| 2 | BACEN-A1 | CLF |
| 3 | BACEN-A2 | CLF |
| 4 | BADAL-A | CLF |
| ... | ... | ... |
| 1755 | USTDH30914 | CLF |
| 1756 | USTDW20320 | CLF |
| 1757 | USTDW60320 | CLF |
| 1758 | USTDW70320 | CLF |
| 1759 | USTDW80320 | CLF |
1760 rows × 2 columns
Filtremos los bonos emitidos en CLP.
bonos_empresa[bonos_empresa['unidad_monetaria'] == 'CLP']
| nemotecnico | fecha_emision | tasa_emision | tasa_descuento | unidad_monetaria | base_tasa_descuento | numero_flujos | meses_entre_vencimiento | tipo_intereses | numero_amortizaciones | plazo_agnos | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 8 | BADRT-D | 2019-02-20 | 7.3 | 7.2941 | CLP | 365 | 6 | 6 | C | 1 | 3 |
| 16 | BAGRS-K | 2018-09-01 | 4.7 | 4.6981 | CLP | 365 | 14 | 6 | C | 9 | 7 |
| 20 | BAGUA-AB | 2018-03-15 | 4.8 | 4.7943 | CLP | 365 | 14 | 6 | C | 8 | 7 |
| 36 | BAGUA-Y | 2016-01-15 | 5.4 | 5.3972 | CLP | 365 | 14 | 6 | C | 8 | 7 |
| 58 | BANDI-H | 2018-09-25 | 4.9 | 4.8980 | CLP | 365 | 14 | 6 | C | 8 | 7 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 1547 | BTMOV-K | 2016-09-13 | 4.9 | 4.8978 | CLP | 365 | 10 | 6 | C | 1 | 5 |
| 1549 | BTMOV-M | 2016-09-13 | 5.4 | 5.3976 | CLP | 365 | 20 | 6 | C | 1 | 10 |
| 1575 | BVIVO-A | 2018-01-05 | 5.5 | 5.4975 | CLP | 365 | 10 | 6 | C | 1 | 5 |
| 1599 | BWATT-Q | 2019-09-01 | 3.9 | 3.8968 | CLP | 365 | 20 | 6 | C | 1 | 10 |
| 1602 | HSTDAC0115 | 2015-01-01 | 5.5 | 5.5732 | CLP | 365 | 20 | 6 | C | 15 | 10 |
250 rows × 11 columns
Podemos mezclar con la primera instrucción y quedarnos sólo con las columnas nemotecnico y unidad_monetaria.
bonos_empresa[bonos_empresa['unidad_monetaria'] == 'CLP'][['nemotecnico', 'unidad_monetaria']]
| nemotecnico | unidad_monetaria | |
|---|---|---|
| 8 | BADRT-D | CLP |
| 16 | BAGRS-K | CLP |
| 20 | BAGUA-AB | CLP |
| 36 | BAGUA-Y | CLP |
| 58 | BANDI-H | CLP |
| ... | ... | ... |
| 1547 | BTMOV-K | CLP |
| 1549 | BTMOV-M | CLP |
| 1575 | BVIVO-A | CLP |
| 1599 | BWATT-Q | CLP |
| 1602 | HSTDAC0115 | CLP |
250 rows × 2 columns
La siguiente es una sintaxis alternativa utilizando la instrucción loc.
bonos_empresa.loc[bonos_empresa['unidad_monetaria'] == 'CLP', ['nemotecnico', 'unidad_monetaria']]
| nemotecnico | unidad_monetaria | |
|---|---|---|
| 8 | BADRT-D | CLP |
| 16 | BAGRS-K | CLP |
| 20 | BAGUA-AB | CLP |
| 36 | BAGUA-Y | CLP |
| 58 | BANDI-H | CLP |
| ... | ... | ... |
| 1547 | BTMOV-K | CLP |
| 1549 | BTMOV-M | CLP |
| 1575 | BVIVO-A | CLP |
| 1599 | BWATT-Q | CLP |
| 1602 | HSTDAC0115 | CLP |
250 rows × 2 columns
Se puede utilizar más de una condición lógica al filtrar.
bonos_empresa[
(bonos_empresa['fecha_emision'] >= '2020-01-01') &
(bonos_empresa['unidad_monetaria'] == 'CLP')
] # Notar que cada condición está entre ()
| nemotecnico | fecha_emision | tasa_emision | tasa_descuento | unidad_monetaria | base_tasa_descuento | numero_flujos | meses_entre_vencimiento | tipo_intereses | numero_amortizaciones | plazo_agnos | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 282 | BBNSAT0320 | 2020-03-01 | 3.00 | 3.0223 | CLP | 365 | 6 | 6 | C | 1 | 3 |
| 367 | BCFSA-H | 2020-05-15 | 3.60 | 3.5981 | CLP | 365 | 5 | 12 | C | 1 | 5 |
| 384 | BCGEI-O | 2020-06-20 | 3.20 | 3.1982 | CLP | 365 | 10 | 6 | C | 1 | 5 |
| 895 | BFALA-AA | 2020-04-15 | 3.45 | 3.4480 | CLP | 365 | 14 | 6 | C | 8 | 7 |
| 1184 | BPARC-Z | 2020-06-05 | 3.75 | 3.7478 | CLP | 365 | 10 | 6 | C | 1 | 5 |
| 1301 | BSCCH-D | 2020-03-01 | 3.50 | 3.4996 | CLP | 365 | 4 | 6 | C | 1 | 2 |
| 1302 | BSCCH-E | 2020-03-01 | 3.80 | 3.7997 | CLP | 365 | 6 | 6 | C | 1 | 3 |
| 1446 | BSPED-E | 2020-06-01 | 3.40 | 3.3981 | CLP | 365 | 10 | 6 | C | 1 | 5 |
También se puede seleccionar utilizando el número de fila y/o de columna con la instrucción iloc.
bonos_empresa.iloc[100:105, 0:5] # Primero los índices de fila, luego los de columna.
| nemotecnico | fecha_emision | tasa_emision | tasa_descuento | unidad_monetaria | |
|---|---|---|---|---|---|
| 100 | BBBVJ80416 | 2016-04-09 | 3.0 | 2.9977 | CLF |
| 101 | BBBVJ90517 | 2017-05-09 | 2.0 | 2.0088 | CLF |
| 102 | BBBVK20212 | 2012-02-01 | 3.0 | 2.9976 | CLF |
| 103 | BBBVK30212 | 2012-02-01 | 3.0 | 2.9978 | CLF |
| 104 | BBBVK40212 | 2012-02-01 | 3.0 | 2.9980 | CLF |
Agregaciones y Agrupaciones#
En ocasiones, tenemos interés en calcular una medida agregada para algunos subconjuntos de la data utilizada. Por ejemplo, vamos a calcular la tasa de emisión promedio y luego vamos a separar ese promedio por moneda.
bonos_empresa[['tasa_emision']].mean()
tasa_emision 3.790569
dtype: float64
Se puede hacer para más de una columna.
bonos_empresa[['tasa_emision', 'tasa_descuento']].mean()
tasa_emision 3.790569
tasa_descuento 3.797983
dtype: float64
Se agrupa por moneda:
bonos_empresa[['unidad_monetaria', 'tasa_emision']].groupby('unidad_monetaria').mean()
| tasa_emision | |
|---|---|
| unidad_monetaria | |
| CLF | 3.615995 |
| CLP | 4.761849 |
| USD | 5.100714 |
Notar que el output de esta operación es un DataFrame cuyo índice (identificador de fila) es el campo por el cual se hizo la agrupación. Se puede evitar este comportamiento de la siguiente forma:
bonos_empresa[['unidad_monetaria', 'tasa_emision']].groupby('unidad_monetaria', as_index=False).mean()
| unidad_monetaria | tasa_emision | |
|---|---|---|
| 0 | CLF | 3.615995 |
| 1 | CLP | 4.761849 |
| 2 | USD | 5.100714 |
Se puede utilizar más de 1 campo en la agrupación:
bonos_empresa[
['unidad_monetaria', 'plazo_agnos', 'tasa_emision']
].groupby(
['unidad_monetaria', 'plazo_agnos']
).mean().tail(30)
| tasa_emision | ||
|---|---|---|
| unidad_monetaria | plazo_agnos | |
| CLF | 35 | 3.650000 |
| CLP | 2 | 3.500000 |
| 3 | 3.527273 | |
| 4 | 3.550000 | |
| 5 | 4.580147 | |
| 6 | 4.511667 | |
| 7 | 4.837097 | |
| 8 | 4.855556 | |
| 9 | 5.325000 | |
| 10 | 6.216006 | |
| 11 | 4.266667 | |
| 12 | 5.000000 | |
| 13 | 5.000000 | |
| 14 | 5.000000 | |
| 15 | 5.000000 | |
| 16 | 5.000000 | |
| 17 | 5.000000 | |
| 18 | 5.000000 | |
| 19 | 5.000000 | |
| 20 | 6.000000 | |
| 21 | 5.000000 | |
| 22 | 5.000000 | |
| 23 | 6.500000 | |
| 24 | 5.000000 | |
| 99 | 0.000000 | |
| USD | 5 | 4.350000 |
| 7 | 4.283333 | |
| 10 | 4.700000 | |
| 15 | 7.203333 | |
| 20 | 4.750000 |
O también,
bonos_empresa[
['unidad_monetaria', 'plazo_agnos', 'tasa_emision']
].groupby(
['unidad_monetaria', 'plazo_agnos'],
as_index=False
).mean()
| unidad_monetaria | plazo_agnos | tasa_emision | |
|---|---|---|---|
| 0 | CLF | 3 | 2.118750 |
| 1 | CLF | 4 | 2.533333 |
| 2 | CLF | 5 | 2.188673 |
| 3 | CLF | 6 | 2.071000 |
| 4 | CLF | 7 | 2.388660 |
| ... | ... | ... | ... |
| 57 | USD | 5 | 4.350000 |
| 58 | USD | 7 | 4.283333 |
| 59 | USD | 10 | 4.700000 |
| 60 | USD | 15 | 7.203333 |
| 61 | USD | 20 | 4.750000 |
62 rows × 3 columns
Podemos también prefiltrar la data y calcular para un subconjunto más reciente de emisiones.
bonos_empresa.loc[
bonos_empresa['fecha_emision'] >= '2019-01-01',
['unidad_monetaria', 'tasa_emision']
].groupby('unidad_monetaria').mean()
| tasa_emision | |
|---|---|
| unidad_monetaria | |
| CLF | 2.041135 |
| CLP | 4.137209 |
| USD | 5.800000 |
¿Cuáles serán la emisiones en USD? O sea, filtrar las emisiones en USD del DataFrame bonos_empresa.
bonos_empresa.loc[
(bonos_empresa['fecha_emision'] >= '2019-01-01') &
(bonos_empresa['unidad_monetaria'] == 'USD')
]
| nemotecnico | fecha_emision | tasa_emision | tasa_descuento | unidad_monetaria | base_tasa_descuento | numero_flujos | meses_entre_vencimiento | tipo_intereses | numero_amortizaciones | plazo_agnos | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 608 | BCOCH-A | 2020-06-14 | 6.25 | 6.2460 | USD | 365 | 30 | 6 | C | 15 | 15 |
| 1572 | BVAPO-C | 2019-05-09 | 5.35 | 5.3429 | USD | 365 | 14 | 6 | C | 4 | 7 |
Tablas Dinámicas#
Las tablas dinámicas permiten agregaciones para más de una métrica y agrupaciones a nivel de fila y columna.
bonos_empresa.pivot_table(
values=['tasa_emision', 'tasa_descuento'],
index=['unidad_monetaria', 'plazo_agnos'], # nombres de fila
columns='tipo_intereses',
aggfunc='mean',
margins_name='LV'
)
| tasa_descuento | tasa_emision | ||
|---|---|---|---|
| tipo_intereses | C | C | |
| unidad_monetaria | plazo_agnos | ||
| CLF | 3 | 2.117750 | 2.118750 |
| 4 | 2.533900 | 2.533333 | |
| 5 | 2.192097 | 2.188673 | |
| 6 | 2.073828 | 2.071000 | |
| 7 | 2.391586 | 2.388660 | |
| ... | ... | ... | ... |
| USD | 5 | 4.364250 | 4.350000 |
| 7 | 4.302333 | 4.283333 | |
| 10 | 4.736760 | 4.700000 | |
| 15 | 7.295267 | 7.203333 | |
| 20 | 4.802800 | 4.750000 |
62 rows × 2 columns
bonos_empresa.pivot_table(
values=['tasa_emision', 'tasa_descuento'],
index=['unidad_monetaria', 'plazo_agnos'], # nombres de fila
columns='tipo_intereses',
aggfunc='mean',
)
| tasa_descuento | tasa_emision | ||
|---|---|---|---|
| tipo_intereses | C | C | |
| unidad_monetaria | plazo_agnos | ||
| CLF | 3 | 2.117750 | 2.118750 |
| 4 | 2.533900 | 2.533333 | |
| 5 | 2.192097 | 2.188673 | |
| 6 | 2.073828 | 2.071000 | |
| 7 | 2.391586 | 2.388660 | |
| ... | ... | ... | ... |
| USD | 5 | 4.364250 | 4.350000 |
| 7 | 4.302333 | 4.283333 | |
| 10 | 4.736760 | 4.700000 | |
| 15 | 7.295267 | 7.203333 | |
| 20 | 4.802800 | 4.750000 |
62 rows × 2 columns
Uso de pivot_ui#
La librería pivot_ui es una herramienta gráfica que permite construir tablas dinámicas de forma muy similar a lo que podemos hacer en Excel.
Para los ejemplos siguientes, vamos a utilizar una nueva fuente de datos que contiene las transacciones de bonos en Bolsa de Comercio del último año calendario.
trans_corp = pd.read_excel('data/transacciones_corporativo.xlsx')
Revisamos los primeros datos.
trans_corp.head()
| Fecha | Nemo | Emisor | Sector | Tipo | Riesgo | TIR | Spread | Precio | Plazo | Dur | Tasa emisión | Cantidad | Moneda | Monto CLP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCHIAQ0213 | BBCHILE | Banco | BB | AAA | -0.50 | 0.99 | 108.129997 | 1.986301 | 1.936758 | 3.6 | 50000 | UF | 1550355938 |
| 1 | 2020-08-06 | BCHIUP1211 | BBCHILE | Banco | BB | AAA | -0.47 | 1.42 | 105.059998 | 1.320548 | 1.296548 | 3.4 | 2000 | UF | 60587083 |
| 2 | 2020-08-06 | BCHIUK0611 | BBCHILE | Banco | BB | AAA | -0.47 | 1.11 | 107.169998 | 1.819178 | 1.770808 | 3.5 | 1000 | UF | 30907486 |
| 3 | 2020-08-06 | BCHIUQ1011 | BBCHILE | Banco | BB | AAA | -0.47 | 0.94 | 108.230003 | 2.153425 | 2.076164 | 3.4 | 4000 | UF | 125529720 |
| 4 | 2020-08-06 | BBNS-M0412 | BBSCOTIABA | Banco | BB | AAA | -0.45 | 1.23 | 106.480003 | 1.652055 | 1.603280 | 3.5 | 1000 | UF | 30888685 |
Los nombres de columnas son feos, tienen mayúsculas y espacios. Vamos a cambiarlos.
trans_corp.columns
Index(['Fecha', 'Nemo', 'Emisor', 'Sector', 'Tipo', 'Riesgo', 'TIR', 'Spread',
'Precio', 'Plazo', 'Dur', 'Tasa emisión', 'Cantidad', 'Moneda',
'Monto CLP'],
dtype='object')
trans_corp.columns = ['fecha', 'nemotecnico', 'emisor', 'sector', 'tipo_deuda',
'clasificacion_riesgo', 'tir', 'spread', 'precio', 'plazo',
'duracion', 'tasa_emision', 'cantidad', 'moneda', 'monto_clp']
trans_corp.head()
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCHIAQ0213 | BBCHILE | Banco | BB | AAA | -0.50 | 0.99 | 108.129997 | 1.986301 | 1.936758 | 3.6 | 50000 | UF | 1550355938 |
| 1 | 2020-08-06 | BCHIUP1211 | BBCHILE | Banco | BB | AAA | -0.47 | 1.42 | 105.059998 | 1.320548 | 1.296548 | 3.4 | 2000 | UF | 60587083 |
| 2 | 2020-08-06 | BCHIUK0611 | BBCHILE | Banco | BB | AAA | -0.47 | 1.11 | 107.169998 | 1.819178 | 1.770808 | 3.5 | 1000 | UF | 30907486 |
| 3 | 2020-08-06 | BCHIUQ1011 | BBCHILE | Banco | BB | AAA | -0.47 | 0.94 | 108.230003 | 2.153425 | 2.076164 | 3.4 | 4000 | UF | 125529720 |
| 4 | 2020-08-06 | BBNS-M0412 | BBSCOTIABA | Banco | BB | AAA | -0.45 | 1.23 | 106.480003 | 1.652055 | 1.603280 | 3.5 | 1000 | UF | 30888685 |
Para utilizar pivot_ui ejecutamos el siguiente import.
from pivottablejs import pivot_ui
Luego, para utilizar el DataFrame trans_corp ejecutamos lo siguiente. pivot_ui construye un archivo html que luego se despliega en el notebook. Con el parámetro outfile_path se puede definir la ruta de guardado de ese archivo, el cual, posteriormente, puede abrirse desde cualquier browser sin necesidad de estar ejecutando un Jupyter notebook.
x = pivot_ui(trans_corp, outfile_path='html_extra/trans_corp.html')
Vamos a abrir el archivo en otra pestaña Pivot UI.
Ejercicio#
Vamos a construir una curva de TIR de mercado para papeles de gobierno en UF. Utilizaremos luego esta curva para calcular el spread de liquidez/crédito de las transacciones de bonos de deuda privada.
Primeramente, importamos un archivo con transacciones de bonos de gobierno (BCU, BTU, BCP, BTP).
trans_gob = pd.read_excel('data/transacciones_gobierno.xlsx')
trans_gob.head()
| Fecha | Nemo | Emisor | Sector | Tipo | Riesgo | TIR | Spread | Precio | Plazo | Dur | Tasa emisión | Cantidad | Moneda | Monto CLP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCU0300322 | CENTRAL | Gobierno | BCU | NaN | -1.46 | 0.29 | 107.019997 | 1.567123 | 1.525479 | 3.0 | 20000 | UF | 621353413 |
| 1 | 2020-08-06 | BTU0150321 | TGEN | Gobierno | BTU | NaN | -1.40 | -0.50 | 101.650002 | 0.567123 | 0.563484 | 1.5 | 849500 | UF | 24908112885 |
| 2 | 2020-08-06 | BCU0500922 | CENTRAL | Gobierno | BCU | NaN | -1.35 | 0.10 | 113.099998 | 2.071233 | 1.962020 | 5.0 | 26000 | UF | 861031965 |
| 3 | 2020-08-06 | BTU0130323 | TGEN | Gobierno | BTU | NaN | -1.31 | -0.06 | 106.809998 | 2.567123 | 2.521369 | 1.3 | 773000 | UF | 23797572440 |
| 4 | 2020-08-06 | BCU0300221 | CENTRAL | Gobierno | BCU | NaN | -1.08 | -0.35 | 102.000000 | 0.490411 | 0.490411 | 3.0 | 10000 | UF | 292470465 |
También para esta data cambiamos el nombre de las columnas.
trans_gob.columns = [
'fecha',
'nemotecnico',
'emisor',
'sector',
'tipo_deuda',
'clasificacion_riesgo',
'tir',
'spread',
'precio',
'plazo',
'duracion',
'tasa_emision',
'cantidad',
'moneda',
'monto_clp'
]
trans_gob.head()
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCU0300322 | CENTRAL | Gobierno | BCU | NaN | -1.46 | 0.29 | 107.019997 | 1.567123 | 1.525479 | 3.0 | 20000 | UF | 621353413 |
| 1 | 2020-08-06 | BTU0150321 | TGEN | Gobierno | BTU | NaN | -1.40 | -0.50 | 101.650002 | 0.567123 | 0.563484 | 1.5 | 849500 | UF | 24908112885 |
| 2 | 2020-08-06 | BCU0500922 | CENTRAL | Gobierno | BCU | NaN | -1.35 | 0.10 | 113.099998 | 2.071233 | 1.962020 | 5.0 | 26000 | UF | 861031965 |
| 3 | 2020-08-06 | BTU0130323 | TGEN | Gobierno | BTU | NaN | -1.31 | -0.06 | 106.809998 | 2.567123 | 2.521369 | 1.3 | 773000 | UF | 23797572440 |
| 4 | 2020-08-06 | BCU0300221 | CENTRAL | Gobierno | BCU | NaN | -1.08 | -0.35 | 102.000000 | 0.490411 | 0.490411 | 3.0 | 10000 | UF | 292470465 |
Podemos ver que en esta data tenemos transacciones de papeles en UF y CLP.
trans_gob['emisor'].unique() # Permite obtener los valores únicos de una columna.
array(['CENTRAL', 'TGEN', 'ECCH'], dtype=object)
trans_gob[trans_gob['emisor'] == 'ECCH']
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 9 | 2020-08-06 | BVL2CX0625 | ECCH | Gobierno | BVL | NaN | 0.05 | 1.13 | 106.050003 | 4.832877 | 2.455261 | 2.50 | 6000 | UF | 54581201 |
| 169 | 2020-08-19 | BVL2CW0629 | ECCH | Gobierno | BVL | NaN | 0.37 | 1.18 | 109.500000 | 8.800000 | 4.397053 | 2.50 | 1000 | UF | 20314146 |
| 170 | 2020-08-19 | BVL2CX0638 | ECCH | Gobierno | BVL | NaN | 0.94 | 1.15 | 113.849998 | 17.805479 | 8.672252 | 2.50 | 1000 | UF | 30207280 |
| 171 | 2020-08-19 | BVL2CX0938 | ECCH | Gobierno | BVL | NaN | 0.95 | 1.15 | 113.949997 | 18.057534 | 8.787534 | 2.50 | 1000 | UF | 30563619 |
| 339 | 2020-09-02 | BVL2BV0331 | ECCH | Gobierno | BVL | NaN | 0.16 | 0.98 | 111.199997 | 10.509589 | 5.243784 | 2.25 | 1424 | UF | 41459048 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 4256 | 2021-08-03 | BVL2CW0636 | ECCH | Gobierno | BVL | NaN | 2.47 | 0.96 | 100.190002 | 14.849315 | 7.011483 | 2.50 | 739 | UF | 22120353 |
| 4257 | 2021-08-03 | BVL2CX1236 | ECCH | Gobierno | BVL | NaN | 2.51 | 0.96 | 99.919998 | 15.350685 | 7.223029 | 2.50 | 10683 | UF | 260250763 |
| 4258 | 2021-08-03 | BVL2CX1238 | ECCH | Gobierno | BVL | NaN | 2.54 | 0.87 | 99.669998 | 17.350685 | 8.080534 | 2.50 | 1000 | UF | 26814063 |
| 4259 | 2021-08-03 | BVL2CX0339 | ECCH | Gobierno | BVL | NaN | 2.56 | 0.87 | 99.510002 | 17.597260 | 8.182191 | 2.50 | 12384 | UF | 335306439 |
| 4296 | 2021-08-05 | BVL2CW1235 | ECCH | Gobierno | BVL | NaN | 2.51 | 0.96 | 99.919998 | 14.342466 | 6.778937 | 2.50 | 922 | UF | 26772074 |
184 rows × 15 columns
# trans_gob = trans_gob[trans_gob['emisor'] != 'ECCH']
trans_gob = trans_gob[trans_gob['emisor'].isin(['CENTRAL', 'TGEN'])]
trans_gob['emisor'].unique()
array(['CENTRAL', 'TGEN'], dtype=object)
Filtramos las transacciones de papeles en UF.
trans_gob_uf = trans_gob[trans_gob['moneda'] == 'UF'].copy()
Agregamos a estas últimas una columna vertice con la duración del papel redondeada a 0 decimales.
trans_gob_uf['vertice'] = round(trans_gob_uf['duracion'], 0)
trans_gob_uf.head()
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCU0300322 | CENTRAL | Gobierno | BCU | NaN | -1.46 | 0.29 | 107.019997 | 1.567123 | 1.525479 | 3.0 | 20000 | UF | 621353413 | 2.0 |
| 1 | 2020-08-06 | BTU0150321 | TGEN | Gobierno | BTU | NaN | -1.40 | -0.50 | 101.650002 | 0.567123 | 0.563484 | 1.5 | 849500 | UF | 24908112885 | 1.0 |
| 2 | 2020-08-06 | BCU0500922 | CENTRAL | Gobierno | BCU | NaN | -1.35 | 0.10 | 113.099998 | 2.071233 | 1.962020 | 5.0 | 26000 | UF | 861031965 | 2.0 |
| 3 | 2020-08-06 | BTU0130323 | TGEN | Gobierno | BTU | NaN | -1.31 | -0.06 | 106.809998 | 2.567123 | 2.521369 | 1.3 | 773000 | UF | 23797572440 | 3.0 |
| 4 | 2020-08-06 | BCU0300221 | CENTRAL | Gobierno | BCU | NaN | -1.08 | -0.35 | 102.000000 | 0.490411 | 0.490411 | 3.0 | 10000 | UF | 292470465 | 0.0 |
Para calcular una TIR promedio diaria ponderada por monto de transacción, vamos a agregar el campo (columna) tir_cantidad.
trans_gob_uf['tir_cantidad'] = trans_gob_uf['tir'] * trans_gob_uf['cantidad']
Calculemos el promedio de TIR ponderada con un objeto pivot_ui.
x = pivot_ui(trans_gob_uf, outfile_path='html_extra/trans_gob_uf.html')
Replicamos el resultado con una tabla dinámica de pandas.
curvas = trans_gob_uf.pivot_table(
values=['cantidad', 'tir_cantidad'],
index=['fecha', 'vertice'], # nombres de fila
aggfunc='sum',
)
Podemos notar que el nuevo DataFrame tiene un multi índice compuesto por la fecha y vértice. Las columnas son cantidad y tir_cantidad.
curvas.head(10)
| cantidad | tir_cantidad | ||
|---|---|---|---|
| fecha | vertice | ||
| 2020-08-06 | 0.0 | 10000 | -10800.0 |
| 1.0 | 849500 | -1189300.0 | |
| 2.0 | 46000 | -64300.0 | |
| 3.0 | 773000 | -1012630.0 | |
| 4.0 | 5000 | -4900.0 | |
| 5.0 | 805000 | -483000.0 | |
| 9.0 | 433500 | -130050.0 | |
| 13.0 | 63500 | 2540.0 | |
| 16.0 | 140000 | 36400.0 | |
| 18.0 | 5000 | 1950.0 |
La TIR promedio por fecha y vértice se calculará como el cociente entre las dos columnas.
curvas['tir_promedio'] = curvas['tir_cantidad'] / curvas['cantidad'] # Operación vectorial
curvas.head(10)
| cantidad | tir_cantidad | tir_promedio | ||
|---|---|---|---|---|
| fecha | vertice | |||
| 2020-08-06 | 0.0 | 10000 | -10800.0 | -1.080000 |
| 1.0 | 849500 | -1189300.0 | -1.400000 | |
| 2.0 | 46000 | -64300.0 | -1.397826 | |
| 3.0 | 773000 | -1012630.0 | -1.310000 | |
| 4.0 | 5000 | -4900.0 | -0.980000 | |
| 5.0 | 805000 | -483000.0 | -0.600000 | |
| 9.0 | 433500 | -130050.0 | -0.300000 | |
| 13.0 | 63500 | 2540.0 | 0.040000 | |
| 16.0 | 140000 | 36400.0 | 0.260000 | |
| 18.0 | 5000 | 1950.0 | 0.390000 |
Nos quedamos sólo con la columna tir_promedio.
curvas = curvas[['tir_promedio']]
curvas.loc['2020-08-06']
| tir_promedio | |
|---|---|
| vertice | |
| 0.0 | -1.080000 |
| 1.0 | -1.400000 |
| 2.0 | -1.397826 |
| 3.0 | -1.310000 |
| 4.0 | -0.980000 |
| 5.0 | -0.600000 |
| 9.0 | -0.300000 |
| 13.0 | 0.040000 |
| 16.0 | 0.260000 |
| 18.0 | 0.390000 |
Realizamos los mismos ajustes a la data de transacciones de deuda privada.
trans_corp_uf = trans_corp[trans_corp['moneda'] == 'UF'].copy()
trans_corp_uf['vertice'] = round(trans_corp_uf['duracion'], 0)
trans_corp_uf.head()
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCHIAQ0213 | BBCHILE | Banco | BB | AAA | -0.50 | 0.99 | 108.129997 | 1.986301 | 1.936758 | 3.6 | 50000 | UF | 1550355938 | 2.0 |
| 1 | 2020-08-06 | BCHIUP1211 | BBCHILE | Banco | BB | AAA | -0.47 | 1.42 | 105.059998 | 1.320548 | 1.296548 | 3.4 | 2000 | UF | 60587083 | 1.0 |
| 2 | 2020-08-06 | BCHIUK0611 | BBCHILE | Banco | BB | AAA | -0.47 | 1.11 | 107.169998 | 1.819178 | 1.770808 | 3.5 | 1000 | UF | 30907486 | 2.0 |
| 3 | 2020-08-06 | BCHIUQ1011 | BBCHILE | Banco | BB | AAA | -0.47 | 0.94 | 108.230003 | 2.153425 | 2.076164 | 3.4 | 4000 | UF | 125529720 | 2.0 |
| 4 | 2020-08-06 | BBNS-M0412 | BBSCOTIABA | Banco | BB | AAA | -0.45 | 1.23 | 106.480003 | 1.652055 | 1.603280 | 3.5 | 1000 | UF | 30888685 | 2.0 |
Nos gustaría ahora poder asociar a cada transacción de deuda privada la tasa base, así como aparece en el DataFrame curvas, esto nos permitiría calcular el spread de la trasacción por sobre la curva de gobierno.
Para proceder vamos a utilizar la operación merge que permite unir dos DataFrame en función de uno o más campos en común. Veamos un ejemplo con data muy sencilla.
panel_a = {'nombre': ['hugo', 'paco', 'luis'], 'numero_cliente': [1, 2, 3]}
dfa = pd.DataFrame(panel_a)
dfa
| nombre | numero_cliente | |
|---|---|---|
| 0 | hugo | 1 |
| 1 | paco | 2 |
| 2 | luis | 3 |
panel_b = {'nombre': ['hugo', 'paco', 'luis', 'hugo', 'paco', 'paco'], 'numero_orden': [1, 2, 3, 4, 5, 6]}
dfb = pd.DataFrame(panel_b)
dfb
| nombre | numero_orden | |
|---|---|---|
| 0 | hugo | 1 |
| 1 | paco | 2 |
| 2 | luis | 3 |
| 3 | hugo | 4 |
| 4 | paco | 5 |
| 5 | paco | 6 |
Nos gustaría tener el campo numero_cliente en dfb. ¿Cómo lo hacemos?
dfc = dfb.merge(dfa, on='nombre', how='left')
dfc
| nombre | numero_orden | numero_cliente | |
|---|---|---|---|
| 0 | hugo | 1 | 1 |
| 1 | paco | 2 | 2 |
| 2 | luis | 3 | 3 |
| 3 | hugo | 4 | 1 |
| 4 | paco | 5 | 2 |
| 5 | paco | 6 | 2 |
Revisemos la documentación: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.merge.html
Como se puede ver en la documentación, podemos hacer merge haciendo el on sobre columnas o índices. Sin embargo, el siguiente truco simplifica el proceso cuando uno de los DataFrame tiene un multi índice.
Vamos a aplanar el DataFrame curvas.
curvas_flat = pd.DataFrame.from_records(curvas.to_records())
curvas_flat.head()
| fecha | vertice | tir_promedio | |
|---|---|---|---|
| 0 | 2020-08-06 | 0.0 | -1.080000 |
| 1 | 2020-08-06 | 1.0 | -1.400000 |
| 2 | 2020-08-06 | 2.0 | -1.397826 |
| 3 | 2020-08-06 | 3.0 | -1.310000 |
| 4 | 2020-08-06 | 4.0 | -0.980000 |
Veamos paso a paso:
curvas.to_records() # retorna un objeto de tipo recarray
rec.array([('2020-08-06T00:00:00.000000000', 0., -1.08 ),
('2020-08-06T00:00:00.000000000', 1., -1.4 ),
('2020-08-06T00:00:00.000000000', 2., -1.39782609), ...,
('2021-08-06T00:00:00.000000000', 8., 1.56 ),
('2021-08-06T00:00:00.000000000', 12., 2. ),
('2021-08-06T00:00:00.000000000', 14., 2.27 )],
dtype=[('fecha', '<M8[ns]'), ('vertice', '<f8'), ('tir_promedio', '<f8')])
Ver https://numpy.org/doc/stable/reference/generated/numpy.recarray.html.
Como el recarray tiene nombre para cada columna, resulta fácil transformarlo en un DataFrame.
curvas_flat = pd.DataFrame.from_records(curvas.to_records())
curvas_flat.head()
| fecha | vertice | tir_promedio | |
|---|---|---|---|
| 0 | 2020-08-06 | 0.0 | -1.080000 |
| 1 | 2020-08-06 | 1.0 | -1.400000 |
| 2 | 2020-08-06 | 2.0 | -1.397826 |
| 3 | 2020-08-06 | 3.0 | -1.310000 |
| 4 | 2020-08-06 | 4.0 | -0.980000 |
Siguiendo con las analogías con Excel, esto es similar a formatear una tabla dinámica de Excel haciendo que repita la etiqueta de los campos repetidos.
Habiendo aplanado el DataFrame curvas podemos hacer el merge con el DataFrame trans_corp_uf.
trans_corp_uf_base = trans_corp_uf.merge(
curvas_flat,
how='left',
on=['vertice', 'fecha']
)
trans_corp_uf_base.head()
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCHIAQ0213 | BBCHILE | Banco | BB | AAA | -0.50 | 0.99 | 108.129997 | 1.986301 | 1.936758 | 3.6 | 50000 | UF | 1550355938 | 2.0 | -1.397826 |
| 1 | 2020-08-06 | BCHIUP1211 | BBCHILE | Banco | BB | AAA | -0.47 | 1.42 | 105.059998 | 1.320548 | 1.296548 | 3.4 | 2000 | UF | 60587083 | 1.0 | -1.400000 |
| 2 | 2020-08-06 | BCHIUK0611 | BBCHILE | Banco | BB | AAA | -0.47 | 1.11 | 107.169998 | 1.819178 | 1.770808 | 3.5 | 1000 | UF | 30907486 | 2.0 | -1.397826 |
| 3 | 2020-08-06 | BCHIUQ1011 | BBCHILE | Banco | BB | AAA | -0.47 | 0.94 | 108.230003 | 2.153425 | 2.076164 | 3.4 | 4000 | UF | 125529720 | 2.0 | -1.397826 |
| 4 | 2020-08-06 | BBNS-M0412 | BBSCOTIABA | Banco | BB | AAA | -0.45 | 1.23 | 106.480003 | 1.652055 | 1.603280 | 3.5 | 1000 | UF | 30888685 | 2.0 | -1.397826 |
Finalmente, podemos calcular el spread de cada transacción respecto a la tir_promedio de la base en el mismo vertice.
trans_corp_uf_base['spread2'] = trans_corp_uf_base['tir'] - trans_corp_uf_base['tir_promedio'] # Vectorial
trans_corp_uf_base.head()
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | spread2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2020-08-06 | BCHIAQ0213 | BBCHILE | Banco | BB | AAA | -0.50 | 0.99 | 108.129997 | 1.986301 | 1.936758 | 3.6 | 50000 | UF | 1550355938 | 2.0 | -1.397826 | 0.897826 |
| 1 | 2020-08-06 | BCHIUP1211 | BBCHILE | Banco | BB | AAA | -0.47 | 1.42 | 105.059998 | 1.320548 | 1.296548 | 3.4 | 2000 | UF | 60587083 | 1.0 | -1.400000 | 0.930000 |
| 2 | 2020-08-06 | BCHIUK0611 | BBCHILE | Banco | BB | AAA | -0.47 | 1.11 | 107.169998 | 1.819178 | 1.770808 | 3.5 | 1000 | UF | 30907486 | 2.0 | -1.397826 | 0.927826 |
| 3 | 2020-08-06 | BCHIUQ1011 | BBCHILE | Banco | BB | AAA | -0.47 | 0.94 | 108.230003 | 2.153425 | 2.076164 | 3.4 | 4000 | UF | 125529720 | 2.0 | -1.397826 | 0.927826 |
| 4 | 2020-08-06 | BBNS-M0412 | BBSCOTIABA | Banco | BB | AAA | -0.45 | 1.23 | 106.480003 | 1.652055 | 1.603280 | 3.5 | 1000 | UF | 30888685 | 2.0 | -1.397826 | 0.947826 |
Todo bien, pero …
trans_corp_uf_base.tail(20)
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | spread2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19518 | 2021-08-06 | BAVSU-A1 | AUTOVESSUR | Concesionaria | BE | AA | 2.34 | 1.79 | 107.410004 | 7.364384 | 3.467707 | 4.50 | 10000 | UF | 169758561 | 3.0 | NaN | NaN |
| 19519 | 2021-08-06 | BSECD30319 | BBSECURITY | Banco | BB | AA | 2.42 | 0.91 | 89.769997 | 8.076712 | 7.721296 | 1.00 | 20000 | UF | 537046683 | 8.0 | 1.560000 | 0.860000 |
| 19520 | 2021-08-06 | BARAU-W | ARAUCO | Forestal | BE | AA | 2.46 | 1.12 | 97.669998 | 7.183562 | 6.655741 | 2.10 | 39000 | UF | 1142077344 | 7.0 | NaN | NaN |
| 19521 | 2021-08-06 | BBNSAY0620 | BBSCOTIABA | Banco | BB | AAA | 2.46 | 0.76 | 84.680000 | 9.347945 | 9.067208 | 0.60 | 100000 | UF | 2522238026 | 9.0 | NaN | NaN |
| 19522 | 2021-08-06 | BAGRS-D | AGROSUPER | Alimentos | BE | AA- | 2.50 | 1.33 | 106.639999 | 11.079452 | 5.176165 | 3.80 | 2500 | UF | 80657283 | 5.0 | NaN | NaN |
| 19523 | 2021-08-06 | BSKSA-E | SK | Industrial | BE | AA- | 2.58 | 1.34 | 107.860001 | 11.613699 | 5.589889 | 4.00 | 1000 | UF | 32613657 | 6.0 | NaN | NaN |
| 19524 | 2021-08-06 | BECOP-K | COPEC | Industrial | BE | AA | 2.60 | 1.30 | 98.099998 | 6.991781 | 6.493786 | 2.30 | 20000 | UF | 584139690 | 6.0 | NaN | NaN |
| 19525 | 2021-08-06 | BBIC770520 | BBICE | Banco | BB | AA | 2.60 | 0.88 | 90.750000 | 9.739726 | 9.018792 | 1.50 | 100000 | UF | 2713860727 | 9.0 | NaN | NaN |
| 19526 | 2021-08-06 | BENTE-N | ENTEL | Telecomunicaciones | BE | A+ | 2.65 | 1.31 | 98.370003 | 7.243836 | 6.648298 | 2.40 | 3000 | UF | 88389706 | 7.0 | NaN | NaN |
| 19527 | 2021-08-06 | BCMPC-O | CMPC | Forestal | BE | AA- | 2.66 | 1.21 | 93.339996 | 7.824658 | 7.317841 | 1.70 | 64000 | UF | 1784601099 | 7.0 | NaN | NaN |
| 19528 | 2021-08-06 | BFALA-AC | FALAB.SACI | Comercio | BE | AA | 2.78 | 1.20 | 93.349998 | 8.695890 | 7.982071 | 1.90 | 97500 | UF | 2724776777 | 8.0 | 1.560000 | 1.220000 |
| 19529 | 2021-08-06 | BPARC-AA | P.ARAUCO | Comercio | BE | AA | 2.83 | 1.29 | 89.559998 | 8.336986 | 7.847611 | 1.40 | 94000 | UF | 2512368655 | 8.0 | 1.560000 | 1.270000 |
| 19530 | 2021-08-06 | BINT-R0919 | BBINTERNAC | Banco | BB | AA- | 2.86 | 1.21 | 85.410004 | 9.076712 | 8.617292 | 1.00 | 500000 | UF | 12773246150 | 9.0 | NaN | NaN |
| 19531 | 2021-08-06 | BESTK20713 | BBESTADO | Banco | BB | AAA | 2.96 | 0.71 | 111.620003 | 21.915068 | 15.607936 | 3.70 | 50000 | UF | 1666293652 | 16.0 | NaN | NaN |
| 19532 | 2021-08-06 | BFACT-A | FACTOTAL | Financiero | BE | A+ | 3.18 | 3.91 | 96.790001 | 3.060274 | 1.766557 | 1.30 | 500 | UF | 14491595 | 2.0 | -1.170958 | 4.350958 |
| 19533 | 2021-08-06 | BPATI-E | PATIOCOMER | Inmobiliario | BE | A | 3.20 | 2.89 | 100.160004 | 3.693151 | 3.478062 | 3.25 | 50000 | UF | 1505916362 | 3.0 | NaN | NaN |
| 19534 | 2021-08-06 | UBCIB31219 | BBCREDITO | Banco | BU | AA | 3.26 | 0.91 | 77.269997 | 28.339726 | 20.577523 | 2.00 | 120000 | UF | 2768113308 | 21.0 | NaN | NaN |
| 19535 | 2021-08-06 | BCALI-A | CALICHERA | Minero | BE | A- | 4.25 | 3.02 | 101.410004 | 8.863014 | 5.930865 | 4.50 | 500 | UF | 15186394 | 6.0 | NaN | NaN |
| 19536 | 2021-08-06 | BSALF-B | SALFACORP | Construcción | BE | BBB- | 4.35 | 3.66 | 100.510002 | 7.821918 | 3.647272 | 4.50 | 500 | UF | 6351901 | 4.0 | 0.625948 | 3.724052 |
| 19537 | 2021-08-06 | BDRMS-D | DREAMS | Entretenimiento | BE | BBB | 7.30 | 6.12 | 83.610001 | 6.610959 | 5.688209 | 3.97 | 1000 | UF | 25290442 | 6.0 | NaN | NaN |
¿Y si pudiéramos interpolar para encontrar los valores que faltan? Por primera vez vamos a usar explícitamente la librería numpy para realizar una interpolación lineal.
import numpy as np
def interpola(fecha, vertice, curvas): # curvas va a ser el DataFrame curvas_flat
datos = curvas.loc[curvas['fecha'] == fecha, ['vertice', 'tir_promedio']]
# print(datos)
return np.interp(vertice, datos['vertice'], datos['tir_promedio'])
interpola('2021-08-06', 9.0, curvas_flat)
np.float64(1.67)
Ahora aplicaremos esta función para arreglar la columna tir_promedio de trans_corp_uf_base. Para esto usaremos el método apply.
trans_corp_uf_base['tir_promedio'] = trans_corp_uf_base.apply(
lambda row: interpola(row['fecha'], row['vertice'], curvas_flat), # lambda es una función sin nombre
axis=1
)
trans_corp_uf_base.tail(20)
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | spread2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19518 | 2021-08-06 | BAVSU-A1 | AUTOVESSUR | Concesionaria | BE | AA | 2.34 | 1.79 | 107.410004 | 7.364384 | 3.467707 | 4.50 | 10000 | UF | 169758561 | 3.0 | -0.272505 | NaN |
| 19519 | 2021-08-06 | BSECD30319 | BBSECURITY | Banco | BB | AA | 2.42 | 0.91 | 89.769997 | 8.076712 | 7.721296 | 1.00 | 20000 | UF | 537046683 | 8.0 | 1.560000 | 0.860000 |
| 19520 | 2021-08-06 | BARAU-W | ARAUCO | Forestal | BE | AA | 2.46 | 1.12 | 97.669998 | 7.183562 | 6.655741 | 2.10 | 39000 | UF | 1142077344 | 7.0 | 1.326487 | NaN |
| 19521 | 2021-08-06 | BBNSAY0620 | BBSCOTIABA | Banco | BB | AAA | 2.46 | 0.76 | 84.680000 | 9.347945 | 9.067208 | 0.60 | 100000 | UF | 2522238026 | 9.0 | 1.670000 | NaN |
| 19522 | 2021-08-06 | BAGRS-D | AGROSUPER | Alimentos | BE | AA- | 2.50 | 1.33 | 106.639999 | 11.079452 | 5.176165 | 3.80 | 2500 | UF | 80657283 | 5.0 | 0.859461 | NaN |
| 19523 | 2021-08-06 | BSKSA-E | SK | Industrial | BE | AA- | 2.58 | 1.34 | 107.860001 | 11.613699 | 5.589889 | 4.00 | 1000 | UF | 32613657 | 6.0 | 1.092974 | NaN |
| 19524 | 2021-08-06 | BECOP-K | COPEC | Industrial | BE | AA | 2.60 | 1.30 | 98.099998 | 6.991781 | 6.493786 | 2.30 | 20000 | UF | 584139690 | 6.0 | 1.092974 | NaN |
| 19525 | 2021-08-06 | BBIC770520 | BBICE | Banco | BB | AA | 2.60 | 0.88 | 90.750000 | 9.739726 | 9.018792 | 1.50 | 100000 | UF | 2713860727 | 9.0 | 1.670000 | NaN |
| 19526 | 2021-08-06 | BENTE-N | ENTEL | Telecomunicaciones | BE | A+ | 2.65 | 1.31 | 98.370003 | 7.243836 | 6.648298 | 2.40 | 3000 | UF | 88389706 | 7.0 | 1.326487 | NaN |
| 19527 | 2021-08-06 | BCMPC-O | CMPC | Forestal | BE | AA- | 2.66 | 1.21 | 93.339996 | 7.824658 | 7.317841 | 1.70 | 64000 | UF | 1784601099 | 7.0 | 1.326487 | NaN |
| 19528 | 2021-08-06 | BFALA-AC | FALAB.SACI | Comercio | BE | AA | 2.78 | 1.20 | 93.349998 | 8.695890 | 7.982071 | 1.90 | 97500 | UF | 2724776777 | 8.0 | 1.560000 | 1.220000 |
| 19529 | 2021-08-06 | BPARC-AA | P.ARAUCO | Comercio | BE | AA | 2.83 | 1.29 | 89.559998 | 8.336986 | 7.847611 | 1.40 | 94000 | UF | 2512368655 | 8.0 | 1.560000 | 1.270000 |
| 19530 | 2021-08-06 | BINT-R0919 | BBINTERNAC | Banco | BB | AA- | 2.86 | 1.21 | 85.410004 | 9.076712 | 8.617292 | 1.00 | 500000 | UF | 12773246150 | 9.0 | 1.670000 | NaN |
| 19531 | 2021-08-06 | BESTK20713 | BBESTADO | Banco | BB | AAA | 2.96 | 0.71 | 111.620003 | 21.915068 | 15.607936 | 3.70 | 50000 | UF | 1666293652 | 16.0 | 2.270000 | NaN |
| 19532 | 2021-08-06 | BFACT-A | FACTOTAL | Financiero | BE | A+ | 3.18 | 3.91 | 96.790001 | 3.060274 | 1.766557 | 1.30 | 500 | UF | 14491595 | 2.0 | -1.170958 | 4.350958 |
| 19533 | 2021-08-06 | BPATI-E | PATIOCOMER | Inmobiliario | BE | A | 3.20 | 2.89 | 100.160004 | 3.693151 | 3.478062 | 3.25 | 50000 | UF | 1505916362 | 3.0 | -0.272505 | NaN |
| 19534 | 2021-08-06 | UBCIB31219 | BBCREDITO | Banco | BU | AA | 3.26 | 0.91 | 77.269997 | 28.339726 | 20.577523 | 2.00 | 120000 | UF | 2768113308 | 21.0 | 2.270000 | NaN |
| 19535 | 2021-08-06 | BCALI-A | CALICHERA | Minero | BE | A- | 4.25 | 3.02 | 101.410004 | 8.863014 | 5.930865 | 4.50 | 500 | UF | 15186394 | 6.0 | 1.092974 | NaN |
| 19536 | 2021-08-06 | BSALF-B | SALFACORP | Construcción | BE | BBB- | 4.35 | 3.66 | 100.510002 | 7.821918 | 3.647272 | 4.50 | 500 | UF | 6351901 | 4.0 | 0.625948 | 3.724052 |
| 19537 | 2021-08-06 | BDRMS-D | DREAMS | Entretenimiento | BE | BBB | 7.30 | 6.12 | 83.610001 | 6.610959 | 5.688209 | 3.97 | 1000 | UF | 25290442 | 6.0 | 1.092974 | NaN |
Y ahora podemos recalcular el campo spread2.
trans_corp_uf_base['spread2'] = trans_corp_uf_base['tir'] - trans_corp_uf_base['tir_promedio']
trans_corp_uf_base.tail(20)
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | spread2 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19518 | 2021-08-06 | BAVSU-A1 | AUTOVESSUR | Concesionaria | BE | AA | 2.34 | 1.79 | 107.410004 | 7.364384 | 3.467707 | 4.50 | 10000 | UF | 169758561 | 3.0 | -0.272505 | 2.612505 |
| 19519 | 2021-08-06 | BSECD30319 | BBSECURITY | Banco | BB | AA | 2.42 | 0.91 | 89.769997 | 8.076712 | 7.721296 | 1.00 | 20000 | UF | 537046683 | 8.0 | 1.560000 | 0.860000 |
| 19520 | 2021-08-06 | BARAU-W | ARAUCO | Forestal | BE | AA | 2.46 | 1.12 | 97.669998 | 7.183562 | 6.655741 | 2.10 | 39000 | UF | 1142077344 | 7.0 | 1.326487 | 1.133513 |
| 19521 | 2021-08-06 | BBNSAY0620 | BBSCOTIABA | Banco | BB | AAA | 2.46 | 0.76 | 84.680000 | 9.347945 | 9.067208 | 0.60 | 100000 | UF | 2522238026 | 9.0 | 1.670000 | 0.790000 |
| 19522 | 2021-08-06 | BAGRS-D | AGROSUPER | Alimentos | BE | AA- | 2.50 | 1.33 | 106.639999 | 11.079452 | 5.176165 | 3.80 | 2500 | UF | 80657283 | 5.0 | 0.859461 | 1.640539 |
| 19523 | 2021-08-06 | BSKSA-E | SK | Industrial | BE | AA- | 2.58 | 1.34 | 107.860001 | 11.613699 | 5.589889 | 4.00 | 1000 | UF | 32613657 | 6.0 | 1.092974 | 1.487026 |
| 19524 | 2021-08-06 | BECOP-K | COPEC | Industrial | BE | AA | 2.60 | 1.30 | 98.099998 | 6.991781 | 6.493786 | 2.30 | 20000 | UF | 584139690 | 6.0 | 1.092974 | 1.507026 |
| 19525 | 2021-08-06 | BBIC770520 | BBICE | Banco | BB | AA | 2.60 | 0.88 | 90.750000 | 9.739726 | 9.018792 | 1.50 | 100000 | UF | 2713860727 | 9.0 | 1.670000 | 0.930000 |
| 19526 | 2021-08-06 | BENTE-N | ENTEL | Telecomunicaciones | BE | A+ | 2.65 | 1.31 | 98.370003 | 7.243836 | 6.648298 | 2.40 | 3000 | UF | 88389706 | 7.0 | 1.326487 | 1.323513 |
| 19527 | 2021-08-06 | BCMPC-O | CMPC | Forestal | BE | AA- | 2.66 | 1.21 | 93.339996 | 7.824658 | 7.317841 | 1.70 | 64000 | UF | 1784601099 | 7.0 | 1.326487 | 1.333513 |
| 19528 | 2021-08-06 | BFALA-AC | FALAB.SACI | Comercio | BE | AA | 2.78 | 1.20 | 93.349998 | 8.695890 | 7.982071 | 1.90 | 97500 | UF | 2724776777 | 8.0 | 1.560000 | 1.220000 |
| 19529 | 2021-08-06 | BPARC-AA | P.ARAUCO | Comercio | BE | AA | 2.83 | 1.29 | 89.559998 | 8.336986 | 7.847611 | 1.40 | 94000 | UF | 2512368655 | 8.0 | 1.560000 | 1.270000 |
| 19530 | 2021-08-06 | BINT-R0919 | BBINTERNAC | Banco | BB | AA- | 2.86 | 1.21 | 85.410004 | 9.076712 | 8.617292 | 1.00 | 500000 | UF | 12773246150 | 9.0 | 1.670000 | 1.190000 |
| 19531 | 2021-08-06 | BESTK20713 | BBESTADO | Banco | BB | AAA | 2.96 | 0.71 | 111.620003 | 21.915068 | 15.607936 | 3.70 | 50000 | UF | 1666293652 | 16.0 | 2.270000 | 0.690000 |
| 19532 | 2021-08-06 | BFACT-A | FACTOTAL | Financiero | BE | A+ | 3.18 | 3.91 | 96.790001 | 3.060274 | 1.766557 | 1.30 | 500 | UF | 14491595 | 2.0 | -1.170958 | 4.350958 |
| 19533 | 2021-08-06 | BPATI-E | PATIOCOMER | Inmobiliario | BE | A | 3.20 | 2.89 | 100.160004 | 3.693151 | 3.478062 | 3.25 | 50000 | UF | 1505916362 | 3.0 | -0.272505 | 3.472505 |
| 19534 | 2021-08-06 | UBCIB31219 | BBCREDITO | Banco | BU | AA | 3.26 | 0.91 | 77.269997 | 28.339726 | 20.577523 | 2.00 | 120000 | UF | 2768113308 | 21.0 | 2.270000 | 0.990000 |
| 19535 | 2021-08-06 | BCALI-A | CALICHERA | Minero | BE | A- | 4.25 | 3.02 | 101.410004 | 8.863014 | 5.930865 | 4.50 | 500 | UF | 15186394 | 6.0 | 1.092974 | 3.157026 |
| 19536 | 2021-08-06 | BSALF-B | SALFACORP | Construcción | BE | BBB- | 4.35 | 3.66 | 100.510002 | 7.821918 | 3.647272 | 4.50 | 500 | UF | 6351901 | 4.0 | 0.625948 | 3.724052 |
| 19537 | 2021-08-06 | BDRMS-D | DREAMS | Entretenimiento | BE | BBB | 7.30 | 6.12 | 83.610001 | 6.610959 | 5.688209 | 3.97 | 1000 | UF | 25290442 | 6.0 | 1.092974 | 6.207026 |
Vectorizando#
Esta es una versión vectorizada de la función interpola. Con esta función se puede evitar el uso del método apply. Sin embargo, para este caso no se aprecia una ganancia en velocidad de ejecución, esto porque la vectorización ocurre en la variable fecha, la cual después no puede aprovecharse en una función vectorizada de numpy.
def vinterpola(fecha, vertice, curvas): # curvas va a ser el DataFrame curvas_flat
if len(fecha) != len(vertice):
raise ValueError("Arrays fecha and vertice must have the same length.")
datos = [curvas.loc[curvas['fecha'] == f, ['vertice', 'tir_promedio']] for f in fecha]
# print(datos)
return np.array([np.interp(v, d['vertice'], d['tir_promedio']) for v, d in zip(vertice, datos)])
Probamos un caso.
vinterpola(
['2021-08-06', '2021-08-06'],
[9.0, 9.0],
curvas_flat
)
array([1.67, 1.67])
Agregamos la columna tir_promedio usando esta función.
trans_corp_uf_base['tir_promedio'] = vinterpola(
trans_corp_uf_base['fecha'],
trans_corp_uf_base['vertice'],
curvas_flat
)
La siguiente versión fue proporcionada por Oliver Mohr y sí genera una importante disminución en el tiempo de ejecución. Esto se debe al uso de la función de interpolación de numpy.
# solución vectorial por fecha
for d in trans_corp_uf_base['fecha'].unique():
plazos = trans_corp_uf_base[trans_corp_uf_base['fecha'] == d]['vertice']
curvas_fecha = curvas_flat[curvas_flat['fecha'] == d].copy()
x = curvas_fecha['vertice']
y = curvas_fecha['tir_promedio']
trans_corp_uf_base.loc[trans_corp_uf_base['fecha'] == d, 'tir_promedio_OM'] = np.interp(
plazos, x, y)
trans_corp_uf_base['spread_OM'] = trans_corp_uf_base['tir'] - \
trans_corp_uf_base['tir_promedio']
trans_corp_uf_base.tail(20)
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | spread2 | tir_promedio_OM | spread_OM | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19518 | 2021-08-06 | BAVSU-A1 | AUTOVESSUR | Concesionaria | BE | AA | 2.34 | 1.79 | 107.410004 | 7.364384 | 3.467707 | 4.50 | 10000 | UF | 169758561 | 3.0 | -0.272505 | 2.612505 | -0.272505 | 2.612505 |
| 19519 | 2021-08-06 | BSECD30319 | BBSECURITY | Banco | BB | AA | 2.42 | 0.91 | 89.769997 | 8.076712 | 7.721296 | 1.00 | 20000 | UF | 537046683 | 8.0 | 1.560000 | 0.860000 | 1.560000 | 0.860000 |
| 19520 | 2021-08-06 | BARAU-W | ARAUCO | Forestal | BE | AA | 2.46 | 1.12 | 97.669998 | 7.183562 | 6.655741 | 2.10 | 39000 | UF | 1142077344 | 7.0 | 1.326487 | 1.133513 | 1.326487 | 1.133513 |
| 19521 | 2021-08-06 | BBNSAY0620 | BBSCOTIABA | Banco | BB | AAA | 2.46 | 0.76 | 84.680000 | 9.347945 | 9.067208 | 0.60 | 100000 | UF | 2522238026 | 9.0 | 1.670000 | 0.790000 | 1.670000 | 0.790000 |
| 19522 | 2021-08-06 | BAGRS-D | AGROSUPER | Alimentos | BE | AA- | 2.50 | 1.33 | 106.639999 | 11.079452 | 5.176165 | 3.80 | 2500 | UF | 80657283 | 5.0 | 0.859461 | 1.640539 | 0.859461 | 1.640539 |
| 19523 | 2021-08-06 | BSKSA-E | SK | Industrial | BE | AA- | 2.58 | 1.34 | 107.860001 | 11.613699 | 5.589889 | 4.00 | 1000 | UF | 32613657 | 6.0 | 1.092974 | 1.487026 | 1.092974 | 1.487026 |
| 19524 | 2021-08-06 | BECOP-K | COPEC | Industrial | BE | AA | 2.60 | 1.30 | 98.099998 | 6.991781 | 6.493786 | 2.30 | 20000 | UF | 584139690 | 6.0 | 1.092974 | 1.507026 | 1.092974 | 1.507026 |
| 19525 | 2021-08-06 | BBIC770520 | BBICE | Banco | BB | AA | 2.60 | 0.88 | 90.750000 | 9.739726 | 9.018792 | 1.50 | 100000 | UF | 2713860727 | 9.0 | 1.670000 | 0.930000 | 1.670000 | 0.930000 |
| 19526 | 2021-08-06 | BENTE-N | ENTEL | Telecomunicaciones | BE | A+ | 2.65 | 1.31 | 98.370003 | 7.243836 | 6.648298 | 2.40 | 3000 | UF | 88389706 | 7.0 | 1.326487 | 1.323513 | 1.326487 | 1.323513 |
| 19527 | 2021-08-06 | BCMPC-O | CMPC | Forestal | BE | AA- | 2.66 | 1.21 | 93.339996 | 7.824658 | 7.317841 | 1.70 | 64000 | UF | 1784601099 | 7.0 | 1.326487 | 1.333513 | 1.326487 | 1.333513 |
| 19528 | 2021-08-06 | BFALA-AC | FALAB.SACI | Comercio | BE | AA | 2.78 | 1.20 | 93.349998 | 8.695890 | 7.982071 | 1.90 | 97500 | UF | 2724776777 | 8.0 | 1.560000 | 1.220000 | 1.560000 | 1.220000 |
| 19529 | 2021-08-06 | BPARC-AA | P.ARAUCO | Comercio | BE | AA | 2.83 | 1.29 | 89.559998 | 8.336986 | 7.847611 | 1.40 | 94000 | UF | 2512368655 | 8.0 | 1.560000 | 1.270000 | 1.560000 | 1.270000 |
| 19530 | 2021-08-06 | BINT-R0919 | BBINTERNAC | Banco | BB | AA- | 2.86 | 1.21 | 85.410004 | 9.076712 | 8.617292 | 1.00 | 500000 | UF | 12773246150 | 9.0 | 1.670000 | 1.190000 | 1.670000 | 1.190000 |
| 19531 | 2021-08-06 | BESTK20713 | BBESTADO | Banco | BB | AAA | 2.96 | 0.71 | 111.620003 | 21.915068 | 15.607936 | 3.70 | 50000 | UF | 1666293652 | 16.0 | 2.270000 | 0.690000 | 2.270000 | 0.690000 |
| 19532 | 2021-08-06 | BFACT-A | FACTOTAL | Financiero | BE | A+ | 3.18 | 3.91 | 96.790001 | 3.060274 | 1.766557 | 1.30 | 500 | UF | 14491595 | 2.0 | -1.170958 | 4.350958 | -1.170958 | 4.350958 |
| 19533 | 2021-08-06 | BPATI-E | PATIOCOMER | Inmobiliario | BE | A | 3.20 | 2.89 | 100.160004 | 3.693151 | 3.478062 | 3.25 | 50000 | UF | 1505916362 | 3.0 | -0.272505 | 3.472505 | -0.272505 | 3.472505 |
| 19534 | 2021-08-06 | UBCIB31219 | BBCREDITO | Banco | BU | AA | 3.26 | 0.91 | 77.269997 | 28.339726 | 20.577523 | 2.00 | 120000 | UF | 2768113308 | 21.0 | 2.270000 | 0.990000 | 2.270000 | 0.990000 |
| 19535 | 2021-08-06 | BCALI-A | CALICHERA | Minero | BE | A- | 4.25 | 3.02 | 101.410004 | 8.863014 | 5.930865 | 4.50 | 500 | UF | 15186394 | 6.0 | 1.092974 | 3.157026 | 1.092974 | 3.157026 |
| 19536 | 2021-08-06 | BSALF-B | SALFACORP | Construcción | BE | BBB- | 4.35 | 3.66 | 100.510002 | 7.821918 | 3.647272 | 4.50 | 500 | UF | 6351901 | 4.0 | 0.625948 | 3.724052 | 0.625948 | 3.724052 |
| 19537 | 2021-08-06 | BDRMS-D | DREAMS | Entretenimiento | BE | BBB | 7.30 | 6.12 | 83.610001 | 6.610959 | 5.688209 | 3.97 | 1000 | UF | 25290442 | 6.0 | 1.092974 | 6.207026 | 1.092974 | 6.207026 |
Bonus Points#
Modificar el lambda dentro del apply para que sólo aplique la función tir_promedio cuando el valor del campo vertice es NaN. Hay que partir con el DataFramecon los NaN.
trans_corp_uf_base_2 = trans_corp_uf.merge(
curvas_flat,
how='left',
on=['vertice', 'fecha']
)
trans_corp_uf_base_2.tail(20)
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19518 | 2021-08-06 | BAVSU-A1 | AUTOVESSUR | Concesionaria | BE | AA | 2.34 | 1.79 | 107.410004 | 7.364384 | 3.467707 | 4.50 | 10000 | UF | 169758561 | 3.0 | NaN |
| 19519 | 2021-08-06 | BSECD30319 | BBSECURITY | Banco | BB | AA | 2.42 | 0.91 | 89.769997 | 8.076712 | 7.721296 | 1.00 | 20000 | UF | 537046683 | 8.0 | 1.560000 |
| 19520 | 2021-08-06 | BARAU-W | ARAUCO | Forestal | BE | AA | 2.46 | 1.12 | 97.669998 | 7.183562 | 6.655741 | 2.10 | 39000 | UF | 1142077344 | 7.0 | NaN |
| 19521 | 2021-08-06 | BBNSAY0620 | BBSCOTIABA | Banco | BB | AAA | 2.46 | 0.76 | 84.680000 | 9.347945 | 9.067208 | 0.60 | 100000 | UF | 2522238026 | 9.0 | NaN |
| 19522 | 2021-08-06 | BAGRS-D | AGROSUPER | Alimentos | BE | AA- | 2.50 | 1.33 | 106.639999 | 11.079452 | 5.176165 | 3.80 | 2500 | UF | 80657283 | 5.0 | NaN |
| 19523 | 2021-08-06 | BSKSA-E | SK | Industrial | BE | AA- | 2.58 | 1.34 | 107.860001 | 11.613699 | 5.589889 | 4.00 | 1000 | UF | 32613657 | 6.0 | NaN |
| 19524 | 2021-08-06 | BECOP-K | COPEC | Industrial | BE | AA | 2.60 | 1.30 | 98.099998 | 6.991781 | 6.493786 | 2.30 | 20000 | UF | 584139690 | 6.0 | NaN |
| 19525 | 2021-08-06 | BBIC770520 | BBICE | Banco | BB | AA | 2.60 | 0.88 | 90.750000 | 9.739726 | 9.018792 | 1.50 | 100000 | UF | 2713860727 | 9.0 | NaN |
| 19526 | 2021-08-06 | BENTE-N | ENTEL | Telecomunicaciones | BE | A+ | 2.65 | 1.31 | 98.370003 | 7.243836 | 6.648298 | 2.40 | 3000 | UF | 88389706 | 7.0 | NaN |
| 19527 | 2021-08-06 | BCMPC-O | CMPC | Forestal | BE | AA- | 2.66 | 1.21 | 93.339996 | 7.824658 | 7.317841 | 1.70 | 64000 | UF | 1784601099 | 7.0 | NaN |
| 19528 | 2021-08-06 | BFALA-AC | FALAB.SACI | Comercio | BE | AA | 2.78 | 1.20 | 93.349998 | 8.695890 | 7.982071 | 1.90 | 97500 | UF | 2724776777 | 8.0 | 1.560000 |
| 19529 | 2021-08-06 | BPARC-AA | P.ARAUCO | Comercio | BE | AA | 2.83 | 1.29 | 89.559998 | 8.336986 | 7.847611 | 1.40 | 94000 | UF | 2512368655 | 8.0 | 1.560000 |
| 19530 | 2021-08-06 | BINT-R0919 | BBINTERNAC | Banco | BB | AA- | 2.86 | 1.21 | 85.410004 | 9.076712 | 8.617292 | 1.00 | 500000 | UF | 12773246150 | 9.0 | NaN |
| 19531 | 2021-08-06 | BESTK20713 | BBESTADO | Banco | BB | AAA | 2.96 | 0.71 | 111.620003 | 21.915068 | 15.607936 | 3.70 | 50000 | UF | 1666293652 | 16.0 | NaN |
| 19532 | 2021-08-06 | BFACT-A | FACTOTAL | Financiero | BE | A+ | 3.18 | 3.91 | 96.790001 | 3.060274 | 1.766557 | 1.30 | 500 | UF | 14491595 | 2.0 | -1.170958 |
| 19533 | 2021-08-06 | BPATI-E | PATIOCOMER | Inmobiliario | BE | A | 3.20 | 2.89 | 100.160004 | 3.693151 | 3.478062 | 3.25 | 50000 | UF | 1505916362 | 3.0 | NaN |
| 19534 | 2021-08-06 | UBCIB31219 | BBCREDITO | Banco | BU | AA | 3.26 | 0.91 | 77.269997 | 28.339726 | 20.577523 | 2.00 | 120000 | UF | 2768113308 | 21.0 | NaN |
| 19535 | 2021-08-06 | BCALI-A | CALICHERA | Minero | BE | A- | 4.25 | 3.02 | 101.410004 | 8.863014 | 5.930865 | 4.50 | 500 | UF | 15186394 | 6.0 | NaN |
| 19536 | 2021-08-06 | BSALF-B | SALFACORP | Construcción | BE | BBB- | 4.35 | 3.66 | 100.510002 | 7.821918 | 3.647272 | 4.50 | 500 | UF | 6351901 | 4.0 | 0.625948 |
| 19537 | 2021-08-06 | BDRMS-D | DREAMS | Entretenimiento | BE | BBB | 7.30 | 6.12 | 83.610001 | 6.610959 | 5.688209 | 3.97 | 1000 | UF | 25290442 | 6.0 | NaN |
trans_corp_uf_base_2['tir_promedio'] = trans_corp_uf_base.apply(
lambda row: row['tir_promedio'] if row['tir_promedio'] else interpola(
row['fecha'], row['vertice'], curvas_flat),
# lambda es una función sin nombre
axis=1
)
trans_corp_uf_base_2.tail(20)
| fecha | nemotecnico | emisor | sector | tipo_deuda | clasificacion_riesgo | tir | spread | precio | plazo | duracion | tasa_emision | cantidad | moneda | monto_clp | vertice | tir_promedio | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 19518 | 2021-08-06 | BAVSU-A1 | AUTOVESSUR | Concesionaria | BE | AA | 2.34 | 1.79 | 107.410004 | 7.364384 | 3.467707 | 4.50 | 10000 | UF | 169758561 | 3.0 | -0.272505 |
| 19519 | 2021-08-06 | BSECD30319 | BBSECURITY | Banco | BB | AA | 2.42 | 0.91 | 89.769997 | 8.076712 | 7.721296 | 1.00 | 20000 | UF | 537046683 | 8.0 | 1.560000 |
| 19520 | 2021-08-06 | BARAU-W | ARAUCO | Forestal | BE | AA | 2.46 | 1.12 | 97.669998 | 7.183562 | 6.655741 | 2.10 | 39000 | UF | 1142077344 | 7.0 | 1.326487 |
| 19521 | 2021-08-06 | BBNSAY0620 | BBSCOTIABA | Banco | BB | AAA | 2.46 | 0.76 | 84.680000 | 9.347945 | 9.067208 | 0.60 | 100000 | UF | 2522238026 | 9.0 | 1.670000 |
| 19522 | 2021-08-06 | BAGRS-D | AGROSUPER | Alimentos | BE | AA- | 2.50 | 1.33 | 106.639999 | 11.079452 | 5.176165 | 3.80 | 2500 | UF | 80657283 | 5.0 | 0.859461 |
| 19523 | 2021-08-06 | BSKSA-E | SK | Industrial | BE | AA- | 2.58 | 1.34 | 107.860001 | 11.613699 | 5.589889 | 4.00 | 1000 | UF | 32613657 | 6.0 | 1.092974 |
| 19524 | 2021-08-06 | BECOP-K | COPEC | Industrial | BE | AA | 2.60 | 1.30 | 98.099998 | 6.991781 | 6.493786 | 2.30 | 20000 | UF | 584139690 | 6.0 | 1.092974 |
| 19525 | 2021-08-06 | BBIC770520 | BBICE | Banco | BB | AA | 2.60 | 0.88 | 90.750000 | 9.739726 | 9.018792 | 1.50 | 100000 | UF | 2713860727 | 9.0 | 1.670000 |
| 19526 | 2021-08-06 | BENTE-N | ENTEL | Telecomunicaciones | BE | A+ | 2.65 | 1.31 | 98.370003 | 7.243836 | 6.648298 | 2.40 | 3000 | UF | 88389706 | 7.0 | 1.326487 |
| 19527 | 2021-08-06 | BCMPC-O | CMPC | Forestal | BE | AA- | 2.66 | 1.21 | 93.339996 | 7.824658 | 7.317841 | 1.70 | 64000 | UF | 1784601099 | 7.0 | 1.326487 |
| 19528 | 2021-08-06 | BFALA-AC | FALAB.SACI | Comercio | BE | AA | 2.78 | 1.20 | 93.349998 | 8.695890 | 7.982071 | 1.90 | 97500 | UF | 2724776777 | 8.0 | 1.560000 |
| 19529 | 2021-08-06 | BPARC-AA | P.ARAUCO | Comercio | BE | AA | 2.83 | 1.29 | 89.559998 | 8.336986 | 7.847611 | 1.40 | 94000 | UF | 2512368655 | 8.0 | 1.560000 |
| 19530 | 2021-08-06 | BINT-R0919 | BBINTERNAC | Banco | BB | AA- | 2.86 | 1.21 | 85.410004 | 9.076712 | 8.617292 | 1.00 | 500000 | UF | 12773246150 | 9.0 | 1.670000 |
| 19531 | 2021-08-06 | BESTK20713 | BBESTADO | Banco | BB | AAA | 2.96 | 0.71 | 111.620003 | 21.915068 | 15.607936 | 3.70 | 50000 | UF | 1666293652 | 16.0 | 2.270000 |
| 19532 | 2021-08-06 | BFACT-A | FACTOTAL | Financiero | BE | A+ | 3.18 | 3.91 | 96.790001 | 3.060274 | 1.766557 | 1.30 | 500 | UF | 14491595 | 2.0 | -1.170958 |
| 19533 | 2021-08-06 | BPATI-E | PATIOCOMER | Inmobiliario | BE | A | 3.20 | 2.89 | 100.160004 | 3.693151 | 3.478062 | 3.25 | 50000 | UF | 1505916362 | 3.0 | -0.272505 |
| 19534 | 2021-08-06 | UBCIB31219 | BBCREDITO | Banco | BU | AA | 3.26 | 0.91 | 77.269997 | 28.339726 | 20.577523 | 2.00 | 120000 | UF | 2768113308 | 21.0 | 2.270000 |
| 19535 | 2021-08-06 | BCALI-A | CALICHERA | Minero | BE | A- | 4.25 | 3.02 | 101.410004 | 8.863014 | 5.930865 | 4.50 | 500 | UF | 15186394 | 6.0 | 1.092974 |
| 19536 | 2021-08-06 | BSALF-B | SALFACORP | Construcción | BE | BBB- | 4.35 | 3.66 | 100.510002 | 7.821918 | 3.647272 | 4.50 | 500 | UF | 6351901 | 4.0 | 0.625948 |
| 19537 | 2021-08-06 | BDRMS-D | DREAMS | Entretenimiento | BE | BBB | 7.30 | 6.12 | 83.610001 | 6.610959 | 5.688209 | 3.97 | 1000 | UF | 25290442 | 6.0 | 1.092974 |