- There is always the option to:
df = df.dropna().reset_index(drop=True)
- That’s fine for the dummy data here, or when dealing with a dataframe where the other columns don’t matter.
- Not a great option for dataframes with additional columns that are required.
- Tested in
python 3.10
,pandas 1.4.3
Case 1
- Since the column contains
str
types, fillna with'{}'
(astr
)
import numpy as np
import pandas as pd
from ast import literal_eval
df = pd.DataFrame({'col_str': ['{"a": "46", "b": "3", "c": "12"}', '{"b": "2", "c": "7"}', '{"c": "11"}', np.NaN]})
col_str
0 {"a": "46", "b": "3", "c": "12"}
1 {"b": "2", "c": "7"}
2 {"c": "11"}
3 NaN
type(df.iloc[0, 0])
[out]: str
# fillna
df.col_str = df.col_str.fillna('{}')
# convert the column to dicts
df.col_str = df.col_str.apply(literal_eval)
# use json_normalize
df = df.join(pd.json_normalize(df.pop('col_str')))
# display(df)
a b c
0 46 3 12
1 NaN 2 7
2 NaN NaN 11
3 NaN NaN NaN
Case 2
As of at least pandas 1.3.4
, pd.json_normalize(df.col_dict)
works without issue, at least for this simple example.
- Since the column contains
dict
types, fillna with{}
(not astr
) - This needs to be filled using a dict-comprehension, since
fillna({})
does not work
df = pd.DataFrame({'col_dict': [{"a": "46", "b": "3", "c": "12"}, {"b": "2", "c": "7"}, {"c": "11"}, np.NaN]})
col_dict
0 {'a': '46', 'b': '3', 'c': '12'}
1 {'b': '2', 'c': '7'}
2 {'c': '11'}
3 NaN
type(df.iloc[0, 0])
[out]: dict
# fillna
df.col_dict = df.col_dict.fillna({i: {} for i in df.index})
# use json_normalize
df = df.join(pd.json_normalize(df.pop('col_dict')))
# display(df)
a b c
0 46 3 12
1 NaN 2 7
2 NaN NaN 11
3 NaN NaN NaN
Case 3
- Fill the
NaNs
with'[]'
(astr
) - Now
literal_eval
will work .explode
can be used on the column to separate thedict
values to rows- Now the
NaNs
need to be filled with{}
(not astr
) - Then the column can be normalized
- For the case when the column is
lists
ofdicts
, that aren’tstr
type, skip to.explode
.
df = pd.DataFrame({'col_str': ['[{"a": "46", "b": "3", "c": "12"}, {"b": "2", "c": "7"}]', '[{"b": "2", "c": "7"}, {"c": "11"}]', np.nan]})
col_str
0 [{"a": "46", "b": "3", "c": "12"}, {"b": "2", "c": "7"}]
1 [{"b": "2", "c": "7"}, {"c": "11"}]
2 NaN
type(df.iloc[0, 0])
[out]: str
# fillna
df.col_str = df.col_str.fillna('[]')
# literal_eval
df.col_str = df.col_str.apply(literal_eval)
# explode
df = df.explode('col_str', ignore_index=True)
# fillna again
df.col_str = df.col_str.fillna({i: {} for i in df.index})
# use json_normalize
df = df.join(pd.json_normalize(df.pop('col_str')))
# display(df)
a b c
0 46 3 12
1 NaN 2 7
2 NaN 2 7
3 NaN NaN 11
4 NaN NaN NaN