pythonexcelpandasdatetimexlsb

Is there a way to set weeknumber/year format from a datetime column in pandas

''' 'I'm trying to set a column in datetime format like this and create a new column Week_No in week number/year format but when I write it to excel it converts it to mm/year format'

datecolumn = ["Date"]
df_2021 = pd.read_csv("C:/Users/Lenovo/Downloads/2021.csv", sep = ";", parse_dates=datecolumn).iloc[:-7]
df_2022 = pd.read_csv("C:/Users/Lenovo/Downloads/2022.csv", sep = ";", parse_dates=datecolumn).iloc[:-7]
df_combined = pd.concat([df_2021,df_2022],ignore_index=False)
df_combined["Date_New"] = pd.to_datetime(df_combined["Date_New"], format='%d/%m/%Y' )
WeekNo = []

def getWeekYear(isodate):
    year = isoDate[0]
    week = isoDate[1]
#     print(f"{week}-{year}")
    cellValue = f"{week}-{year}"
#     df_combined["Week_No"] = cellValue
    
#     WeekNo = []
    WeekNo.append(cellValue)

for value in df_combined["Date_New"]:
    day = int(value[:2])
    month = int(value[3:5])
    year = int(value[6:])
    isoDate = date(year, month, day).isocalendar() 
    getWeekYear(isoDate)

df_combined["Week_No"]= WeekNo

''' 'Also I need the Week_No to be in **Number ** format when I write it in excel'

Solution

  • If this only happens when you write it to excel it might be because the type of the excel cell is set to date, and by default, the content of the cell will be changed to the date format defined in excel.

    I would suggest setting the type of the excel cells to text instead of date, so excel does not overwrite the content.

    Otherwise, you can change the date format defined in your excel sheet, so the defined format is week number/year.