How do I convert an object to a week number in datetime

Question

I am a beginner in Python and I am trying to change column names that currently represent the week number, to something easier to digest. I wanted to change them to show the date of the week commencing but I am having issues with converting the types.

I have a table that looks similar to the following:

import pandas as pd

data = [[0,'John',1,2,3]

df = pd.dataframe(data, columns = ['Index','Owner','32.0','33.0','34.0']

print(df)

I tried to use df.melt to get a column with the week numbers and then convert them to datetime and obtain the week commencing from that but I have not been successfull.

df = df.melt(id_vars=['Owner'])
df['variable'] = pd.to_datetime(df['variable'], format = %U)

This is as far as I have gotten as I have not been able to obtain the week number as a datetime type to then use it to get the week commencing.

After this, I was going to then transform the dataframe back to its original shape and have the newly obtained week commencing date times as the column headers again.

Can anyone advise me on what I am doing wrong, or alternatively is there a better way to do this?

Any help would be greatly appreciated!

score 0 · Answer 1 · answered Dec 06 '21 at 14:17

One solution to convert a week number to a date is to use a timedelta. For example you may have

from datetime import timedelta, datetime

week_number = 5
first_monday_of_the_year = datetime(2021, 1, 3)
week_date = first_monday_of_the_year + timedelta(weeks=week_number)

jezrael · Accepted Answer · 2021-12-07T06:32:30.353

Add Index column to melt first for only week values in variable, then convert to floats, integers and strings, so possible match by weeks:

data = [[0,'John',1,2,3]]

df = pd.DataFrame(data, columns = ['Index','Owner','32.0','33.0','34.0'])

print(df)
   Index Owner  32.0  33.0  34.0
0      0  John     1     2     3

df = df.melt(id_vars=['Index','Owner'])

s = df['variable'].astype(float).astype(int).astype(str) + '-0-2021'
print (s)
0    32-0-2021
1    33-0-2021
2    34-0-2021
Name: variable, dtype: object

#https://stackoverflow.com/a/17087427/2901002
df['variable'] = pd.to_datetime(s, format = '%W-%w-%Y')

print (df)
   Index Owner   variable  value
0      0  John 2021-08-15      1
1      0  John 2021-08-22      2
2      0  John 2021-08-29      3

EDIT:

For get original DataFrame (integers columns for weeks) use DataFrame.pivot:

df1 = (df.pivot(index=['Index','Owner'], columns='variable', values='value')
         .rename_axis(None, axis=1))
df1.columns = df1.columns.strftime('%W')
df1 = df1.reset_index()
print (df1)
   Index Owner  32  33  34
0      0  John   1   2   3

Thank you very much for this! It worked perfectly. Do you have any advice on how to then get these values back into where the week numbers were originally i.e. get these dates back to being column headers? If I use the .pivot/.pivot_table method the values get aggregated so I am a little stuck now on what to do. — Kris, Dec 06 '21 at 15:40

How do I convert an object to a week number in datetime

2 Answers2