重新索引并填充 pandas 中的NaN值 [英] Reindexing and filling NaN values in Pandas

问题描述

考虑此数据集:

data_dict = {'ind' : [1, 2, 3, 4], 'location' : [301, 301, 302, 303], 'ind_var' : [4, 8, 10, 15], 'loc_var' : [1, 1, 7, 3]}
df = pd.DataFrame(data_dict)

df_indexed = df.set_index(['ind', 'location'])
df_indexed

看起来像

           ind_var loc_var
ind location        
1   301      4      1
2   301      8      1
3   302     10      7
4   303     15      3

ind_var是随ind(=独立)而变化的变量，而loc_var因位置而异. (我还有一个额外的变量，该变量随ind和位置而变化，但是为了简化演示，我省略了该变量)

ind_var is a variable that varies by ind ( = individual) and loc_var varies by location. (I also have an extra variable that varies by both ind and location, but I'm omitting it to simplify the presentation)

我需要转换数据以使每个单独的索引都包含所有可能的位置. 我可以通过这种方式重新编制索引(只显示1至3个人):

I need to transform the data to have each individual index contain all the possible locations. I can reindex in this way (just showing individuals 1 to 3):

new_shape = [(1, 301), (1, 302), (1, 303), (2, 301), (2, 302), (2, 303), (3, 301), (3, 302), (3, 303)]
idx = pd.Index(new_shape)
df2 = df_indexed.reindex(idx, method = None)
df2.index.names = ['id', 'location']

给出

        ind_var loc_var
id  location        
1   301     4     1
    302    NaN   NaN
    303    NaN   NaN
2   301     8     1
    302    NaN   NaN
    303    NaN   NaN
3   301    NaN   NaN
    302    10     7
    303    NaN   NaN

但是我需要一种方法来填充缺少的值，以便得到:

but I need a way to fill the missing values, so that I get:

        ind_var loc_var
id  location        
1   301     4     1
    302     4     7
    303     4     3
2   301     8     1
    302     8     7
    303     8     3
3   301    10     1
    302    10     7
    303    10     3

我尝试了两种不同的方法，但均未成功:

I tried two different things with no success:

1)使用loc_dict = {301:1、302:7、303:3}替换loc_var和使用ind_dict = {1:4:2:8:3:10、4:15}替换ind_var

1) Using a loc_dict = {301 : 1, 302 : 7, 303 : 3} to replace loc_var and a ind_dict = {1 : 4, 2: 8, 3: 10, 4 : 15} to replace ind_var

2)使用groupby方法.

2) Using a groupby method.

# First reset index
df_non_indexed = df2.reset_index() 
df_non_indexed['loc_var'] = df_non_indexed.groupby(['location'])['loc_var'].transform(lambda x: x.fillna(method='ffill')) 

这几乎可行，但只能向前(或向后)填充

This almost works, but only does the fill forward (or backwards)

必须有一个非常简单的方法来执行此操作，但是我还无法弄清楚！ 谢谢您的宝贵时间.

There must be a very simple way of doing this, but I haven't been able to figure it out! Thanks for your time.

注意:这与我的问题

Note: this is related to my question reshaping from wide to long. I've taken a different approach and simplified in hope that this one is easier to understand.

推荐答案

这可以通过stack/unstack和groupby轻松完成:

This can be done by stack/unstack and groupby very easily:

# unstack to wide, fillna as 0s
df_wide = df_indexed.unstack().fillna(0)
# stack back to long
df_long = df_wide.stack()
# change 0s to max using groupby.
df_long['ind_var'] = df_long['ind_var'].groupby(level = 0).transform(lambda x: x.max())
df_long['loc_var'] = df_long['loc_var'].groupby(level = 1).transform(lambda x: x.max())
print df_long

这将为您提供结果:

                   ind_var  loc_var
ind location                  
1   301             4        1
    302             4        7
    303             4        3
2   301             8        1
    302             8        7
    303             8        3
3   301            10        1
    302            10        7
    303            10        3
4   301            15        1
    302            15        7
    303            15        3

这篇关于重新索引并填充 pandas 中的NaN值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！