PANDAS: Do a calculation on pandas dataframe element based on a bool value in a separate dataframe

user_8217204 0
Viewed 53

I have 3 dataframes they are all the same index length but have a different number of columns. These are their outputs

df1: 
             HPMI  LQPN  VHJ  AEBV  WOY
2008-08-31     0     0    0     0    0
2008-09-30     0     0    0     0    0
2008-10-31     0     1    0     1    1
2008-11-30     1     1    1     0    0

df2: 
             HPMI  LQPN  VHJ  AEBV  WOY
2008-08-31     0     0    0     0    0
2008-09-30     0     0    0     0    0
2008-10-31     1     0    1     1    0
2008-11-30     0     1    0     1    1

df3:
                   HPMI        LQPN         VHJ        AEBV         WOY
2008-08-31  3.13172138  0.72709204  5.76874778  1.77557845  0.04098317
2008-09-30 -3.78816218 -0.67583590 -4.95433863 -1.67093250 -0.24929051
2008-10-31  0.05579709  0.29199789  0.00697116  1.05956179  0.30686995
2008-11-30  1.25459098  6.87369275  2.58265839  6.92676837  0.84632677

I would like to do a calculation for example in df2 if HPMI == 1 at date 2008-10-31 then I would like to do a calculation under the same column name and index value of df3 otherwise if HPMI == 0 then make df3 == 0.

This is what I have so far and it works but does not work with dataframes that have different amount of columns or rows.

I'm not sure if I'm phrasing the question correctly so please let me know if something is not clear and any help would be appreciated

def portfolio_returns(df1, df2, df3, n_stocks):
    
    weight = 1 / n_stocks 

    output_short = np.where(df1 == 1, (df3 * weight)* -1, 0)
    output_long = np.where(df2 == 1, df3 * weight, 0)
   
    output = output_short + output_long
    
    expected_returns = pd.DataFrame(output, columns=lookahead_returns.columns, index=lookahead_returns.index)
    
    return expected_returns
pandas dataframe numpy python
1 Answers

If need same number and order in all 3 DataFrames use Index.union with DataFrame.reindex, here is set 0 for not existed columns by parameter fill_value=0:

def portfolio_returns(df1, df2, df3, n_stocks):
    
    cols = df1.columns.union(df2.columns).union(df3.columns)
    df1 = df1.reindex(cols, axis=1, fill_value=0)
    df2 = df2.reindex(cols, axis=1, fill_value=0)
    df3 = df3.reindex(cols, axis=1, fill_value=0)
        
    weight = 1 / n_stocks 

    output_short = np.where(df1 == 1, (df3 * weight)* -1, 0)
    output_long = np.where(df2 == 1, df3 * weight, 0)
   
    output = output_short + output_long
    
    expected_returns = pd.DataFrame(output, columns=cols, index=lookahead_returns.index)
    
    return expected_returns
user_29010020
Related