Home / Questions / 252089
Accepted
efueyo
Asked: 2020-04-06 14:40:43 +0800 CST 2020-04-06 14:40:43 +0800 CST

Operations with columns of DataFrames

  • 772

I have the following DataFrame

d = {'Ticker': ["Total_Pond", "Total_Pond", "Total_Pond", "ValorRelativo", "Avantage","Acatis" ],
        'Tipo_Inv': ["FI", "FI", "FI", "FI","FI", "FI"], 'Fact_Pond': [2.313857e+06, 1.342821e+06,3.486820e+06,2.788324e+06, 4.929096e+06, 2.663564e+06],
        'Total_Pond': [-53196.922835, -37853.091155, -114935.107120, -26013.111409,374923.428199, -235590.992103 ]}
df = pd.DataFrame(data=d)
df

I try to calculate the weighted average interest of each value, for which I pivot the table and add the column df_Imp_value with the result of the division ["Total_Pond"] by df ["Fact_Pond"] 

# Interes medio ponderado de cada valor
df_Imp_valor = pd.pivot_table(df,index=["Ticker"],values=['Fact_Pond', 'Total_Pond' ],aggfunc=np.sum)
df_Imp_valor["Int_Med_Pond_Val"] = df ["Total_Pond"]/  df ["Fact_Pond"]
df_Imp_valor[:3]

The result is not correct, since it adds NaN to the value of that column.

          Fact_Pond     Total_Pond     Int_Med_Pond_Val
Ticker          
Acatis    2663564.0     -235590.992103    NaN
Avantage    4929096.0   374923.428199      NaN
Total_Pond  7143498.0   -205985.121110     NaN

I check in another way, that the result of the division is a number

a = df ["Total_Pond"]
b = df ["Fact_Pond"] 
a/b

0   -0.022991
1   -0.028189
2   -0.032963
3   -0.009329
4    0.076063
5   -0.088450
dtype: float64

Surely I am obsessed. What is the mistake I make? Thanks for the help

pandas

1 Answers

  1. Best Answer

    I understand that you want to do the division on the result of pivoting, and not on the columns of the original table.

    The problem is that here:

    df_Imp_valor["Int_Med_Pond_Val"] = df ["Total_Pond"]/  df ["Fact_Pond"]

    you split columns from the original dataframe df but assign the result to the pivoted result dataframe, which has other indices. Since the indices don't match, there are no rows corresponding to the ticker names in the split result, so it fills it with NaN.

    Doing it like this:

    df_Imp_valor = pd.pivot_table(df,index=["Ticker"],values=['Fact_Pond', 'Total_Pond' ],aggfunc=np.sum)
df_Imp_valor["Int_Med_Pond_Val"] = df_Imp_valor["Total_Pond"]/  df_Imp_valor["Fact_Pond"]
df_Imp_valor

    The result is already correct:

                  Fact_Pond     Total_Pond  Int_Med_Pond_Val
Ticker                                                   
Acatis         2663564.0 -235590.992103         -0.088450
Avantage       4929096.0  374923.428199          0.076063
Total_Pond     7143498.0 -205985.121110         -0.028835
ValorRelativo  2788324.0  -26013.111409         -0.009329
    • 3