填充 MultiIndex Pandas Dataframe 中的日期间隔
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17287933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filling in date gaps in MultiIndex Pandas Dataframe
提问by Michael
I would like to modify a pandas MultiIndex DataFrame such that each index group includes Dates between a specified range. I would like each group to fill in missing dates 2013-06-11 to 2013-12-31 with the value 0 (or NaN).
我想修改一个 Pandas MultiIndex DataFrame，以便每个索引组都包含指定范围之间的日期。我希望每个组用值 0（或NaN）填写缺失的日期 2013-06-11 到 2013-12-31 。
Group A, Group B, Date, Value
loc_a group_a 2013-06-11 22
2013-07-02 35
2013-07-09 14
2013-07-30 9
2013-08-06 4
2013-09-03 40
2013-10-01 18
group_b 2013-07-09 4
2013-08-06 2
2013-09-03 5
group_c 2013-07-09 1
2013-09-03 2
loc_b group_a 2013-10-01 3
I've seen a few discussions of reindexing, but that is for a simple (non-grouped) time-series data.
我看过一些关于reindexing 的讨论，但那是针对简单（非分组）时间序列数据的。
Is there an easy way to do this?
是否有捷径可寻？
Following are some attempts I've made at accomplishing this. For example: Once I've unstacked by ['A', 'B'], I can then reindex.
以下是我为实现这一目标所做的一些尝试。例如：一旦我通过 取消堆叠['A', 'B']，我就可以重新索引。
df = pd.DataFrame({'A': ['loc_a'] * 12 + ['loc_b'],
'B': ['group_a'] * 7 + ['group_b'] * 3 + ['group_c'] * 2 + ['group_a'],
'Date': ["2013-06-11",
"2013-07-02",
"2013-07-09",
"2013-07-30",
"2013-08-06",
"2013-09-03",
"2013-10-01",
"2013-07-09",
"2013-08-06",
"2013-09-03",
"2013-07-09",
"2013-09-03",
"2013-10-01"],
'Value': [22, 35, 14, 9, 4, 40, 18, 4, 2, 5, 1, 2, 3]})
df.Date = df['Date'].apply(lambda x: pd.to_datetime(x).date())
df = df.set_index(['A', 'B', 'Date'])
dt_start = dt.datetime(2013,6,1)
all_dates = [(dt_start + dt.timedelta(days=x)).date() for x in range(0,60)]
df2 = df.unstack(['A', 'B'])
df3 = df2.reindex(index=all_dates).fillna(0)
df4 = df3.stack(['A', 'B'])
## df4 is about where I want to get, now I'm trying to get it back in the form of df...
df5 = df4.reset_index()
df6 = df5.rename(columns={'level_0' : 'Date'})
df7 = df6.groupby(['A', 'B', 'Date'])['Value'].sum()
The last few lines make me a little sad. I was hoping that at df6I could simply set_indexback to ['A', 'B', 'Date'], but that did not group the values as they are grouped in the initial dfDataFrame.
最后几行让我有点难过。我希望在df6我可以简单地set_index回到['A', 'B', 'Date']，但这并没有将值分组，因为它们在初始数据df帧中分组。
Any thoughts on how I can reindex the unstacked DataFrame, restack, and have the DataFrame in the same format as the original?
关于如何重新索引未堆叠的 DataFrame、重新堆叠并使 DataFrame 与原始格式相同的任何想法？
采纳答案by TomAugspurger
Your question wasn't clear about exactly which dates you were missing; I'm just assuming that you want to fill NaNfor any date for which you dohave an observation elsewhere. My solution will have to be amended if this assumption is faulty.
您的问题不清楚您错过了哪些日期；我只是假设您想填写NaN您在其他地方有观察的任何日期。如果这个假设是错误的，我的解决方案将不得不修改。
Side note: it may be nice to include a line to create the DataFrame
旁注：包含一行来创建 DataFrame
In [55]: df = pd.DataFrame({'A': ['loc_a'] * 12 + ['loc_b'],
....: 'B': ['group_a'] * 7 + ['group_b'] * 3 + ['group_c'] * 2 + ['group_a'],
....: 'Date': ["2013-06-11",
....: "2013-07-02",
....: "2013-07-09",
....: "2013-07-30",
....: "2013-08-06",
....: "2013-09-03",
....: "2013-10-01",
....: "2013-07-09",
....: "2013-08-06",
....: "2013-09-03",
....: "2013-07-09",
....: "2013-09-03",
....: "2013-10-01"],
....: 'Value': [22, 35, 14, 9, 4, 40, 18, 4, 2, 5, 1, 2, 3]})
In [56]:
In [56]: df.Date = pd.to_datetime(df.Date)
In [57]: df = df.set_index(['A', 'B', 'Date'])
In [58]:
In [58]: print(df)
Value
A B Date
loc_a group_a 2013-06-11 22
2013-07-02 35
2013-07-09 14
2013-07-30 9
2013-08-06 4
2013-09-03 40
2013-10-01 18
group_b 2013-07-09 4
2013-08-06 2
2013-09-03 5
group_c 2013-07-09 1
2013-09-03 2
loc_b group_a 2013-10-01 3
To get the unobserved values filled, we'll use the unstackand stackmethods. Unstacking will create the NaNs we're interested in, and then we'll stack them up to work with.
为了填充未观察到的值，我们将使用unstack和stack方法。Unstacking 将创建NaN我们感兴趣的s，然后我们将它们堆叠起来使用。
In [71]: df.unstack(['A', 'B'])
Out[71]:
Value
A loc_a loc_b
B group_a group_b group_c group_a
Date
2013-06-11 22 NaN NaN NaN
2013-07-02 35 NaN NaN NaN
2013-07-09 14 4 1 NaN
2013-07-30 9 NaN NaN NaN
2013-08-06 4 2 NaN NaN
2013-09-03 40 5 2 NaN
2013-10-01 18 NaN NaN 3
In [59]: df.unstack(['A', 'B']).fillna(0).stack(['A', 'B'])
Out[59]:
Value
Date A B
2013-06-11 loc_a group_a 22
group_b 0
group_c 0
loc_b group_a 0
2013-07-02 loc_a group_a 35
group_b 0
group_c 0
loc_b group_a 0
2013-07-09 loc_a group_a 14
group_b 4
group_c 1
loc_b group_a 0
2013-07-30 loc_a group_a 9
group_b 0
group_c 0
loc_b group_a 0
2013-08-06 loc_a group_a 4
group_b 2
group_c 0
loc_b group_a 0
2013-09-03 loc_a group_a 40
group_b 5
group_c 2
loc_b group_a 0
2013-10-01 loc_a group_a 18
group_b 0
group_c 0
loc_b group_a 3
Reorder the index levels as necessary.
根据需要重新排序索引级别。
I had to slip that fillna(0)in the middle there so that the NaNs weren't dropped. stackdoes have a dropnaargument. I would think that setting that to false would keep the all NaNrows around. A bug maybe?
我不得不把它fillna(0)放在中间，这样NaNs 就不会掉下来。 stack确实有dropna争论。我认为将其设置为 false 会保留所有NaN行。可能是一个错误？
回答by Christian Long
You can make a new multi index based on the Cartesian product of the levels of the existing multi index. Then, re-index your data frame using the new index.
您可以根据现有多索引的级别的笛卡尔积制作新的多索引。然后，使用新索引重新索引您的数据框。
new_index = pd.MultiIndex.from_product(df.index.levels)
new_df = df.reindex(new_index)
# Optional: convert missing values to zero, and convert the data back
# to integers. See explanation below.
new_df = new_df.fillna(0).astype(int)
That's it! The new data frame has all the possible index values. The existing data is indexed correctly.
就是这样！新数据框具有所有可能的索引值。现有数据已正确编入索引。
Read on for a more detailed explanation.
继续阅读以获得更详细的解释。
Explanation
解释
Set up sample data
设置样本数据
import pandas as pd
df = pd.DataFrame({'A': ['loc_a'] * 12 + ['loc_b'],
'B': ['group_a'] * 7 + ['group_b'] * 3 + ['group_c'] * 2 + ['group_a'],
'Date': ["2013-06-11",
"2013-07-02",
"2013-07-09",
"2013-07-30",
"2013-08-06",
"2013-09-03",
"2013-10-01",
"2013-07-09",
"2013-08-06",
"2013-09-03",
"2013-07-09",
"2013-09-03",
"2013-10-01"],
'Value': [22, 35, 14, 9, 4, 40, 18, 4, 2, 5, 1, 2, 3]})
df.Date = pd.to_datetime(df.Date)
df = df.set_index(['A', 'B', 'Date'])
Here's what the sample data looks like
这是示例数据的样子
Value
A B Date
loc_a group_a 2013-06-11 22
2013-07-02 35
2013-07-09 14
2013-07-30 9
2013-08-06 4
2013-09-03 40
2013-10-01 18
group_b 2013-07-09 4
2013-08-06 2
2013-09-03 5
group_c 2013-07-09 1
2013-09-03 2
loc_b group_a 2013-10-01 3
Make new index
建立新索引
Using from_productwe can make a new multi index. This new index is the Cartesian productof all the values from all the levels of the old index.
使用from_product我们可以创建一个新的多索引。这个新索引是旧索引所有级别的所有值的笛卡尔积。
new_index = pd.MultiIndex.from_product(df.index.levels)
Reindex
重新索引
Use the new index to reindex the existing data frame.
使用新索引重新索引现有数据框。
new_df = df.reindex(new_index)
All the possible combinations are now present. The missing values are null (NaN).
所有可能的组合现在都存在。缺失值为空 (NaN)。
The expanded, re-indexed data frame looks like this:
扩展的、重新索引的数据框如下所示：
Value
loc_a group_a 2013-06-11 22.0
2013-07-02 35.0
2013-07-09 14.0
2013-07-30 9.0
2013-08-06 4.0
2013-09-03 40.0
2013-10-01 18.0
group_b 2013-06-11 NaN
2013-07-02 NaN
2013-07-09 4.0
2013-07-30 NaN
2013-08-06 2.0
2013-09-03 5.0
2013-10-01 NaN
group_c 2013-06-11 NaN
2013-07-02 NaN
2013-07-09 1.0
2013-07-30 NaN
2013-08-06 NaN
2013-09-03 2.0
2013-10-01 NaN
loc_b group_a 2013-06-11 NaN
2013-07-02 NaN
2013-07-09 NaN
2013-07-30 NaN
2013-08-06 NaN
2013-09-03 NaN
2013-10-01 3.0
group_b 2013-06-11 NaN
2013-07-02 NaN
2013-07-09 NaN
2013-07-30 NaN
2013-08-06 NaN
2013-09-03 NaN
2013-10-01 NaN
group_c 2013-06-11 NaN
2013-07-02 NaN
2013-07-09 NaN
2013-07-30 NaN
2013-08-06 NaN
2013-09-03 NaN
2013-10-01 NaN
Nulls in integer column
整数列中的空值
You can see that the data in the new data frame has been converted from ints to floats. Pandas can't have nulls in an integer column. Optionally, we can convert all the nulls to 0, and cast the data back to integers.
可以看到新数据框中的数据已经从int转换为float了。Pandas 在整数列中不能有空值。或者，我们可以将所有空值转换为 0，并将数据转换回整数。
new_df = new_df.fillna(0).astype(int)
Result
结果
Value
loc_a group_a 2013-06-11 22
2013-07-02 35
2013-07-09 14
2013-07-30 9
2013-08-06 4
2013-09-03 40
2013-10-01 18
group_b 2013-06-11 0
2013-07-02 0
2013-07-09 4
2013-07-30 0
2013-08-06 2
2013-09-03 5
2013-10-01 0
group_c 2013-06-11 0
2013-07-02 0
2013-07-09 1
2013-07-30 0
2013-08-06 0
2013-09-03 2
2013-10-01 0
loc_b group_a 2013-06-11 0
2013-07-02 0
2013-07-09 0
2013-07-30 0
2013-08-06 0
2013-09-03 0
2013-10-01 3
group_b 2013-06-11 0
2013-07-02 0
2013-07-09 0
2013-07-30 0
2013-08-06 0
2013-09-03 0
2013-10-01 0
group_c 2013-06-11 0
2013-07-02 0
2013-07-09 0
2013-07-30 0
2013-08-06 0
2013-09-03 0
2013-10-01 0
