我有一个 227x4 的数据帧,其中包含要清理的国家/地区名称和数值(争吵?
下面是数据帧的抽象:
import pandas as pd
import random
import string
import numpy as np
pdn = pd.DataFrame(["".join([random.choice(string.ascii_letters) for i in range(3)]) for j in range (6)], columns =['Country Name'])
measures = pd.DataFrame(np.random.random_integers(10,size=(6,2)), columns=['Measure1','Measure2'])
df = pdn.merge(measures, how= 'inner', left_index=True, right_index =True)
df.iloc[4,1] = 'str'
df.iloc[1,2] = 'stuff'
print(df)
Country Name Measure1 Measure2
0 tua 6 3
1 MDK 3 stuff
2 RJU 7 2
3 WyB 7 8
4 Nnr str 3
5 rVN 7 4
如何在不触及国家/地区名称的情况下将所有列中的字符串值替换为np.nan
?
我尝试使用布尔掩码:
mask = df.loc[:,measures.columns].applymap(lambda x: isinstance(x, (int, float))).values
print(mask)
[[ True True]
[ True False]
[ True True]
[ True True]
[False True]
[ True True]]
# I thought the following would replace by default false with np.nan in place, but it didn't
df.loc[:,measures.columns].where(mask, inplace=True)
print(df)
Country Name Measure1 Measure2
0 tua 6 3
1 MDK 3 stuff
2 RJU 7 2
3 WyB 7 8
4 Nnr str 3
5 rVN 7 4
# this give a good output, unfortunately it's missing the country names
print(df.loc[:,measures.columns].where(mask))
Measure1 Measure2
0 6 3
1 3 NaN
2 7 2
3 7 8
4 NaN 3
5 7 4
我看了几个与我相关的问题([1],[2],[3],[4],[5],[6],[7],[8]),但找不到一个回答我的问题。
仅分配感兴趣的列:
cols = ['Measure1','Measure2']
mask = df[cols].applymap(lambda x: isinstance(x, (int, float)))
df[cols] = df[cols].where(mask)
print (df)
Country Name Measure1 Measure2
0 uFv 7 8
1 vCr 5 NaN
2 qPp 2 6
3 QIC 10 10
4 Suy NaN 8
5 eFS 6 4
一个元问题,我在这里提出一个问题(包括研究)需要 3 个多小时正常吗?
在我看来,是的,创造好的问题真的很难。
使用带有错误强制的数字,即
cols = ['Measure1','Measure2']
df[cols] = df[cols].apply(pd.to_numeric,errors='coerce')
国家名称 度量1 度量20 PuB 7.0 6.01 JHq 2.0 NaN2 opE 4.0 3.03 像素 3.0 6.04 ouP NaN 4.05 qZR 4.0 6.0
cols = ['Measure1','Measure2']
df[cols] = df[cols].applymap(lambda x: x if not isinstance(x, str) else np.nan)
或
df[cols] = df[cols].applymap(lambda x: np.nan if isinstance(x, str) else x)
结果:
In [22]: df
Out[22]:
Country Name Measure1 Measure2
0 nBl 10.0 9.0
1 Ayp 8.0 NaN
2 diz 4.0 1.0
3 aad 7.0 3.0
4 JYI NaN 10.0
5 BJO 9.0 8.0