0

I trying to do a match with the following 2 dataframe:

df_co:

Cntr No Labour Material Amount BHCU 2604370 0.0 82.5 82.5 BHCU 2604370 24.0 22.0 46.0

df:

Cntr No Total BHCU 2604370 82.0 BHCU 2604370 46.0

code:

 df['Tally'] = ((df_co['Cntr No'].isin(df['Cntr No'])) & ((df_co['Labour'].isin(df['Total'])) | (df_co['Material'].isin(df['Total'])) | (df_co['Amount'].isin(df['Total'])))).map({True:'Yes',False:'No'})

It should not give me a match as df_co 'Amount' is 82.5 and df 'Total' amount is 82.00.

But my result give me both matched.

Result:

Cntr No Total Tally BHCU 2604370 82.0 Yes BHCU 2604370 46.0 Yes

Suspect the code comparison is wrong.

convert to float:

 a = df.iloc[:, :5] b = df.iloc[:,5:29].apply(lambda x : x.str.extract('(\d+)',expand=False).astype(float)) c = df.iloc[:, 29:] df = pd.concat([a,b,c], axis=1)
Improve this question

1 Answer 1

Reset to default
1

Use:

df = df.merge(df_co.melt('Cntr No', value_name='Total', var_name='Tally'), on=['Cntr No', 'Total'], how='left') df['Tally'] = df['Tally'].notnull().map({True:'Yes',False:'No'}) print (df) Cntr No Total Tally 0 BHCU 2604370 82.0 No 1 BHCU 2604370 46.0 Yes
Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

further checking it seem the number is originally 82.50 ,it only changed to 82.0 after converted to float type. pls see my post for the updated code.
@okl - I think need x.str.extract('(\d+\.\d+)' - from this
@okl - is possible share data if not confidental?
oh my mistake, the value is still there, only 0 changed to nan. is it possible to change nan back to 0? i tried df = df.fillna('0') but for error ...AttributeError: ('Can only use .str accessor with string values, which use np.object_ dtype in pandas', 'occurred at index SERVICE INT / EX WASH40 FEET')
maybe need b = df.iloc[:,5:29].fillna('0').astype(str)
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.