Python Pandas筛选CSV保留指定两列的技术实现求助

我有如下格式的CSV文件：

result,table,_start,_stop,_time,_value,_field,_measurement,device
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:12:35Z,44.61,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:12:40Z,17.33,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:12:45Z,41.2,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:12:51Z,33.49,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:12:56Z,55.68,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:12:57Z,55.68,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:13:02Z,25.92,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0
,0,2022-10-23T08:22:04.124457277Z,2022-11-22T08:22:04.124457277Z,2022-10-24T12:13:08Z,5.71,power,shellies,Shelly_Kitchen-C_CoffeMachine/relay/0

需要将其处理为仅包含time和value两列的格式，示例输出如下：

time  value
0  2022-10-24T12:12:35Z  44.61
1  2022-10-24T12:12:40Z  17.33
2  2022-10-24T12:12:45Z  41.20
3  2022-10-24T12:12:51Z  33.49
4  2022-10-24T12:12:56Z  55.68

该处理用于异常检测代码，无需手动删除列。我尝试了以下Pandas代码但未得到预期结果：

df = pd.read_csv('coffee_machine_2022-11-22_09_22_influxdb_data.csv')
df['_time'] = pd.to_datetime(df['_time'], format='%Y-%m-%dT%H:%M:%SZ')
df = pd.pivot(df, index = '_time', columns = '_field', values = '_value')
df.interpolate(method='linear') # not neccesary

现寻求正确的Python Pandas实现方法。

你之前用pivot的思路不对，它会把_field作为列名、_time设为索引，不符合需求。直接选择目标列并重命名，再调整数值格式即可：

import pandas as pd

# 读取CSV文件
df = pd.read_csv('coffee_machine_2022-11-22_09_22_influxdb_data.csv')

# 选择需要的列，并重命名为目标列名
df_result = df[['_time', '_value']].rename(columns={'_time': 'time', '_value': 'value'})

# 将value列格式化为两位小数，匹配示例输出的格式
df_result['value'] = df_result['value'].round(2)

# 可选：如果需要和示例输出一样只保留前5行
# df_result = df_result.head(5)

print(df_result)

代码说明 #

• df[['_time', '_value']]直接筛选出需要的两列，无需手动删除其他列；
• rename方法直接将原始列名替换为time和value；
• round(2)确保数值都是两位小数，和示例输出对齐；
• 如果后续异常检测不需要datetime类型，保留原始时间字符串格式即可，不用额外转换。

内容的提问来源于stack exchange，提问作者Gaara