5 Replies Latest reply on Apr 20, 2018 5:38 AM by Martin Dunleavy

    Sub-grouping string category and averaging across a group

    Charlie Pottss

      I have a data table where each row is broken out by an individual day, for a particular string. This string is in one of the columns, and represents the stock's ticker symbol (ie MSFT or DATA).. There are several fields (columns), but i'll refer to the stock price field in this example. So to be clear, there is a distinct row for DATA on 04/18/2018, and MSFT for 04/18/2018, each with their respective stock px on that day

       

      There are about 90 different stocks in my data set. I would like to do two things:

      1. Categorize these strings/stock tickers into groups, so that I can more easily refer to them when creating visualizations. For example, specify a sub-group of companies in the data set for which I would like to categorize as "security stocks".
      2. Create an average & median value for the group (and sub-groups mentioned above) on a specific day. For example, the average stock px for all 90 companies yesterday. This needs to be a continuous variable (so i can graph how median and avg change over time), and one which excludes situations where the "stock price" field has a string in it (such as N/A, for when a company was not publicly traded). It also needs to disregard 0's in the avg/median, because I do have some 0's in my data set.. I would like to be able to also do this for the sub groups, but if this complicates things I can live without it.

       

      I've tried to calculate the average, but it keeps calculating the average for an individual stock, applying it over time.

       

      Any help would be much appreciated. Thanks.