python - 将字符串与元组列表中的元组进行比较 - python
问题描述
尝试创建Tax_Year
具有适当纳税年度的新列,通过检查date
列中的日期时间是否在个人的元组元素的边界内形成txYear_
......
salesReport = pd.DataFrame({'date': ['2017-07-02 09:00:00', '2017-07-03 15:00:00', '2018-04-05 15:00:00',
'2018-12-20 11:00:00', '2019-01-06 14:00:00', '2020-09-06 17:00:00'],
'sales': [100, 339, 98, 1020, 630, 765]})
txYear_0304 = (dt.datetime(2003, 4, 6), dt.datetime(2004, 4, 5))
txYear_0405 = (dt.datetime(2004, 4, 6), dt.datetime(2005, 4, 5))
txYear_0506 = (dt.datetime(2005, 4, 6), dt.datetime(2006, 4, 5))
txYear_0607 = (dt.datetime(2006, 4, 6), dt.datetime(2007, 4, 5))
txYear_0708 = (dt.datetime(2007, 4, 6), dt.datetime(2008, 4, 5))
txYear_0809 = (dt.datetime(2008, 4, 6), dt.datetime(2009, 4, 5))
txYear_0910 = (dt.datetime(2009, 4, 6), dt.datetime(2010, 4, 5))
txYear_1011 = (dt.datetime(2010, 4, 6), dt.datetime(2011, 4, 5))
txYear_1112 = (dt.datetime(2011, 4, 6), dt.datetime(2012, 4, 5))
txYear_1213 = (dt.datetime(2012, 4, 6), dt.datetime(2013, 4, 5))
txYear_1314 = (dt.datetime(2013, 4, 6), dt.datetime(2014, 4, 5))
txYear_1415 = (dt.datetime(2014, 4, 6), dt.datetime(2015, 4, 5))
txYear_1516 = (dt.datetime(2015, 4, 6), dt.datetime(2016, 4, 5))
txYear_1617 = (dt.datetime(2016, 4, 6), dt.datetime(2017, 4, 5))
txYear_1718 = (dt.datetime(2017, 4, 6), dt.datetime(2018, 4, 5))
txYear_1819 = (dt.datetime(2018, 4, 6), dt.datetime(2019, 4, 5))
txYear_1920 = (dt.datetime(2019, 4, 6), dt.datetime(2020, 4, 5))
txYear_2021 = (dt.datetime(2020, 4, 6), dt.datetime(2021, 4, 5))
tax_year = [txYear_0304, txYear_0405, txYear_0506, txYear_0607, txYear_0708, txYear_0809, txYear_0910, txYear_1011, txYear_1112,
txYear_1213, txYear_1314, txYear_1415, txYear_1516, txYear_1617, txYear_1718, txYear_1819, txYear_1920, txYear_2021]
满足此条件时,我希望变量名称出现在新列的相应行中
例如
date sales Tax_Year
0 2017-07-02 09:00:00 100 txYear_1617
1 2017-07-03 15:00:00 339 txYear_1617
2 2018-04-05 15:00:00 98 txYear_1718
3 2018-12-20 11:00:00 1020 txYear_1819
4 2019-01-06 14:00:00 630 txYear_1819
5 2020-09-06 17:00:00 765 txYear_2021
np.where
我已经使用....解决了这个问题。
salesReport['Tax_Year'] = np.where(tax_year[0] <= salesReport['date'] and tax_year[1] >= salesReport['date'], tax_year, np.nan)
但是,我无法解决收到的错误...
TypeError: '>=' not supported between instances of 'str' and 'tuple'
此外,我也不确定如何获取变量名,因为目前我将返回实际的元组内容,这不是我想要的
解决方案
免责声明:
我不精通熊猫。如果有更好的方法来做到这一点,我不会感到惊讶。
我已将tax_years
元组列表转换为字典,并定义了一个独立函数来获取给定日期时间对象的纳税年度。我实际上并不是 100% 纳税年度结束/开始的时间,因此比较仅在 MM-DD-YY 上进行,并从数据框中存在的时间戳中删除时间。
import pandas as pd
import numpy as np
import datetime
tax_years = {
(datetime.datetime(2003, 4, 6), datetime.datetime(2004, 4, 5)): "TY0304",
(datetime.datetime(2004, 4, 6), datetime.datetime(2005, 4, 5)): "TY0405",
(datetime.datetime(2005, 4, 6), datetime.datetime(2006, 4, 5)): "TY0506",
(datetime.datetime(2006, 4, 6), datetime.datetime(2007, 4, 5)): "TY0607",
(datetime.datetime(2007, 4, 6), datetime.datetime(2008, 4, 5)): "TY0708",
(datetime.datetime(2008, 4, 6), datetime.datetime(2009, 4, 5)): "TY0809",
(datetime.datetime(2009, 4, 6), datetime.datetime(2010, 4, 5)): "TY0910",
(datetime.datetime(2010, 4, 6), datetime.datetime(2011, 4, 5)): "TY1011",
(datetime.datetime(2011, 4, 6), datetime.datetime(2012, 4, 5)): "TY1112",
(datetime.datetime(2012, 4, 6), datetime.datetime(2013, 4, 5)): "TY1213",
(datetime.datetime(2013, 4, 6), datetime.datetime(2014, 4, 5)): "TY1314",
(datetime.datetime(2014, 4, 6), datetime.datetime(2015, 4, 5)): "TY1415",
(datetime.datetime(2015, 4, 6), datetime.datetime(2016, 4, 5)): "TY1516",
(datetime.datetime(2016, 4, 6), datetime.datetime(2017, 4, 5)): "TY1617",
(datetime.datetime(2017, 4, 6), datetime.datetime(2018, 4, 5)): "TY1718",
(datetime.datetime(2018, 4, 6), datetime.datetime(2019, 4, 5)): "TY1819",
(datetime.datetime(2019, 4, 6), datetime.datetime(2020, 4, 5)): "TY1920",
(datetime.datetime(2020, 4, 6), datetime.datetime(2021, 4, 5)): "TY2021"
}
salesReport = pd.DataFrame({'date': ['2017-07-02 09:00:00',
'2017-07-03 15:00:00',
'2018-04-05 15:00:00',
'2018-12-20 11:00:00',
'2019-01-06 14:00:00',
'2020-09-06 17:00:00'],
'sales': [100, 339, 98, 1020, 630, 765]})
salesReport["date"] = pd.to_datetime(salesReport["date"])
def get_tax_year(date):
for (start, end), tax_year in tax_years.items():
if start.date() <= date.date() <= end.date():
return tax_year
return "null"
salesReport["tax_year"] = [get_tax_year(date) for date in salesReport["date"]]
print(salesReport)
和输出:
date sales tax_year
0 2017-07-02 09:00:00 100 TY1718
1 2017-07-03 15:00:00 339 TY1718
2 2018-04-05 15:00:00 98 TY1718
3 2018-12-20 11:00:00 1020 TY1819
4 2019-01-06 14:00:00 630 TY1819
5 2020-09-06 17:00:00 765 TY2021
推荐阅读
- postgresql - 如何在 plpgsql 中运行动态查询时访问 FOUND
- ruby-on-rails - 从下到上无限滚动的rails分页
- r - 数据框列的每个子集的差异
- css - Sass mixin 不导出/导入前缀
- mysql - 将一列设置为另一列的记录,只需用逗号替换空格
- jquery - 按钮事件在 Bootstrap 模式中不起作用
- flutter - 在 Flutter 中从 API 获取数据时修复此格式化异常错误
- python - Pandas:如何将 dicts 列表中的 dicts 列表展平到数据框中,如果嵌套列表中的任何 dict 缺少任何指定的键,则抛出错误?
- ios - 在 iOS 中,对具有 2 个复合键的表的 Dexie 更新变得非常慢
- sql - DB2 中具有位/十六进制混淆的字符