Append dictionary values to dataframe if values are missing using Pandas-CodePudding

I have a dataset where I would like to append rows if this value is missing from a specific column.

Data

ID  Date    type    energy
AA  Q1 2022 a       1
AA  Q2 2022 ok      1
AA  Q3 2022 yes     1
AA  Q4 2022 yes     4
FC  Q1 2022 no      4
FC  Q2 2022 no      4
FC  Q3 2022 yes     45
FC  Q4 2022 yes     5

Desired

ID  Date    type    energy
AA  Q1 2022 a       1
AA  Q2 2022 ok      1
AA  Q3 2022 yes     1
AA  Q4 2022 yes     4
FC  Q1 2022 no      4
FC  Q2 2022 no      4
FC  Q3 2022 yes     45
FC  Q4 2022 yes     5
BC  Q1 2022         0
DA  Q1 2022         0

Doing

#values =   {'BC': 'Q1 2022', 'DA': 'Q1 2022}
#df1 = df.merge(df, how='left').fillna({'energy': 0})

However, this is not appending, not sure how to attach the values dictionary within this script. I am still researching, any assistance is appreciated.

CodePudding user response：

You can try with a dict-comprehension and append. In a one-liner:

df = df.append(pd.DataFrame({'ID':[x for x in values],
                        'Date':[values[x] for x in values]}))

CodePudding user response：

We could do

apdf = pd.Series(values).reset_index()
apdf.columns = df.columns[:2]
df = pd.concat([df, apdf]).fillna({'energy': 0})

CodePudding user response：

Python expects a list for the values of the dictionary rather than values. Then, use append()

df1 = pd.DataFrame({'BC': ['Q1 2022'], 'DA': ['Q1 2022']})
df = df.append(df1)

# replace to satisfy the desired output
df['energy'].fillna(0, inplace=True)
df['type'].fillna('', inplace=True)

CodePudding user response：

def setter(df:pd.DataFrame, dictionary: dict):
    temp = pd.DataFrame(
        data = [[*data.split(), "", 0] for data in dictionary.values()],
        index=list(dictionary.keys()),
        columns=['ID', 'Date', 'type', 'energy'])
    return pd.concat([df, temp])
setter(df, {'BC': 'Q1 2022', 'DA': 'Q1 2022'})

data = pd.DataFrame({'BC': 'Q1 2022', 'DA': 'Q1 2022'}.items())
data = pd.concat([data[0], data[1].str.split(expand=True).rename(columns={0: 'ID', 1: 'Date'})], axis=1)
data['type'] = ""
data['energy'] = 0