key: pandas, DataFrame, dict, Series
DataFrame数据处理。希望将DataFrame将指定列的数据,根据指定字典,替换为对应字段。
由于需求不同,一般有两种情况:
- 不在映射规则中(字典),处理为
NaN
。 - 不在映射规则中(字典),保留原值。
待处理数据
import pandas as pd
df = pd.DataFrame({'Date':['10/2/2011', '11/2/2011', '12/2/2011', '13/2/2011'],
'Event':['Music', 'Poetry', 'Theatre', 'Comedy'],
'Cost':[10000, 5000, 15000, 2000]})
print(df)
Date Event Cost
0 10/2/2011 Music 10000
1 11/2/2011 Poetry 5000
2 12/2/2011 Theatre 15000
3 13/2/2011 Comedy 2000
method1 - replace
保留原值
df = pd.DataFrame({'Date':['10/2/2011', '11/2/2011', '12/2/2011', '13/2/2011'],
'Event':['Music', 'Poetry', 'Theatre', 'Comedy'],
'Cost':[10000, 5000, 15000, 2000]})
dict = {'Music' : 'M', 'Poetry' : 'P', 'Theatre' : 'T', 'xxx' : 'C'}
df = df.replace({"Event": dict})
print(df)
Date Event Cost
0 10/2/2011 M 10000
1 11/2/2011 P 5000
2 12/2/2011 T 15000
3 13/2/2011 Comedy 2000
method2 - map
置为NaN
df = pd.DataFrame({'Date':['10/2/2011', '11/2/2011', '12/2/2011', '13/2/2011'],
'Event':['Music', 'Poetry', 'Theatre', 'Comedy'],
'Cost':[10000, 5000, 15000, 2000]})
dict = {'Music' : 'M', 'Poetry' : 'P', 'Theatre' : 'T', 'xxx' : 'C'}
df["Event"] = df["Event"].map(dict)
print(df)
Date Event Cost
0 10/2/2011 M 10000
1 11/2/2011 P 5000
2 12/2/2011 T 15000
3 13/2/2011 NaN 2000
method3 - map + lambda
保留原值
df = pd.DataFrame({'Date':['10/2/2011', '11/2/2011', '12/2/2011', '13/2/2011'],
'Event':['Music', 'Poetry', 'Theatre', 'Comedy'],
'Cost':[10000, 5000, 15000, 2000]})
dict = {'Music' : 'M', 'Poetry' : 'P', 'Theatre' : 'T', 'xxx' : 'C'}
df["Event"] = df["Event"].map(lambda x: dict[x] if x in dict else x)
print(df)
Date Event Cost
0 10/2/2011 M 10000
1 11/2/2011 P 5000
2 12/2/2011 T 15000
3 13/2/2011 Comedy 2000
method4 - update
保留原值(注意,这里的映射字典改变了)
df = pd.DataFrame({'Date':['10/2/2011', '11/2/2011', '12/2/2011', '13/2/2011'],
'Event':['Music', 'Poetry', 'Theatre', 'Comedy'],
'Cost':[10000, 5000, 15000, 2000]})
dict = {0 : 'M', 1 : 'P', 2 : 'T', 999 : 'C'}
df["Event"].update(pd.Series(dict))
print(df)
Date Event Cost
0 10/2/2011 M 10000
1 11/2/2011 P 5000
2 12/2/2011 T 15000
3 13/2/2011 Comedy 2000
其他
method3 - map + lambda
比 method1 - replace
快了1-2倍左右。
测试数据:
循环100次 %timeit -n100 code
rows | method3 | method1 |
---|---|---|
4 | 6us | 14us |
710 | 240us | 750us |
循环1000次
https://www.heelpbook.net/2020/python-pandas-using-dictionary-to-remap-values-in-dataframe/