0

I have the following dataframe in Pandas:

column_names = ["item", "item_dict", "loc1", "loc2", "loc3", "loc4", "loc5", "loc6", "loc7", "loc8", "loc9"]
data = [['p26CE0DEAC1', {'loc1': 50, 'loc4': 50, 'loc7': 33}, 0, 0, 0, 0, 0, 0, 0, 0, 0], ['p5505CB1A96', {'loc1': 48}, 0, 0, 0, 0, 0, 0, 0, 0, 0], ['p1B9E6A73EC', {}, 0, 0, 0, 0, 0, 0, 0, 0, 0]]

df_sample = pd.DataFrame(data, columns = column_names)

The first column ["item"] is an identification number of an item. The second column ["item_dict"] is a dictionary with a key showing an item location and a value showing the quantity of the item at the location. The third through eleventh columns correspond to each of the locations where items are stored and those columns labels match the keys from the item_dict column.

What I'm trying to figure out is how to take the dictionary embedded in each row's item_dict column and move the values to the columns with a name that matches the key in that dictionary.

I want the output to end up being:

column_names = ["item", "item_dict", "loc1", "loc2", "loc3", "loc4", "loc5", "loc6", "loc7", "loc8", "loc9"]
data = [['p26CE0DEAC1', {'loc1': 50, 'loc4': 50, 'loc7': 33}, 50, 0, 0, 50, 0, 0, 33, 0, 0], ['p5505CB1A96', {'loc1': 48}, 48, 0, 0, 0, 0, 0, 0, 0, 0], ['p1B9E6A73EC', {}, 0, 0, 0, 0, 0, 0, 0, 0, 0]]

df_sample = pd.DataFrame(data, columns = column_names)

As a hobbyist I'm not sure how to do this. Each of my attempts have been horrible failures. If someone even has a reference for using dictionaries that are embedded in a pandas dataframe they can point me to, I'd appreciate it but I wasn't able to find anything on stack overflow addressing this scenario.

mgadfly
  • 77
  • 8
  • Is this question a duplicate of this one: [Split / Explode a column of dictionaries into separate columns with pandas](https://stackoverflow.com/q/38231591/1609514)? – Bill May 06 '21 at 22:34
  • Thanks! I didn't see that one, but it is a little different as it adds new columns in a new dataframe while I was looking to update existing columns in the same dataframe. I'm going to read up on the update function that Corralien used as that did the trick. – mgadfly May 06 '21 at 22:47

1 Answers1

1
df.update(df["item_dict"].apply(pd.Series))

>>> df
          item                             item_dict  loc1  loc2  loc3  loc4  loc5  loc6  loc7  loc8  loc9
0  p26CE0DEAC1  {'loc1': 50, 'loc4': 50, 'loc7': 33}  50.0     0     0  50.0     0     0  33.0     0     0
1  p5505CB1A96                          {'loc1': 48}  48.0     0     0   0.0     0     0   0.0     0     0
2  p1B9E6A73EC                                    {}   0.0     0     0   0.0     0     0   0.0     0     0
Corralien
  • 109,409
  • 8
  • 28
  • 52