CODEWITHSUNDEEP
pythonpandasif-statementdataframe
MCM
MCM

Reputation: 1511

Calculation function based on selected dataframe columns and values

I want to calculate based on a if statement a result as explained below. Somehow i receive only NaN and dont understand why? 

import pandas as pd

def Calculation(values6M, values6M3M):

    Calc = (values6M/(values6M3M/1000))          
    return Calc

df = pd.DataFrame([
        ['A', '6M3M', '1Y', 3.25],
        ['A', '6M3M', '2Y', 3.25],
        ['A', '6M3M', '3Y', 3.25],
        ['A', '6M3M', '4Y', 3.375],
        ['A', '6M3M', '5Y', 3.5],
        ['B', '6M', '1Y', 0.1475],
        ['B', '6M', '2Y', 0.15],
        ['B', '6M', '3Y', 0.155],
        ['B', '6M', '4Y', 0.18],
        ['B', '6M', '5Y', 0.21875],
        ['A', '3M', '1Y', 0.1113],
        ['A', '3M', '2Y', 0.1138],
        ['A', '3M', '3Y', 0.115],
        ['A', '3M', '4Y', 0.1175],
        ['A', '3M', '5Y', 0.1188]        
        ], columns=['Type', 'Course', 'Course_Period', 'Values'])

for index, row in df.iterrows():
    if row['Type'] in ['A', 'B'] and row['Course'] in ['6M', '6M3M']:

        test6M = df.loc[df['Course'] == '6M']        
        test6M3M = df.loc[df['Course'] == '6M3M']

        result = Calculation(test6M,test6M3M)
        print(result)

What I want as result is for the first value e.g. 

1Y: 0.1475/(3.25/1000) = 45.38461538

2Y: 46.15384615
3Y: 47.69230769
4Y: 53.33333333
5Y: 62.5

Upvotes: 0

Views: 46

Answers (2)

jpp
jpp

Reputation: 164693

This is one way, which is convenient if you want to see the inputs.

df2 = pd.pivot_table(df, index=['Course_Period'],
                     columns=['Type', 'Course'], values=['Values']).reset_index()

df2.columns = df2.columns = [' '.join(col).strip() for col in df2.columns.values]

df2['Result'] = df2['Values B 6M'] / df2['Values A 6M3M'] * 1000

#   Course_Period  Values A 3M  Values A 6M3M  Values B 6M     Result
# 0            1Y       0.1113          3.250      0.14750  45.384615
# 1            2Y       0.1138          3.250      0.15000  46.153846
# 2            3Y       0.1150          3.250      0.15500  47.692308
# 3            4Y       0.1175          3.375      0.18000  53.333333
# 4            5Y       0.1188          3.500      0.21875  62.500000

Upvotes: 1

BENY
BENY

Reputation: 323306

YOu can using isin for filter , and using groupby + pct_change get the ratio :-) 

(df.loc[df.Type.isin(['A','B'])&df.Course.isin(['6M', '6M3M'])].groupby('Course_Period').pct_change()+1).dropna()*1000
Out[294]: 
      Values
5  45.384615
6  46.153846
7  47.692308
8  53.333333
9  62.500000

Upvotes: 1

Related Questions