从以前的值中填充NaN值

Question

下面有来自excel表的数据，我希望每个NaN只从它以前的值中被填充，即使它是一个或多个NaN。我尝试过使用ffill()方法，但没有解决这个问题，因为它在列的NaN之前获得了非常重要的值，并将其填充到所有NaN中。

有人能帮忙吗。

我的达菲：

import pandas as pd    
df = pd.read_excel("Example-sheat.xlsx",sheet_name='Sheet1')
#df = df.fillna(method='ffill')
#df = df['AuthenticZTed domaTT controller'].ffill()
print(df)

我的DataFrame输出：

    AuthenticZTed domaTT controller source KTvice naHR
0       ZTPGRKMIK1DC200.example.com            TTv1614
1         TT1NDZ45DC202.example.com            TTv1459
2         TT1NDZ45DC202.example.com            TTv1495
3                               NaN            TTv1670
4         TT1NDZ45DC202.example.com            TTv1048
5         TN1CQI02DC200.example.com            TTv1001
6       DU2RDCRDC1DC204.example.com            TTva082
7                               NaN           xxgb-gen
8       ZTPGRKMIK1DC200.example.com            TTva038
9       DU2RDCRDC1DC204.example.com            TTv0071
10                              NaN            ttv0032
11      KT1MUC02DUDC201.example.com            TTv0073
12                              NaN            TTv0679
13        TN1SZZ67DC200.example.com            TTv1180
14        TT1NDZ45DC202.example.com            TTv1181
15      TT1BLR01APDC200.example.com            TTv0859
16        TN1SZZ67DC200.example.com            xxg2089
17                              NaN            TTv1846
18      ZTPGRKMIK1DC200.example.com           TTvtp064
19        PR1CPQ01DC200.example.com            TTv0950
20        PR1CPQ01DC200.example.com            TTc7005
21                              NaN            TTv0678
22      KT1MUC02DUDC201.example.com       TTv257032798
23        PR1CPQ01DC200.example.com            xxg2016
24                              NaN            TTv0313
25      TT1BLR01APDC200.example.com            TTc4901
26                              NaN            TTv0710
27      DU2RDCRDC1DC204.example.com            xxg3008
28                              NaN            TTv1080
29        PR1CPQ01DC200.example.com            xxg2022
30                              NaN            xxg2057
31                              NaN            TTv1522
32        TN1SZZ67DC200.example.com       TTv258998881
33        PR1CPQ01DC200.example.com       TTv259064418
34      ZTPGRKMIK1DC200.example.com       TTv259129955
35      TT1BLR01APDC200.example.com            xxg2034
36                              NaN       TTv259326564
37      TNHSZPBCD2DC200.example.com       TTv259129952
38      KT1MUC02DUDC201.example.com       TTv259195489
39      ZTPGRKMIK1DC200.example.com            TTv0683
40      ZTPGRKMIK1DC200.example.com            TTv0885
41      TT1BLR01APDC200.example.com              dbexh
42                              NaN           TTvtp065
43      TN1PEK01APDC200.example.com           TTvtp057
44      ZTPGRKMIK1DC200.example.com           TTvtp007
45                              NaN           TTvtp063
46      TT1BLR01APDC200.example.com           TTvtp032
47      KTphbgsa11dc201.example.com           TTvtp046
48                              NaN           TTvtp062
49        PR1CPQ01DC200.example.com            TTv0235
50                              NaN            TTv0485
51        TT1NDZ45DC202.example.com            TTv0236
52                              NaN            TTv0486
53        PR1CPQ01DC200.example.com            TTv0237
54                              NaN            TTv0487
55      TT1BLR01APDC200.example.com            TTv0516
56        TN1CQI02DC200.example.com            TTv1285
57      TN1PEK01APDC200.example.com            TTv0440
58                              NaN            liv9007
59        HR1GDL28DC200.example.com            TTv0445
60                              NaN             tuv006
61      FTGFTPTP34DC203.example.com            TTv0477
62                              NaN             tuv002
63        TN1CQI02DC200.example.com            TTv0534
64        TN1SZZ67DC200.example.com            TTv0639
65                              NaN            TTv0825
66                              NaN            TTv1856
67      TT1BLR01APDC200.example.com            TTva101
68        TN1SZZ67DC200.example.com            TTv1306
69      KTphbgsa11dc201.example.com            TTv1072
70                              NaN             webx02
71      KT1MUC02DUDC201.example.com            TTv1310
72        PR1CPQ01DC200.example.com            TTv1151
73        TN1CQI02DC200.example.com            TTv1165
74                              NaN              tuv90
75        TN1SZZ67DC200.example.com            TTv1065
76      KTphbgsa11dc201.example.com            TTv1737
77                              NaN             ramn01
78        HR1GDL28DC200.example.com             ramn02
79                              NaN             ptb001
80        HR1GDL28DC200.example.com             ptn002
81                              NaN             ptn003
82        TN1SZZ67DC200.example.com            TTs0057
83        PR1CPQ01DC200.example.com            TTs0058
84                              NaN  TTs0058-duplicZTe
85        PR1CPQ01DC200.example.com            xxg2080
86      KTphbgsa11dc204.example.com            xxg2081
87      TN1PEK01APDC200.example.com            xxg2082
88                              NaN            xxg3002
89        TN1SZZ67DC200.example.com            xxg2084
90                              NaN            xxg3005
91      ZTPGRKMIK1DC200.example.com            xxg2086
92                              NaN            xxg3007
93      KT1MUC02DUDC201.example.com            xxg2098
94                              NaN            xxg3014
95      TN1PEK01APDC200.example.com            xxg2026
96                              NaN            xxg2094
97      TN1PEK01APDC200.example.com           livtp005
98      KT1MUC02DUDC201.example.com            xxg2059
99      ZTPGRKMIK1DC200.example.com            acc9102
100                             NaN            xxg2111
101       TN1CQI02DC200.example.com           xxgtp009

期望产出：

    AuthenticZTed domaTT controller source KTvice naHR
0       ZTPGRKMIK1DC200.example.com            TTv1614
1         TT1NDZ45DC202.example.com            TTv1459
2         TT1NDZ45DC202.example.com            TTv1495
3         TT1NDZ45DC202.example.com            TTv1670   <---
4         TT1NDZ45DC202.example.com            TTv1048
5         TN1CQI02DC200.example.com            TTv1001
6       DU2RDCRDC1DC204.example.com            TTva082
7       DU2RDCRDC1DC204.example.com           xxgb-gen   <---

Answer 1

1-您已经接近您的解决方案了，只需将shift()与ffill()一起使用就可以了。

df = df.apply(lambda x: x.fillna(df['AuthenticZTed domaTT controller']).shift()).ffill()

正如Quang建议的那样，在评论中麻生太郎..。

df['AuthenticZTed domaTT controller'] = df['AuthenticZTed domaTT controller'].ffill()

3-或者你也可以尝试以下方法

df = df.fillna({var: df['AuthenticZTed domaTT controller'].shift() for var in df}).ffill()

4-相反，如果您有多个列，则可以定义一个cols变量，然后遍历它。

cols = ['AuthenticZTed domaTT controller', 'source KTvice naHR']

for cols in df.columns:
    df[cols] = df[cols].ffill()

print(df)

     OR

df.loc[:,cols] = df.loc[:,cols].ffill()