Proper way to join data based on coditions-CodePudding

I want to add a new column to a datframe "table" (name: conc) which uses the values in columns (plate, ab) to get the numeric value from the dataframe "concs"

Below is what I mean, with the dataframe "exp" used to show what I expect the data to look like

what is the proper way to do this. Is it using some multiple condition, or do I need to reshape the concs dataframe somehow?

CodePudding user response：

Use DataFrame.melt with left join for new column concs, if no match is created NaNs:

exp = concs.melt('plate', var_name='ab', value_name='concs').merge(table,on=['plate', 'ab'], how='left')

Solution should be simplify - if same columns names 'plate', 'ab' in both DataFrames and need merge by both is possible omit on parameter:

exp = concs.melt('plate', var_name='ab', value_name='concs').merge(table, how='left')

CodePudding user response：

First melt the concs dataframe and then merge with table:

out = concs.melt(id_vars=['plate'],
                 value_vars=concs.columns.drop('plate').tolist(),
                 var_name='ab').merge(table, on=['plate', 'ab'
        ]).rename(columns={'value': 'concs'})

or just make good use of parameters of melt like in jezraels' answer:

out = concs.melt(id_vars=['plate'],
                 value_name='concs',
                 var_name='ab').merge(table, on=['plate', 'ab'])