Append rows to dataset if missing from declared dictionary in Python

Let us create a delta dataframe from the items of dictionary, then do a outer merge to append distinct rows

delta = pd.DataFrame(values.items(), columns=['ID', 'Date'])
df.merge(delta, how='outer')

      ID      Date Type  Cost
0  Alpha   Q1 2022   ok   1.0
1  Alpha   Q2 2022   ok   1.0
2  Alpha   Q3 2022   hi   1.0
3  Alpha   Q4 2022   hi   2.0
4  Gamma   Q1 2022  NaN   NaN
5  Theta   Q1 2022  NaN   NaN

My test data:

df = pd.DataFrame({'col1': [1, 2],
                   'col2': [0.5, 0.75]})
values = {1: 0.5, 4: 2.0}

First get the keys of your dataframe:

existing_items = dict(zip(df.col1, df.col2))

Find the items in your dict that are not in the dataframe:

difference = set(values.items())-set(existing_items.items()

Make it a dictionary again:

diff_dict = dict(difference)

Results: {2: 0.75}

And then append those results to your pandas dataframe: https://stackoverflow.com/a/71132602/9611924

You were almost there. pandas.dataframe.merge is the right method to go with. What you missed was to create the correct table as it should look like (with regard to the columns ID and *Date). So let's do this again step by step.

Assuming, we have your dataframe

import pandas as pd

df = pd.DataFrame([{'ID': 'Alpha', 'Date': 'Q1 2022', 'Type': 'OK', 'Cost': 1},
                   {'ID': 'Alpha', 'Date': 'Q2 2022', 'Type': 'OK', 'Cost': 1},
                   {'ID': 'Alpha', 'Date': 'Q3 2022', 'Type': 'hi', 'Cost': 1},
                   {'ID': 'Alpha', 'Date': 'Q4 2022', 'Type': 'hi', 'Cost': 2}])

Let's construct the desired dataframe. (You could also just construct a table with the missing rows but to me it looks like an easy pattern that you may want to scale.) For now I assume a pattern ('alpha', 'beta' for the column ID and 4 quarters per year):

id_keys = ['Alpha', 'Beta']
date_years = [2022]

rows = []
for id in id_keys:
    # construct dates
    for y in date_years:
        for q in range(1, 5):
            rows.append({'ID': id, 'Date': f'Q{q} {y}'})
        
df_ideal = pd.DataFrame(rows)

Now go ahead and merge the actual table df with the just constructed, desired table df_ideal:

df.merge(df_ideal, on=['ID', 'Date']).fillna({'Cost': 0})

This question and the above answers focus on merging two frames; however, have you tried concatenating them? It might be easier if the values for the type and cost column change for Gamma and Theta in the future. If you merge - you will get NaN. Here is how I would go about it,

a working notebook is located here- https://colab.research.google.com/drive/10DCteuD02HVnYHRX8aZyq_Mi2IW6SAHz?usp=sharing

data1 = data.copy(deep=True) #copy given data 
values =   { 'Gamma':' Q1 2022', 'Theta': 'Q1 2022'} #I removed alpha
data2 = pd.DataFrame(columns=data1.columns)
data2['ID'] = values.keys()
data2['Date'] = data2['ID'].map(values)
((pd.concat([data1,data2])).reset_index(drop=True)).fillna({'Cost': 0,'Type':""})

The output I got is -

ID  Date    Type    Cost
0   Alpha   Q1 2022 OK  1
1   Alpha   Q2 2022 OK  1
2   Alpha   Q3 2022 hi  1
3   Alpha   Q4 2022 hi  2
4   Gamma   Q1 2022     0
5   Theta   Q1 2022     0

An attempt to functionalize the above to solve a common problem in retail ETL flow and automate will be -

def add_me(data1:pd.DataFrame,values:dict,COL1,COL2,COL3,COL4) -> pd.DataFrame:
  """
  COL1,COL2 - ARE COLUMNS IN VALUE MAP,
  COL3,COL4 - FILL NA WITH ZERO with integer or space in case of string
  """
  data2 = pd.DataFrame(columns=data1.columns)
  data2[COL1] = values.keys()
  data2[COL2] = data2[COL1].map(values)
  return ((pd.concat([data1,data2])).reset_index(drop=True)).fillna({COL3: 0,COL4:""})

executing above, spits out:

ID  Date    Type    Cost
0   Alpha   Q1 2022 OK  1
1   Alpha   Q2 2022 OK  1
2   Alpha   Q3 2022 hi  1
3   Alpha   Q4 2022 hi  2
4   Gamma   Q1 2022     0
5   Theta   Q1 2022     0

Here is a way using set_index() and using reindex() with a newly created MultiIndex.

cols = ['ID','Date'] #columns to include in MultiIndex.

df = df.set_index(cols) #set columns as MultiIndex

    (df.reindex(
    df.index.union(pd.MultiIndex.from_tuples(values.items(),names = cols)) #Create union with index created above and values dictionary
    )
.reset_index()
.fillna({'Type':'','Cost':0}))

Output:

      ID      Date Type  Cost
0  Alpha   Q1 2022   ok   1.0
1  Alpha   Q2 2022   ok   1.0
2  Alpha   Q3 2022   hi   1.0
3  Alpha   Q4 2022   hi   2.0
4  Gamma   Q1 2022        0.0
5  Theta   Q1 2022        0.0