I have a pandas dataframe that has two columns, the first column is ‘Week Starting’ and the other is ‘Day’. I wanna create a new column that uses the data from the other two columns to give a full date. For example, from the table below, the first entry of the new column should be 5/04/2021 and the second should be should 6/04/2021.
Week Starting | Day |
---|---|
5/04/2021 | Monday |
5/04/2021 | Tuesday |
5/04/2021 | Wednesday |
I’ve tried the follwing the solution but i get and error
g['Week Starting'] = pd.to_datetime(g['Week Starting']) conditions = [ (g['Day'] == 'Monday'), (g['Day'] == 'Tuesay'), (g['Day'] == 'Wednesday')] values = [g['Week Starting'],(g['Week Starting'] + timedelta(days=1)), (g['Week Starting'] + timedelta(days=2))] g['Date'] = np.select(conditions, values)
ERROR:
The DTypes <class ‘numpy.dtype[uint8]’> and <class ‘numpy.dtype[datetime64]’> do not have a common DType. For example they cannot be stored in a single array unless the dtype is object
.
Thanks.
Advertisement
Answer
I think this would be the simplest solution:
df = pd.DataFrame({"week_starting":["04/05/2021","04/05/2021","04/05/2021"], "day":["Monday","Tuesday","Wednesday"]}) df['week_starting'] = pd.to_datetime(df['week_starting']) conditions = {"Monday":0,"Tuesday":1,"Wednesday":2} df["date"] = df.apply(lambda x:x['week_starting']+pd.Timedelta(conditions[x["day"]],"day"),axis=1)
You add the timedelta to each date using the apply method.
Hope it works!