import pandas as pd
# Sample DataFrame with mixed data
data = {
'Instrument1': "{'tag1': {'units': 'unit1', 'description': 'desc1'}}",
'Instrument2': "{'tag2': {'units': 'unit2', 'description': 'desc2'}}",
'NonInstrument1': 'Some non-instrument data',
'Instrument3': None, # Missing data in this column
'NonInstrument2': 'More non-instrument data',
}
# Create the DataFrame from the sample data
df = pd.DataFrame(data, index=[0])
# Initialize empty lists to store the extracted values
instrument_names = []
tags = []
units = []
descriptions = []
# Define a placeholder for missing values
placeholder = 'N/A'
# Iterate over each column (instrument) in the DataFrame
for column_name in df.columns:
# Check if the column contains valid JSON data
data_str = df[column_name].iloc[0]
if isinstance(data_str, str) and data_str.startswith('{') and data_str.endswith('}'):
try:
data_dict = eval(data_str) # Using eval to convert the string to a dictionary
# Find the first key in the dictionary (assuming it's the 'tag')
tag_key = list(data_dict.keys())[0]
tag_data = data_dict.get(tag_key, {})
units_value = tag_data.get('units', placeholder)
description_value = tag_data.get('description', placeholder)
except (SyntaxError, TypeError, IndexError):
# Handle cases where JSON data is not valid or data is missing
tag_key = placeholder
units_value = placeholder
description_value = placeholder
else:
# Handle non-dictionary data in the column
tag_key = placeholder
units_value = placeholder
description_value = placeholder
# Append the extracted values to the respective lists
if tag_key != placeholder:
# Only add columns that contain valid JSON data
instrument_names.append(column_name)
tags.append(tag_key)
units.append(units_value)
descriptions.append(description_value)
# Create a new DataFrame from the extracted values
result_df = pd.DataFrame({
'Instrument Name': instrument_names,
'Tag': tags,
'Units': units,
'Description': descriptions
})
# Display the resulting DataFrame
print(result_df)