Extract data (date) from a string to use as a filter in python using for loops and if statements

Question

I am trying to use this date information:

date_of_report = "Wednesday, May 25, 2022"

to use in an if statement that will determine which range of cell to use:

months = {
    "Jan": 31,
    "Feb": 28,
    "Mar": 31,
    "Apr": 30,
    "May": 31,
    "Jun": 30,
    "Jul": 31,
    "Aug": 31,
    "Sep": 30,
    "Oct": 31,
    "Nov": 30,
    "Dec": 31,
}

days = [
    "Sun",
    "Mon",
    "Tue",
    "Wed",
    "Thu",
    "Fri",
    "Sat",
]

weeks = [
    "Week 1",
    "Week 2",
    "Week 3",
    "Week 4",

days_of_the_month_cells = {
    "Week 1": {
        "Sun": "D6:D22",
        "Mon": "E6:E22",
        "Tue": "F6:F22",
        "Wed": "G6:G22",
        "Thu": "H6:H22",
        "Fri": "I6:I22",
        "Sat": "J6:J22",
    }, "Week 2": {
        "Sun": "D23:D39",
        "Mon": "E23:E39",
        "Tue": "F23:F39",
        "Wed": "G23:G39",
        "Thu": "H23:H39",
        "Fri": "I23:I39",
        "Sat": "J23:J39",
    }, "Week 3": {
        "Sun": "D40:D56",
        "Mon": "E40:E56",
        "Tue": "F40:F56",
        "Wed": "G40:G56",
        "Thu": "H40:H56",
        "Fri": "I40:I56",
        "Sat": "J40:J56",
    }, "Week 4": {
        "Sun": "D57:D73",
        "Mon": "E57:E73",
        "Tue": "F57:F73",
        "Wed": "G57:G73",
        "Thu": "H57:H73",
        "Fri": "I57:I73",
        "Sat": "J57:J73",
    }

}

I have this code as a base but I cannot figure out how to use an if statement to filter the information from the string I have which is a date (I beleive this would need to be done in steps)

for item in days_of_the_month_cells.values():
            for day, range in item.items():
                for index, cells in enumerate(ws[range]):
                    for cell in cells:
                        if index == 0:
                            # if ?
                            cell.value = day

any help would be appreciate

Thanks!

It is not clear what you are trying to achieve. This is supposed to be a question but I don't see any question here. — Asocia, Jun 18 '22 at 07:55
I think you are going abou this the wrong way. Just parse your date and look that up in your tables. — Jay M, Jun 18 '22 at 07:56
I am trying to use an if statement to filter which cells are being used based on the date, how would I do that? — littlejiver, Jun 18 '22 at 07:56
You should convert your data into a DatFrame. That will make it easier to interact with. You can convert back to JSON if you like. — NelsonGon, Jun 18 '22 at 07:58
I never thought of use the a dateframe instead of JSON (it's late where I am :)) — littlejiver, Jun 18 '22 at 07:59
It looks even your sample of data is incomplete/incorrect - e.g. there is missing closing `]` of the `weeks` list (and have extra comma there). Also what about the 5th week in the month? And it's not clear what's the purpose of `months`, `day`, `weeks` lists anyway... — buran, Jun 18 '22 at 08:07

score 1 · Accepted Answer · answered Jun 18 '22 at 08:10



days = {
    "mon":1,
    "tue":2,
    "wed":3,
    "thu":4,
    "fri":5,
    "sat":6,
    "sun":7,
}

days_of_the_month_cells = {
    1: {
        "sun": "D6:D22",
        "mon": "E6:E22",
        "tue": "F6:F22",
        "wed": "G6:G22",
        "thu": "H6:H22",
        "fri": "I6:I22",
        "sat": "J6:J22",
    }, 2: {
        "sun": "D23:D39",
        "mon": "E23:E39",
        "tue": "F23:F39",
        "wed": "G23:G39",
        "thu": "H23:H39",
        "fri": "I23:I39",
        "sat": "J23:J39",
    }, 3: {
        "sun": "D40:D56",
        "mon": "E40:E56",
        "tue": "F40:F56",
        "wed": "G40:G56",
        "thu": "H40:H56",
        "fri": "I40:I56",
        "sat": "J40:J56",
    }, 4: {
        "sun": "D57:D73",
        "mon": "E57:E73",
        "tue": "F57:F73",
        "wed": "G57:G73",
        "thu": "H57:H73",
        "fri": "I57:I73",
        "sat": "J57:J73",
    }


def get_cell_address(datetime_str):
    dow, date = [x.strip() for x in date_of_report.split(",", 1)]
    dt = datetime.datetime.strptime(date, "%M %d, %Y")
    dow = dow[:3].lower()

    wk = dt.isocalendar()[1]
    assert dow == days[dt.isoweekday()], "Invalid day name"

    wom = wk % 4  # Get week number
    return days_of_the_month_cells[wom+1][days[dow]]

date_of_report = "Wednesday, May 25, 2022"
cell = get_cell_address(date_of_report)

Extract data (date) from a string to use as a filter in python using for loops and if statements

1 Answers1