2
$\begingroup$

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

df1

df1

and df2

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?

Improve this question
$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

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

Something like this:

useful_ids = [
    'A01',
    'A03',
    'A04',
    'A05',
]
df2 = df1.pivot(index='ID', columns='Mode')
df2 = df2.filter(items=useful_ids, axis='index')
Improve this answer
$\endgroup$
3
  • $\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
    Mar 17, 2021 at 23:19
  • $\begingroup$ try "items" with an s instead of "item" $\endgroup$
    – zachdj
    Mar 17, 2021 at 23:37
  • $\begingroup$ many thanks, it works $\endgroup$
    – LU Che
    Mar 18, 2021 at 0:08

Your Answer

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

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