In python pandas,How to use outer join using where condition?

Question

Table 1

S.No  BusNo Timings People
1     1234  3:05 pm  55
2     3456  3:30 pm  45
3     8945  3:45 pm  50

Table 2 
BusNo  Model
1234   Leyland
3456   Viking

Join this table using pandas for condition: busno model people count for people between 50 and 55 and group by model

Expected Output:

Table3

S.No BusNo Timings People Model
1    1234  3:05 pm  55    Leyland
3    8945  3:45 pm  50    Nan    

Answer 1

You can do a simple merge on those two dataframes and do a simple condition check inside loc to get the desired output like shown below.

df = pd.DataFrame()

df['S.No'] = [1, 2, 3]
df['BusNo'] = [1234, 3456, 8945]
df['Timings'] = ['3:05 pm', '3:30 pm', '3:45 pm']
df['People'] = [55, 45, 50]

df_ = pd.DataFrame()

df_['BusNo'] = [1234, 8945]
df_['Model'] = ['Leyland', 'viking']


merged = pd.merge(df, df_, on='BusNo', how='outer')

merged.loc[(merged['People'] >= 50) & (merged['People'] <= 55), :]