Python Forum
How to convert dates in odd format to months
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to convert dates in odd format to months
#1
So I have a column called 'age' in my sql column that is inconsistent in format. It lists ages of kids in days, weeks or months. For example:

Output:
0d 6d 7d 1w, 3d 7w, 5d 4m 5m
What I want to do is convert that into months, like, anyone under 30 days would be 0 months, anyone between 1-2 months would be 1 month, etc. The max-age I have is seven months, so it would be changed to one of eight categories: 0,1,2,3,4,5,6,7.

I was thinking of a clunky way of doing it, like searching to see if there is a 'w', and if so, remove the w and everything after the w, and take that number. Do the same for days, and then for months. But I am sure there has to be a cleaner way?
Reply
#2
Maybe something like this? It assumes a month is exactly 30 days. Not correct, but pretty close....

sample = """0d
6d
7d
1w, 3d
7w, 5d
4m
5m
"""

period = {
        "d": 1,
        "w": 7,
        "m": 30,
        }

for line in sample.splitlines():
    total_time = 0
    for time in line.split(", "):
        qty, unit = time[:-1], time[-1]
        qty = int(qty)
        total_time += qty * period[unit]
        months = total_time // 30
    print(f"{line} => time in days: {total_time}, time in full months: {months}")
Output:
0d => time in days: 0, time in full months: 0 6d => time in days: 6, time in full months: 0 7d => time in days: 7, time in full months: 0 1w, 3d => time in days: 10, time in full months: 0 7w, 5d => time in days: 54, time in full months: 1 4m => time in days: 120, time in full months: 4 5m => time in days: 150, time in full months: 5
Reply
#3
Thank you! That worked wonderfully.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Illegal instruction? working code for months? korenron 4 1,278 Aug-05-2021, 09:57 AM
Last Post: korenron
  Convert timedelta to specified format Planetary_Assault_Systems 3 859 Jun-27-2021, 01:46 PM
Last Post: snippsat
  Matplotlib: How do I convert Dates from Excel to use in Matplotlib JaneTan 1 1,036 Mar-11-2021, 10:52 AM
Last Post: buran
  Convert email addresses to VCF format jehoshua 2 2,317 Mar-06-2021, 12:50 AM
Last Post: jehoshua
  Convert date integers (ex. 43831) to regular format Galven 2 999 Nov-15-2020, 11:38 PM
Last Post: bowlofred
  How to calculate a months' 1st, 4th, 7th day and also 1st again? cananb 3 887 Nov-12-2020, 08:23 AM
Last Post: perfringo
  CPC File Format (Cartesian Perceptual Compression) - Can Python Convert / Handle Them PSKrieger 2 932 Nov-11-2020, 02:57 PM
Last Post: PSKrieger
  How to groupby Months showing average order value - Pandas & matplotlib Rwood90 0 731 Oct-20-2020, 12:53 PM
Last Post: Rwood90
  'Age' categorical (years -months -days ) to numeric Smiling29 4 1,389 Oct-17-2019, 05:26 PM
Last Post: Smiling29
  Convert SAS Dates Format in a loop MohanReddy 2 1,567 Apr-02-2019, 10:31 AM
Last Post: scidam

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020