Modify values in a DataFrame index based on another index value

Question

I'm new to pandas and working with indices, especially MultiIndex. I have a DataFrame as such:

df = pd.DataFrame({
    'ID':[1,2,1,2],
    'Measurement': ['ScanA', 'ScanA', 'ScanB', 'ScanB'],
    'Result':[0.1,0.2,0.5,0.7],
    'ResultType':['A','B','C','B']})
piv = df.pivot(index = 'ID', columns = 'Measurement', values = ['Result', 'ResultType'])

This creates two indices Result and Type, but I'd like to modify the values in the Result index based on the Type value.

For example, if Type == 'C' then I want the corresponding Result to be -1.

Another example would be if Type in ('A', 'B') then I want to set Result to be 0 if < 0.5 else 1

How can I programatically do this without looping through each row/column?

Output for piv.Result would look like:

Measurement     ScanA   ScanB
ID      
1               0.0     -1
2               0.0     1

What is your expected output dataframe?

Scott Boston
– Scott Boston

2019-02-26 20:38:24 +00:00
Commented Feb 26, 2019 at 20:38 — Scott Boston
– Scott Boston, Commented Feb 26, 2019 at 20:38

Vaishali · Accepted Answer · 2019-02-26 20:43:21Z

3

You can first modify the dataframe and then pivot

df.loc[df['Type'] == 'C', 'Result'] = -1
df.loc[(df['Type'].isin(['A', 'B'])) & (df['Result'] < 0.5), 'Result'] = 0
df.loc[(df['Type'].isin(['A', 'B'])) & (df['Result'] >= 0.5), 'Result'] = 1

df.pivot(index = 'ID', columns = 'Measurement', values = ['Result', 'Type'])

            Result          Type
Measurement ScanA   ScanB   ScanA   ScanB
ID              
1           0       1       A       A
2           0       -1      B       C

Edit: An alternative using np.select as @Wen-Ben suggested

cond = [df['Type'] == 'C', (df['Type'].isin(['A', 'B'])) & (df['Result'] < 0.5), (df['Type'].isin(['A', 'B'])) & (df['Result'] >= 0.5)]
choice = [-1, 0, 1]
df['Result'] = np.select(cond, choice)

df.pivot(index = 'ID', columns = 'Measurement', values = ['Result', 'Type'])

edited Feb 26, 2019 at 20:43

answered Feb 26, 2019 at 20:40

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BENY Over a year ago

May check with np.select

TomNash Over a year ago

I like the use np.select since these conditions may need to be adjusted with tuning over time.

cs95 · Accepted Answer · 2019-02-26 20:41:44Z

3

As an alternative to what Vaishali suggested, you can use numpy.where to modify piv after it is generated.

t = piv['Type']  
r = piv['Result'].astype(float)

piv.loc[:, 'Result'] = np.where(
    t == 'A', np.where(r < 0.5, 0, 1), np.where(t == 'C', -1, r))
piv

            Result        Type      
Measurement  ScanA ScanB ScanA ScanB
ID                                  
1              0.0   1.0     A     A
2              0.2  -1.0     B     C

answered Feb 26, 2019 at 20:41

cs95

406k106 gold badges744 silver badges797 bronze badges

Comments

Daniel Weber · Accepted Answer · 2019-02-26 21:34:08Z

There is a method in the pandas library called loc() which allows the user to find the values of the row through calling its label. This function, along with the conditional selection offered by pandas, allows the user to find the row where certain conditions are true (similar to the WHERE functionality found in SQL) and set values for other columns in that row. Using these methods, I have added a portion to your code that sets the 'Result' column to -1 when 'Type' == 'A'. You can apply the same structure to alter other 'Result' rows based on 'Type' values.

df = pd.DataFrame({
    'ID':[1,2,1,2],
    'Measurement': ['ScanA', 'ScanA', 'ScanB', 'ScanB'],
    'Result':[0.1,0.2,0.5,0.7],
    'Type':['A','B','A','C']})
piv = df.pivot(index = 'ID', columns = 'Measurement', values = ['Result', 'Type'])
df.loc[df['Type'] == 'C', ['Result']] = -1
print(df)

Collectives™ on Stack Overflow

Modify values in a DataFrame index based on another index value

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related