我有一个几乎无尽的水平CSV,其中变量在整个标题上分布,我有许多重复的时间戳,这会导致这样的情况:
+------------+------------+------------+------------+
| Timestamp | Variable1 | Variable2 | .... |
+------------+------------+------------+------------+
| 2017/02/12 | 20 | | |
| 2017/02/13 | 20 | | |
| 2017/02/14 | 30 | | |
| 2017/02/12 | | 5 | |
| 2017/02/13 | | 2 | |
| 2017/02/14 | | 10 | |
| ... | | | |
+------------+------------+------------+------------+
我试图在时间戳上加入,以获得这样的结果:
+------------+------------+------------+------------+
| Timestamp | Variable1 | Variable2 | .... |
+------------+------------+------------+------------+
| 2017/02/12 | 20 | 5 | |
| 2017/02/13 | 20 | 2 | |
| 2017/02/14 | 30 | 10 | |
+------------+------------+------------+------------+
我在熊猫中相对较新,但我觉得这可以轻松地使用多个数据框架来完成,但我毫无疑问将单个数据框架分组。有人可以给我一只手吗?非常感谢!
您可以分组时间戳并组合值
df.groupby('Timestamp')['Variable1', 'Variable2'].apply(lambda x: x.sum()).reset_index()
你得到
Timestamp Variable1 Variable2
0 2017/02/12 20 5
1 2017/02/13 20 2
2 2017/02/14 30 10
编辑:非常感谢@pirsquared
df.set_index('Timestamp').groupby(level=0).sum().reset_index()
我会使用 pd.concat
pd.DataFrame.iteritems
pd.concat([s.dropna() for c, s in df.set_index('Timestamp').iteritems()], axis=1)
Variable1 Variable2
Timestamp
2017/02/12 20.0 5.0
2017/02/13 20.0 2.0
2017/02/14 30.0 10.0
与reset_index
pd.concat(
[s.dropna() for c, s in df.set_index('Timestamp').iteritems()],
axis=1
).reset_index()
Timestamp Variable1 Variable2
0 2017/02/12 20.0 5.0
1 2017/02/13 20.0 2.0
2 2017/02/14 30.0 10.0
最简单的解决方案:
set_index
DataFrame.sum
带有参数level=0
:
df = df.set_index('Timestamp').sum(level=0)
print (df)
Variable1 Variable2
Timestamp
2017/02/12 20.0 5.0
2017/02/13 20.0 2.0
2017/02/14 30.0 10.0
df = df.set_index('Timestamp').sum(level=0).reset_index()
print (df)
Timestamp Variable1 Variable2
0 2017/02/12 20.0 5.0
1 2017/02/13 20.0 2.0
2 2017/02/14 30.0 10.0
groupby
GroupBy.sum
:
df = df.groupby('Timestamp').sum()
print (df)
Variable1 Variable2
Timestamp
2017/02/12 20.0 5.0
2017/02/13 20.0 2.0
2017/02/14 30.0 10.0
df = df.groupby('Timestamp').sum().reset_index()
print (df)
Timestamp Variable1 Variable2
0 2017/02/12 20.0 5.0
1 2017/02/13 20.0 2.0
2 2017/02/14 30.0 10.0
使用用.sum()
链接的.groupby()
df_grouped = df.groupby('Timestamp').sum()
通过将该组的每一列求和。在您的示例中,每列有一个每个日期的条目,因此总结应提供正确的解决方案。