Reverse Scorecard Generation

[eschwein-usds]

MENTOR

• @eschwein-usds
• Sr Technical Advisor, Zero Trust Program, HHS Scorecard updates
• Communication Channel(s): CMS Slack, Enterprise GitHub

BRIEF DESCRIPTION

This section should contain a clearly defined result to be achieved by the end of the sprint, such as: Starting with a series of Excel spreadsheets, gather the contents of a specific sheet in each spreadsheet and generate a spreadsheet or CSV file where each sheet is 1 line in the sheet.

This is the inverse of the script Gaelan wrote last summer.

SCOPING

• hours

SKILLS NEEDED

Python scripting

REQUIRED DELIVERABLES

1. Script to extract data from a series of spreadsheets and transform it into 1 CSV file

Outline:

1. Read the names of the spreadsheets from the directory. Each spreadsheet represents the Zero Trust Scorecard for an application or system at CMS.
2. For each spreadsheet:
   • Read the system name (either in the title or in a specific cell in the spreadsheet)
   • collect the Number score and Explanation for each of the 40 scores (they are in 2 columns next to each other)
   • Make the columns into rows: score1, explanation1, score2, explanation2, etc
3. Output the entries into a CSV. The order of the rows doesn't matter, but the order of the columns is important and we can provide.

A previous script has a recommendation for a python library for reading and writing excel files and the code for how we created these sheets.

DESIRED DELIVERY WINDOW

• before July 8? After July 8 I can probably do it myself.

NOTES/INSPO

ACKNOWLEDGEMENTS

• [X] - Yes, I agree to spend file a project proposal ticket
• [X] - Yes, I agree this project falls within a 2 week or less (ideally less) scope of time
• [X] - Yes, I agree to spend an hour per week of office hours and 1x1 mentoring
• [X] - Yes, I agree to spend an hour at the end of the sprint for filling out a Retro Template

[decause-gov]

07/29:

• ACTION: @eschwein-usds is this still a rotation that we'd like to complete?

[eschwein-usds]

Yes! This work still needs to be done and I'm happy to mentor someone else through it!

[Firebird1029]

https://github.com/DSACMS/reverse-scorecard-generation

[decause-gov]

08/02:

• Working with @eschwein-usds and the script is ready!
• Nothing left to change, she's been added to repo for review/access/approval :)
• After her +1, we can close

[decause-gov]

08/05:

• Will reping her today.

[Firebird1029]

@decause-gov Elizabeth said the script works, so you may close the ticket