異常描述

有時(shí)我們的Excel有一個(gè)調(diào)整過自定義格式的日期字段：

當(dāng)我們用pandas讀取時(shí)卻是這樣的效果：

不管如何指定參數(shù)都無效。

出現(xiàn)原因

沒有使用系統(tǒng)內(nèi)置的日期單元格格式，自定義格式?jīng)]有對負(fù)數(shù)格式進(jìn)行定義，pandas讀取時(shí)無法識別出是日期格式，而是讀取出單元格實(shí)際存儲的數(shù)值。

解決方案：修改自定義格式

可以修改為系統(tǒng)內(nèi)置的自定義格式：

或者在自定義格式上補(bǔ)充負(fù)數(shù)的定義：

增加;@即可

pandas直接解析Excel數(shù)值為日期

有時(shí)這種Excel很多，我們需要通過pandas批量讀取，挨個(gè)人工修改Excel的自定義格式費(fèi)時(shí)費(fèi)力，下面我演示如何使用pandas直接解析這些數(shù)值成為日期格式。

excel中常規(guī)格式和日期格式的轉(zhuǎn)換規(guī)則如下：

1900/1/1為起始日期，轉(zhuǎn)換的數(shù)字是1，往后的每一天增加1

1900/1/2轉(zhuǎn)換為數(shù)字是 2

1900/1/3轉(zhuǎn)換為數(shù)字是 3

1900/1/4轉(zhuǎn)換為數(shù)字是 4

以此類推

excel中時(shí)間轉(zhuǎn)換規(guī)則如下：

在時(shí)間中的規(guī)則是把1天轉(zhuǎn)換為數(shù)字是 1

每1小時(shí)就是 1/24

每1分鐘就是 1/(24×60)=1/1440

每1秒鐘就是 1/(24×60×60)=1/86400

根據(jù)Excel的日期存儲規(guī)則，我們只需要以1900/1/1為基準(zhǔn)日期，根據(jù)數(shù)值n偏移n-1天即可得到實(shí)際日期。不過還有個(gè)問題，Excel多存儲了1900年2月29日這一天，而正常的日歷是沒有這一天的，而我們的日期又都是大于1900年的，所以應(yīng)該偏移n-2天，干脆使用1899年12月30日作為基準(zhǔn)，這樣不需要作減法操作。

解析代碼如下：

import pandas as pd
from pandas.tseries.offsets import Day

df = pd.read_excel("日期.xlsx")
basetime = pd.to_datetime("1899/12/30")
df.日期 = df.日期.apply(lambda x: basetime+Day(x))
df.日期 = df.日期.apply(lambda x: f"{x.month}月{x.day}日")
df.head()

如果需要調(diào)用time的strftime方法，由于包含中文則需要設(shè)置locale：

import pandas as pd
from pandas.tseries.offsets import Day
import locale
locale.setlocale(locale.LC_CTYPE, 'chinese')

df = pd.read_excel("日期.xlsx")
basetime = pd.to_datetime("1899/12/30")
df.日期 = df.日期.apply(lambda x: basetime+Day(x))
df.日期 = df.日期.dt.strftime('%Y年%m月%d日')
df.head()

總結(jié)?