How can I parse specific text from a hierarchical list either into columns or separate report

Ellison Keller · 01/10/25

global-ami-details_yaml_1-7-2025.txt

I need to pull the info from this file and be able to report, query or organize data based on regions (e.g., us-east-1), vnf-vendor (e.g., versa), vnf-model (e.g., analytics), version, and/or ami-name variables. For example, how do I pull a list of all the ami-ids per region for the image named "release-sbc-V11.01.01R000-connexip-os_11.01.01-R000_304_amd64_07_14_23_10_43"?

jmyzk_cloudsmart_jp · 01/10/25

Hi @Ellison Keller

First, I flattened the Amazon Machine Images (AMIs) date to import to Smartsheet.

I used the following Python code; your text file looks like Yaml format.

import yaml

import pandas as pd


# Load the hierarchical data from the file

file_path = '/content/global-ami-details_yaml_1-7-2025.txt'  # Replace with your file path

with open(file_path, 'r') as file:

    hierarchical_data = yaml.safe_load(file)


# Flatten the data into a list of dictionaries

flat_data = []

for region, vnf_data in hierarchical_data['regions'].items():

    for vnf in vnf_data:

        vnf_vendor = vnf['vnf-vendor']

        vnf_model = vnf['vnf-model']

        for ami in vnf['amis']:

            flat_data.append({

                "Region": region,

                "VNF Vendor": vnf_vendor,

                "VNF Model": vnf_model,

                "Version": ami['version'],

                "AMI ID": ami['ami-id'],

                "AMI Name": ami['ami-name'],

                "Is Public": ami['is-public'],

                "Deprecated": ami['deprecated'],

            })


# Convert the flattened data into a DataFrame

df_flat = pd.DataFrame(flat_data)


# Save the flattened data to a CSV file

output_csv_path = 'Flattened_AMI_Details.csv'  # Change to desired output path

df_flat.to_csv(output_csv_path, index=False)


print(f"Flattened data saved to {output_csv_path}")

https://gist.github.com/jmyzk/72c3a15ffa4347b964d236b43f7f5021

Then, I imported the CSV file.

https://app.smartsheet.com/b/publish?EQBCT=fd65d9a66aa64fb2ba3a7e429449abe9

After that, you can use cross-sheet formulas or reports to analyze the data.

Example

https://app.smartsheet.com/b/publish?EQBCT=21b8ee2890dd404b91767534c5e3c0f1

[VNF Model Count] =COUNTIFS({Flattened_AMI_Details : VNF Model}, [VNF Model]#, {Flattened_AMI_Details : Region}, Region@row)

https://app.smartsheet.com/b/publish?EQBCT=506c64b804c54a2b9610a9bb600bfc6e

jmyzk_cloudsmart_jp · 01/10/25

Hi @Ellison Keller

First, I flattened the Amazon Machine Images (AMIs) date to import to Smartsheet.

I used the following Python code; your text file looks like Yaml format.

import yaml

import pandas as pd


# Load the hierarchical data from the file

file_path = '/content/global-ami-details_yaml_1-7-2025.txt'  # Replace with your file path

with open(file_path, 'r') as file:

    hierarchical_data = yaml.safe_load(file)


# Flatten the data into a list of dictionaries

flat_data = []

for region, vnf_data in hierarchical_data['regions'].items():

    for vnf in vnf_data:

        vnf_vendor = vnf['vnf-vendor']

        vnf_model = vnf['vnf-model']

        for ami in vnf['amis']:

            flat_data.append({

                "Region": region,

                "VNF Vendor": vnf_vendor,

                "VNF Model": vnf_model,

                "Version": ami['version'],

                "AMI ID": ami['ami-id'],

                "AMI Name": ami['ami-name'],

                "Is Public": ami['is-public'],

                "Deprecated": ami['deprecated'],

            })


# Convert the flattened data into a DataFrame

df_flat = pd.DataFrame(flat_data)


# Save the flattened data to a CSV file

output_csv_path = 'Flattened_AMI_Details.csv'  # Change to desired output path

df_flat.to_csv(output_csv_path, index=False)


print(f"Flattened data saved to {output_csv_path}")