Split / Explode a column of dictionaries into separate columns with pandas

👻 Check our latest review to choose the best laptop for Machine Learning engineers and Deep learning tasks!

I have data saved in a postgreSQL database. I am querying this data using Python2.7 and turning it into a Pandas DataFrame. However, the last column of this dataframe has a dictionary of values inside it. The DataFrame df looks like this:

Station ID     Pollutants
8809           {"a": "46", "b": "3", "c": "12"}
8810           {"a": "36", "b": "5", "c": "8"}
8811           {"b": "2", "c": "7"}
8812           {"c": "11"}
8813           {"a": "82", "c": "15"}

I need to split this column into separate columns, so that the DataFrame ’df2 looks like this:

Station ID     a      b       c
8809           46     3       12
8810           36     5       8
8811           NaN    2       7
8812           NaN    NaN     11
8813           82     NaN     15

The major issue I"m having is that the lists are not the same lengths. But all of the lists only contain up to the same 3 values: "a", "b", and "c". And they always appear in the same order ("a" first, "b" second, "c" third).

The following code USED to work and return exactly what I wanted (df2).

objs = [df, pandas.DataFrame(df["Pollutant Levels"].tolist()).iloc[:, :3]]
df2 = pandas.concat(objs, axis=1).drop("Pollutant Levels", axis=1)
print(df2)

I was running this code just last week and it was working fine. But now my code is broken and I get this error from line [4]:

IndexError: out-of-bounds on slice (end) 

I made no changes to the code but am now getting the error. I feel this is due to my method not being robust or proper.

Any suggestions or guidance on how to split this column of lists into separate columns would be super appreciated!

EDIT: I think the .tolist() and .apply methods are not working on my code because it is one Unicode string, i.e.:

#My data format 
u{"a": "1", "b": "2", "c": "3"}

#and not
{u"a": "1", u"b": "2", u"c": "3"}

The data is imported from the postgreSQL database in this format. Any help or ideas with this issue? is there a way to convert the Unicode?

👻 Read also: what is the best laptop for engineering students?

We hope this article has helped you to resolve the problem. Apart from Split / Explode a column of dictionaries into separate columns with pandas, check other ast Python module-related topics.

Want to excel in Python? See our review of the best Python online courses 2023. If you are interested in Data Science, check also how to learn programming in R.

By the way, this material is also available in other languages:

• Italiano Split / Explode a column of dictionaries into separate columns with pandas
• Deutsch Split / Explode a column of dictionaries into separate columns with pandas
• Français Split / Explode a column of dictionaries into separate columns with pandas
• Español Split / Explode a column of dictionaries into separate columns with pandas
• Türk Split / Explode a column of dictionaries into separate columns with pandas
• Русский Split / Explode a column of dictionaries into separate columns with pandas
• Português Split / Explode a column of dictionaries into separate columns with pandas
• Polski Split / Explode a column of dictionaries into separate columns with pandas
• Nederlandse Split / Explode a column of dictionaries into separate columns with pandas
• 中文 Split / Explode a column of dictionaries into separate columns with pandas
• 한국어 Split / Explode a column of dictionaries into separate columns with pandas
• 日本語 Split / Explode a column of dictionaries into separate columns with pandas
• हिन्दी Split / Explode a column of dictionaries into separate columns with pandas