Calculate average change % in a group

I have a 35000x5 table, and the first two columns are:

 
id  price
12  5.4  
12  4.6  
12  3.4  
12  5  
12  3.2  
23  2.1  
23  4.69  
23  2.3  
23  4.69  
23  3  
32  5  
32  4.3
32  4  
32  21  
32  4.3  
49  2.3  
49  4  
49  2.1  
49  4.9

I need help to calculate:

• the total number of groups from column 1. (i.e, 4 groups)
• the absolute average price change (in %) from column 2 per group -- i.e, the absolute average price change from the first group is -12.8%.
• the absolute average price change from column 2 for the total number of observations. i.e., the average price change from the average of all the groups.
• the group name (id) with the highest absolute average change.
• the group name (id) with the lowest absolute average change.

This is probably really easy, but I am still learning the basics. Any help is appreciated

[Walter Roberson]

What are the average price changes to be taken relative to? Relative to the previous entry for the same group? Relative to the first entry for the same group?

[A2017]

Relative to the previous entry for the same group.

[Walter Roberson]

The problem with relative to the previous entry for the same group is that increases and decreases are not symmetric in percentage.

Suppose you start with 100. A 10% decrease would get you to 90. A 10% increase on that would get you to 99. A 10% decrease on that would get you to 89.1. A 10% increase on that would get you to 98.01. Average price increase (percentage): -10+10-10+10 = 0, even though the value fell by 1.99 over that time.

[A2017]

Hmm... so would you suggest I go about calculating the average variation in price from a given group? (if that makes any sense)

Answer 1

[Rik]

The unique function should help you out here.

[A2017]

Thanks. And how would I go about calculating the average change using that function?

[Rik]

For that you will need to answer the questions Walter asked. You should also try to do a crash course on Matlab. If you can't find one, Mathworks offers one for free here.

Answer 2

[Guillaume]

Since you're using a table the rowfun function (or maybe even varfun) with the 'GroupingVariables' option is very useful. No idea what absolute average price change for a group actually mean so here is an example calculating the average of each group:

groupedtable = rowfun(@mean, yourtable, 'GroupingVariables', 'id', 'InputVariables', 'price')
numgroups = height(groupedtable)  %the total number of groups 
[~, row] = max(groupedtable{:, 3});
higehestmeanid = groupedtable.id(row)  %the group name (id) with the highest absolute average