这个数据框中填充缺失值的最有效方法是什么？

Question

我有以下的pandas数据框：

df = pd.DataFrame([
    ['A', 2017, 1],
    ['A', 2019, 1],
    ['B', 2017, 1],
    ['B', 2018, 1],
    ['C', 2016, 1],
    ['C', 2019, 1],
], columns=['ID', 'year', 'number'])

我正在寻找一种最有效的方法，在number列中用默认值0填补缺少的年份。

期望输出如下：

  ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

我拥有的数据框相对较大，因此我正在寻找一种高效的解决方案。

编辑：

这是我目前拥有的代码:

min_max_dict = df[['ID', 'year']].groupby('ID').agg([min, max]).to_dict('index')

new_ix = [[], []]
for id_ in df['ID'].unique():
    for year in range(min_max_dict[id_][('year', 'min')], min_max_dict[id_][('year', 'max')]+1): 
        new_ix[0].append(id_)
        new_ix[1].append(year)


df.set_index(['ID', 'year'], inplace=True)
df = df.reindex(new_ix, fill_value=0).reset_index()

结果

  ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

- borisdonchev

---

可能是重复问题，https://dev59.com/R2Ik5IYBdhLWcg3wYtT7#19324591 - sushanth

@Sushanth 我最初也是这么想的，但不完全是这样，有一个问题- 对于ID a，不应该插入2016年，只能插入以后的年份。 - anky

@Sushanth，问题在于我有多个ID和多个不同的日期范围（对于ID A，它是从2017年到2019年，对于ID B，它是从2017年到2018年）。 - borisdonchev

@SebastienD 我已经编辑了原始帖子。 - borisdonchev

Answer 1

使用pd.Series构造函数是一个比使用explode稍微快一点的方法。如果年份已经按照从早到晚的顺序排序，您可以使用.iloc。

idx = df.groupby('ID')['year'].apply(lambda x: pd.Series(np.arange(x.iloc[0], x.iloc[-1]+1))).reset_index()
df.set_index(['ID','year']).reindex(pd.MultiIndex.from_arrays([idx['ID'], idx['year']]), fill_value=0).reset_index()

输出：

  ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

- Scott Boston

---

在这个例子中，使用explode函数的平均运行时间为17.2毫秒，标准差为332微秒，共进行了7次循环，每个循环100次。使用pd.Series构造函数的平均运行时间为13.1毫秒，标准差为208微秒，同样进行了7次循环，每个循环100次。 - Scott Boston

Answer 2

这里是使用 reindex 的另一种方法

u = df.groupby('ID')['year'].apply(lambda x: range(x.min(),x.max()+1)).explode()

out = (df.set_index(['ID','year']).reindex(u.reset_index().to_numpy(),fill_value=0)
         .reset_index())

---

  ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

- anky

Answer 3

t = df.groupby('ID')['year'].agg(['min','max']).reset_index()
t['missing'] = t.transform(lambda x: [y for y in range(x['min'], x['max']+1) if y not in x.values], axis=1)
t = t[['ID','missing']].explode('missing').dropna()
t['number'] = 0
t.columns = ['ID','year','number']
pd.concat([df,t]).sort_values(by=['ID','year'])

输出

    ID  year    number
0   A   2017    1
0   A   2018    0
1   A   2019    1
2   B   2017    1
3   B   2018    1
4   C   2016    1
2   C   2017    0
2   C   2018    0
5   C   2019    1

- Chris

Answer 4

这里有一种避免使用lambda的方法，可以避免任何缓慢的应用程序。从DataFrame中创建基础DataFrame时，它是一种内存效率低下的解决方案，其交叉乘积是所有ID和年份范围。更新后，我们可以使用布尔掩码cummax在正向和反向方向上检查，将其切片到您需要的期间，实现高效的操作。

如果大多数ID跨越相同的年份范围，则在从产品中创建基础DataFrame方面不会有太多浪费。如果您想要更好的性能，则有许多关于如何进行交叉积的更有效的方法 的帖子。

