I have 2 dataframes, df1,and df2 as below.



and df2


I would like to lookup "result" from df1 and fill into df2 by "Mode" as below format. Note "Mode" has become my column names and the results have been filled into corresponding columns.

also note that "ID" from df2 may not necessary equal to "ID" from df1.For example, I am only interested in 4 IDs (A01,A03,A04 and A05, no A02) while df1 may contain more IDs

enter image description here

I tried to use below code but it doesn't give me a good result.

merged_df = pd.merge(df2, df1,left_on = 'ID', right_on = 'ID', how='outer')

How can I do this?


1 Answer 1


I would recommend "pivoting" the first dataframe, then filtering for the IDs you actually care about.

Something like this:

useful_ids = [
df2 = df1.pivot(index='ID', columns='Mode')
df2 = df2.filter(items=useful_ids, axis='index')
  • $\begingroup$ the pivoting idea looks good, but i have trouble to filter. I tried to get the useful_ids from dateframe <pre> idlist = df2 ['ID'].tolist() <code> and do the filter like this <pre> df2 =df2.filter(item=idlist, axis= 'index') <code> and i failed with unexpected keyword argument 'item' $\endgroup$
    – LU Che
    Commented Mar 17, 2021 at 23:19
  • $\begingroup$ try "items" with an s instead of "item" $\endgroup$
    – zachdj
    Commented Mar 17, 2021 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.