Python Forum
Pandas, Assign a value in a row, to another column based on a condition
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pandas, Assign a value in a row, to another column based on a condition
#1
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
Reply
#2
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?
Reply
#3
(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.
Reply
#4
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
Reply
#5
(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.
Reply
#6
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
klllmmm likes this post
Reply
#7
(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!!!
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Get an average of the unique values of a column with group by condition and assign it klllmmm 0 222 Feb-17-2024, 05:53 PM
Last Post: klllmmm
  unable to remove all elements from list based on a condition sg_python 3 373 Jan-27-2024, 04:03 PM
Last Post: deanhystad
  Python Alteryx QS-Passing pandas dataframe column inside SQL query where condition sanky1990 0 690 Dec-04-2023, 09:48 PM
Last Post: sanky1990
  pandas : problem with conditional filling of a column Xigris 2 593 Jul-22-2023, 11:44 AM
Last Post: Xigris
  Sent email based on if condition stewietopg 1 804 Mar-15-2023, 08:54 AM
Last Post: menator01
  create new column based on condition arvin 12 2,133 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 798 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  Basic Pandas, obtaining a value from column and row JamesOzone 2 1,054 Jun-30-2022, 07:16 PM
Last Post: jefsummers
  select Eof extension files based on text list of filenames with if condition RolanRoll 1 1,475 Apr-04-2022, 09:29 PM
Last Post: Larz60+
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,573 Mar-30-2022, 11:05 PM
Last Post: Pedroski55

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020