0

I have been trying to address an issue mentioned here

I had been trying to use a list of dates to filter a dataframe, and a very gracious person was helping me, but now with the current code, I am receiving these errors. You are trying to merge on object and int32 columns. If you wish to proceed you should use pd.concat

# Assign a sequential number to each trading day
df_melt_test_percent = df_melt_test_percent.sort_index().assign(DayNumber=lambda x: range(len(x)))

# Find the indices of the FOMC_dates
tmp = pd.merge(
   df_FOMC_dates, df_melt_test_percent[['DayNumber']],
   left_on='FOMC_date', right_on='DayNumber'
)

# For each row, get the FOMC_dates ± 3 days
tmp['delta'] = tmp.apply(lambda _: range(-3, 4), axis=1)

tmp = tmp.explode('delta')
tmp['DayNumber'] += tmp['delta']

# Assemble the result
result = pd.merge(tmp, df_melt_test_percent, on='DayNumber')

Screenshots of dataframes:

enter image description here enter image description here

If anyone has any advice on how to fix this, it would be greatly appreciated.

EDIT #1: enter image description here

birdman
  • 249
  • 1
  • 13
  • The columns on which you want to merge are not the same types in both dataframes, likely one is string the other int. You should convert to the same type before merging – mozway Aug 27 '21 at 14:06
  • Does this answer your question? [Trying to merge 2 dataframes but get ValueError](https://stackoverflow.com/questions/50649853/trying-to-merge-2-dataframes-but-get-valueerror) – mozway Aug 27 '21 at 14:07
  • There is no column ''DayNumber'' on the pictures, so it's not clear what problem you have – Anna Iliukovich-Strakovskaia Aug 27 '21 at 14:10
  • Try changing it to `tmp = pd.merge(..., right_index=True)`. You mentioned you get blank `result` after that. Can you post some data so I can work on it? – Code Different Aug 27 '21 at 14:10
  • @CodeDifferent By setting `right_index = True` the error went away, but the dataframe is not displaying correctly. Edited OP to show. – birdman Aug 27 '21 at 16:08
  • That means the last `merge` is not matching anything between `tmp` and `df_melt`. Take the first row in `tmp` and see if you can do the merge manually – Code Different Aug 27 '21 at 16:11
  • Forgive my naivety, but what would that look like? – birdman Aug 27 '21 at 16:15
  • `tmp.iloc[0]`. Get the `DayNumber` from that. Then find if there is such day number in `df_melt` – Code Different Aug 27 '21 at 17:44

1 Answers1

1

The columns on which you want to merge are not the same types in both dataframes. Likely one is string the other integer. You should convert to the same type before merging. Assuming from the little bit you showed, before your merge, run:

tmp['DayNumber'] = tmp['DayNumber'].astype(int)

Alternatively:

df_melt_test_percent['DayNumber'] = df_melt_test_percent['DayNumber'].astype(str)

NB. This might not work as you did not provide a full example. Either search by yourself the right types or provide a reproducible example.

mozway
  • 194,879
  • 13
  • 39
  • 75