Visualizing headcount data over a particular time period

Question

I have a data visualization question that I would like to get some input on. I'm currently using python pandas to clean up a data set then subsequently uploading it in SISENSE for use. What I am trying to do is visualize active jobs grouped by week/month based on the start and end dates of particular assignments. For example, I have a set of jobs with the following start dates, organized in rows within a dataframe:

Job ID Start Date End Date

Job 1 5/25/2020 6/7/2020

Job 2 5/25/2020 5/31/2020

For the week of 5/25/2020 I have two active jobs, and for the week of 6/1/2020 I have 1 active job. The visualization should look like a bar chart with the x axis being the week/time period and y axis being the count of active jobs.

How can I best organize this into a data frame and visualize it?

score 0 · Answer 1 · answered May 27 '20 at 20:14

something like

    df = pd.DataFrame({'Job ID': [1,2], 'Start Date': ['5/25/2020', '5/25/2020'], 'End Date': ['6/7/2020', '5/31/2020']})

You could then apply a function to generate a new column 'week beginning' - take a look here for a solution in python Get week start date (Monday) from a date column in Python (pandas)?

import datetime as dt
# Change 'myday' to contains dates as datetime objects
df['Start Date'] = pd.to_datetime(df['Start Date'])  
# 'daysoffset' will container the weekday, as integers
df['daysoffset'] = df['Start Date'].apply(lambda x: x.weekday())
# We apply, row by row (axis=1) a timedelta operation
df['Week Beginning'] = df.apply(lambda x: x['Start Date'] - dt.TimeDelta(days=x['daysoffset']), axis=1)

and then groupby on this week beginning

counr = df.groupby(df['Week Beginning']).sum()

Following this, you could plot using

count_by_job_id = count['Job ID']
pd.DataFrame(count_by_job_id).plot.bar()

score 0 · Answer 2 · answered May 29 '20 at 18:38

You will need a custom SQL in Sisense elasticube to make this work easily. You will then join your dataframe table with the dim_dates ( excel file from the below link)

This is similar to scenario described here : https://support.sisense.com/hc/en-us/articles/230644208-Date-Dimension-File

You custom SQL will be something like this :

  Select JobID, 
         CAST(startdate as date) as Startdate, 
         CAST(enddate as date) as Enddate,
         C.RECORD_DATE AS week_start
  FROM JOB j
  JOIN tbl_Calendar C ON c.RECORD_DATE BETWEEN j.StartDate and j.EndDate
  WHERE DATENAME(DW,C.RECORD_DATE) = 'MONDAY'

Then you can just create a column chart and drop the fields week_start( you can format in a few different ways) under categories section and drop the field count(JOBID) under values section.

Visualizing headcount data over a particular time period

2 Answers2