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

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!

CodePudding user response：

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

CodePudding user response：

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)