ENH: Add `force_suffixes` boolean argument to `pd.merge`
State: open

## Motivation

Often, when working with wide (i.e. multiple columns) dataframes in exploratory, merging them leads to an even wider dataframe. Currently, the `suffixes` mechanism is only applied on *equally named* columns from both dataframes.

However, often developers alter the column names beforehand, or use solutions similar to the one suggested [here](https://github.com/pandas-dev/pandas/issues/17834#issuecomment-1242794050).

## Changes

This PR adds a `force_suffixes` boolean argument to `pd.merge` which applies the suffixes on all columns, no matter if they equally named or not.

The goal is to have the following:

```python
df1 = pd.DataFrame({
                'ID': [1, 2, 3],
                'Value': ['A', 'B', 'C']
                })

  df2 = pd.DataFrame({
                  'ID': [2, 3, 4],
                  'Value': ['D', 'E', 'F']
              })

merged_df = pd.merge(df1, df2, on='ID', how="inner", suffixes=('_left', '_right'), force_suffixes=True)

# Goal:
expected = DataFrame([[2, 2, "B", "D"], [3, 3, "C", "E"]],
                                        columns=["ID_left", "Value_left", "ID_right", "Value_right"])
```
- [x] addresses #17834
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Labels:
name: Enhancement
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode
name: Stale

Comments:
Hey @mroeschke, can you please take a look at my if the direction is right for you (i.e. you are OK with an additional argument) before I will fix the failing tests, linting errors and adjust the documentation. Ty in advance!
`merge` already has a quite complex signature, and what you are trying to solve here ca be easily done in pandas with:

```python
pd.merge(df1.add_suffix("_left"), df2.add_suffix("_right"))
```

Let me know if I'm missing something, but while seems some people would appreciate that, doesn't seem any core dev is excited about this, ans I understand why, since it makes a tricky method even trickier.

@TomAugspurger in the issue seems like you were a bit more positive than others about adding this when discussed some time ago. Would you move on with this PR? Otherwise let's close it.
> `merge` already has a quite complex signature, and what you are trying to solve here ca be easily done in pandas with:
> 
> ```python
> pd.merge(df1.add_suffix("_left"), df2.add_suffix("_right"))
> ```
> 
> Let me know if I'm missing something, but while seems some people would appreciate that, doesn't seem any core dev is excited about this, ans I understand why, since it makes a tricky method even trickier.

Thanks for your feedback! 

Let me motivate the additional flag approach. I agree with you, using `add_suffix` is a valid approach, which however adds complexity on the code of the uses, forcing them to pass additional arguments like `left_on="uuid_left", right_on="id_right"`, which makes it even more complicated.

Using `force_suffixes` would make the joins with wide data frames in exploratory settings very easy, because people cannot remember 10 different column names for each of the participants. 

However I also agree on the additional complexity of the upcoming implementation. The internal logic of renaming and returning columns is quite complex already, which is not easy to grasp, maintain and test.

I will wait upon your decision. 




Thanks for the clarification. I didn't realize the suffix would be added to the columns to join and it'd make things more complex than just adding the `add_suffix`, which otherwise feels like a quite clean approach. There is clearly a trade-off here, I'm personally fine with the change even if adds some complexity, since there seems to be many users who would appreciate that.

@pandas-dev/pandas-core any opinion on adding a flag to `pandas.merge` to make the suffixes be added also to non-conflicting names?
> @pandas-dev/pandas-core any opinion on adding a flag to `pandas.merge` to make the suffixes be added also to non-conflicting names?

I think this is a nice idea.  If we default it to `False`, then the current behavior is preserved.
This is a situation I've run into occasionally. It's a few lines of user code, and yes, you need to track what you're joining on. I don't think it's unreasonable for the onus to be on users here, but no objection to adding a flag.
> I don't think it's unreasonable for the onus to be on users here

I agree, maybe adding additional flags is a "lost" effort if the LLMs create this boilerplate code in the future anyway for the majority of pandas users. However for those who want to be in control while exploring data, I still believe such a flag would be helpful.

Anyway - thank you for sharing your opinions! It seems you folks have no major objections to an additional flag, and I feel your agreement to the feature in the end will depend upon my implementation and its maintainability. I will work on it and ping some of you.

One more design question though: Do we want to add suffixes to the "key"-columns (i.e. those in `on, left_on, right_on`) as well? I would suggest to for consistency.
> One more design question though: Do we want to add suffixes to the "key"-columns (i.e. those in `on, left_on, right_on`) as well? I would suggest to for consistency.

If yes, is the proposal to create both a `key_left` and `key_right` column if the suffixes are `("_left", "_right")`?
> If yes, is the proposal to create both a `key_left` and `key_right` column if the suffixes are `("_left", "_right")`?

Exactly, while working with the resulting data frame the users will know, from which data source certain fields originated. Especially in left/right joins we can easily identify which keys were missing. 


I would not expect the keys to be suffixed, especially on an inner join.
> I would not expect the keys to be suffixed, especially on an inner join.

 You're right - I kept the join columns untouched.

Hey folks, can someone of you check if the direction of my PR is fine for you? The logic and flag work as we discussed, however I am not convinced into its maintenability. To be honest, I have a strong feeling I am building some if/else-balcony and adding unnecessary complexity to the already non-trivial code just for adding a cosmetic feature... Shall I continue?
This pull request is stale because it has been open for thirty days with no activity. Please [update](https://pandas.pydata.org/pandas-docs/stable/development/contributing.html#updating-your-pull-request) and respond to this comment if you're still interested in working on this.