Add new keys and values to an existing dictionary where the keys are matched

Question

I would like to calculate the cost of product c = 20 $ and d = 30$. Since I have meta data and df as separated data frame I need to join them somehow where I can then get the price and the number of items bought for each product (e.g. c:5) in a dictionary for each individual id, and then calculated the price of each product (e.g. for product c 1 * 20)

1. My First data frame

   Metadata  = {'product_name': ["c", "d"], 'product_price': [20, 30]}
   Metadata = pd.DataFrame(data=Metadata )
   

2. My second data frame

   df = pd.DataFrame({'id':[1,2,3], 'product':[{'c':1}, {'d':3}, {'c':5, 
   'd':6}]})
   

Edited

3. My attempts: So I thought I need to get access to the keys and then use them for later for matching. I start with converting the Metadata table into a dictionary:

   def get_product_price_dictionary(Metadata):
       product_info = Metadata
       product_price_dict = dict()
       for d in product_info.to_dict('records'): 
           p_name = d["product_name"]
           p_price = d["product_price"]
           product_price_dict[p_name] = p_price
       return product_price_dict
   test = get_product_price_dictionary(Metadata)
   test 
   

Output:

{'c': 20, 'd': 30}

Then I get the keys inside my data frame.

list_keys = []
df_dic = df['product']
for i in range(len(df_dic)):
    if df_dic.iloc[i] is not None:
        each_dic = df_dic.iloc[i]
    for key, value in each_dic.items():
        list_keys.append(key)
list_keys_uique = list(set(list_keys))
list_keys_uique[0:5]

Output

['c', 'd']

I have recently get started working with python and now, I am really stuck in working with dictionary! to get the column called product_cost in the df data frame.

And now I do not know how to precede with it!!!

Answer 1

I would not turn everything to dict as Pandas is already very fast. You can search for certain values within a database using double equators together with the row name:

df[df['row']==key].value

I added a little piece of code which walks your database and calculates the total money of each transaction:

Metadata  = {'product_name': ["c", "d"], 'product_price': [20, 30]}
Metadata = pd.DataFrame(data=Metadata )

df = pd.DataFrame({'id':[1,2,3], 'product':[{'c':1}, {'d':3}, {'c':5, 'd':6}]})

print (Metadata)
print (df)

for action in df['product']:
  print ('action:', action)
  total = 0
  for product in action:
    price = float(Metadata[Metadata['product_name']==product].product_price)
    print ('  product: %s, price: %.2f' % (product, price))
    print ('    count: %i, sum: %.2f' % (action[product], price * action[product]))
    total += price * action[product]
  print ('  total: %.2f' % total)

Console output of the above code:

  product_name  product_price
0            c             20
1            d             30
   id           product
0   1          {'c': 1}
1   2          {'d': 3}
2   3  {'c': 5, 'd': 6}
action: {'c': 1}
  product: c, price: 20.00
    count: 1, sum: 20.00
  total: 20.00
action: {'d': 3}
  product: d, price: 30.00
    count: 3, sum: 90.00
  total: 90.00
action: {'c': 5, 'd': 6}
  product: c, price: 20.00
    count: 5, sum: 100.00
  product: d, price: 30.00
    count: 6, sum: 180.00
  total: 280.00