Append dictionary values to dataframe if values are missing using Pandas

Question

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.

BENY · Accepted Answer · 2022-08-09 00:58:18Z

1

We could do

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

answered Aug 9, 2022 at 0:58

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Celius Stingher · Accepted Answer · 2022-08-09 00:02:49Z

1

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]}))

answered Aug 9, 2022 at 0:02

Celius Stingher

18.4k6 gold badges26 silver badges54 bronze badges

1 Comment

MoRe Over a year ago

append? deprecated...

7shoe · Accepted Answer · 2022-08-09 00:05:12Z

0

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)

edited Aug 9, 2022 at 0:05

answered Aug 9, 2022 at 0:02

7shoe

1,5261 gold badge8 silver badges13 bronze badges

2 Comments

Celius Stingher Over a year ago

Not a fan of manually inputting the data for the DataFrame function

mitoRibo Over a year ago

agreed but I think this answer makes it clear how to go about creating a df from a dict which I think might be where the OP was stuck

MoRe · Accepted Answer · 2022-08-09 10:59:12Z

0

values = {'BC': 'Q1 2022', 'DA': 'Q1 2022'}
df = pd.concat([df, pd.DataFrame({
    "Date": values.values(),
    "type": "",
    "energy": 0,
    }, index=values.keys())])

edited Aug 9, 2022 at 10:59

answered Aug 8, 2022 at 23:59

MoRe

2,3942 gold badges5 silver badges26 bronze badges

Collectives™ on Stack Overflow

Append dictionary values to dataframe if values are missing using Pandas

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related