马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
代码:import pandas as pd
import numpy as np
# 打开数据表,Excel是DataFrame数据类型
df = pd.read_excel("analysis.xlsx")
# 提取各个环节
def ky_link():
key_link = []
key_link_date = list(df.columns)
for i in range(len(key_link_date)):
if "DATE" in key_link_date[i]:
[key_split,other_split] = key_link_date[i].split("_DAT",1)
if key_split[-4:] == "_END":
key_link.append(key_split[:-4])
else:
key_link.append(key_split[:-6])
key_link = set(key_link)
key_link = list(key_link)
return key_link
# 各个环节的平均时长
def link_analysis():
key_link = ky_link()
for each in key_link:
key_link1 = each + "_END_DATE"
key_link2 = each + "_BEGIN_DATE"
dg = df[key_link1].fillna(value=0)
# 提取有效数据
dt = df.loc[df[key_link1] != 0]
# 初始化时间列表
time = []
working_time = []
for i in range(dt.shape[0]):
link_time = dt[key_link1].iloc[i] - dt[key_link2].iloc[i]
time.append(link_time)
# time_link分析时长
for j in time:
days = str(j).split("days")
working_time.append(int(days[0]))
link_mean = np.mean(working_time)
link_median = np.median(working_time)
counts = np.bincount(working_time)
link_count = np.argmax(counts)
print(each,link_mean,link_median,link_count)
link_analysis()
问题:
Traceback (most recent call last):
File "C:\Users\Chysial\Desktop\业扩高压归档\高压数据\4.第二季度业扩高压数据\1.data_filter.py", line 44, in <module>
link_analysis()
File "C:\Users\Chysial\Desktop\业扩高压归档\高压数据\4.第二季度业扩高压数据\1.data_filter.py", line 38, in link_analysis
working_time.append(int(float(days[0])))
ValueError: could not convert string to float: 'NaT'
但是,我单独拿出来一行运行是没有问题的?
>>> a=df["REPLY_TO_RECEIVE_POWER_SUPPLY_SCHEME_END_DATE"][1]-df["REPLY_TO_RECEIVE_POWER_SUPPLY_SCHEME_BEGIN_DATE"][1]
>>> a
Timedelta('0 days 02:25:02')
>>> d=str(a).split("days")
>>> type(d[0])
<class 'str'>
>>> float(d[0])
0.0
>>> int(d[0])
0
原excel文件,哪些都是时间格式,下面是样例:
SCENE_INVESTIGATION_BEGIN_DATE SCENE_INVESTIGATION_END_DATE
2018/3/2 10:43:43 2018/3/6 8:51:30
2018/1/29 13:22:45 2018/1/30 8:51:18
2018/1/9 13:42:24 2018/1/9 15:10:32
2018/5/3 11:13:31 2018/5/4 16:49:47
2018/3/13 9:56:47 2018/3/13 10:12:21
2018/4/18 10:13:42 2018/4/23 10:00:58
2018/3/14 9:48:14 2018/3/14 10:09:37
2018/4/8 15:01:18 2018/4/8 15:23:28
各位大哥帮下忙,自己懵逼了,谢谢!!!
红色报错是因为解释器说它,不能把字符类型的参数转化为浮点数。
最好是先转化时间戳,然后再转化成你需要的时间格式或计算
|