Merge dataframes base on Column and Row values

I have a dataframe that I'd like to merge with another dataframe with the same column values. Also with specified row values.

Dataframe 1

d = {'id': ['111', '222', '333'], 'queries': ['High', 'Mid', 'Low'], 'time_stay': ['High', 'Mid', 'Low']}  dd = pd.DataFrame(data=d)  

Dataframe 2

l = {'Features': ['queries', 'queries', 'queries', 'time_stay', 'time_stay', 'time_stay'], 'groups':['High', 'Mid', 'Low', 'High', 'Mid', 'Low'], 'parameters':[1.2, 1.1, 1.0, 1000, 2000, 3000]}  feature_data = pd.DataFrame(data=l)  feature_data  

I pivoted dataframe 2 to make the first row as columns.

feature_data = feature_data.T  feature_data.columns = feature_data.loc['Features', :]  

Then I merged it dd.merge(feature_data, on=list(feature_data.columns), how='left') As expected, pandas doesn't let me merge it because column queries is duplicated.

Expected output

What's a better way to do this ? thanks

https://stackoverflow.com/questions/66847271/merge-dataframes-base-on-column-and-row-values March 29, 2021 at 07:57AM