Techniques for trend extraction from unbalanced panel data

Question

My data set is formatted like this:

User-id | Threat_score
aaa       45
bbb       32
ccc       20

The list contains the top 100 users with the highest threat scores. I generate such a list monthly and store each month's list in its own file.

There are three things I would like to get from this data:

1. Users who are consistently showing up in this list

2. Users who are consistently showing up in this list with high threat scores

3. Users whose threat scores are increasing very quickly

I am thinking a visual summary would be nice; each month (somehow) decide which users I want to plot on a graph of historic threat scores.

Are there any known visualization techniques that deal with similar requirements?

How should I be transforming my current data to achieve what I am looking for?

Answer 1

I would add a third column called month and then concatenate each list. So if you have a top 100 list for 5 months you will create one big table with 500 entries:

User-id | Threat_score | month
aaa       45             1
bbb       32             1
ccc       20             1
...       ...            ...
bbb       64             2
ccc       29             2
...       ...            ...

Then, to answer your first question, you could simply count the occurrences of each user-id. For example, if user bbb is in your concatenated table five times, then you know that person made your list all five months.

To answer you second question, you could do a group by operation to compute some aggregate function of the users. A group by operation with an average function is a little crude and sensitive to outliers, but it would probably get you close to what you are looking for.

One possibility for the third question is to compute the difference in threat score between month n-1 and month n. That is, for each month (not including the first month) you subtract the user's previous threat score from the current threat score. You can make this a new column so your table would now look like:

User-id | Threat_score | month | difference
aaa       45             1       null
bbb       32             1       null
ccc       20             1       null
...       ...            ...     ...
bbb       64             2       32
ccc       29             2       9
...       ...            ...

With this table, you could again do a group by operation to find people who consistently have a higher threat score than the previous month or you could simply find people with a large difference between the current month and the previous month.

As you suggest, visualizing this data is a really good idea. If you care about these threat scores over time (which I think you do), I strongly recommend a simple line chart, with month on the x-axis and threat score on the y-axis. It's not fancy, but it's extremely easy to interpret and should give you useful information about the trends.

Most of this stuff (not the visualization) can be done in SQL and all of it can be done in R or Python (and many other languages). Good luck!

Answer 2

You have three questions to answer and 100 records per month to analyze.

Based on this size, I'd recommend doing analysis in a simple SQL database or a spreadsheet to start off with. The first two questions are fairly easy to figure out. The third is a little more difficult.

I'd definitely add a column for month and group all of that data together into a spreadsheet or database table given the questions you want to answer.

question 1. Users who are consistently showing up in this list

In excel, this answer should help you out: https://superuser.com/questions/442653/ms-excel-how-count-occurence-of-item-in-a-list

For a SQL database: https://stackoverflow.com/questions/2516546/select-count-duplicates

question 2. Users who are consistently showing up in this list with high risk score

This is just adding a little complexity to the above. For SQL, you would further qualify your query based on a minimum risk score value.

In excel, a straight pivot isn't going to work, you'll have to copy the unique values in one column to another, then drag a CountIf function adjacent to each unique value, qualifying the countif function with a minimum risk score.

question 3. Users who have/reaching the high risk level very fast.

A fast rise in risk level could be defined as the difference between two months being larger than a given value.

For each user record you want to know the previous month's threat value, or assume zero as the previous threat value.

If that difference is greater than your risk threshold, you want to include it in your report. If not, they can be filtered from the list.

If I had to do this month after month, I would spend the two hours it might take to automate a report after the first couple of months. I'd throw all the data in a SQL database and write a quick script in perl or java to iterate through the 100 records, do the calculation, and output the users who crossed the threshold.

If I needed it to look pretty, I'd use a reporting tool. I'm not particularly partial to any of them.

If I needed to trend threshold values over time, I'd output the results for all people into a second table add records to that table each month.

If I just needed to do it once or twice, figuring out how to do it in excel by adding a new column using VLookUp and some basic math and a filter would probably be the fastest and easiest way to get it done. I tend to avoid using excel for things I'll need to use with consistency because there are limits that you run into when your data gets sizeable.