Error Creating DataFrame with Single MultiIndexed Column

[woztheproblem]

Attempting to create a DataFrame with a single column that is multiindexed, I get "IndexError: list index out of range".

Code Sample, a copy-pastable example if possible

df = pd.DataFrame(data=zip(range(100)), columns=[['a','b']])

---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
<ipython-input-9-5b88e876b2de> in <module>()
----> 1 df = pd.DataFrame(data=zip(range(100)), columns=[['a','b']])

/Users/ewozniak/anaconda/lib/python2.7/site-packages/pandas/core/frame.pyc in __init__(self, data, index, columns, dtype, copy)
    273 
    274                     mgr = _arrays_to_mgr(arrays, columns, index, columns,
--> 275                                          dtype=dtype)
    276                 else:
    277                     mgr = self._init_ndarray(data, index, columns, dtype=dtype,

/Users/ewozniak/anaconda/lib/python2.7/site-packages/pandas/core/frame.pyc in _arrays_to_mgr(arrays, arr_names, index, columns, dtype)
   5236     axes = [_ensure_index(columns), _ensure_index(index)]
   5237 
-> 5238     return create_block_manager_from_arrays(arrays, arr_names, axes)
   5239 
   5240 

/Users/ewozniak/anaconda/lib/python2.7/site-packages/pandas/core/internals.pyc in create_block_manager_from_arrays(arrays, names, axes)
   3894 
   3895     try:
-> 3896         blocks = form_blocks(arrays, names, axes)
   3897         mgr = BlockManager(blocks, axes)
   3898         mgr._consolidate_inplace()

/Users/ewozniak/anaconda/lib/python2.7/site-packages/pandas/core/internals.pyc in form_blocks(arrays, names, axes)
   3929 
   3930         k = names[name_idx]
-> 3931         v = arrays[name_idx]
   3932 
   3933         if is_sparse(v):

IndexError: list index out of range

Note, I use zip() in the example above in order to match what the data would look like when creating one dataframe from the data of another data frame using df.to_json(orient='split') (which is what I'm trying to do). If I don't use zip(), then I get:

df = pd.DataFrame(data=range(100), columns=[['a','b']])
ValueError: Shape of passed values is (1, 100), indices imply (2, 100)

This works fine with two (or more) columns:

df = pd.DataFrame(data=zip(range(100),range(100)), columns=[['a','b'],['c','d']])

output of pd.show_versions()

I have tried with both pandas 0.17.1 and 0.18.0rc1.

INSTALLED VERSIONS

commit: None
python: 2.7.11.final.0
python-bits: 64
OS: Darwin
OS-release: 15.0.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.0rc1
nose: 1.3.7
pip: 8.0.3
setuptools: 20.1.1
Cython: 0.23.4
numpy: 1.10.4
scipy: 0.17.0
statsmodels: 0.6.1
xarray: None
IPython: 4.1.1
sphinx: 1.3.1
patsy: 0.4.0
dateutil: 2.4.2
pytz: 2015.7
blosc: None
bottleneck: 1.0.0
tables: 3.2.2
numexpr: 2.5
matplotlib: 1.5.1
openpyxl: 2.2.6
xlrd: 0.9.4
xlwt: 1.0.0
xlsxwriter: 0.7.7
lxml: 3.4.4
bs4: 4.4.1
html5lib: None
httplib2: 0.9.2
apiclient: None
sqlalchemy: 1.0.9
pymysql: None
psycopg2: None
jinja2: 2.8

[jreback]

So this is a resaonable error message

In [5]: DataFrame(data=zip(range(100)), columns=['a','b'])
AssertionError: 2 columns passed, passed data had 1 columns

but because of this, it doesn't error properly
This should error as creating an Index with a list-of-anything but tuples is invalid

In [6]: Index([['a','b']])
Out[6]: Index([[u'a', u'b']], dtype='object')

[jreback]

@woztheproblem want to do a PR for the above and see if can fix?

[woztheproblem]

I do now see that it works if I use a list of tuples for the columns, but I guess I don't follow why a list of lists wouldn't also work when the outer list is of length 1, since it works fine when the outer list is of length two (or more).

#works!
df = pd.DataFrame(data=zip(range(100), range(100)), columns=[['a','b'],['c','d']])

#List index out of range error
df = pd.DataFrame(data=zip(range(100)), columns=[['a','b']])

The reason that I'd like to use list of lists instead of list of tuples, is that my goal is to do this:

#Create some data
df = pd.DataFrame(data=range(100), columns=[('a','b')])

#convert to JSON
myjson = df.to_json(orient='split')

#send over the network to another computer

#load json into dataframe
df2 = pd.DataFrame(**json.loads(myjson))

to_json() converts the columns to a list of lists when converting to JSON, so without the ability to load the columns from a list of lists even when there is only one column, a more complicated workflow is needed. (which is fine if loading columns from a list (of length 1) of lists really isn't feasible for some reason)

[jreback]

why are you using json.loads at all?
does pd.read_json(...) not work for you?

[jreback]

as I said you are specifying 2 columns but have a single list of single element values, its the wrong shape. , you are passing a list-of-lists for the Index which is not allowed, you can create a multi-index if you want.

[woztheproblem]

Thanks for your responses. However, I'm having a hard time connecting what you are saying and what I'm seeing. You said it's the wrong shape, but this works:

df = pd.DataFrame(data=zip(range(100)), columns=[('a','b')])

while this doesn't:

df = pd.DataFrame(data=zip(range(100)), columns=[['a','b']])

Yet they have the same shape for the columns argument.

You also said list of lists isn't allowed, but this works:

df = pd.DataFrame(data=zip(range(100), range(100)), columns=[['a','b'],['c','d']])

while this doesn't:

df = pd.DataFrame(data=zip(range(100)), columns=[['a','b']])

Yet both have list of lists for the columns argument.

So the failure is specific to list of lists for the columns argument when the outer list is of length 1.

I had forgotten about pd.read_json(), but it appears to have the same problem. This works:

#example 1
df = pd.DataFrame(data=zip(range(100), range(100)), columns=[['a','b'],['c','d']])
df2 = pd.read_json(df.to_json(orient='split'), orient='split')

But this doesn't:

#example 2
df = pd.DataFrame(data=zip(range(100)), columns=[('a','b')])
df2 = pd.read_json(df.to_json(orient='split'), orient='split')

The first line in example 2 succeeds due to the use of a list of tuples when the number of columns is 1, but the second line gets "IndexError: list index out of range" because to_json() creates a list of lists for the columns element, where the outer list is of length 1, which fails per the previous examples.

[javpaw]

Hi.

I was trying to make my first contribution to Pandas with this ticket.

This is what I got:

• The function to_json stores the columns of the DataFrame as a list of list (LL), but the correct interpretation is as a list of tuples (LT)
• given that this is Json, we have to store tuples as lists, so in our json, we store a LL as, for example, [['a','b']].
• The function read_json uses the DataFrame constructor, if we pass [['a','b']] to the columns parameter we get an index of length 2 given that, internally, MultiIndex.from_arrays is called with the LL.

-For this specific case, I mean to_json <-> read_json, the method that should be called is MultiIndex.from_tuples (or Index alone, is the same thing) to be consistent on how the columns are stored in Json.

One solution

A solution for this specific case is to change the method read_json to check if the columns contains a list of list and if that's the case, create the MultiIndex before the DataFrame constructor is called, something like:

...
if result['columns']  and all(type(i) == list for i in result['columns']:
   result['columns'] = MultiIndex.from_tuples( results['columns'])

self.obj = DataFrame(**result)
...

Hacky solution

A hacky solution is to change the method MultiIndex.from_arrays to MultiIndex.to_arrays in the line:
https://github.com/pydata/pandas/blob/master/pandas/indexes/base.py#L3285-3285

Notice that logic in other sides have to change as well, for example in this line:
https://github.com/pydata/pandas/blob/master/pandas/core/frame.py#L5492-5492

because the len of columns and the len of the data can differ but still the multiindex generated from columns can be consistent with the data, for example:

DataFrame(data= [1], columns=[['a'],['b']] ) 

can generate a dataframe of one column with a multiindex of two levels, but for this case len(data) != len(columns) so right now this is not allowed throwing the error:

ValueError: Shape of passed values is (1, 1), indices imply (2, 1)

A better solution

IMHO we need extra information in the DataFrame constructor to know how to interpret the columns parameter.

this could imply create new params for the DataFrame constructor, for example three params called column_from_list, column_from_tuple and columns_from_product (It doesn't feel right to me but it's an option).

We can also create an extra parameter, for example colums_info, to indicate the correct interpretation of the columns.

Maybe the best solution is to decide that it's better to create first the index and the pass it to the DataFrame constructor and change the code in the parts a new DataFrame is generated.

If you guys agree that there is something to do here I would be happy to contribute, if not, I'll be looking for other issues to start with.

[jreback]

This is simply invalid input, the Index is not allowed to have a list-of-lists. The point of this issue is to have a more informative error here. This has nothing to do with JSON construction, rather Index construction.

In [9]: DataFrame([[1],[2]],columns=[['a', 'b']])
IndexError: list index out of range

You would need to explicity construct a list-of-tuples

In [8]: DataFrame([[1],[2]],columns=pd.MultiIndex.from_tuples([('a', 'b')]))
Out[8]: 
   a
   b
0  1
1  2

[javpaw]

@jreback, the problem is that in one side to_json generates a list-of-lists, on the other side pd.read_json calls the DataFrame constructor with this list-of-lists; this generates the error.

One solution:

Change the code in read_json method to do what you say: create the MultiIndex before call the DataFrame constructor.

Other option:

Be able to send extra information to the DataFrame constructor so it can interpret the parameter columns as expected (list-of-list, list-of-tuples, etc...) , basically add one or more new parameters to the DataFrame constructor.

This is, in summary, what I was talking about in my last comment.