pd.json_normalize doesn't return data with index from series · Issue #51452 · pandas-dev/pandas (original) (raw)

TLDR;

pd.json_normalize creates a new index for the normalised series.

Issue

pd.json_normalize returns a new index for the normalised series, rather than the one supplied. Given the other methods in pandas, this seems like it violates the 'principal of least surprise'? This caught me out for longer than I would care to admit :D

Minimum working example:

import pandas as pd from IPython.display import display data = [ {'col1':1, 'col2':'positive', 'json_data':{'a':1, 'b':2, 'c':3}}, {'col1':2, 'col2':'negative', 'json_data':{'a':-1, 'b':-2, 'c':-3}} ] df1 = pd.DataFrame(data) display(df1) df2 = df1.sort_values('col1', ascending=False) display(df2)

image

df1j = pd.json_normalize(df1['json_data'])
df2j = pd.json_normalize(df2['json_data'])
display(df1.join(df1j))
display(df2.join(df2j))

image

The join on the two indices provides and unexpected result as the two rows are now no-longer consistent.

When looking at the data returned from json_normalize, we can see that the index of the returned data has been reset, which ultimately means the join is not as expected.

display(df1j)
display(df2j)

image