Python Forum

Full Version: Pandas, Assign a value in a row, to another column based on a condition
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I'm trying to put a particular value in the data frame row to other rows also if it matches a condition. However i'm not getting what is expected. Can someone help to get the value as shown in the expected value column. Thanks in advance!                                                               Result I'm getting is    Code  Expected_Cum_Value   Value  ValueCount  ResultValue

0   100                1000     NaN         NaN          NaN
1   100                1000  1000.0         1.0       1000.0
2   100                1000  2000.0         2.0          NaN
3   200                2000     NaN         NaN          NaN
4   200                2000  2000.0         1.0       2000.0
df = pd.DataFrame(data = {'Code':[100,100,100,200,200],
                          'Value':[np.nan,1000,2000,np.nan,2000],
                          'ValueCount':[np.nan,1,2,np.nan,1],
                          'Expected_Cum_Value':[1000,1000,1000,2000,2000],})


for i in df['Code'].unique():
    print(i)
    Insu1= df.loc[((df['Code'] == i)&(df['ValueCount']==1)), 'Value']
    print(Insu1)
    df.loc[((df['Code'] == i)), 'ResultValue'] = Insu1
Not quite sure what you are trying to do. In the for loop, are you trying to set Insu1 to the contents of the Value column if Code and ValueCount are both 1? And then are you trying to set the value of ResultValue to Insu1 in that same row?
(Oct-14-2020, 11:21 AM)jefsummers Wrote: [ -> ]Not quite sure what you are trying to do. In the for loop, are you trying to set Insu1 to the contents of the Value column if Code and ValueCount are both 1? And then are you trying to set the value of ResultValue to Insu1 in that same row?

Yes, In the for loop, for each "Code" if the Code field is equal to code (or i) and Value Count is equal to 1, then the Value in "Value" field is assigned to Variable "Insu1". Then such Value has to be assigned to entire "ResultValue" field when Code filed is equal to i.
Something like
import pandas as pd
import numpy as np
df = pd.DataFrame(data = {'Code':[100,100,100,200,200],
                          'Value':[np.nan,1000,2000,np.nan,2000],
                          'ValueCount':[np.nan,1,2,np.nan,1],
                          'Expected_Cum_Value':[1000,1000,1000,2000,2000],})
df['Result_Value'] = np.where(df['ValueCount'] == 1, df['Value'],'')
df

Can't wait for formatting to return....
J
(Oct-14-2020, 07:19 PM)jefsummers Wrote: [ -> ]Something like
import pandas as pd
import numpy as np
df = pd.DataFrame(data = {'Code':[100,100,100,200,200],
                          'Value':[np.nan,1000,2000,np.nan,2000],
                          'ValueCount':[np.nan,1,2,np.nan,1],
                          'Expected_Cum_Value':[1000,1000,1000,2000,2000],})
df['Result_Value'] = np.where(df['ValueCount'] == 1, df['Value'],'')
df

Can't wait for formatting to return....
J

Thanks for the reply!
I'm expecting to populate the result of np.where(df['ValueCount'] == 1, df['Value'],'') to subset of the entire df['Result_Value'] column if df['Code'] is equal to df['code'] value of the particular instance. You can find the expected result in df['Expected_Cum_Value'] column.
I think I got it. Used a 2 pass approach - first pass to pick up the codes and values to be set in a dictionary. Second pass to set the values.

import pandas as pd
import numpy as np

df = pd.DataFrame(data = {'Code':[100,100,100,200,200],
                          'Value':[np.nan,1000,2000,np.nan,2000],
                          'ValueCount':[np.nan,1,2,np.nan,1],
                          'Expected_Cum_Value':[1000,1000,1000,2000,2000],})
df['Result_Value'] = ''
code_list = dict()
for row in df.itertuples() :
    if row.ValueCount == 1 :
        code_list[row.Code] = row.Value
for row_index in range(len(df)) :
    if df.iloc[row_index,0] in code_list:
        df.iloc[row_index,4] = code_list[df.iloc[row_index,0]]
df
Output:
Code Value ValueCount Expected_Cum_Value Result_Value 0 100 NaN NaN 1000 1000 1 100 1000.0 1.0 1000 1000 2 100 2000.0 2.0 1000 1000 3 200 NaN NaN 2000 2000 4 200 2000.0 1.0 2000 2000
(Oct-15-2020, 08:08 PM)jefsummers Wrote: [ -> ]I think I got it. Used a 2 pass approach - first pass to pick up the codes and values to be set in a dictionary. Second pass to set the values.

import pandas as pd
import numpy as np

df = pd.DataFrame(data = {'Code':[100,100,100,200,200],
                          'Value':[np.nan,1000,2000,np.nan,2000],
                          'ValueCount':[np.nan,1,2,np.nan,1],
                          'Expected_Cum_Value':[1000,1000,1000,2000,2000],})
df['Result_Value'] = ''
code_list = dict()
for row in df.itertuples() :
    if row.ValueCount == 1 :
        code_list[row.Code] = row.Value
for row_index in range(len(df)) :
    if df.iloc[row_index,0] in code_list:
        df.iloc[row_index,4] = code_list[df.iloc[row_index,0]]
df
Output:
Code Value ValueCount Expected_Cum_Value Result_Value 0 100 NaN NaN 1000 1000 1 100 1000.0 1.0 1000 1000 2 100 2000.0 2.0 1000 1000 3 200 NaN NaN 2000 2000 4 200 2000.0 1.0 2000 2000
@jefsummers Thank you so much!!!