How to convert excel strings to datetime?

Question

Python

There is an Excel file, the structure is as follows:


The first column contains the month and year, then there are values ​​for each day.
How can this be elegantly converted to a datetime DataFrame so that the first column is the date and the second is the data?

Answer 1

Pull out data from excel to python lists (whichever is more convenient for you).
It turns out:

data=[
    ['дата', '1', '2', '3', '4', '5', '6', '7', ...],
    ['Ноябрь 2019', '11', '22', '33', '44', '55', '66', '77', ...],
    ['Декабрь 2019', '11', '22', '33', '44', '55', '66', '77', ...],
    ...
]

Then collect the data in the desired format:

import datetime
import locale

def replace_month(dt: str) -> str:
    dt = dt.replace('Январь', 'января')
    dt = dt.replace('Февраль', 'февраля')
    # ...
    return dt

locale.setlocale(locale.LC_TIME, "ru_RU.utf8")
head = data[0]
data = data[1:]
result_rows = []
for row in data:
    for day, value in zip(head[1:], row[1:]):
        dt = replace_month(f'{day} {row[0]}')
        try:
            dt = datetime.datetime.strptime(dt, '%d %B %Y')
        except ValueError:
            pass  # тут у нас дата не сконвертнулась, например, 30 февраля. Пофиг.
        else:
            result_rows.append((dt, value))

I wrote the code in the browser, did not check it, so it may have been misprinted somewhere. Don't be embarrassed.
If the date formats are not consistent, multiple conversion attempts can be made across different formats and locales.
Throwing it back into excel is also a separate task.

Answer 2

Hmm, have you tried selecting the first column, pressing Ctrl+1 and setting the cell type as the Custom category and the d-mmm type?

Answer 3

We need an Excel file in which there is what is and what is needed.
For the screenshot does not convey the nuances of cell formatting.
As they say on the forums on "photos do not treat."