I have a dataframe with lists of nested dictionaries that want to unpack.
I need to get the date and price from the priceHistory and the items listed in both WaterConservation and EnergyEfficient. The sample below is only two rows of a much larger dataframe where there are not the same number of dictionary items per dataframe row.
df = pd.DataFrame(
[[19, [{'priceChangeRate': 0, 'date': '2015-05-29', 'source': 'Public Record', 'postingIsRental': False, 'time': 1432857600000, 'sellerAgent': None, 'showCountyLink': False, 'attributeSource': {'infoString2': 'Public Record', 'infoString3': None, 'infoString1': None}, 'pricePerSquareFoot': 275, 'buyerAgent': None, 'event': 'Sold', 'price': 877205}], ['Low flow commode', 'Low flow fixtures', 'Water-Smart Landscaping'],''],
[89, [{'priceChangeRate': 0.090909090909091, 'date': '2023-07-14', 'source': 'Public Record', 'postingIsRental': False, 'time': 1689292800000, 'sellerAgent': {'name': 'seller1', 'photo': {'url': 'https://sellerphoto1.jpg'}, 'profileUrl': '/profile/sellerprofile1/'}, 'showCountyLink': False, 'attributeSource': {'infoString2': 'Public Record', 'infoString3': None, 'infoString1': None}, 'pricePerSquareFoot': 308, 'buyerAgent': {'name': 'buyer1', 'photo': {'url': 'https://buyerphoto1.jpg'}, 'profileUrl': '/profile/buyerprofile1/'}, 'event': 'Sold', 'price': 1200000}, {'priceChangeRate': 0, 'date': '2015-08-20', 'source': 'Public Record', 'postingIsRental': False, 'time': 1440028800000, 'sellerAgent': None, 'showCountyLink': False, 'attributeSource': {'infoString2': 'Public Record', 'infoString3': None, 'infoString1': None}, 'pricePerSquareFoot': 50, 'buyerAgent': None, 'event': 'Sold', 'price': 195000}],'', ['Windows', 'Insulation', 'HVAC', 'Appliances', 'Lighting']]],
columns=['id', 'priceHistory', 'WaterConservation', 'EnergyEfficient'])
I have tried too many things to list here, but this seems to be the most efficient (just to get priceHistory) (source):
df = pd.concat(
[
df,
df.pop("priceHistory").apply(
lambda x: pd.Series({k: v for d in x for k, v in d.items()})
),
],
axis=1,
)
print(df)
But I get this error: TypeError: 'float' object is not iterable
You can use Series.explode with json_normalize, creste same index by DataFrame.set_index, so possible use DataFrame.join:
s = df.pop('priceHistory').explode()
out = df.join(pd.json_normalize(s).set_index(s.index))
print (out)
id WaterConservation \
0 19 [Low flow commode, Low flow fixtures, Water-Sm...
1 89
1 89
EnergyEfficient priceChangeRate \
0 0.000000
1 [Windows, Insulation, HVAC, Appliances, Lighting] 0.090909
1 [Windows, Insulation, HVAC, Appliances, Lighting] 0.000000
date source postingIsRental time sellerAgent \
0 2015-05-29 Public Record False 1432857600000 NaN
1 2023-07-14 Public Record False 1689292800000 NaN
1 2015-08-20 Public Record False 1440028800000 NaN
showCountyLink pricePerSquareFoot buyerAgent event price \
0 False 275 NaN Sold 877205
1 False 308 NaN Sold 1200000
1 False 50 NaN Sold 195000
attributeSource.infoString2 attributeSource.infoString3 \
0 Public Record None
1 Public Record None
1 Public Record None
attributeSource.infoString1 sellerAgent.name sellerAgent.photo.url \
0 None NaN NaN
1 None seller1 https://sellerphoto1.jpg
1 None NaN NaN
sellerAgent.profileUrl buyerAgent.name buyerAgent.photo.url \
0 NaN NaN NaN
1 /profile/sellerprofile1/ buyer1 https://buyerphoto1.jpg
1 NaN NaN NaN
buyerAgent.profileUrl
0 NaN
1 /profile/buyerprofile1/
1 NaN