General question about binning data in excel

[abberyfarm]

Hi there,

I have three columns of data, 100,000 plus rows, and I am wondering how would you go about binning the data into say 10 different categories depending on the values in the columns?

Would you use a macro?

Thanks

 

[Fazza]

thanks, that doesn't help me though

what final result/format/table would be wanted? simple assignations to bins as you've answered already or some sort of summary

 

[shg]

I don't know how to answer the question you asked, so let me respond to the one I can.

Once you've binned the data and assigned a state to each bin, then you could say this:

State 1 appears twice (ignoring the final state). The first time it changes to state 2, and the second time it remains in state 1. So in state 1, there's a 50% chance it will remain in that state, and a 50% chance it will change to state 2.

Similarly, state 3 will remain in state 3 2/3 of the time, and 1/3 of the time will change to state 4.

Once you have the transition probabilities, you can make a state machine that behaves (statistically) the same way.

So the objective of binning is to translate state vectors (speed and VSP in this example) to discrete states, so you can proceed to the next step.

 

[Fazza]

thanks, shg

if the exercise is like that, I'm thinking it would be inefficient to load a worksheet with tens of thousands of intermediate results - unless needed for auditing - and instead simply go directly to the final result (whatever that might be, I don't know)

I don't like worksheets with multiple columns each with tens of thousand of formulas

 

[shg]

You have a reference system defined by a list of state vectors; one way or another, you need to extract a model from that.

 

[Fazza]

sure, and that can be done without filling in many tens of thousands of cells with formulas or intermediate results. regards

 

[shg]

Well, jump in!

A pivot table with groupings on the two state variables (one as row, one as column) would be a good start (that I can do). In the body of the table, you need the count of transitions to each state. I'm no pivot table wiz, and would be happy to see how it's done.

 

[Fazza]

well, I don't know the real question yet!

and I'm thinking it doesn't need to be done on a worksheet (such as a pivot table), perhaps

SQL might be OK on its own - so yes, a pivot table or maybe a query table, or ADO - or use arrays in VBA, or whatever else is best

 

[shg]

well, I don't know the real question yet!

1. Given a list of state vectors, bin the state variables in some fashion. (The purpose of binning is to reduce the total number of states; otherwise, with continuous variables, every vector might be a unique state).

2. Assign each vector to a state number based on those bins.

3. Create a transition table that shows the count of transitions of each state to every state.

 

[Fazza]

my reference to the real question is the question in this thread

I don't want to hijack/divert the thread - I'd like to leave this thread to the OP's question

 

[abberyfarm]

Hi Guys,

First of all I would like to thank you all for your help. This forum is a great resource.

Shg is correct in that I am working on a markov chain analysis and he helped me out with a method for creating transition probabilities on this thread http://www.mrexcel.com/forum/showthread.php?t=576628
Just in relation to this shg, I decided to use a different transitional state in the end, not speed. I am using change in driving mode. i.e acceleration, deceleration, idle and cruising. I adapted your method to do this.
Example

This thread is not directly linked to markov chain aspect of my work. It precedes it and is just to segment my data into different categories before I proceed to the markov chain part.

So the purpose of this thread is that I want to bin the data into 17 different operating mode bins according to this. FYI, VSP = vehicle specific power.

Then I will perform a principle component analyses (using excel stat) on these bins to reduce the data dimensions and then I will cluster the data in different driving patterns based on the most significant principle components.

But at the moment I just focusing on binning the data.

Thank for the help again. Any suggestions?