Select Only One Value In Df Col Rows In Same Df For Calc Results From Different Val, And Calc Df Only On One Ticker At A Time

I try to calculate some KPIs from different companies/tickers. My stock-info resides in a df, with this structure Ticker Open High Low Adj Close

Solution 1:

You can use transform on your groupby object to maintain a column with the same shape:

Here, for example, is the 3 day moving average of the Adj Close (Pandas < 0.18.0).

df['MA3'] = df.groupby('Ticker').Adj_Close.transform(lambda group: pd.rolling_mean(group, window=3))

>>> df
          Date  Ticker  Open  High  Low  Adj_Close   Volume  MA3
0   2015-04-09  vws.co   315   316  312        312  1686800  NaN
1   2015-04-10  vws.co   317   320  316        313  1396500  NaN
2   2015-04-13  vws.co   318   322  315        316  1564500  313
3   2015-04-14  vws.co   320   322  319        315  1370600  314
4   2015-04-15  vws.co   320   322  319        316   945000  316
5   2015-04-16  vws.co   319   320  310        308  2236100  313
6   2015-04-17  vws.co   310   310  302        299  2711900  308
7   2015-04-20  vws.co   303   312  303        306  1629700  304
8   2016-03-31     mmm   167   168  166        167  1762800  NaN
9   2016-04-01     mmm   166   168  165        168  1993700  NaN
10  2016-04-04     mmm   167   167  166        166  2022800  167
11  2016-04-05     mmm   165   167  165        166  1610300  167
12  2016-04-06     mmm   165   167  165        167  2092200  166
13  2016-04-07     mmm   166   167  165        167  2721900  167

Solution 2:

Use groupby

Setup

import pandas as pd
from StringIO import StringIO

text = """Date   Ticker        Open        High         Low   Adj_Close   Volume
2015-04-09  vws.co  315.000000  316.100000  312.500000  311.520000  1686800
2015-04-10  vws.co  317.000000  319.700000  316.400000  312.700000  1396500
2015-04-13  vws.co  317.900000  321.500000  315.200000  315.850000  1564500
2015-04-14  vws.co  320.000000  322.400000  318.700000  314.870000  1370600
2015-04-15  vws.co  320.000000  321.500000  319.200000  316.150000   945000
2015-04-16  vws.co  319.000000  320.200000  310.400000  307.870000  2236100
2015-04-17  vws.co  309.900000  310.000000  302.500000  299.100000  2711900
2015-04-20  vws.co  303.000000  312.000000  303.000000  306.490000  1629700
2016-03-31     mmm  166.750000  167.500000  166.500000  166.630005  1762800
2016-04-01     mmm  165.630005  167.740005  164.789993  167.529999  1993700
2016-04-04     mmm  167.110001  167.490005  165.919998  166.399994  2022800
2016-04-05     mmm  165.179993  166.550003  164.649994  165.809998  1610300
2016-04-06     mmm  165.339996  167.080002  164.839996  166.809998  2092200
2016-04-07     mmm  165.880005  167.229996  165.250000  167.160004  2721900"""

df = pd.read_csv(StringIO(text), delim_whitespace=1, parse_dates=[0], index_col=0)

Looks like:

print df

            Ticker        Open        High         Low   Adj_Close   Volume
Date                                                                       
2015-04-09  vws.co  315.000000  316.100000  312.500000  311.520000  1686800
2015-04-10  vws.co  317.000000  319.700000  316.400000  312.700000  1396500
2015-04-13  vws.co  317.900000  321.500000  315.200000  315.850000  1564500
2015-04-14  vws.co  320.000000  322.400000  318.700000  314.870000  1370600
2015-04-15  vws.co  320.000000  321.500000  319.200000  316.150000   945000
2015-04-16  vws.co  319.000000  320.200000  310.400000  307.870000  2236100
2015-04-17  vws.co  309.900000  310.000000  302.500000  299.100000  2711900
2015-04-20  vws.co  303.000000  312.000000  303.000000  306.490000  1629700
2016-03-31     mmm  166.750000  167.500000  166.500000  166.630005  1762800
2016-04-01     mmm  165.630005  167.740005  164.789993  167.529999  1993700
2016-04-04     mmm  167.110001  167.490005  165.919998  166.399994  2022800
2016-04-05     mmm  165.179993  166.550003  164.649994  165.809998  1610300
2016-04-06     mmm  165.339996  167.080002  164.839996  166.809998  2092200
2016-04-07     mmm  165.880005  167.229996  165.250000  167.160004  2721900

Solution

df.groupby('Ticker').sum()

           Open         High          Low    Adj_Close    Volume
Ticker                                                          
mmm      995.89  1003.590011   991.949981  1000.339998  12203700
vws.co  2521.80  2543.400000  2497.900000  2484.550000  13541100

You can aggregate and do many things with the groupby object.