学习python 有一段时间了,一直徘徊与基础,最近接了一个解析数据的任务,算是学习以来第一次实战,故而作此记录。
问题解析:为了节省数据存储空间,信息以字符串存在了一个单元格中,我们需要的是时间的首尾值,最后要做差计算,使用BI 软件(spotifire)进行页面展示。
原始数据如图
1.第一步要解决的问题是时间转换问题
因为数据中step_times是long格式(timestamp类似,long格式的除以1000的值与timestamp相同)的,要将其转换为datetime 或者str 格式
用到的知识点
- 时间转化 https://blog.csdn.net/weixin_42223090/article/details/103304440
- 遍历数据
import pandas as pd
import copy
import time,datetime
sv_data1 = pd.read_csv('F:/Users/算法/新建文件夹/E.CSV')
sv_data = sv_data1[:]
# sv_data = sv_data1[:-2]
step_times = sv_data['step_times'].apply(lambda x: list(map(float, x.split(sep="|")))).to_frame()
step_values = sv_data['step_values'].apply(lambda x: list(map(float, x.split(sep="|")))).to_frame()
step_times.columns = ['times_list']
step_values.columns = ['values_list']
sv_data['times_list'] = step_times['times_list']
sv_data['values_list'] = step_values['values_list']
# 转化时间格式
sv_data['time_type_list'] = sv_data.apply(lambda x : copy.deepcopy(x['times_list']),axis=1)
sv_data['non'] = sv_data.apply(lambda x:x['time_type_list'].clear(),axis=1)
for index,row in sv_data.iterrows():
list_len = len(row['times_list'])
for i in range(list_len):
dateArray = datetime.datetime.fromtimestamp(row['times_list'][i]/1000)
TimeStyle = dateArray.strftime('%Y-%m-%d %H:%M:%S')
row['time_type_list'].append(TimeStyle)
sv_data = sv_data.drop(columns=['non'])
- 要拆列,筛选每一个value_list 中值的对应时间首尾值
# 将values_list 和 time_type_list 进行拼接
sv_data['list_combine'] = sv_data.apply(lambda x:list(zip(x['values_list'],x['time_type_list'])),axis=1)
#生成空列表,目的添加未去重参数/时间匹配值
sv_data['list_combinev1'] = sv_data.apply(lambda x:copy.deepcopy(x['list_combine']),axis=1)
sv_data['non'] = sv_data.apply(lambda x:x['list_combinev1'].clear(),axis=1)
#向list中插入未去重参数/时间始末值
for index,row in sv_data.iterrows():
list_len = len(row['list_combine'])
for i in range(list_len-1):
if row['list_combine'][i+1][0] != row['list_combine'][i][0]:
row['list_combinev1'].append(row['list_combine'][i])
row['list_combinev1'].append(row['list_combine'][i+1])
row['list_combinev1'].insert(0,row['list_combine'][0])
if list_len>1:
row['list_combinev1'].append(row['list_combine'][i+1])
sv_data = sv_data.drop(columns=['non'])
3. 找出首位值,接下来要进行做差计算
# 创建空的列表,作为存储计算结果的容器
sv_data['values_list_final'] = sv_data.apply(lambda x :copy.deepcopy(x['list_combinev1']),axis=1)
sv_data['non_value'] = sv_data.apply(lambda x :x['values_list_final'].clear(),axis=1)
sv_data['time_list_final'] = sv_data.apply(lambda x :copy.deepcopy(x['list_combinev1']),axis=1)
sv_data['non_time'] = sv_data.apply(lambda x :x['time_list_final'].clear(),axis=1)
# 计算时间差
def str_to_date(string_time, _format="%Y-%m-%d %H:%M:%S"):
return datetime.datetime.strptime(string_time, _format)
for index,row in sv_data.iterrows():
list_len = len(row['list_combinev1'])
for i in range(list_len-1):
if row['list_combinev1'][i+1][0] == row['list_combinev1'][i][0]:
row['values_list_final'].append(row['list_combinev1'][i][0])
date_diff = (str_to_date(row['list_combinev1'][i+1][1])-str_to_date(row['list_combinev1'][i][1])).total_seconds()
row['time_list_final'].append(date_diff)
sv_data = sv_data.drop(columns=['non_value'])
sv_data = sv_data.drop(columns=['non_time'])
4 最后,由于BI 软件无法使用list直接作图,所以想了将列值拆分成行的方法
需要将单元格值拆分成行
这一步卡了很久,最后google 解决。。。stackoverflow真的强大
方法
https://stackoverflow.com/questions/12680754/split-explode-pandas-dataframe-string-entry-to-separate-rows
直接copy 代码
# 定义拆行函数
import numpy as np
def explode(df, lst_cols, fill_value='', preserve_index=False):
# make sure `lst_cols` is list-alike
if (lst_cols is not None
and len(lst_cols) > 0
and not isinstance(lst_cols, (list, tuple, np.ndarray, pd.Series))):
lst_cols = [lst_cols]
# all columns except `lst_cols`
idx_cols = df.columns.difference(lst_cols)
# calculate lengths of lists
lens = df[lst_cols[0]].str.len()
# preserve original index values
idx = np.repeat(df.index.values, lens)
# create "exploded" DF
res = (pd.DataFrame({
col:np.repeat(df[col].values, lens)
for col in idx_cols},
index=idx)
.assign(**{col:np.concatenate(df.loc[lens>0, col].values)
for col in lst_cols}))
# append those rows that have empty lists
if (lens == 0).any():
# at least one list in cells is empty
res = (res.append(df.loc[lens==0, idx_cols], sort=False)
.fillna(fill_value))
# revert the original index order
res = res.sort_index()
# reset index if requested
if not preserve_index:
res = res.reset_index(drop=True)
return res
explode(sv_data_final,['values_list_final','time_list_final'],fill_value='').head()
运行成功,以为一切都搞定了,但是在后面运行数据时,失败,原因是之中有空值!
sv_data.dropna(axis=0, subset = ['time_value'])
到此为止,大功搞成!