Make Department Format Reports

[amhanson9]

Having a summary of everything in ARCHive, which the script currently does, gives us documentation of our big picture but is not enough nuance for departments to act on.

Make a department report with a summary of all of their formats, focusing on risk. Work with Emmeline on what is most useful for a department doing risk mediation. The focus is on a spreadsheet with data summaries, but also include a brief narrative report to provide an overview. The narrative is made by hand using data from the spreadsheet generated by the script.

[amhanson9]

Spreadsheet: Risk Data

This is the full format data used to generate the summaries. It is organized by AIP and has Group, Collection ID, AIP ID, Format Name, Format Version, PRONOM URL (combine registry name and key), NARA risk level from the current and previous analysis, NARA preservation plan from the current analysis, and the type of change between the previous and current analysis (increase, decrease, etc.).

Because the collection information is essential for born-digital risk analysis (the current use case), use the by_aip instead of by_group version of the ARCHive format data. That means there will not be size or file count information.

Group is in the spreadsheet, even though it is one spreadsheet per group, so that spreadsheets can easily be combined for more aggregate analysis when an individual is responsible for format risk in more than one group.

Type of change is included because recent changes indicate a potential urgency to review the format, such as a limited window the vendor may make a migration tool available. Risk order for comparison, high-low, is No Match, High Risk, Moderate Risk, Low Risk. Change types:

• Increase (does not include a match to No Match)
• Decrease (does not include No Match to a match)
• New Format: this format was not present in the previous analysis
• New Match: this format was No Match in previous and is high, moderate or low risk in current analysis
• Unmatched: this format was high, moderate or low risk in the previous analysis and is No Match in current
• Unchanged (including No Match each time)

Resources used: https://stackoverflow.com/questions/13148429/how-to-change-the-order-of-dataframe-columns https://stackoverflow.com/questions/37787698/how-to-sort-pandas-dataframe-from-one-column https://stackoverflow.com/questions/41476150/remove-or-replace-spaces-in-column-names https://www.statology.org/pandas-create-new-column-based-on-multiple-condition-if-else/

[amhanson9]

Spreadsheet: Risk Levels

For the entire department/group, for each collection, and for each AIP, have a summary with the percentage of formats at each NARA risk level (no match, high, moderate, low) to see where high risk is concentrated.

Remove duplicates (format name and version) across unit of measurement (department, collection, or AIP), because a format may be in more than one AIP and may be in a single AIP twice with and without a PUID. When removing duplicates, keep the one with the PUID since it is most likely to have matched NARA automatically and is only in legacy data due to identifying tool variation. Our current practice is to remove the match without PUID from the format identification.

Resources used: https://stackoverflow.com/questions/25896453/when-using-a-pandas-dataframe-how-do-i-add-column-if-does-not-exist https://stackoverflow.com/questions/29517072/add-column-to-dataframe-with-constant-value https://stackoverflow.com/questions/23667369/drop-all-duplicate-rows-across-multiple-columns-in-python-pandas https://www.statology.org/pandas-drop-duplicates-keep-latest/ https://stackoverflow.com/questions/45487410/how-to-calculate-counts-on-pandas-pivot-table https://stackoverflow.com/questions/40427943/how-do-i-change-a-single-index-value-in-pandas-dataframe https://stackoverflow.com/questions/47932937/drop-rows-by-index-from-dataframe

Tried to calculate the percentages as a pivot table (https://stackoverflow.com/questions/40301973/pandas-make-pivot-table-with-percentage) but could not get it to work, so converted the count rows to percentages in the script. It is only 4 rows, so wasn't worth further effort.

[amhanson9]

Spreadsheet: Formats

Show which formats (name and version, if any) are in each AIP. Rows are by collection and then by AIP, columns are by risk (high-low) and then by format. Values are True/False since a file count per AIP would always be 0, 1, or 2 (has PUID and no PUID).

Resources used: https://stackoverflow.com/questions/54334674/how-to-use-pandas-to-pivot-a-table-while-changing-values-to-boolean https://stackoverflow.com/questions/53934834/how-to-sort-the-values-in-pandas-pivot-table https://stackoverflow.com/questions/13838405/custom-sorting-in-pandas-dataframe

[amhanson9]

@emkaser When I remove duplicates from PUID matches for the AIP risk report, there is a chance the format with the PUID matched to NARA based on the PUID but the same format without the PUID did not match since the name was different. Should I use the risk level from the PUID match (probably accurate) or one without the PUID (probably higher risk)? I was thinking PUID match, since our cleanup currently would only have kept the one with the PUID.

[emkaser]

@amhanson9 Per our conversation: let's use the risk level from the PUID match. Thank you!