I don't know whether it's a bug or not because it's working well on my side. After all, pd.join is exactly equivalent to what the merge line does. Maybe @jbrightuniverse can help verify whether this is indeed a problem?

Totally up to you Peifan if you think this is expected behavior

When I copy the example and run it on my end in a Python script, I get the same set of NaN entries.

When I use the fix avg_delays_w_code = avg_delays.merge(carrier_code, left_index = True, right_on = 'Code'), it works.

According to the documentation at https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html, the join() function without specifying any keys will join "index on index" (not entirely sure what that's referring to). I haven't debugged far enough to determine why it shows up as all NaN because of that, but the merge line works for sure because its specifically linking through the Code from the RHS database linked to the existing order of entries on the left.

For reference, we currently see this:

I think we should be seeing this instead:

It could have something to do with the left dataset not having a specified index; I can check this later today.

@jbrightuniverse Thanks, let me have a look at this. It could be because of different versions of Pandas (because it worked well on the Syzygy server).

OK. This is because pd.read_csv changed its default behavior by setting an additional level of numerical indices.
Change avg_delays_w_code = avg_delays.join(carrier_code ) to avg_delays_w_code = avg_delays.join(carrier_code.set_index("Code") would be a minimal working adjustment.