pandas dataframe column based on row and multiple columns

Question

I have the following dataframe, I wish to add a new column called open_next_year.

This column will be selected by comparing two columns; fiscalYear + 1 and ticker. Then using the value from column open.

Original dataframe:

   fiscalYear ticker     open  
         2017   FINL  17.4880  
         2017   AAPL  17.4880  
...
         2016   FINL  16.4880  
         2016   AAPL  16.4880  
         2015   FINL  15.4880  
         2015   AAPL  15.4880

Desired dataframe:

   fiscalYear ticker     open  open_next_year
         2017   FINL  17.4880  
         2017   AAPL  17.4880  
         2016   FINL  16.4880  17.4880 
         2016   AAPL  16.4880  17.4880
         2015   FINL  15.4880  16.4880 
         2015   AAPL  15.4880  16.4880

What is the pandas way to achieve this please?

jezrael · Accepted Answer · 2018-07-01 11:16:12Z

2

I believe need for each group shift all values by DataFrameGroupBy.shift:

df['open_next_year'] = df.groupby('ticker')['open'].shift()
print (df)
   fiscalYear ticker    open  open_next_year
0        2017   FINL  17.488             NaN
1        2017   AAPL  17.488             NaN
2        2016   FINL  16.488          17.488
3        2016   AAPL  16.488          17.488
4        2015   FINL  15.488          16.488
5        2015   AAPL  15.488          16.488

Changed sample for unique open values:

print (df)
   fiscalYear ticker     open
0        2017   FINL  17.4881
1        2017   AAPL  17.4882
2        2016   FINL  16.4883
3        2016   AAPL  16.4884
4        2015   FINL  15.4885
5        2015   AAPL  15.4886

df['open_next_year'] = df.groupby('ticker')['open'].shift()
print (df)
   fiscalYear ticker     open  open_next_year
0        2017   FINL  17.4881             NaN
1        2017   AAPL  17.4882             NaN
2        2016   FINL  16.4883         17.4881
3        2016   AAPL  16.4884         17.4882
4        2015   FINL  15.4885         16.4883
5        2015   AAPL  15.4886         16.4884

edited Jul 1, 2018 at 11:16

answered Jul 1, 2018 at 10:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

gibo Over a year ago

This doesn't seem to take into account the 'fiscalYear'

jezrael Over a year ago

OK, can you add more data to question?

jezrael Over a year ago

@gibo - Can you add more data to sample? fiscalYear values are unique? Or not? There are multiple tickers? Maybe 8 rows should be perfect with 2 groups if necessary.

gibo Over a year ago

Added. fiscalYear will be unique to the ticker, there are mulitple tickers

jezrael Over a year ago

@gibo - thank you. But if there are unique years per groups, my solution working nice.

Anton vBR · Accepted Answer · 2018-07-01 11:46:58Z

Here is another approach creating a map first.

m = dict(zip(tuple(zip(df.fiscalYear - 1, df.ticker)),df.open))
df['open_next_year'] = df[['fiscalYear','ticker']].apply(tuple, 1).map(m)

The map/dictionary looks like this and is obtained by zipping together year - 1, ticker and open value:

{(2014, 'AAPL'): 15.488,
 (2014, 'FINL'): 15.488,
 (2015, 'AAPL'): 16.488,
 (2015, 'FINL'): 16.488,
 (2016, 'AAPL'): 17.488,
 (2016, 'FINL'): 17.488}

Full example:

data = '''\
fiscalYear ticker    open
2017   FINL  17.488
2017   AAPL  17.488
2016   FINL  16.488
2016   AAPL  16.488
2015   FINL  15.488
2015   AAPL  15.488'''

fileobj = pd.compat.StringIO(data)
df = pd.read_csv(fileobj, sep='\s+')

m = dict(zip(tuple(zip(df.fiscalYear - 1, df.ticker)),df.open))
df['open_next_year'] = df[['fiscalYear','ticker']].apply(tuple, 1).map(m)

print(df)

Returns:

   fiscalYear ticker    open  open_next_year
0        2017   FINL  17.488             NaN
1        2017   AAPL  17.488             NaN
2        2016   FINL  16.488          17.488
3        2016   AAPL  16.488          17.488
4        2015   FINL  15.488          16.488
5        2015   AAPL  15.488          16.488

Collectives™ on Stack Overflow

pandas dataframe column based on row and multiple columns

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related