0

I am trying to figure out how to turn date time from a csv into the day of week equivelant, i have a column in a CSV with date format as listed below (Y.M.D) and i want to turn that into mon, tue, wed, thur, fri, sat, sun, instead. I have data corresponding to each date, I need a variable for each day of the 7 day week for an ML model. (Im trying to see if theres a correlation between my dependant variables and the specific day of the week)

I want to turn this

Day
2019.12.24
2019.12.26
2019.12.27
2019.12.28
2019.12.29
2019.12.30
2019.12.31

into this

Day
'Wednesday'
'Thursday'
'Friday'
'Saturday'
'Sunday'
'Monday'
'Tuesday'

The data is In a CSV in google sheets if anyone knows how to do it in there (google sheets) that would be ideal, or in Python that would be good as well, any help is much appreciated.

shimo
  • 2,156
  • 4
  • 17
  • 21
oz.vegas
  • 129
  • 7
  • Does this answer your question? [Convert Date String to Day of Week](https://stackoverflow.com/questions/16766643/convert-date-string-to-day-of-week) – wwii Dec 31 '19 at 21:59

3 Answers3

1

You need to convert your string into datetime obj like that: And then you will be able to export day [or any other date property]

def date_to_week_day(date_time_str):
  date_time_obj = datetime.datetime.strptime(date_time_str, '%Y-%m-%d')
  logging.debug( date_time_obj.isoweekday())
  return date_time_obj.isoweekday()
hd1
  • 33,938
  • 5
  • 80
  • 91
Oded BD
  • 2,788
  • 27
  • 30
1

In google-sheets

Begin with search_replace . with - for your date column then run

=CHOOSE( weekday(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")

Kenan
  • 13,156
  • 8
  • 43
  • 50
0

If you'd prefer to do this in Google Sheets and not modify the Day column, you can use the following formula to get the day of the week:

=TEXT(DATE(INDEX(SPLIT(A2, "."), 0, 1), INDEX(SPLIT(A2, "."), 0, 2), INDEX(SPLIT(A2, "."), 0, 3)), "dddd")
Jesse Young
  • 23
  • 4