Python：从奇怪的时间戳转换日期

Question

我正在尝试从excel中读取列，该列位于URL:https://www.ema.europa.eu/sites/default/files/Medicines_output_european_public_assessment_reports.xlsx

我正在使用pythonxlrd模块读取这个excel文件。我正在以dict格式一行一行地读（注意：这里，我修改了小写的dict键）

示例：（单行）

{
    'category': 'Human',
    'medicine name': 'Avastin',
    'therapeutic area': 'Carcinoma, Non-Small-Cell Lung, Breast Neoplasms, Ovarian Neoplasms, Colorectal Neoplasms, Carcinoma, Renal Cell',
    'international non-proprietary name (inn) / common name': 'bevacizumab',
    'active substance': 'bevacizumab',
    'product number': 'EMEA/H/C/000582',
    'patient safety': 'no',
    'authorisation status': 'Authorised',
    'atc code': 'L01XC07',
    'additional monitoring': 'no',
    'generic': 'no',
    'biosimilar': 'no',
    'conditional approval': 'no',
    'exceptional circumstances': 'no',
    'accelerated assessment': 'no',
    'orphan medicine': 'no',
    'marketing authorisation date': 38363.95833333334,
    'date of refusal of marketing authorisation': '',
    'marketing authorisation holder/company name': 'Roche Registration GmbH',
    'human pharmacotherapeutic group': 'Antineoplastic agents, ',
    'vet pharmacotherapeutic group': '',
    'date of opinion': '',
    'decision date': 43580.91666666666,
    'revision number': 51.0,
    'condition / indication': 'Bevacizumab in combination with fluoropyrimidine-based chemotherapy is indicated for treatment of adult patients with metastatic carcinoma of the colon or rectum.Bevacizumab in combination with paclitaxel is indicated for first-line treatment of adult patients with metastatic breast cancer. For further information as to human epidermal growth factor receptor 2 (HER2) status.Bevacizumab in combination with capecitabine is indicated for first-line treatment of adult patients with metastatic breast cancer in whom treatment with other chemotherapy options including taxanes or anthracyclines is not considered appropriate. Patients who have received taxane and anthracycline-containing regimens in the adjuvant setting within the last 12 months should be excluded from treatment with Avastin in combination with capecitabine. For further information as to HER2 status.Bevacizumab, in addition to platinum-based chemotherapy, is indicated for first-line treatment of adult patients with unresectable advanced, metastatic or recurrent non-small cell lung cancer other than predominantly squamous cell histology.Bevacizumab, in combination with erlotinib, is indicated for first-line treatment of adult patients with unresectable advanced, metastatic or recurrent non-squamous non-small cell lung cancer with Epidermal Growth Factor Receptor (EGFR) activating mutations.Bevacizumab in combination with interferon alfa-2a is indicated for first line treatment of adult patients with advanced and/or metastatic renal cell cancer.Bevacizumab, in combination with carboplatin and paclitaxel is indicated for the front-line treatment of adult patients with advanced (International Federation of Gynecology and Obstetrics (FIGO) stages III B, III C and IV) epithelial ovarian, fallopian tube, or primary peritoneal cancer.Bevacizumab, in combination with carboplatin and gemcitabine, is indicated for treatment of adult patients with first recurrence of platinum-sensitive epithelial ovarian, fallopian tube or primary peritoneal cancer who have not received prior therapy with bevacizumab or other VEGF inhibitors or VEGF receptor–targeted agents.Bevacizumab in combination with paclitaxel, topotecan, or pegylated liposomal doxorubicin is indicated for the treatment of adult patients with platinum-resistant recurrent epithelial ovarian, fallopian tube, or primary peritoneal cancer who received no more than two prior chemotherapy regimens and who have not received prior therapy with bevacizumab or other VEGF inhibitors or VEGF receptor–targeted agents.Bevacizumab, in combination with paclitaxel and cisplatin or, alternatively, paclitaxel and topotecan in patients who cannot receive platinum therapy, is indicated for the treatment of adult patients with persistent, recurrent, or metastatic carcinoma of the cervix.',
    'species': '',
    'atcvet code': '',
    'first published': 43321.43680555555,
    'revision date': 43704.375,
    'url': 'https://www.ema.europa.eu/en/medicines/human/EPAR/avastin'
}

问题是，在excel中，我将Revision Date列转换为8/27/2019 9:00:00 AM，但在使用xlrd读取excel时，它被转换为43704.375，因为它的列类型是date。你知道吗

如何使用python读取/获取正确格式的日期和时间？

简而言之，（43704.375甚至不是该日期的时间戳），我如何将8/27/2019 9:00:00 AM转换为43704.375？ 反之亦然？

Answer 1

Excel内部将日期值存储为浮点数。因此，在xlrd中，如果要将Excel日期值读取为Python日期值，则必须使用xldate\u as\u tuple方法来获取日期。你知道吗

文档：http://www.lexicon.net/sjmachin/xlrd.html#xlrd.xldate_as_tuple-function

下面是一个通用示例：

import datetime, xlrd
book = xlrd.open_workbook("myexcelfile.xls")
sh = book.sheet_by_index(0)
a1 = sh.cell_value(rowx=0, colx=0)
a1_as_datetime = datetime.datetime(*xlrd.xldate_as_tuple(a1, book.datemode))
print 'datetime: %s' % a1_as_datetime

如果创建文件myexcelfile.xls，并在单元格A1中输入日期并运行上述代码，则应该能够在a1_as_datetime变量中看到正确的datetime值。你知道吗