Skip to content
Advertisement

Create new pd dataframe column that gives a date based on day and week starting data

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!

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement