Home > Enterprise >  append column with values based on values of another column in the dataframe
append column with values based on values of another column in the dataframe

Time:12-30

# Sample of existing Dataframe
data = {'portfolio': ['40/60', '60/40', '80/20', '100/0']}

test_df = pd.DataFrame(data)

# print dataframe.
test_df


Output:

portfolio
40/60
60/40
80/20
100/20

I am trying to create new columns to include the name of the portfolio and model name based on the value in the existing column. Note: there are other several other rows that include these existing values/ratios that make up a complete allocation of each portfolio. So I need the portfolio name to align with the corresponding value in the "Portfolio" column. Here is the current code I am using below:

def test(df):
    column1 = []
    column2 = []
    for row in df['Portfolio']:
        if row == '40/60':
            column1.append('Portfolio 1')
            column2.append('Portfolio 1 Model')
        elif row == '60/40':
            column1.append('Portfolio 2')
            column2.append('Portfolio 2 Model')
        elif row == '80/20':
            column1.append('Portfolio 3')
            column2.append('Portfolio 3 Model')
        elif row == '100/0':
            column1.append('Portfolio 4')
            column2.append('Portfolio 4 Model')
        else:
            column1.append('N/A')
            column2.append('N/A')

    df['portfolio_name'] = column1
    df['model_name'] = column2

    return df

test(test_df)

Expected output:

portfolio.   portfolio_name.  model_name
40/60        Portfolio 1      Portfolio 1 Model
60/40        Portfolio 2      Portfolio 2 Model
80/20        Portfolio 3      Portfolio 3 Model
100/0        Portfolio 4      Portfolio 4 Model


Actual Output:

portfolio.   portfolio_name.  model_name
40/60        N/A              N/A
60/40        N/A              N/A
80/20        N/A              N/A
100/0        N/A              N/A

I am just not sure what I am missing here and why the values appending to the newly created columns are only recognizing the "else" condition?

CodePudding user response:

I think the problem is in the iterable of the for loop, you should use this instead:

for i, row in df['Portfolio'].items():

CodePudding user response:

Try to map your values with a dictionary:

names = {'40/60': {'portfolio_name': 'Portfolio 1', 'model_name': 'Portfolio 1 Model'},
         '60/40': {'portfolio_name': 'Portfolio 2', 'model_name': 'Portfolio 2 Model'},
         '80/20': {'portfolio_name': 'Portfolio 3', 'model_name': 'Portfolio 3 Model'},
         '100/0': {'portfolio_name': 'Portfolio 4', 'model_name': 'Portfolio 4 Model'},}

df = df.join(df['Portfolio'].map(names).dropna().apply(pd.Series))
print(df)

# Output
  Portfolio portfolio_name         model_name
0     40/60    Portfolio 1  Portfolio 1 Model
1     60/40    Portfolio 2  Portfolio 2 Model
2     80/20    Portfolio 3  Portfolio 3 Model
3     100/0    Portfolio 4  Portfolio 4 Model
4     50/50            NaN                NaN

Setup:

data = {'Portfolio': ['40/60', '60/40', '80/20', '100/0', '50/50']}
df = pd.DataFrame(data)

CodePudding user response:

data = {'portfolio': ['40/60', '60/40', '80/20', '100/0']}
df = pd.DataFrame(data)
df['portfolioName'] = "portfolio "   df.index.map(str)
df['modelName'] = "portfolio "   df.index.map(str)   " Model"
df = df.set_index('portfolio')

Results in the following

   portfolio  portfolioName   modelName
        
    40/60     portfolio 0     portfolio 0 Model
    60/40     portfolio 1     portfolio 1 Model
    80/20     portfolio 2     portfolio 2 Model
    100/0     portfolio 3     portfolio 3 Model
  • Related