我一直在努力寻找执行以下操作的方法。
我刚刚收到一个excel文件,其中包含以下公式。
Row Value Excel formula Result
608332e6943e7263a56fb3ff =(((HEX2DEC(LEFT("row value",8))/60)/60)/24)+DATE(1970,1,1) 4/23/2021 8:49:42 PM
我想知道是否有一种方法可以让我在python中实现同样的结果。我尝试了以下操作,但没有成功。
df_final = pd.read_csv("Book1.csv")
print(df_final)
Card ID # of Members Created Date
0 608332c0806da55df13b498b 2 608332c0
1 60819c7ccd3a695b79f54f53 5 60819c7c
2 60817b9df8f5422bbaf2cff9 9 60817b9d
3 60806f3d24f11404f1470904 2 60806f3d
4 607ed78a89de73411e937655 1 607ed78a
5 608332e6943e7263a56fb3ff 2 608332e6
6 6.05364E+23 5 6.05364E
7 6.04084E+23 6 6.04084E
8 6.05395E+22 2 6.05395E
9 6.04716E+23 1 6.04716E
hexa = df_final["Created Date"]
df_final["Created Date"] = ((int(hexa,16)/60)/60)/24 + datetime.datetime(1970,1,1)
print(df_final)
我希望得到这样的输出:
Card ID # of Members Created Date
0 608332c0806da55df13b498b 2 4/23/2021
1 60819c7ccd3a695b79f54f53 5 4/22/2021
2 60817b9df8f5422bbaf2cff9 9 4/22/2021
3 60806f3d24f11404f1470904 2 4/21/2021
4 607ed78a89de73411e937655 1 4/20/2021
5 608332e6943e7263a56fb3ff 2 4/23/2021
6 6.05364E+23 5 Invalid
7 6.04084E+23 6 Invalid
8 6.05395E+22 2 Invalid
9 6.04716E+23 1 Invalid
然而,我得到的只是以下错误:
df_final["Created Date"] = ((int(hexa,16)/60)/60)/24 + datetime.datetime(1970,1,1)
TypeError: int() can't convert non-string with explicit base
思考如何完成这项工作?我感谢任何帮助!乔。
根据Otávio的回应,我试图重新运行,但我仍然无法实现我想要的目标。
如果只有一个字符串,那么练习就完成了工作,而如果有多个值,那么它就不能完成工作。以下是示例:
df_final = pd.read_csv("Book1.csv")
df_final["Created Date"] = df_final["Card ID"].str[0:8]
print(df_final)
hex = df_final["Created Date"]
dtime_obj = datetime.datetime.utcfromtimestamp(int(hex, 16))
datetime_str = dtime_obj.strftime('%Y-%m-%dT%H:%M')
print(df_final)
Card ID # of Members Created Date
0 608332c0806da55df13b498b 2 608332c0
1 60819c7ccd3a695b79f54f53 5 60819c7c
2 60817b9df8f5422bbaf2cff9 9 60817b9d
3 60806f3d24f11404f1470904 2 60806f3d
4 607ed78a89de73411e937655 1 607ed78a
5 608332e6943e7263a56fb3ff 2 608332e6
6 6.05364E+23 5 6.05364E
7 6.04084E+23 6 6.04084E
8 6.05395E+22 2 6.05395E
9 6.04716E+23 1 6.04716E
Traceback (most recent call last):
File "c:UsersJUBDesktopManual Formulas 1hexa.py", line 16, in <module>
dtime_obj = datetime.datetime.utcfromtimestamp(int(hex, 16))
TypeError: int() can't convert non-string with explicit base
谢谢你们帮我!乔。
将十六进制字符串转换为int,然后将此int转换为日期时间元组:
import datetime
hex = '608332c0'
dtime_obj = datetime.datetime.utcfromtimestamp(int(hex, 16))
datetime_str = dtime_obj.strftime('%Y-%m-%dT%H:%M') # '2021-04-23T20:49:04Z'