CODEWITHSUNDEEP
pythondataframedatetime
realkes
realkes

Reputation: 881

Combining year, month, week number and day to date

I have the below dataframe:

year  month  week_num    day
2019   8       31       Thurs
2019   8       31        Fri
2019   8       32        Tues

The day abbreviations are Mon, Tues, Weds, Thurs, Fri, Sat, Sun.

I want to generate another column which will provide me the date in yyyy-mm-dd format. How can I do that? Thanks in advance!

Upvotes: 1

Views: 404

Answers (1)

Alexandre B.
Alexandre B.

Reputation: 5500

The module datetime gives you this opportunity. This discussion explains how to get the date from the week number.

Then, you can define a function to get the date and apply it to you dataframe.

Here the code:

# Import modules
import datetime

# Your data
df = pd.DataFrame([
                   [2019, 8, 29, "Fri"],
                    [2019, 8, 31, "Sun"],
                   [2019, 8, 29, "Tues"]],
                  columns=["year", "month", "week_num", "day"])

# A value per day
val_day = {"Mon": 0, "Tues": 1, "Weds": 2, "Thurs": 3,
           "Fri": 4, "Sat": 5, "Sun": 6}

# Get the date from the year, number of week and the day


def getDate(row):
    # Create string format
    str_date = "{0}-W{1}-1".format(row.year,
                                     row.week_num - 1)
    print(str_date)
    # Get the date
    date = datetime.datetime.strptime(
        str_date, "%Y-W%W-%w") + datetime.timedelta(days=val_day[row.day])
    # Update date field
    row["date"] = date.strftime("%Y-%m-%d")
    return row


# apply the function to each row
df = df.apply(getDate, axis=1)
print(df)
#    year  month  week_num    day        date
# 0  2019      8         1  Thurs  2019-01-03
# 1  2019      8        29    Fri  2019-07-19
# 2  2019      8        29   Tues  2019-07-16

Upvotes: 2

Related Questions