I have a database with a column describing the dates when a certain data was collected. However, the dates were inserted as MM-DD (eg., Jul-13) and they are coded as string.
ids = pd.Series([1, 2, 3, 4])
dates = pd.Series(["Jul-29", "Jul-29", "Dec-29", "Apr-22"])
df = pd.DataFrame({"ids" : ids, "dates" : dates})
ids dates
0 1 Jul-29
1 2 Jul-29
2 3 Dec-29
3 4 Apr-22
I would like to insert the year in these dates before converting to date based on a condition. I know that data from December belongs to 2021, whereas the rest of the data was collected in 2022. Therefore I need something like this:
ids dates corrected_dates
0 1 Jul-29 Jul-29-2022
1 2 Jul-29 Jul-29-2022
2 3 Dec-29 Dec-29-2021
3 4 Apr-22 Apr-22-2022
I have tried:
df["corrected_dates"] = np.where("Dec" in df["dates"], df["dates"] + "-2021", df["dates"] + "-2022")
but this resulted in
ids dates corrected_dates
0 1 Jul-29 Jul-29-2022
1 2 Jul-29 Jul-29-2022
2 3 Dec-29 Dec-29-2022
3 4 Apr-22 Apr-22-2022
Therefore, I am probably not coding the conditional properly but I can't find out what I am doing wrong.
I was able to insert the the year in a new column by doing
corrected_dates = []
for date in df["dates"]:
if "Dec" in date:
new_date = date + "-2021"
else:
new_date = date + "-2022"
corrected_dates.append(new_date)
and then df["corrected_dates"] = corrected_dates but this seems too cumbersome (not to mention that I am not sure this would work if there were missing data in df["dates"].
Can anyone help me understand what I am doing wrong when using np.where() or suggest a better alternative than using a for loop?
Thanks