从 Pandas 数据框中删除 NaN 值并重塑表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46235097/
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
Remove NaN values from pandas dataframe and reshape table
提问by user1621127
i have a 500*100 Pandas DataFrame with alot of NaN values.
I know that each column will contain 495 NaN and 5 real values. I want to reshape the table to only contain the 5 rows with the real values, final dimensions should thus be 5*100.
我有一个 500*100 的 Pandas DataFrame，里面有很多 NaN 值。
我知道每列将包含 495 个 NaN 和 5 个实数值。我想将表格重塑为仅包含具有实际值的 5 行，因此最终尺寸应为 5*100。
I know there's alot of questions on how to remove NaN values but i have not found a way to also reshape the table accordingly.
我知道有很多关于如何删除 NaN 值的问题，但我还没有找到一种方法来相应地重塑表格。
Thanks in advance.
提前致谢。
回答by jezrael
You need applywith dropna, only is necessary create numpy arrayand reassign Seriesfor reset indices:
您需要applywith dropna，只需要为重置索引创建numpy array和重新分配Series：
df.apply(lambda x: pd.Series(x.dropna().values))
Sample:
样本：
df = pd.DataFrame({'B':[4,np.nan,4,np.nan,np.nan,4],
'C':[7,np.nan,9,np.nan,2,np.nan],
'D':[1,3,np.nan,7,np.nan,np.nan],
'E':[np.nan,3,np.nan,9,2,np.nan]})
print (df)
B C D E
0 4.0 7.0 1.0 NaN
1 NaN NaN 3.0 3.0
2 4.0 9.0 NaN NaN
3 NaN NaN 7.0 9.0
4 NaN 2.0 NaN 2.0
5 4.0 NaN NaN NaN
df1 = df.apply(lambda x: pd.Series(x.dropna().values))
print (df1)
B C D E
0 4.0 7.0 1.0 3.0
1 4.0 9.0 3.0 9.0
2 4.0 2.0 7.0 2.0
回答by Divakar
Approach #1Here's one with array data -
方法 #1这是一个带有数组数据的方法 -
a = df.values.T
df_out = pd.DataFrame(a[~np.isnan(a)].reshape(a.shape[0],-1).T)
Sample run -
样品运行 -
In [450]: df
Out[450]:
0 1 2
0 1.0 NaN NaN
1 9.0 7.0 8.0
2 NaN NaN NaN
3 NaN 5.0 7.0
In [451]: a = df.values.T
In [452]: pd.DataFrame(a[~np.isnan(a)].reshape(a.shape[0],-1).T)
Out[452]:
0 1 2
0 1.0 7.0 8.0
1 9.0 5.0 7.0
Approach #2As it turns out, we already have an utility for it : justify-
方法#2事实证明，我们已经有了它的实用程序：justify-
In [1]: df
Out[1]:
0 1 2
0 1.0 NaN NaN
1 9.0 7.0 8.0
2 NaN NaN NaN
3 NaN 5.0 7.0
In [2]: pd.DataFrame(justify(df.values, invalid_val=np.nan, axis=0, side='up')[:2])
Out[2]:
0 1 2
0 1.0 7.0 8.0
1 9.0 5.0 7.0
Benchmarking
基准测试
Approaches -
方法 -
def app0(df): # @jezrael's soln
return df.apply(lambda x: pd.Series(x.dropna().values))
def app1(df): # Proposed in this post
a = df.values.T
return pd.DataFrame(a[~np.isnan(a)].reshape(a.shape[0],-1).T)
def app2(df): # Proposed in this post
a = df.values
return pd.DataFrame(justify(a, invalid_val=np.nan, axis=0, side='up')[:5])
def app3(df): # @piRSquared's soln-1
v = df.values
r = np.arange(v.shape[1])[None, :]
a = np.isnan(v).argsort(0)
return pd.DataFrame(v[a[:5], r], columns=df.columns)
def app4(df): # @piRSquared's soln-2
return pd.DataFrame(
(lambda a, s: a[~np.isnan(a)].reshape(-1, s, order='F'))
(df.values.ravel('F'), df.shape[1]),
columns=df.columns
)
Timings -
时间 -
In [513]: # Setup input dataframe with exactly 5 non-NaNs per col
...: m,n = 500,100
...: N = 5
...: a = np.full((m,n), np.nan)
...: row_idx = np.random.rand(m,n).argsort(0)[:N]
...: a[row_idx, np.arange(n)] = np.random.randint(0,9,(N,n))
...: df = pd.DataFrame(a)
...:
In [572]: %timeit app0(df)
...: %timeit app1(df)
...: %timeit app2(df)
...: %timeit app3(df)
...: %timeit app4(df)
...:
10 loops, best of 3: 46.1 ms per loop
10000 loops, best of 3: 132 μs per loop
1000 loops, best of 3: 554 μs per loop
1000 loops, best of 3: 446 μs per loop
10000 loops, best of 3: 148 μs per loop
回答by piRSquared
Using @Divakar's sample dataframe
使用@Divakar 的示例数据框
df
0 1 2
0 1.0 NaN NaN
1 9.0 7.0 8.0
2 NaN NaN NaN
3 NaN 5.0 7.0
v = df.values
r = np.arange(v.shape[1])[None, :]
a = np.isnan(v).argsort(0)
pd.DataFrame(v[a[:2], r], columns=df.columns)
0 1 2
0 1.0 7.0 8.0
1 9.0 5.0 7.0
Inspired by @Divakar's answer
灵感来自@Divakar 的回答
pd.DataFrame(
(lambda a, s: a[~np.isnan(a)].reshape(-1, s, order='F'))(df.values.ravel('F'), df.shape[1]),
columns=df.columns
)
0 1 2
0 1.0 7.0 8.0
1 9.0 5.0 7.0
