python - 当它们共享一个列值时，有没有办法将数据压缩到 Pandas DataFrame 的一行中？
问题描述
我有一个几千行的 DataFrame。DF 保存我组织内单位的单位标识符和响应时间。它在 DF 中结构化，列 ["Event#"、"UnitID"、"First UnitEnroute"、"First UnitArrived"、"First UnitAtHospital"]
同一个 Event# 有许多不同的行，最后我只希望每个 Event# 一行，其中 ["First UnitEnroute", "First UnitArrived", "First UnitAtHospital]" 由其他行填写相同的事件#。
造成这种情况的原因是一些季度末的计费惨败，我们需要知道这些不同的事件是否有这 3 次分布在不同的单元中。我不需要列出的单位，只需要从同一事件的其他行中提取的第一个非 0 值。
以下是一些示例数据：
Event# Unit First UnitEnroute First UnitArrived First UnitAtHospital
2020000394 37 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000394 38 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000394 36 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000394 39 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000617 58 ['1/2/2020', '12:06:13 PM'] ['1/2/2020', '12:07:39 PM'] ['1/2/2020', '12:43:10 PM']
2020000849 74 ['1/2/2020', '6:42:19 PM'] ['1/2/2020', '6:53:53 PM'] ['1/2/2020', '7:28:32 PM']
2020000849 75 ['0'] ['0'] ['0']
2020000927 81 ['0'] ['0'] ['0']
2020000927 80 ['0'] ['0'] ['0']
2020000997 86 ['0'] ['0'] ['0']
2020000997 87 ['0'] ['0'] ['0']
2020001218 99 ['1/3/2020', '11:50:39 AM'] ['1/3/2020', '11:52:40 AM'] ['1/3/2020', '12:29:37 PM']
2020001218 98 ['0'] ['1/3/2020', '11:52:40 AM'] ['0']
2020001255 102 ['1/3/2020', '12:44:30 PM'] ['0'] ['0']
2020001255 103 ['1/3/2020', '12:40:19 PM'] ['0'] ['0']
2020001258 98 ['1/3/2020', '12:49:00 PM'] ['1/3/2020', '12:57:22 PM'] ['1/3/2020', '1:39:03 PM']
2020001258 103 ['0'] ['0'] ['0']
2020001258 104 ['0'] ['0'] ['0']
2020001258 105 ['0'] ['0'] ['0']
这是我尝试过的：
蛮力遍历每一行以找到一个非 0 值，然后将该值附加到该行。
for row in DF: compare = list() for i in DF: if i[0] == row[0]: addition = list(i) compare = compare.append(addition) print("Compare: {}".format(compare)) return compare for el in row.index: whatisit = row[el] if whatisit == 0: for item in compare.index: if item[el] == 0: return else: replacement = item[el] print("Replacement: {}".format(replacement)) return replacement row[el] = replacement return DF使用 pandas.groupby().fillna() 对具有类似 event#s 和回填时间的行进行分组。--我没有达到任何可以接受的结果。大多数时候，它把我的 DF 切成了一些奇怪的形状。我可能会误解如何使用它。
DF = DF.groupby("Event#")["Unit", "First UnitEnroute", "First UnitArrived", "First UnitAtHospital"].fillna(method="bfill")
任何方向都值得赞赏，如果之前已经发布过，我很抱歉，我花了很多时间寻找潜在的答案。我想我还没有完全发展出我需要查看代码的直觉，看看我如何将它应用到我的项目中。我不是专业的开发人员，我更像是一个动手提重物的员工哈哈。
解决方案
这是IIUC的一个。
from io import StringIO
import pandas as pd
# create data frame
df = pd.read_csv(StringIO(data), sep='\s\s+', engine='python')
# drop the column `Unit`
df = df.drop(columns='Unit')
# re-shape
df = df.melt(id_vars='Event#', var_name='first_unit', value_name='timestamp')
# drop timestamp == ['0']
mask = df['timestamp'].astype(str) != "['0']"
df = df[mask]
# drop duplicates
df = df.drop_duplicates()
# get min value for each group -- and re-shape
df = (df.groupby(['Event#', 'first_unit'])['timestamp'].min()
.unstack(level='first_unit')
.reset_index()
)
print(df)
first_unit Event# First UnitArrived \
0 2020000394 ['1/1/2020', '10:48:33 PM']
1 2020000617 ['1/2/2020', '12:07:39 PM']
2 2020000849 ['1/2/2020', '6:53:53 PM']
3 2020001218 ['1/3/2020', '11:52:40 AM']
4 2020001255 NaN
5 2020001258 ['1/3/2020', '12:57:22 PM']
first_unit First UnitAtHospital First UnitEnroute
0 ['1/1/2020', '11:45:01 PM'] ['1/1/2020', '10:45:34 PM']
1 ['1/2/2020', '12:43:10 PM'] ['1/2/2020', '12:06:13 PM']
2 ['1/2/2020', '7:28:32 PM'] ['1/2/2020', '6:42:19 PM']
3 ['1/3/2020', '12:29:37 PM'] ['1/3/2020', '11:50:39 AM']
4 NaN ['1/3/2020', '12:40:19 PM']
5 ['1/3/2020', '1:39:03 PM'] ['1/3/2020', '12:49:00 PM']
这是原始数据（即用于创建数据框）：
data = '''Event# Unit First UnitEnroute First UnitArrived First UnitAtHospital
2020000394 37 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000394 38 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000394 36 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000394 39 ['1/1/2020', '10:45:34 PM'] ['1/1/2020', '10:48:33 PM'] ['1/1/2020', '11:45:01 PM']
2020000617 58 ['1/2/2020', '12:06:13 PM'] ['1/2/2020', '12:07:39 PM'] ['1/2/2020', '12:43:10 PM']
2020000849 74 ['1/2/2020', '6:42:19 PM'] ['1/2/2020', '6:53:53 PM'] ['1/2/2020', '7:28:32 PM']
2020000849 75 ['0'] ['0'] ['0']
2020000927 81 ['0'] ['0'] ['0']
2020000927 80 ['0'] ['0'] ['0']
2020000997 86 ['0'] ['0'] ['0']
2020000997 87 ['0'] ['0'] ['0']
2020001218 99 ['1/3/2020', '11:50:39 AM'] ['1/3/2020', '11:52:40 AM'] ['1/3/2020', '12:29:37 PM']
2020001218 98 ['0'] ['1/3/2020', '11:52:40 AM'] ['0']
2020001255 102 ['1/3/2020', '12:44:30 PM'] ['0'] ['0']
2020001255 103 ['1/3/2020', '12:40:19 PM'] ['0'] ['0']
2020001258 98 ['1/3/2020', '12:49:00 PM'] ['1/3/2020', '12:57:22 PM'] ['1/3/2020', '1:39:03 PM']
2020001258 103 ['0'] ['0'] ['0']
2020001258 104 ['0'] ['0'] ['0']
2020001258 105 ['0'] ['0'] ['0']
'''
推荐阅读
- nestjs - 捆绑一个 NestJS + TypeORM 应用程序（使用 webpack）
- firebase - 用户私有 Cloud Firestore 的 API
- python - 在 Keras 中使用双向 LSTM 填空句子
- python - 使用 for 循环中的值从 txt 创建字典列表
- reactjs - ReactJS 动态遍历对象并显示 4 列
- python - 如何忽略数据框中的空列？（熊猫）
- python - Python ModuleNotFoundError：没有名为“google.analytics”的模块
- python - VC2019 的开发者 shell 中的 pip install 产生“错误：需要 Microsoft Visual C++ 14.0 或更高版本。” 错误
- html5-canvas - Angular 10 - Chart.js 在运行时使用 convas id 在数组中绘制图表
- reactjs - “无法从...安装，因为它不包含 package.json 文件”通过 npx create-react-app app-name 在 Windows 10 中创建反应应用程序时