I have data that looks like the below (edited for confidentiality):

And another sheet I want to match against:

I would like to add the “pattern” column from sheet 2 to sheet 1 based on matching route, trip, direction. For example, for R3 with trip ID 1330, whose service is sunday and has direction L, should have a pattern element of LP inserted. I have initially tried using a df merge first by mapping the string names in “service” to a matching datetime format, then doing a left merge in pandas, but I get a bunch of extra rows.

merged_df = pd.merge(route_df, pattern_df[['Route', 'Direction', 'Date', 'Pattern']], 
                     on=['Route', 'Direction', 'Date'], how='left')

The easy way out would be to set up a for loop to iterate through each row and define the conditions in a dictionary, however my dataset is several millions of rows and it would be very computationally expensive to do it this way.