CODEWITHSUNDEEP
pythonpandasnumpyjoindataframe
Xingfang Lee
Xingfang Lee

Reputation: 63

Merging dataframes with specific condition

I have two pandas dataframe. The first contain Course ID and Instructor ID and the second contain course ID, Rating and remarks. Please see example below.

First dataframe:

df1 = pd.DataFrame({"Course ID": ["MTH101.A", "MTH102.A", "MTH101.B", "MTH203.A", "MTH123.C"], "Instructor ID": ["X01", "T08", "C02", "D03", "Q01"]})

Second data frame:

df2 = pd.DataFrame({"Course ID": ["MTH101.A", "MTH102.X", "MTH203.A", "MTH101.B", "MTH123.Q"], "Rating": ["3.7", "4.1", "4.7", "2.9", "3.1"], "Remarks": ['B', 'A', 'A+', 'C', 'B']})

Unfortunately, data entry person has made mistake in the course ID in second dataframe. Such that in certain case only first few characters match with first data frame.

I wish to get a consolidated dataframe such as shown below: enter image description here

enter image description here

Any leads on this?

Upvotes: 1

Views: 311

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210922

We can merge by matching Course ID and append those that don't have "full match":

bad1 = np.where(~df1['Course ID'].isin(df2['Course ID']))[0]
bad2 = np.where(~df2['Course ID'].isin(df1['Course ID']))[0]

r = \
df1.merge(df2) \
   .append(pd.merge(df1.loc[bad1].assign(x=df1['Course ID'].str.split('\.').str[0]),
                    df2.loc[bad2].drop('Course ID',1).assign(x=df2['Course ID'].str.split('\.').str[0]),
                    on='x') \
             .drop('x', 1))

Result:

In [44]: r
Out[44]:
  Course ID Instructor ID Rating Remarks
0  MTH101.A           X01    3.7       B
1  MTH101.B           C02    2.9       C
2  MTH203.A           D03    4.7      A+
0  MTH102.A           T08    4.1       A
1  MTH123.C           Q01    3.1       B

Upvotes: 3

Related Questions