Excaliburst Excaliburst - 6 months ago 12
Python Question

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 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


I need to do calculations on a per ticker basis, so I do not skew the moving-avg results when traversing companies/tickers.

Could someone please help me with that?

Split-up and concatenating dfs
How would I logically do the separation of tickers into maybe different dfs appended with the ticker-name, and afterwards how do I append them all again to one single df?

Selection from same df
Or select only the relevant ticker-rows in the same df? (maybe like this Splitting dataframe into multiple dataframes)

Any help is appreciated...

---------- From Alexanders post below.
This code

df['MA1'] = df.groupby('Ticker').df['Adj Close'].transform(lambda group: pd.rolling_mean(group, window=10))


Throws this error:

AttributeError: 'DataFrameGroupBy' object has no attribute 'df'


Or a more close cut'n paste

Adj_Close = df['Adj Close']

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


Does NOT anymore throw an error. It was the naming of the col 'Adj Close' with a space that groupby does not like... This must be a bug

If I try this syntax, that should work - it doesn't!

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


And throws this error:

AttributeError: 'DataFrameGroupBy' object has no attribute 'df'


But I can work with the above. Thanks Alexander

Answer

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