R - Employee Reporting Structure

Question

Background: I am using R along with some packages to pull JSON data from a ticketing system. I'm pulling all the users and want to build a reporting structure.

I have a data set that contains employees and their managers. The columns are named as such ("Employee" and "Manager"). I am trying to build a tree of a reporting structure that goes up to the root. We are in an IT organization, but I am pulling all employee data, so this would look something like:

Company -> Business Unit -> Executive -> Director -> Group Manager -> Manager -> Employee

That's the basic idea. Some areas have a tree structure that is small, others it's multiple levels. Basically, what I am trying to do is get a tree, or reporting structure I can reference, so I can determine for an employee, who their director is. This could be 1 level removed or up to 5 or 6 levels removed.

I came across data.tree, but so far, as I look at it, I have to provide a pathString that defines that structure. Since I only have the two columns, what I'd like to do is throw this data frame into a function and have it traverse the list as it finds the employee, put it under that manager, when it finds that manager as an employee, nest it under their direct report, along with anything nested under them.

I haven't been able to figure out how to make data.tree do this without defining the pathString, but in doing so, I can only build the pathString on what I know for each row - the employee and their manager. The result is a tree that only has 2 levels and directors aren't connected to their Group Manager and Group Managers aren't connected to their managers and so forth.

I thought about writing some logic/loops to go through and do this, but there must be an easier way or a package that I can use to do this. Maybe I am not defining the pathString correctly....

Ultimately, what I'd like the end result to be is a data frame with columns that look like:

Employee, Manager1, Manager2, Manager3, ManagerX, ...

Of course some rows will only have entries in columns 1 and 2, but others could go up many levels. Once I have this, I can look up devices in our configuration management system, find the owner and aggregate those counts under the appropriate director.

Any help would be appreciate. I cannot post the data, as it is confidential in nature, but it simply contains the employee and their managers. I just need to connect all the dots... Thanks!

Answer 1

The data.tree package has the FromDataFrameNetwork function for just this scenario:

library(data.tree)

DataForTree <- data.frame(manager = c("CEO","sally","sally","sue","mary", "mary"),
                          employee = c("sally","sue","paul","mary","greg", "don"),
                          stringsAsFactors = FALSE)


tree <- FromDataFrameNetwork(DataForTree)

print(tree)

Results in:

1 CEO                 
2  °--sally           
3      ¦--sue         
4      ¦   °--mary    
5      ¦       ¦--greg
6      ¦       °--don 
7      °--paul  

Answer 2

The hR package is specifically designed to address the needs for data analysis using people/employee data; albeit, it is minimal at this point. The hierarchy function can produce a wide data frame as you would like; this helps with joining in other data and continuing an analysis.

library(hR)
ee = c("[email protected]","[email protected]","[email protected]","[email protected]")
supv = c("[email protected]","[email protected]","[email protected]","[email protected]")
hierarchy(ee,supv,format="wide")

  Employee      Supv1         Supv2        Supv3
1   [email protected] [email protected] [email protected] [email protected]
2    [email protected] [email protected] [email protected] [email protected]
3  [email protected] [email protected] [email protected]         <NA>
4 [email protected] [email protected]          <NA>         <NA>