Adding new row to a column in pandas pivot table

Question

Let's say I have 2 pivot tables: dfp1 and dfp2.

My current code is:

if not dfp2.empty:
 for key in dfp1['col1'].keys():
  if key not in dfp2['col2'].keys():
   dfp2['col2'][key] = 0

The purpose is to check if a value exist in col1 of dfp1 exists in col2 of dfp2. If not, make it 0.

However this does not add the value to dfp2['col2']. How should I go to add this 0 value?

Thanks in advance.

Edit:

Some sample data:

dfp1['col1']

*Currency Code
CNH         0.00
INR         0.00
JPY   -985718.00
SGD       -70.69
USD      9103.94

dfp2['col2']

JPY    8030442.00
MYR     153050.00
SGD     636836.44
USD     553863.09

Expected output for dfp2['col2']:

JPY    8030442.00
MYR     153050.00
SGD     636836.44
USD     553863.09
CNH     0.00
INR     0.00

have you tried pd.merge() then use df.fillna(0)? It would be helpful if you show a sample of your data — Ade_1
– Ade_1, Commented Aug 10, 2021 at 3:26
Can you share sample data and expected output, to help you better. — Ibrahim Ayoup
– Ibrahim Ayoup, Commented Aug 10, 2021 at 3:48

Shubham Sharma · Accepted Answer · 2021-08-10 04:43:19Z

1

We can align the two columns along the index axis

col2 = dfp2['col2'].align(dfp1['col1'], fill_value=0)[0]

col2 = dfp2['col2'].reindex(dfp2.index.union(dfp1.index), fill_value=0)

print(col2)

CNH          0.00
INR          0.00
JPY    8030442.00
MYR     153050.00
SGD     636836.44
USD     553863.09
Name: col2, dtype: float64

answered Aug 10, 2021 at 4:43

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

1 Answer 1