Posts: 107
Threads: 42
Joined: Oct 2016
Oct-14-2020, 05:34 AM
(This post was last modified: Oct-14-2020, 05:34 AM by klllmmm.)
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
Posts: 1,358
Threads: 2
Joined: May 2019
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?
Posts: 107
Threads: 42
Joined: Oct 2016
(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.
Posts: 1,358
Threads: 2
Joined: May 2019
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
Posts: 107
Threads: 42
Joined: Oct 2016
(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.
Posts: 1,358
Threads: 2
Joined: May 2019
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
Posts: 107
Threads: 42
Joined: Oct 2016
(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!!!
|