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
  Python Pandas: How do I extract all the >1000 data from a certain column? JaneTan 0 296 Jul-17-2021, 09:09 AM
Last Post: JaneTan
  Python Pandas: How do I sumproduct by rows with an if condition? JaneTan 2 518 Jul-13-2021, 11:36 AM
Last Post: jefsummers
  Pandas DataFrame combine rows by column value, where Date Rows are NULL rhat398 0 480 May-04-2021, 10:51 PM
Last Post: rhat398
Photo Creating column in python based on the other colums value count Bartek635 2 802 Apr-15-2021, 03:47 PM
Last Post: Bartek635
  Data extraction from a table based on column and row names tgottsc1 1 859 Jan-09-2021, 10:04 PM
Last Post: buran
  Copying column values up based on other column values codelines 1 596 Jan-03-2021, 05:55 PM
Last Post: codelines
  How to generate rows based on values in a column to fill missing values codesmatter 1 693 Oct-31-2020, 12:05 AM
Last Post: Larz60+
  Removing some elements from array based on a condition claw91 0 551 Oct-27-2020, 03:42 PM
Last Post: claw91
  How to diff pandas rows and modify column value Mekala 1 557 Sep-18-2020, 12:38 PM
Last Post: Mekala
  How to merge three DataFrames based on specific column Mekala 0 617 Sep-08-2020, 02:01 PM
Last Post: Mekala

Forum Jump:

User Panel Messages

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