lookup and fill some value from one dataframe to another

Question

lookup and fill some value from one dataframe to another

LU Che

2022年4月30日 14:03

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

df1

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

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?

Topic dataframe pandas python

Category Data Science

zachdj · Accepted Answer · 2021年3月17日 22:29

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')

lookup and fill some value from one dataframe to another

About