Using Pandas series values as index for assigning values to another series

Question

I am trying to find a shortcut to assigning a pandas series from elements in a list.

My code:

import pandas as pd
df=pd.DataFrame([[1,2,2183],[1,4,2235],[2,3,6123],[3,4,4213]],columns=['month','staff','sales'])

goals=[1346,4456,4574]

df['goals']=goals[df['month']-1]

The last line throws an error: TypeError: list indices must be integers or slices, not Series

Desired output looks like this:

   month  staff  sales  goals
0      1      2   2183   1346
1      1      4   2235   1346
2      2      3   6123   4456
3      3      3   4213   4574

This is a general case, and in the future, the goals array might actually be multidimensional.

I gave a simple example. In reality, there might be a number of months that need to be looked up and not in order in the Pandas series. Goals will always be in order. — hrschbck
– hrschbck, Commented Nov 28, 2017 at 17:34

MaxU - stand with Ukraine · Accepted Answer · 2017-11-28 17:52:04Z

3

In [80]: mapping = pd.Series(goals, index=df.month.unique())

In [81]: mapping
Out[81]:
1    1346
2    4456
3    4574
dtype: int64

In [82]: df['goals'] = df.month.map(mapping)

In [83]: df
Out[83]:
   month  staff  sales  goals
0      1      2   2183   1346
1      1      4   2235   1346
2      2      3   6123   4456
3      3      4   4213   4574

edited Nov 28, 2017 at 17:52

answered Nov 28, 2017 at 17:37

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

hrschbck Over a year ago

Can this work for goals determined by month and staff#? A matrix of goals?

MaxU - stand with Ukraine Over a year ago

@hrschbck, i don't understand - what is matrix of goals?

hrschbck Over a year ago

If the goal depended on both the month and the staff#, if Goal(month=1,staff=2)=1500 and Goal(month=1,staff=4)=1346?

MaxU - stand with Ukraine Over a year ago

@hrschbck, please ask a new question and provide there a corresponding data sets (input and output)...

A.Kot · Accepted Answer · 2017-11-28 17:49:47Z

1

import numpy as np

First, a dictionary of values for goals and their respective months:

goals_dict = dict(zip(np.arange(1,len(goals)+1), goals))

Now use map

df['goals'] = df.month.map(goals_dict)

answered Nov 28, 2017 at 17:49

A.Kot

7,9832 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Using Pandas series values as index for assigning values to another series

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related