def Alollz(df):
    idx = pd.MultiIndex.from_product([np.unique(df['ID']), 
                                      np.arange(df['year'].min(), df['year'].max()+1)],
                                     names=['ID', 'year'])
   
    df_b = pd.DataFrame({'number': 0}, index=idx)
    df_b.update(df.set_index(['ID', 'year']))
    
    m = (df_b.groupby(level=0)['number'].cummax().eq(1) 
         & df_b[::-1].groupby(level=0)['number'].cummax().eq(1))
    
    return df_b.loc[m].reset_index()

---

Alollz(df)

  ID  year  number
0  A  2017     1.0
1  A  2018     0.0
2  A  2019     1.0
3  B  2017     1.0
4  B  2018     1.0
5  C  2016     1.0
6  C  2017     0.0
7  C  2018     0.0
8  C  2019     1.0

---

相比其他方案，这确实是更多的代码。但为了看出它真正的优势，让我们创建一些带有50K个ID的虚拟数据（为了测试数据的简便性，我将让所有日期范围都相同）。

N = 50000
df = pd.DataFrame({'ID': np.repeat(range(N), 2),
                   'year': np.tile([2010,2018], N),
                   'number': 1})

#@Scott Boston's Answer
def SB(df):
    idx = df.groupby('ID')['year'].apply(lambda x: pd.Series(np.arange(x.iloc[0], x.iloc[-1]+1))).reset_index()
    df = df.set_index(['ID','year']).reindex(pd.MultiIndex.from_arrays([idx['ID'], idx['year']]), fill_value=0).reset_index()
    return df

# Make sure they give the same output:
(Alollz(df) == SB(df)).all().all()
#True

%timeit Alollz(df)
#1.9 s ± 73.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

%timeit SB(df)
#10.8 s ± 539 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

这样速度快了5倍，如果事情需要几秒钟的时间，这就是一个非常重要的进步。

- ALollz

Answer 5

以下是一种方法：

letter_keys = df.ID.unique()
data = df.values
missing_records = []
for letter in letter_keys:
    print(letter)
    years = [x[1] for x in data if x[0] == letter]
    min_year = min(years)
    max_year = max(years)
    current_year = min_year
    while current_year<max_year:
        if current_year not in years:
            missing_records.append([letter, current_year,0])
            print('missing', current_year)
        current_year +=1

new_df = df.append(pd.DataFrame(missing_records, columns = df.columns)).sort_values(['ID','year'])

输出

| ID   |   year |   number |
|:-----|-------:|---------:|
| A    |   2017 |        1 |
| A    |   2018 |        0 |
| A    |   2019 |        1 |
| B    |   2017 |        1 |
| B    |   2018 |        1 |
| C    |   2016 |        1 |
| C    |   2017 |        0 |
| C    |   2018 |        0 |
| C    |   2019 |        1 |

- Sebastien D

Answer 6

您可以尝试使用date_range和pd.merge来解决问题:

g = df.groupby("ID")["year"].agg({"min":"min","max":"max"}).reset_index()
id_years = pd.DataFrame(list(g.apply(lambda row: list(row["ID"]) + 
                    list(pd.date_range(start=f"01/01/{row['min']}", \
                    end=f"01/01/{row['max']+1}",freq='12M').year), axis=1))).melt(0).dropna()[[0,"value"]]

id_years.loc[:,"value"] = id_years["value"].astype(int)
id_years = id_years.rename(columns = {0:"ID","value":'year'})
id_years = id_years.sort_values(["ID","year"]).reset_index(drop=True)

## Merge two dataframe
output_df = pd.merge(id_years, df, on=["ID","year"], how="left").fillna(0)
output_df.loc[:,"number"] = output_df["number"].astype(int)
output_df

输出：

    ID  year    number
0   A   2017    1
1   A   2018    0
2   A   2019    1
3   B   2017    1
4   B   2018    1
5   C   2016    1
6   C   2017    0
7   C   2018    0
8   C   2019    1

- Narendra Prasath

Answer 7

我们可以使用来自 pyjanitor 的 complete 函数，它提供了一个方便的抽象来生成缺失的行，这种情况下是按 ID 组：

# pip install pyjanitor
import pandas as pd
import janitor as jn

