Pandas ValueError: cannot reindex from a duplicate axis when trying to add a new column

I have this data:

          Inv Dt        Due Dt
22        2020-10-31    2020-11-15
181       2020-10-01    2020-11-15
182       2020-10-01    2020-11-15
1845      2020-10-30    2020-11-14
2185      2020-10-14    2020-10-16
... ... ...
3080858   2020-09-01    2020-09-01
3080900   2020-09-30    2020-10-08
3081015   2020-09-19    2020-12-18
3081022   2020-09-17    2020-09-25
3081051   2020-09-03    2020-09-03

When I run this:

rep_date = pd.to_datetime('2021-08-31')
(comp_data.loc[(comp_data['Inv Dt'] <= rep_date) & (comp_data['Due Dt'] > rep_date), 'Due Dt'] - rep_date).dt.days

As expected, I get this Series:

4766385     68
4766388     68
81077        6
81081        6
81082        6
            ..
12747495    29
12747496    29
12747595    40
12747596    40
12748738    82

But when I try to add a column with that series to my data with this code:

comp_data['Aug-2021 DPO'] = (comp_data.loc[(comp_data['Inv Dt'] <= rep_date) & (comp_data['Due Dt'] > rep_date), 'Due Dt'] - rep_date).dt.days

I get this error: Pandas ValueError: cannot reindex from a duplicate axis

Answer

The reason is that you got duplicated indexes, so I suggest you to make the index a column and add a new index:

comp_data = comp_data.reset_index()
comp_data['Aug-2021 DPO'] = (comp_data.loc[(comp_data['Inv Dt'] <= rep_date) & (comp_data['Due Dt'] > rep_date), 'Due Dt'] - rep_date).dt.days