Roman Kazmin
Reputation: 981
Add new column to dataframe based on dictionary
I have a dataframe and a dictionary. I need to add a new column to the dataframe and calculate its values based on the dictionary.
Machine learning, adding new feature based on some table:
score = {(1, 45, 1, 1) : 4, (0, 1, 2, 1) : 5}
df = pd.DataFrame(data = {
'gender' : [1, 1, 0, 1, 1, 0, 0, 0, 1, 0],
'age' : [13, 45, 1, 45, 15, 16, 16, 16, 15, 15],
'cholesterol' : [1, 2, 2, 1, 1, 1, 1, 1, 1, 1],
'smoke' : [0, 0, 1, 1, 7, 8, 3, 4, 4, 2]},
dtype = np.int64)
print(df, '\n')
df['score'] = 0
df.score = score[(df.gender, df.age, df.cholesterol, df.smoke)]
print(df)
I expect the following output:
gender age cholesterol smoke score
0 1 13 1 0 0
1 1 45 2 0 0
2 0 1 2 1 5
3 1 45 1 1 4
4 1 15 1 7 0
5 0 16 1 8 0
6 0 16 1 3 0
7 0 16 1 4 0
8 1 15 1 4 0
9 0 15 1 2 0
Upvotes: 24
Views: 3340
Answers (7)
Alexander
Reputation: 109510
Using assign with a list comprehension, getting a tuple of values (each row) from the score dictionary, defaulting to zero if not found.
>>> df.assign(score=[score.get(tuple(row), 0) for row in df.values])
gender age cholesterol smoke score
0 1 13 1 0 0
1 1 45 2 0 0
2 0 1 2 1 5
3 1 45 1 1 4
4 1 15 1 7 0
5 0 16 1 8 0
6 0 16 1 3 0
7 0 16 1 4 0
8 1 15 1 4 0
9 0 15 1 2 0
Timings
Given the variety of approaches, I though it would be interesting to compare some of the timings.
# Initial dataframe 100k rows (10 rows of identical data replicated 10k times).
df = pd.DataFrame(data = {
'gender' : [1, 1, 0, 1, 1, 0, 0, 0, 1, 0] * 10000,
'age' : [13, 45, 1, 45, 15, 16, 16, 16, 15, 15] * 10000,
'cholesterol' : [1, 2, 2, 1, 1, 1, 1, 1, 1, 1] * 10000,
'smoke' : [0, 0, 1, 1, 7, 8, 3, 4, 4, 2] * 10000},
dtype = np.int64)
%timeit -n 10 df.assign(score=[score.get(tuple(v), 0) for v in df.values])
# 223 ms ± 9.28 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%%timeit -n 10
df.assign(score=[score.get(t, 0) for t in zip(*map(df.get, df))])
# 76.8 ms ± 2.8 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%%timeit -n 10
df.assign(score=[score.get(v, 0) for v in df.itertuples(index=False)])
# 113 ms ± 2.58 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%timeit -n 10 df.assign(score=df.apply(lambda x: score.get(tuple(x), 0), axis=1))
# 1.84 s ± 77.3 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%%timeit -n 10
(df
.set_index(['gender', 'age', 'cholesterol', 'smoke'])
.assign(score=pd.Series(score))
.fillna(0, downcast='infer')
.reset_index()
)
# 138 ms ± 11.5 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%%timeit -n 10
s=pd.Series(score)
s.index.names=['gender','age','cholesterol','smoke']
df.merge(s.to_frame('score').reset_index(),how='left').fillna(0).astype(int)
# 24 ms ± 2.27 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%%timeit -n 10
df.assign(score=pd.Series(zip(df.gender, df.age, df.cholesterol, df.smoke))
.map(score)
.fillna(0)
.astype(int))
# 191 ms ± 7.54 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
%%timeit -n 10
df.assign(score=df[['gender', 'age', 'cholesterol', 'smoke']]
.apply(tuple, axis=1)
.map(score)
.fillna(0))
# 1.95 s ± 134 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)
Upvotes: 8
Vishnudev Krishnadas
Reputation: 10960
Simple one line solution, Use get and tuple row-wise,
df['score'] = df.apply(lambda x: score.get(tuple(x), 0), axis=1)
Above solution is assuming there are no columns other than desired ones in order. If not, just use columns
cols = ['gender','age','cholesterol','smoke']
df['score'] = df[cols].apply(lambda x: score.get(tuple(x), 0), axis=1)
Upvotes: 2
anky
Reputation: 75080
May be another way would be using .loc[]:
m=df.set_index(df.columns.tolist())
m.loc[list(score.keys())].assign(
score=score.values()).reindex(m.index,fill_value=0).reset_index()
gender age cholesterol smoke score
0 1 13 1 0 0
1 1 45 2 0 0
2 0 1 2 1 5
3 1 45 1 1 4
4 1 15 1 7 0
5 0 16 1 8 0
6 0 16 1 3 0
7 0 16 1 4 0
8 1 15 1 4 0
9 0 15 1 2 0
Upvotes: 2
BENY
Reputation: 323226
reindex
df['socre']=pd.Series(score).reindex(pd.MultiIndex.from_frame(df),fill_value=0).values
df
Out[173]:
gender age cholesterol smoke socre
0 1 13 1 0 0
1 1 45 2 0 0
2 0 1 2 1 5
3 1 45 1 1 4
4 1 15 1 7 0
5 0 16 1 8 0
6 0 16 1 3 0
7 0 16 1 4 0
8 1 15 1 4 0
9 0 15 1 2 0
Or merge
s=pd.Series(score)
s.index.names=['gender','age','cholesterol','smoke']
df=df.merge(s.to_frame('score').reset_index(),how='left').fillna(0)
Out[166]:
gender age cholesterol smoke score
0 1 13 1 0 0.0
1 1 45 2 0 0.0
2 0 1 2 1 5.0
3 1 45 1 1 4.0
4 1 15 1 7 0.0
5 0 16 1 8 0.0
6 0 16 1 3 0.0
7 0 16 1 4 0.0
8 1 15 1 4 0.0
9 0 15 1 2 0.0
Upvotes: 4
Quang Hoang
Reputation: 150725
List comprehension and map:
df['score'] = (pd.Series(zip(df.gender, df.age, df.cholesterol, df.smoke))
.map(score)
.fillna(0)
.astype(int)
)
Output:
gender age cholesterol smoke score
0 1 13 1 0 0
1 1 45 2 0 0
2 0 1 2 1 5
3 1 45 1 1 4
4 1 15 1 7 0
5 0 16 1 8 0
6 0 16 1 3 0
7 0 16 1 4 0
8 1 15 1 4 0
9 0 15 1 2 0
9 0 15 1 2 0.0
Upvotes: 4
ALollz
Reputation: 59519
Since score is a dictionary (so the keys are unique) we can use MultiIndex alignment
df = df.set_index(['gender', 'age', 'cholesterol', 'smoke'])
df['score'] = pd.Series(score) # Assign values based on the tuple
df = df.fillna(0, downcast='infer').reset_index() # Back to columns
gender age cholesterol smoke score
0 1 13 1 0 0
1 1 45 2 0 0
2 0 1 2 1 5
3 1 45 1 1 4
4 1 15 1 7 0
5 0 16 1 8 0
6 0 16 1 3 0
7 0 16 1 4 0
8 1 15 1 4 0
9 0 15 1 2 0
Upvotes: 13
Dani Mesejo
Reputation: 61900
You could use map, since score is a dictionary:
df['score'] = df[['gender', 'age', 'cholesterol', 'smoke']].apply(tuple, axis=1).map(score).fillna(0)
print(df)
Output
gender age cholesterol smoke score
0 1 13 1 0 0.0
1 1 45 2 0 0.0
2 0 1 2 1 5.0
3 1 45 1 1 4.0
4 1 15 1 7 0.0
5 0 16 1 8 0.0
6 0 16 1 3 0.0
7 0 16 1 4 0.0
8 1 15 1 4 0.0
9 0 15 1 2 0.0
As an alternative you could use a list comprehension:
df['score'] = [score.get(t, 0) for t in zip(df.gender, df.age, df.cholesterol, df.smoke)]
print(df)
Upvotes: 4
Related Questions
- Create new dataframe column with values based existing column AND on dictionary?
- Add new column based on values of another column from a dictionary in pandas
- Adding Column in Pandas Dataframe Based on column with dictionary values
- Create new columns in pandas data frame based on existing column
- adding a column to a pandas dataframe using a dictionary
- How to add a column with values of a dictionary in Python
- Add column to pandas.Dataframe using key values from dictionary
- Create a new column based on other columns and a dictionary
- Adding dictionary to Pandas dataframe column based on value in the dictionary
- Add a new column to pandas data frame from dictionary