设置索引、数据重采样更换时间频率0420办公自动化（四）

import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings("ignore")
data = pd.read_csv('示范区微波检测数据.csv')

data = data.iloc[:, 3:5]  #选取有用数据

data['COLLEC_TIME'] = pd.to_datetime(data['COLLEC_TIME']) #转换成日期格式
data['日期'] = data['COLLEC_TIME'].dt.date
data['时间'] = data['COLLEC_TIME'].dt.time
data = data.set_index(data['COLLEC_TIME'])  #时间设为索引，方便数据重采样
data = data.iloc[:, [0, 2]]
road = data.groupby('ROAD_ID').size().index #统计非重复值
date_time = data.groupby('日期').size().index


sum_list = []
for m in road:
    for n in date_time:
        data_new = data[data['ROAD_ID'] == m]  #筛选数据
        data_new = data_new[data_new['日期'] == n]
        data_new = data_new.ix[:, ].resample('15min').count()  #数据重采样并进行聚合
        data_new = data_new.drop('ROAD_ID', axis=1)
        index = data_new.index.time
        data_new['新'] = index
        data_new = data_new.set_index('新').T
        data_new = data_new.reset_index(drop=True)
        data_new.insert(0, 'ROAD_ID', m)
        data_new.insert(1, '日期', n)
        sum_list.append(data_new)


df = pd.DataFrame()  #创建df
for i in sum_list:
    df = pd.concat([df, i])  #合并每个DataFrame
df = df.reset_index(drop=True)
df = df.replace(0, np.nan)  #改变0的数值
for column in list(df.columns[df.isnull().sum() > 0]):  #np.nan换成每列均值
    mean_val = df[column].mean()
    df[column].fillna(mean_val, inplace=True)
df.iloc[:, 2:] = df.iloc[:, 2:].astype(int)
print(df)

df.to_csv('result.csv', encoding='gbk', index=None)

原始数据：

结果：