# create mapping for range of years
years = dict(year = lambda year: range(year.min(), year.max() + 1))

# apply the complete function per group and fill the nulls with 0

df.complete(years, by = 'ID', sort = True).fillna(0, downcast = 'infer')
 
  ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

然而，by主要是为了方便起见；在某些情况下，做更多的工作可能更有效率，类似于@Alollz的解决方案：

# get the mapping for the year for the entire dataframe
years = dict(year =  range(df.year.min(), df.year.max() + 1))

# create a groupby
group = df.groupby('ID').year

#  assign the max and min years to the dataframe
(df.assign(year_max = group.transform('max'), 
           year_min = group.transform('min'))
     # run complete on the entire dataframe, without `by`
    # note that ID, year_min, year_max are grouped together
    # think of it as a DataFrame of just these three columns
    # combined with years .. we are not modifying these three columns
    # only the years 
   .complete(years, ('ID', 'year_min', 'year_max'))
    # filter rows where year is between max and min
   .loc[lambda df: df.year.between(df.year_min, df.year_max), 
        df.columns]
    # sort the values and fillna
   .sort_values([*df], ignore_index = True)
   .fillna(0, downcast = 'infer')
)
 
  ID  year  number
0  A  2017       1
1  A  2018       0
2  A  2019       1
3  B  2017       1
4  B  2018       1
5  C  2016       1
6  C  2017       0
7  C  2018       0
8  C  2019       1

使用 @Allolz 的示例数据：

N = 50000
df = pd.DataFrame({'ID': np.repeat(range(N), 2),
                   'year': np.tile([2010,2018], N),
                   'number': 1})

def complete_sam(df):
    years = dict(year =  range(df.year.min(), df.year.max() + 1))
    group = df.groupby('ID').year
    outcome = (df.assign(year_max = group.transform('max'),
                         year_min = group.transform('min'))
                 .complete(years, ('ID', 'year_min', 'year_max'))
                 .loc[lambda df: df.year.between(df.year_min, 
                                                 df.year_max),
                     df.columns]
                 .sort_values([*df], ignore_index = True)
                 .fillna(0)
              )
    return outcome

#@Scott Boston's Answer
def SB(df):
    idx = df.groupby('ID')['year'].apply(lambda x: pd.Series(np.arange(x.iloc[0], x.iloc[-1]+1))).reset_index()
    df = df.set_index(['ID','year']).reindex(pd.MultiIndex.from_arrays([idx['ID'], idx['year']]), fill_value=0).reset_index()
    return df

#@Alollz's answer
def Alollz(df):
    idx = pd.MultiIndex.from_product([np.unique(df['ID']), 
                                      np.arange(df['year'].min(), df['year'].max()+1)],
                                     names=['ID', 'year'])
   
    df_b = pd.DataFrame({'number': 0}, index=idx)
    df_b.update(df.set_index(['ID', 'year']))
    
    m = (df_b.groupby(level=0)['number'].cummax().eq(1) 
         & df_b[::-1].groupby(level=0)['number'].cummax().eq(1))
    
    return df_b.loc[m].reset_index()

In [310]: Alollz(df).equals(complete_sam(df))
Out[310]: True

In [311]: %timeit complete_sam(df)
268 ms ± 24.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [312]: %timeit Alollz(df)
1.84 s ± 58.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [316]: SB(df).eq(complete_sam(df)).all().all()
Out[316]: True

In [317]: %timeit SB(df)
6.13 s ± 87.3 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

- sammywemmy

Answer 8

这样做是可行的，但会为'B'创建一个名为'2019'的条目：

df.pivot(index='ID', columns='year', values='number').fillna(0).stack().to_frame('number')

返回：

    number
ID  year    
A   2016    0.0
2017    1.0
2018    0.0
2019    1.0
B   2016    0.0
2017    1.0
2018    1.0
2019    0.0
C   2016    1.0
2017    0.0
2018    0.0
2019    1.0

- dokteurwho

---

很遗憾，我不需要这样的解决方案，因为它假设每个ID都是相同的。它不仅包括B的2019年，还包括A的2016年。 - borisdonchev