DataFrame from hierarchical NumPy recarray with hierarchical MultiIndex results in all NaN values

[jzwinck]

I filed #13415 in which it was said that DataFrame(recarray, columns=MultiIndex) does reindexing and so only selects matching columns to be in the resultant frame. I can see how this might be a backward compatibility constraint. However, I have discovered a similar but different case which still seems broken:

arr = np.zeros(3, [('q', [('x',float), ('y',int)])])
ind = pd.MultiIndex.from_tuples([('q','x'),('q','y')])
pd.DataFrame(arr, columns=ind)

This creates a 3x2 array of zeros, but results in a 3x2 DataFrame of NaNs. Note that the column names basically match: the NumPy array has a top-level q with subitems x and y, and so does the MultiIndex. If the top-level name in the MultiIndex is changed to something other than q it results in an empty DataFrame, meaning that there is some recognized correspondence between the input data and the requested columns. But the data is lost nevertheless, putting NaNs where should be zeros.

Either the columns are considered non-matching, in which case the result should be an empty DataFrame, or they do match, in which case the result should be a DataFrame with contents from the input array.

[jorisvandenbossche]

The issue is rather that pandas does not parses that hierarchical dtype as you expect:

In [74]: arr = np.zeros(3, [('q', [('x',float), ('y',int)])])

In [76]: pd.DataFrame(arr)
Out[76]:
          q
0  (0.0, 0)
1  (0.0, 0)
2  (0.0, 0)

Given the above result, the rest (empty frame when providing columns) is logical again.
However, I am not sure what the correct way to convert such a recarray should be. The above also seems to make sense, as the records of the recarray consist of tuples, the resulting dataframe has tuples as well.

BTW, I closed the previous issue, but that does not mean it is prohibited to ask further questions on that topic over there :-)

[jreback]

This sort of works with the only constructor that accepts rec-arrays.

In [4]: pd.DataFrame.from_records(arr, columns=ind)
Out[4]: 
          q
0  (0.0, 0)
1  (0.0, 0)
2  (0.0, 0)

[jreback]

this is essentially another case of #7893

[jzwinck]

I disagree that this is another case of #7893. As I tried to explain:

Either the columns are considered non-matching, in which case the result should be an empty DataFrame, or they do match, in which case the result should be a DataFrame with contents from the input array.

The current behavior is that an erroneous DataFrame is created, which does not contain data from the input array, but is also not empty. If Pandas recognizes that the column names match, it should use the input data; if it believes the names don't match then the result should be an empty DataFrame. The current behavior is half-and-half.

[jreback]

and that's a bug I agree

we just need another issue that covers the same material as another issue
it will just even more lost - if you would like to address that issue then you can include this as a test case

[jzwinck]

What I would like more than anything is to have a simple way to take a hierarchical recarray (as my example arr) and get it into a DataFrame with MultiIndex. I think you see what I am trying to do--can you offer a workaround?

[jorisvandenbossche]

@jreback the result you show from from_records is exactly the same as from DataFrame():

In [1]: arr = np.zeros(3, [('q', [('x',float), ('y',int)])])

In [2]: pd.DataFrame(arr)
Out[2]:
          q
0  (0.0, 0)
1  (0.0, 0)
2  (0.0, 0)

In [3]: pd.DataFrame.from_records(arr)
Out[3]:
          q
0  (0.0, 0)
1  (0.0, 0)
2  (0.0, 0)

In [4]: ind = pd.MultiIndex.from_tuples([('q','x'),('q','y')])

In [5]: pd.DataFrame.from_records(arr, columns=ind)
Out[5]:
          q
0  (0.0, 0)
1  (0.0, 0)
2  (0.0, 0)

So in the last line, the columns=ind is actually ignored which rather looks like a bug

[jreback]

assign the columns directly