BUG: Check for duplicate names columns and index in crosstab
State: closed

- [x] closes #22529
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

**Problem:** Currently if you give two Series with the same name to pd.crosstab it will ignore one of the columns. By minimally modifying a current test, it no longer passes.

**Replicable example of the issue (more examples in the tests of this PR):**
```
import pandas as pd
s1 = pd.Series(range(3), name='foo')
s2 = s1 + 1

expected = pd.crosstab(s1, s2.rename("bar")) # If we rename one of the series we get the correct result
result = pd.crosstab(s1, s2)

pd.testing.assert_frame_equal(result, expected)
```

**Proposed solution (implemented in this PR):**

- ~~Raise an exception if the user tries to use duplicate column names, duplicated index, or a name is shared between the index and columns~~.

Updated (implemented) proposal:
      - Find which names are duplicated and add a number to them
      - If a name is shared between rows and columns, add a "_row" and "_col" to them.

**Alternative solutions:**

- Append a number without checking to each name.
   - Pro: Simpler code (don't have to look for duplicates nor know how many times a name is duplicated).
   - Con: Might be confusing for the user to see an error in column "column_name_3", specially if their names already had numbers in them.



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

Comments:
Hmm I might be overlooking it but why don't we want to allow this? One of the names is for the index and one for the columns, no?
By the way not sure if we want to do this...but in any case this is a good first PR!
Can also make it support the behaviour if that's what we want (there is a dictionary key being overwritten when using duplicate names). 

Do you prefer a solution that appends an _x, _y or one that just works our of the box?
IIUC, the problem was us overwriting a key in some internal dictionary we're building up the results in.

So if we wanted to use custom keys in that dictionary like "index" and "columns", that'd be fine. Then we build up the resulting DataFrame for the user and ensure that the names are correct (not using "index" and "columns" anymore).
Yes, that's the issue. Although adding an index_ and columns_ names is not enough because row names might also be duplicated (and column names as well).

**Proposal**

1. If the column/row name is a string concat a counter_id to make them unique.
2. Send the transformed DataFrame to pivot_table()*.
3. Remove the ids.

*: A quick test showed me that pivot table doesn't support duplicated column names.

**Aside from strings and tuples, what other column name types are supported? Any inmutable object? Any ideas of what to do in those cases?** One "solution" is to convert them to strings, but it doesn't seem right.
I think our only requirement on labels is that they're hashable.

Is it possible to just use our own names when `index` or `columns` is passed as an array with a name? Sorry, I'm not too familiar with this section of the code.
That's a good idea. So we create a mapping from our names to the names the user uses, send the DataFrame with our names to pivot_table and when it comes back we use the new names.

Our names could be the string representation of user's names (+ a dedup string) so if an error occurs they can check it. 

Will give it a shot.
**Built a solution that achieves the following:**

- Adds a _0, _1, etc. to the duplicated names in the order the user provided it **if** the names is duplicated within rows or within columns.
- Adds _row or _col **if** the name is shared between columns and rows.
- Returns back the names of the columns the user provided.
- Only modifies the columns that are duplicated within rows/columns or shared across rows/columns. The objective of this is that if pivot_table() raises an error referring to a column or row, the user will see (unless duplicated) the names they gave us.

**Notes:**
- _get_duplicate_count() could be replaced by Counter() with some small refactoring. Wasn't sure is there is a preference to avoid importing it. Only saw Counter() being used in the tests.
- Added tests for each case and assert to check that is returning the original column names.
- If this solution makes sense I can add tests for the helper functions.

Happy to do any changes needed. 

Anything I can do to move this PR forward?
@cuchoi if you merge master and repush will take another look
@cuchoi can you merge master
Done, thanks for taking the time to review!
can you merge master and we'll look again
@cuchoi can you fix merge conflict?
Just in case, I resolved this a few day ago. Is there a guideline of how updated should I keep the PR? (example: merge to master twice a week; or merge to master on request with a response time of X hours).
> is there a guideline of how updated should I keep the PR? (example: merge to master twice a week; or merge to master on request with a response time of X hours).

When I rebase I generally wait until the CI passes and then post "rebased+green".  
have to look at this again. The implementation is a bit complex. can you rebase.
Merged
@cuchoi looks like CI is in a failed state - can you try to get green and someone will take a look?
Updated
can you merge master
this looked good last time i looked, can you merge master and ping on green.
Green :)
thanks @cuchoi - cc @jreback / @WillAyd to take a look whenever they have a moment.
Great, let me know if I can be of any help.
can you merge master
Green
@cuchoi pretty close on this, just a couple of comments.
can u merge master and resolve conflicts
@jreback rebased + green if you wanted to get this in. I've had a brief look here, but having a long history and quite a bit of code added not in a position to say if this is ready.