CODEWITHSUNDEEP
pandas
David
David

Reputation: 1267

Create column with values only for some multiindex in pandas

I have a dataframe like this:

df = pd.DataFrame(np.random.randint(50, size=(4, 4),
        index=[['a', 'a', 'b', 'b'], [800, 900, 800, 900]],
        columns=['X', 'Y', 'r_value', 'z_value'])
df.index.names = ["dat", "recor"]

                    X     Y   r_value    z_value   
dat       recor                                                                             
a           800     14    28       12         18 
            900     47    34       59         49       
b           800     33    18       24         33
            900     18    25       44         19 
...

I want to apply a function to create a new column based on r_value that gives values only for the case of recor==900, so, in the end I would like something like:

                    X     Y   r_value    z_value    BB 
dat       recor                                                                             
a           800     14    28       12         18   NaN
            900     47    34       59         49     0   
b           800     33    18       24         33   NaN
            900     18    25       44         19     2
...

I have created the function like:

x = df.loc[pd.IndexSlice[:,900], "r_value"]
conditions = [x >=70, np.logical_and(x >= 40, x < 70), \
             np.logical_and(x >= 10, x < 40), x <10]
choices = [0, 1, 2, 3]
BB = np.select(conditions, choices)

So now I need to append BB as a column, filling with NaNs the rows corresponding to recor==800. How can I do it? I have tried a couple of ideas (not commented here) without result. Thx.

Upvotes: 0

Views: 33

Answers (1)

user9413641
user9413641

Reputation:

Try

df.loc[df.index.get_level_values('recor')==900, 'BB'] = BB

the part df.index.get_level_values('recor')==900 creates a boolean array with True where the index level "recor" equals 900

indexing using a columns that does not already exist, ie "BB" creates that new column.

The rest of the column should automatically be filled with NaN.

I cant test it since you didn't include a minimal reproducible example.

Upvotes: 1

Related Questions