DataFrame.from_dict sets DataFrame values to NaN if original dict key tuples contain None

dmparker0 commented 5 years ago

Code Sample

import pandas as pd

d = {('East',1):{'Home':'MIL','Away':'BOS','Winner':'MIL'},
     ('East',2):{'Home':'PHI','Away':'IND','Winner':'PHI'},
     ('East',3):{'Home':'MIL','Away':'PHI','Winner':'PHI'},
     ('West',1):{'Home':'HOU','Away':'UTA','Winner':'HOU'},
     ('West',2):{'Home':'LAC','Away':'LAL','Winner':'LAC'},
     ('West',3):{'Home':'HOU','Away':'LAC','Winner':'HOU'},
     (None,1):{'Home':'HOU','Away':'PHI','Winner':'PHI'}}

df = pd.DataFrame.from_dict(d, orient='index')
print(df)

Output

        Home Away   Winner
East 1  MIL  BOS    MIL
     2  PHI  IND    PHI
     3  MIL  PHI    PHI 
West 1  HOU  UTA    HOU
     2  LAC  LAL    LAC
     3  HOU  LAC    HOU
NaN  1  NaN  NaN    NaN

Problem description

When constructing a DataFrame from a dictionary with tuple keys, values for all columns are set to NaN if the index tuple contains None.

I only started encountering this bug when I upgraded from pandas 0.22 to pandas 0.25.

Possibly related: #19993

Expected Output

        Home Away   Winner
East 1  MIL  BOS    MIL
     2  PHI  IND    PHI
     3  MIL  PHI    PHI 
West 1  HOU  UTA    HOU
     2  LAC  LAL    LAC
     3  HOU  LAC    HOU
NaN  1  HOU  PHI    PHI

Output of `pd.show_versions()`

INSTALLED VERSIONS ------------------ commit : None python : 3.6.7.final.0 python-bits : 64 OS : Windows OS-release : 10 machine : AMD64 processor : Intel64 Family 6 Model 45 Stepping 7, GenuineIntel byteorder : little LC_ALL : None LANG : en_US.UTF-8 LOCALE : None.None pandas : 0.25.1 numpy : 1.14.3 pytz : 2017.3 dateutil : 2.7.3 pip : 10.0.1 setuptools : 39.0.1 Cython : 0.28.2 pytest : None hypothesis : None sphinx : None blosc : None feather : None xlsxwriter : None lxml.etree : 4.2.4 html5lib : 1.0.1 pymysql : None psycopg2 : None jinja2 : None IPython : None pandas_datareader: None bs4 : 4.6.3 bottleneck : None fastparquet : None gcsfs : None lxml.etree : 4.2.4 matplotlib : 3.0.3 numexpr : None odfpy : None openpyxl : 2.4.9 pandas_gbq : None pyarrow : None pytables : None s3fs : None scipy : 1.1.0 sqlalchemy : 1.1.15 tables : None xarray : None xlrd : 1.1.0 xlwt : None xlsxwriter : None None

WillAyd commented 5 years ago

For your expected output you want None in the index instead of NaN right? I think reasonable so if you want to investigate and submit a patch would certainly be welcome

dmparker0 commented 5 years ago

For your expected output you want None in the index instead of NaN right?

I would never expect None to appear in a DataFrame, since my understanding is that NaN (or NaT) is used universally for missing values. The issue isn't having NaN instead of None in the index, it's having NaN instead of the appropriate string values in the other columns.

dsaxton commented 5 years ago

FWIW, what seems to be happening is that None is getting converted to np.nan when the MultiIndex is built, but the actual data are still labeled internally with None. Then we try to "look up" the data here: https://github.com/pandas-dev/pandas/blob/master/pandas/core/internals/construction.py#L310, but the keys aren't found in the index so we end up with np.nan for values. Note that this doesn't happen if you change None to np.nan in your example, or if you use single values instead of tuples as your keys.

I wonder if the Index constructor should be consistent in how it handles None? It seems like it doesn't convert to np.nan if you pass a list of scalars, but it does if you pass a list of tuples.

erezinman commented 1 week ago

Confirmed to still occur in pandas 2.2.3. Also, if you replace None with nan it still won't work!

To anyone reading this, the workaround is to change

df = pd.DataFrame.from_dict(d, orient="index")

To

df = pd.DataFrame.from_records(list(d.values())) 
df.index = pd.MultiIndex.from_tuples(d.keys())

pandas-dev / pandas