ENH/DOC: wide_to_long performance and docstring clarification 
State: closed

 - [x] closes #14778 
 - [x] tests passed
 - [x] passes ``git diff upstream/master | flake8 --diff``
 - [x] whatsnew entry

Please see #14778 for details.

I make `wide_to_long`  a bit faster (avoid slow regex search on long columns by first converting to Categorical, avoid melting all dataframes with all the id variables, and wait with trying to convert the "time" variable to `int` until last), and clear up the docstring. 




Labels:
name: Performance
description: Memory or execution speed performance
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode

Comments:
you can simply push to this pr as you update FYI
pls add an asv for this, see: http://pandas.pydata.org/pandas-docs/stable/contributing.html#running-the-performance-test-suite
asv added:

```
[  0.00%] · For pandas commit hash 4014f118:
[  0.00%] ·· Building for conda-py2.7-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py2.7-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 50.00%] ··· Running reshape.wide_to_long_big.time_wide_to_long_big                                                                                 130.66ms
[ 50.00%] · For pandas commit hash 06f26b51:
[ 50.00%] ·· Building for conda-py2.7-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 50.00%] ·· Benchmarking conda-py2.7-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[100.00%] ··· Running reshape.wide_to_long_big.time_wide_to_long_big                                                                                    2.05s    before     after       ratio
  [06f26b51] [4014f118]
-     2.05s   130.66ms      0.06  reshape.wide_to_long_big.time_wide_to_long_big
```
## [Current coverage](https://codecov.io/gh/pandas-dev/pandas/pull/14779?src=pr) is 85.27% (diff: 96.15%)
> Merging [#14779](https://codecov.io/gh/pandas-dev/pandas/pull/14779?src=pr) into [master](https://codecov.io/gh/pandas-dev/pandas/branch/master?src=pr) will increase coverage by **<.01%**

```diff
@@             master     #14779   diff @@
==========================================
  Files           144        144          
  Lines         50981      50989     +8   
  Methods           0          0          
  Messages          0          0          
  Branches          0          0          
==========================================
+ Hits          43470      43481    +11   
+ Misses         7511       7508     -3   
  Partials          0          0          
```

> Powered by [Codecov](https://codecov.io?src=pr). Last update [cb2d6eb...df1edf8](https://codecov.io/gh/pandas-dev/pandas/compare/cb2d6eb53ebe14f10a713a419a39faa545446a3e...df1edf8e182047f330df02e1697ad6feaff1dbf4?src=pr)
needs a whatsnew entry (0.20.)
you could add an argument to specify the split (or make it take a regex)

yes it should get stripped
further, not sure if http://pandas.pydata.org/pandas-docs/stable/reshaping.html#reshaping-by-melt
needs any updates?
that's a regex
Sorry, I didn't get that? And `melt` looks pretty rock solid.
@nuffe I was referring to ``wide_to_long`` in the docs (e.g. is the example ok).

you are splitting on the ``sep`` character, but it could also be a regext, so doing something like
```
In [7]: re.split('\s+','A  2010')
Out[7]: ['A', '2010']

In [8]: re.split('\s+','A 2010')
Out[8]: ['A', '2010']
```

is probably reasonable
To keep things simple I propose we break the API of `wide_to_long` and  change the signature to adhere more to how R's `reshape` does this.

`wide_to_long(df, varying, i, j, sep=' ')`

I.e. the user passes the names of the time varying column as a list `varying`, and these (`x`) are expected (checked) to adhere to the following rules (more or less what `reshape` assumes):

based on `sep` `x` is split into exactly two strings.
`sep` (a single character) is constrained to be  non-alphanumeric: \s, ., ;, etc...
the first part is the 'stubname', the second part is the 'time' part

```python
In [3]: df = pd.DataFrame({"A 1970" : {0 : "a", 1 : "b", 2 : "c"},
   ...:                     "A 1980" : {0 : "d", 1 : "e", 2 : "f"},
   ...:                     "B 1970" : {0 : 2.5, 1 : 1.2, 2 : .7},
   ...:                     "B 1980" : {0 : 3.2, 1 : 1.3, 2 : .1},
   ...:                     "X"     : dict(zip(range(3), np.random.randn(3)))
   ...:                    })
   ...: df['id'] = df.index
   ...: df
   ...:
Out[3]:
  A 1970 A 1980  B 1970  B 1980         X  id
0      a      d     2.5     3.2  0.136953   0
1      b      e     1.2     1.3 -1.238109   1
2      c      f     0.7     0.1  1.249809   2
```

```python
In [4]: varying = ['A 1970', 'A 1980', 'B 1970', 'B 1980']
   ...: pd.wide_to_long(df, varying, i='id', j='year', sep=' ')
   ...:
Out[4]:
                X  A    B
id year
0  1970  0.136953  a  2.5
1  1970 -1.238109  b  1.2
2  1970  1.249809  c  0.7
0  1980  0.136953  d  3.2
1  1980 -1.238109  e  1.3
2  1980  1.249809  f  0.1
```

The user can easily construct the `varying` list with a regex, a doc example can show this.

If the existing columns does not adhere to the above specification, they need to be changed to a suitable format first. A doc example can show how this can be easily done with a regex with a backreference.

What do you think?







the varying should be a list of tuples
space separated elements in lists are not pythonic 

but otherwise looks ok

can this be backward compat?
I didn't understand the first comment: `varying` is just the names of all columns that should be varying. For a sample dataframe it could be `df.iloc[:, 4:11].columns.tolist()`. I.e. that the names happen to be space separated is just how the data columns ended up looking like in messy real world data?

And I do not think this can be made backward compat because the `varying` argument would be different, `stubnames` would now be computed inside the function.

The old doc example were there is no single character separator, f.eks `varying = ['A1970', 'A1980', 'B1970', 'B1980']` will only work by first converting the column names to the allowed format with

```python
df.columns.str.replace('([A-B])', '\\1.')
Index([u'A.1970', u'A.1980', u'B.1970', u'B.1980', u'X', u'id'], dtype='object')
```
then calling `wide_to_long` with `varying = ['A.1970', 'A.1980', 'B.1970', 'B.1980']`

I do not know if this really is considered too unwieldy...? R's `reshape` interface is perhaps not the most user friendly, but with plenty of doc examples its flexibility should maybe be appreciated?

The original function author seemed to have tried to mimic `Stata`s [reshape](http://www.ats.ucla.edu/stat/stata/modules/reshapel.htm) where reshape essentially only would take the stubnames as argument. The problem is that in stata column names are highly constrained (can for ex not have whitespaces or non alphanumeric characters), while in pandas they can be any utf8 string, which makes it much harder to generalize.

So if we should preserve the original function authors intention, where the user only supplies stubnames as in Stata, we need to impose some strict assumptions on the column names passed, like the only kind of column names we can have (that are varying) are of the type `PrefixPostfix`, where `Prefix` and `Postfix` are alphanumeric. These are the only ones `Stata`'s `reshape` needs to consider, and what the original `wide_to_long` function implicitly assumes. We can also handle   `PrefixSepPostfix` where `Sep` is a single separating character.

Perhaps it is just better to make this implicit assumption explicit and keep its "Stata like" interface? And make it robust to this specificaton (`PrefixSepPostfix`), because the master branch function breaks with plenty of variations of this. 

(sorry for the messyness here but I ended up spending some time familiarizing myself more with `R`s less user friendly approach to this problem, and `Stata`'s more user friendly but less flexible approach)


So here is an attempt to make the original interface more robust, these two examples fail on the master branch, but should be able to produce the correct result which is:

```python
In [12]: df = pd.DataFrame({
    ...:         'A11': ['a11', 'a22', 'a33'],
    ...:         'A12': ['a21', 'a22', 'a23'],
    ...:         'B11': ['b11', 'b12', 'b13'],
    ...:         'B12': ['b21', 'b22', 'b23'],
    ...:         'BB11': [1, 2, 3],
    ...:         'BB12': [4, 5, 6],
    ...:         'BBBX' : [91, 92, 93],
    ...:         'BBBZ' : [91, 92, 93]
    ...:     })
    ...: df['id'] = df.index
    ...: df
    ...:
Out[12]:
   A11  A12  B11  B12  BB11  BB12  BBBX  BBBZ  id
0  a11  a21  b11  b21     1     4    91    91   0
1  a22  a22  b12  b22     2     5    92    92   1
2  a33  a23  b13  b23     3     6    93    93   2

In [13]: pd.wide_to_long(df, ['A', 'B', 'BB'], i='id', j='year')
Out[13]:
         BBBX  BBBZ    A    B  BB
id year
0  11      91    91  a11  b11   1
1  11      92    92  a22  b12   2
2  11      93    93  a33  b13   3
0  12      91    91  a21  b21   4
1  12      92    92  a22  b22   5
2  12      93    93  a23  b23   6
```

```python
In [14]: df = pd.DataFrame({
    ...:         'A(quarterly)2011': ['a11', 'a22', 'a33'],
    ...:         'A(quarterly)2012': ['a21', 'a22', 'a23'],
    ...:         'B(quarterly)2011': ['b11', 'b12', 'b13'],
    ...:         'B(quarterly)2012': ['b21', 'b22', 'b23'],
    ...:         'BB(quarterly)2011': [1, 2, 3],
    ...:         'BB(quarterly)2012': [4, 5, 6],
    ...:         'BBBX' : [91, 92, 93],
    ...:         'BBBZ' : [91, 92, 93]
    ...:     })
    ...: df['id'] = df.index
    ...: df
    ...:
Out[14]:
  A(quarterly)2011 A(quarterly)2012 B(quarterly)2011 B(quarterly)2012  \
0              a11              a21              b11              b21
1              a22              a22              b12              b22
2              a33              a23              b13              b23

   BB(quarterly)2011  BB(quarterly)2012  BBBX  BBBZ  id
0                  1                  4    91    91   0
1                  2                  5    92    92   1
2                  3                  6    93    93   2

In [15]: pd.wide_to_long(df, ['A(quarterly)', 'B(quarterly)', 'BB(quarterly)'], i='id', j='year')
Out[15]:
         BBBX  BBBZ A(quarterly) B(quarterly)  BB(quarterly)
id year
0  2011    91    91          a11          b11              1
1  2011    92    92          a22          b12              2
2  2011    93    93          a33          b13              3
0  2012    91    91          a21          b21              4
1  2012    92    92          a22          b22              5
2  2012    93    93          a23          b23              6
```

The first one fails because the regex confuses the same substrings in the `id_vars` and `value_vars`, the second ones because of the parenthesis.


Assuming a `Prefix(Optional Sep)Postfix` structure on the "time" variables, I tried to make it robust:

```python
 [16]: df = pd.DataFrame({
    ...:         'A11': ['a11', 'a22', 'a33'],
    ...:         'A12': ['a21', 'a22', 'a23'],
    ...:         'B11': ['b11', 'b12', 'b13'],
    ...:         'B12': ['b21', 'b22', 'b23'],
    ...:         'BB11': [1, 2, 3],
    ...:         'BB12': [4, 5, 6],
    ...:         'Acat' : [91, 92, 93],
    ...:         'BBBZ' : [91, 92, 93]
    ...:     })
    ...: df['id'] = df.index
    ...: df
    ...:
Out[16]:
   A11  A12  Acat  B11  B12  BB11  BB12  BBBZ  id
0  a11  a21    91  b11  b21     1     4    91   0
1  a22  a22    92  b12  b22     2     5    92   1
2  a33  a23    93  b13  b23     3     6    93   2
```

raises a `ValueError: ('Ambiguous names: ', ['A11', 'A12', 'Acat'])` .

While the following works

```python
 df = pd.DataFrame({
    ...:         'A-11': ['a11', 'a22', 'a33'],
    ...:         'A-12': ['a21', 'a22', 'a23'],
    ...:         'B-11': ['b11', 'b12', 'b13'],
    ...:         'B-12': ['b21', 'b22', 'b23'],
    ...:         'BB-11': [1, 2, 3],
    ...:         'BB-12': [4, 5, 6],
    ...:         'Acat' : [91, 92, 93],
    ...:         'BBBZ' : [91, 92, 93]
    ...:     })
    ...: df['id'] = df.index
    ...: df
    ...:
Out[18]:
  A-11 A-12  Acat B-11 B-12  BB-11  BB-12  BBBZ  id
0  a11  a21    91  b11  b21      1      4    91   0
1  a22  a22    92  b12  b22      2      5    92   1
2  a33  a23    93  b13  b23      3      6    93   2

In [19]: pd.wide_to_long(df, ['A', 'B', 'BB'], i='id', j='year', sep='-')
Out[19]:
         Acat  BBBZ    A    B  BB
id year
0  11      91    91  a11  b11   1
1  11      92    92  a22  b12   2
2  11      93    93  a33  b13   3
0  12      91    91  a21  b21   4
1  12      92    92  a22  b22   5
2  12      93    93  a23  b23   6
```




cc @jseabold any thought here.

@nuffe ideally we want to make this back-compat (you can do introspection in the code to figure out what you are passed and such). And have as simple as API as possible. 
I have maintained the user friendly (and evidently Stata inspired) interface (and stated what structure this function assumes on the column names), and tried to fix mistakes that arise with various "pathological" input, for example if the `stubnames` contains groups that share a substring (which I discovered when I tried different examples in Stata and compared them to `wide_to_long`)


I found another "Stata like" use case `wide_to_long` doesn't handle at [this](http://www.ats.ucla.edu/stat/stata/modules/reshapel.htm) page: if the data frame does not have a single `id` column that identifies the wide variables, Stata users can supply the necessary column names, fex `['id1', 'id2']`. Currently the user would have to generate a new column that identifies the `['id1', 'id2']` combination and pass that as `id`.

(I am going to add the option of supplying a list of 'id' variables, it will require another short rewrite since I have to move from using `join` to `merge`, to handle the new multilevel index)
Sometimes AppVeyor/Travis fails with unrelated tests (like `test_bar_log_subplots`, right now). Any hint on what I should do here? I've looked at the failing cases (unrelated plot methods), and there doesn't seem to be any state that has mutated because for ex. a random number seed has changed. (tests pass on my OS X laptop with both python 2 and python 3 virtual envs). Thanks
can you rebase, problem with appeveyor which i just fixed
lgtm. some minor comments.

@jorisvandenbossche 
@jreback another minor issue: sphinx doesn't print the `\` in the docstring suffix section. The only way I managed to get these printed was to set the entire docstring to a raw string literal, and escape the slash:  `'\\d+'`
> set the entire docstring to a raw string literal

You can indeed do that, but normally then the escaping should not be needed
@jorisvandenbossche Yes this version is back compat. The PR got a bit lengthy because I did more than I anticipated (was originally a simple PR for a quick speed improvement - but I discovered afterwards that there where several use cases the original function couldn't handle). 
thanks @nuffe very nice PR, and you were very responsive!

if you want to tackle other issues would be much appreciated!