0

''' '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'

Mahesh Katula
  • 3
  • 3

1 Answers1

0

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.

pauescofet
  • 98
  • 1
  • 10
  • yes i have tried this but there are many other sheets in the excel file which use this Week_No column so it cannot be in text format!!!!! – Mahesh Katula Jun 01 '22 at 08:30