How to set row values base on index levels in Pandas?-CodePudding

I would like to add a column to a Pandas dataframe and set values based on index levels 0 and 1. For example, set the value to buy for indexes where level 0 is hom and column delta is greater than 0. And set the value to sell for indexes where level 0 is hom and column delta is lower than 0. There are other rules for different value of level 0.

How can I do that ?

>df
         delta
fut ABC  15284.233222
pos DEF  0.248976
    POL  0.002041
    ABC  0.043585
hom YTY  0.054100
    MNN -0.356873

This is the desired output:

>df
         delta    new_col
fut ABC  15284.23 nan
pos DEF  0.248976 nan
    POL  0.002041 nan
    ABC  0.043585 nan
hom YTY  0.054100 buy
    MNN -0.356873 sell

I can filter the dataframe with loc for not sure how to create the new column.

df.loc[df.index.get_level_values(level=0) == 'hom'] > 0

         delta    new_col
hom YTY  0.054100 True
    MNN -0.356873 False

CodePudding user response：

You can access index values with df.index.get_level_values, then np.where/select, for example:

is_hom = df.index.get_level_values(level=0) == 'hom'

df['new_col'] = np.select((!is_hom, df.delta > 0), (np.nan, 'buy'), 'sell')

CodePudding user response：

No need to do any boolean masking since your data already has a usable index! You can simply use .loc to subset and create a new column.

import pandas as pd
import numpy as np

df.loc['hom', 'new_col'] = np.where(df.loc['hom', 'delta'] > 0, 'buy', 'sell')

print(df)
                delta new_col
fut ABC  15284.233222     NaN
pos DEF      0.248976     NaN
    POL      0.002041     NaN
    ABC      0.043585     NaN
hom YTY      0.054100     buy
    MNN     -0.356873    sell

CodePudding user response：

To do conditional manipulations like that I would move the index values to the columns:

df.index = df.index.rename(names=['a', 'b'])
df = df.reset_index()
df['new_col'] = None

# Assignments
df.loc[(df.a == 'hom') & (df.delta > 0), 'new_col'] = 'buy'
df.loc[(df.a == 'hom') & (df.delta < 0), 'new_col'] = 'sell'

# Reset index
df = df.set_index(['a', 'b'])

Result:

                delta new_col
a   b                        
fut ABC  15284.233222    None
pos DEF      0.248976    None
    POL      0.002041    None
    ABC      0.043585    None
hom YTY      0.054100     buy
    MNN     -0.356873    sell