How to create new column in DataFrame based on substrings from another columns using numpy

Viewed 20

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

1 Answers

Let us do str.startswith

df['new'] = np.where(df["dates"].str.startswith('Dec'), df["dates"] + "-2021", df["dates"] + "-2022")

df
Out[19]: 
   ids   dates          new
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
Related