Compare data within a dataframe and add new column with modified data values in that column

Question

Attached is my data frame and I want to compare column SOI priority and column %stake and form comment accordingly. I tried the below code.

treasury_shares['Priority comment']=""

    temp=round(treasury_shares['%Stake'] * 100, 0)
treasury_shares['%Stake'] = round(treasury_shares['%Stake'] * 100, 0).astype(str) + "%"
    # treasury_shares["%Stake"] = treasury_shares["%Stake"].str.replace(".0", "")
    treasury_shares = treasury_shares.reindex(
        columns=["performance_id", "SOI priority", "Date", "issued_shares_as_reported",
                 "share_level",
                 "share_be", "%Stake","Priority comment"])
    if((temp>10)&(treasury_shares['SOI priority']==1)):
        treasury_shares['Priority comment'] = 'SOI'+treasury_shares['SOI priority']+'&Stake>10'

I am getting the following error. line 1329, in nonzero raise ValueError( ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Attached is the data frame image

+-------------+-------------+------------------+
| SOI_prority |   %Stake    | Priority_comment |
+-------------+-------------+------------------+
|           1 |         44% |   SOI1&Stake>10% |
+-------------+-------------+------------------+

please fill your data here: senseful.github.io/text-table To avoid making people lost their time trying to help you because they won't be typing the data from your excel. — renan
– renan, Commented Aug 15, 2022 at 18:06
I have filled. Can you please check if you require some more information? — Pratik Pathare
– Pratik Pathare, Commented Aug 15, 2022 at 18:14
So where is the table? I can't see it, please update your post with the table — renan
– renan, Commented Aug 15, 2022 at 18:18
Added. I have added one row of my table with the required columns in the data frame — Pratik Pathare
– Pratik Pathare, Commented Aug 15, 2022 at 18:20

renan · Accepted Answer · 2022-08-15 18:53:35Z

1

import pandas as pd
import numpy as np

data = {
    'performance_id': ['ASD'],
    'SOI priority': ['1'],
    'Date': ['31-Mar-22'],
    'issued_shares_as_reported': ['6,06,13,663'],
    'share_level': ['2,55,85,542'],
    'share_be': ['3,42,28,121'],
    '%Stake': ['0.44'],
    'Priority': ['P1'],
    'Priority comment': ['SOI1 & Stake>10%'],

}
treasury_shares = pd.DataFrame(data)

treasury_shares['Priority comment'] = ""

temp = treasury_shares['%Stake'].astype(float) * 100

# print(temp)
# treasury_shares['%Stake'] = round(treasury_shares['%Stake'].astype(int) * 100, 0).astype(str) + "%"
# treasury_shares["%Stake"] = treasury_shares["%Stake"].str.replace(".0", "")
treasury_shares = treasury_shares.reindex(
    columns=["performance_id", "SOI priority", "Date", "issued_shares_as_reported",
             "share_level",
             "share_be", "%Stake", "Priority comment"])

# creating conditional masks, where the condition that you want will be = 1, you can also use boolean like = True/False
treasury_shares['new_conditional'] = np.where(
    (temp > 10) &
    (treasury_shares['SOI priority'].astype('int32') == 1),
    1, 0
).astype('int32')

# Using the mask for your conditionals, where the same column is changed
treasury_shares['Priority comment'] = np.where(treasury_shares['new_conditional'] == 1,
                                               'SOI' + (treasury_shares[
                                                   'SOI priority']).astype('string') + '&Stake>10',
                                               treasury_shares['Priority comment'])

print(treasury_shares['Priority comment'])

# Panda doesn't work with 'if' clause, this is built-in for python, but panda is not built-in
# if((temp>10)&(treasury_shares['SOI priority']==1)):
#     treasury_shares['Priority comment'] = 'SOI'+treasury_shares['SOI priority']+'&Stake>10'

answered Aug 15, 2022 at 18:53

renan

1407 bronze badges

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

3 Comments

Pratik Pathare Over a year ago

Thank you @renan but I am facing one issue in this. The code is not comparing the value greater than or equal to 10 properly. Means in some cases even if the value is greater than 10, the comment is not given in some cases.

renan Over a year ago

I did some tests, in my code it's working smoothly. Maybe it's related to your data type or even the numbers that you're getting here in "temp", checkout this output and see if the data type or numbers are indeed the ones that you're expecting..

renan Over a year ago

@PratikPathare Nice, glad to be able to help you :)

Collectives™ on Stack Overflow

Compare data within a dataframe and add new column with modified data values in that column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related