Pandas读取Excel日期数据的异常处理
2023-09-11 14:14:31 时间
作者:小小明
异常描述
有时我们的Excel有一个调整过自定义格式的日期字段:
当我们用pandas读取时却是这样的效果:
不管如何指定参数都无效。
出现原因
没有使用系统内置的日期单元格格式,自定义格式没有对负数格式进行定义,pandas读取时无法识别出是日期格式,而是读取出单元格实际存储的数值。
解决方案:修改自定义格式
可以修改为系统内置的自定义格式:
或者在自定义格式上补充负数的定义:
增加;@即可
pandas直接解析Excel数值为日期
有时这种Excel很多,我们需要通过pandas批量读取,挨个人工修改Excel的自定义格式费时费力,下面我演示如何使用pandas直接解析这些数值成为日期格式。
excel中常规格式和日期格式的转换规则如下:
1900/1/1为起始日期,转换的数字是1,往后的每一天增加1
1900/1/2转换为数字是 2
1900/1/3转换为数字是 3
1900/1/4转换为数字是 4
以此类推
excel中时间转换规则如下:
在时间中的规则是把1天转换为数字是 1
每1小时就是 1/24
每1分钟就是 1/(24×60)=1/1440
每1秒钟就是 1/(24×60×60)=1/86400
根据Excel的日期存储规则,我们只需要以1900/1/1为基准日期,根据数值n偏移n-1天即可得到实际日期。不过还有个问题,Excel多存储了1900年2月29日这一天,而正常的日历是没有这一天的,而我们的日期又都是大于1900年的,所以应该偏移n-2天,干脆使用1899年12月30日作为基准,这样不需要作减法操作。
解析代码如下:
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()
日期 | |
---|---|
0 | 6月8日 |
1 | 6月9日 |
2 | 6月10日 |
3 | 6月11日 |
4 | 6月12日 |
如果需要调用time的strftime方法,由于包含中文则需要设置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()
日期 | |
---|---|
0 | 2021年06月08日 |
1 | 2021年06月09日 |
2 | 2021年06月10日 |
3 | 2021年06月11日 |
4 | 2021年06月12日 |
相关文章
- Excel显示当前日期
- 自从学会了Pandas,我用Python处理Excel更高效了
- html table表格导出excel的方法 html5 table导出Excel HTML用JS导出Excel的五种方法 html中table导出Excel 前端开发 将table内容导出到excel HTML table导出到Excel中的解决办法 js实现table导出Excel,保留table样式
- PHPExcel对于Excel中日期和时间类型的处理
- 使用Magicodes.IE快速导出Excel
- 最全的Excel导入导出实战(通用版), 注解+反射 优雅的实现
- 将excel表导入到mysql中
- vue前端导出Excel
- Excel VLOOKUP实用教程之 05 vlookup如何从列表中获取最后一个值?(教程含数据excel)
- Excel VLOOKUP实用教程之 03 使用下拉列表作为查找值vlookup?(教程含数据excel)
- Excel 数据透视表教程大全之 07 数据透视表使用日期字段自动按月、年对销售数据进行分组(教程含数据)
- Excel VLOOKUP实用教程之 10 在使用 VLOOKUP 函数时处理错误?(教程含数据excel)
- Excel VLOOKUP实用教程之 07 vlookup如何解决肉眼看完全匹配,但是就是返回N/A错误(教程含数据excel)
- Excel 数据透视表教程大全之 04 按日期分组(教程含样本数据)
- Excel VLOOKUP实用教程之 08 VLOOKUP如果实现区分大小写的查找?(教程含数据excel)
- Excel 函数教程之VLOOKUP实用教程与10个适用于初学者和高级用户的VLOOKUP案例(教程含数据excel)
- Excel VLOOKUP实用教程之 10 在使用 VLOOKUP 函数时处理错误?(教程含数据excel)
- Excel教程之什么是好的仪表板工具
- 接口测试框架第一节:重构操作Excel工具类
- csharp: Export or Import excel using NPOI
- Excel-返回列表或数据库中的分类汇总(汇总可以实现要还是不要统计隐藏行功能) subtotal()
- winform 利用 NPOI导入,导出excel
- PowerDesigner 表格导出为excel
- NPOI帮助类(Excel转DataTable、DataTable转Excel)
- python 调用打印机,打开excel并打印笔记
- excel合并日期和时间(转载)
- ANSYS WORKBENCH——参数化建模以及参数优化(结果导出为Excel)
- Excel日期中那个著名的bug
- NPOI导出EXCEL 打印设置分页及打印标题