向 pandas 数据框添加缺少的日期 [英] Add missing dates to pandas dataframe
问题描述
[我在类似的问题上发现了很多话题,但没有关于我如何处理这个问题]
[I've found quite of bit of talk on similar issues, but nothing on how I'm approaching this]
我有一个CSV文件在给定日期可能有多个事件的解析。有时候,没有任何事件。我收到这些事件,按日期计算并绘制它们。
I have a CSV file I'm parsing which could have multiple events on a given date. Sometimes there are NO events on a date. I take these events, get a count by date and plot them.
但是,当我绘制它们时,我的两个系列不会总是匹配。
However, when I plot them, my two series dont always match.
df = pd.read_csv(inFile, parse_dates=True)
idx = pd.date_range(df['simpleDate'].min(), df['simpleDate'].max())
s = df.groupby(['simpleDate']).size()
fig, ax = plt.subplots()
ax.bar(idx.to_pydatetime(), s, color='green')
在上面的代码中, idx 成为30个日期的范围。 09-01-2013至09-30-2013
但是, S 可能只有25或26天,因为在给定日期没有发生任何事件。然后我得到一个AssertionError,因为大小不匹配。
In the above code idx becomes a range of say 30 dates. 09-01-2013 to 09-30-2013 However S may only have 25 or 26 days because no events happened for a given date. I then get an AssertionError as the sizes dont match.
解决这个问题的正确方法是什么?
我想从 IDX 中删除没有值的日期,或者(我宁愿做的)将系列添加到缺少的日期,计数为0.我宁愿30天的完整图表,0值。如果这种方法是正确的,有什么建议如何开始?我需要一些动态的 reindex
函数?
What's the proper way to tackle this?
Do I want to remove dates with no values from IDX or (which I'd rather do) is add to the series the missing date with a count of 0. I'd rather have a full graph of 30 days with 0 values. If this approach is right, any suggestions on how to get started? Do I need some sort of dynamic reindex
function?
这是一个 S ( df.groupby(['simpleDate'])。size()
),请注意04和05的条目。
Here's a snippet of S ( df.groupby(['simpleDate']).size()
), notice no entries for 04 and 05.
09-02-2013 2
09-03-2013 10
09-06-2013 5
09-07-2013 1
推荐答案
您可以使用 Series.reindex
:
import pandas as pd
idx = pd.date_range('09-01-2013', '09-30-2013')
s = pd.Series({'09-02-2013': 2,
'09-03-2013': 10,
'09-06-2013': 5,
'09-07-2013': 1})
s.index = pd.DatetimeIndex(s.index)
s = s.reindex(idx, fill_value=0)
print(s)
2013-09-01 0
2013-09-02 2
2013-09-03 10
2013-09-04 0
2013-09-05 0
2013-09-06 5
2013-09-07 1
2013-09-08 0
...
这篇关于向 pandas 数据框添加缺少的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!