importing multiple csv files

[Surya Natarajan]

I have multiple csv files with data of different devices. The data format and date time format is same in all the files. Can I import them all together and create an asset tree or asset group?

[Mark Derbecker]

Surya, what are the column names in your CSV files? That will help me understand the nature of the data.

Do you anticipate that you'll be wanting to import these types of files repeatedly as time passes, adding on to the data in the various signals?

[Surya Natarajan]

Hi Mark,

The columns numbers and type of data are fixed. Sometimes need to repeat/add new data for the same assets and in future it might be expanded.

I have shared the sample column names and first row of the data 

Edited May 24 by Surya Natarajan

[Mark Derbecker]

This looks like it is not time series (signal) data, it looks perhaps like single-valued (scalar) data and that's what you want to import into your asset tree. To illustrate, I asked our (new) AI Assistant (available in R64 and later) the following:

Quote

Write a CSV file with the following columns: 
"Device number"
"Device name"
"Parameter 1"
"Parameter 1 upper limit"
"Parameter 1 lower limit"
and a single row with the following values:
"ABC 100"
"Tank 1"
7.2
8
6

which produced this code:

import pandas as pd

# Define the data as a dictionary
data = {
    "Device number": ["ABC 100"],
    "Device name": ["Tank 1"],
    "Parameter 1": [7.2],
    "Parameter 1 upper limit": [8],
    "Parameter 1 lower limit": [6]
}

# Create a DataFrame from the dictionary
df = pd.DataFrame(data)

# Write the DataFrame to a CSV file
df.to_csv('device_parameters.csv', index=False)

I then asked the following:

Quote

I want to create a Seeq metadata DataFrame where the Path is Device number and Device name and the value is the Formula

which produced the following code:

from seeq import spy
import pandas as pd

# Read the CSV file into a DataFrame
df = pd.read_csv('device_parameters.csv')

# Melt the DataFrame to transform the specified columns into rows
melted_df = df.melt(id_vars=["Device number", "Device name"], 
                    value_vars=["Parameter 1", "Parameter 1 upper limit", "Parameter 1 lower limit"],
                    var_name="Name", 
                    value_name="Value")

# Create the Path column by combining "Device number" and "Device name"
melted_df['Path'] = melted_df['Device number'] + ' >> ' + melted_df['Device name']

# Create the metadata DataFrame
metadata_df = pd.DataFrame({
    'Path': melted_df['Path'],
    'Name': melted_df['Name'],
    'Formula': melted_df['Value']
})

# Display the metadata DataFrame to ensure it has been created correctly
print(metadata_df)

# Push the metadata DataFrame to Seeq
push_results = spy.push(metadata=metadata_df, workbook='Forum Post 2379')

# Display the push results
push_results

This code looked correct to me, and the results look right too. There is an Asset Tree with Device Number and Device Name as levels, and there are three scalars:

 

[Surya Natarajan]

Hi Mark,

Sorry for providing an incomplete data.

This is the correct sample data.

I have numerous files for different assets with same parameters and I am currently uploading them individually and making them into an asset group. The analysis needed is similar in all so I just scale it up for all the assets. But I am struggling with uploading all the files individually.

 

[Mark Derbecker]

OK, the Event time column is important 🙂

Here's code that should make it easier:

import pandas as pd

# Read the CSV file into a DataFrame
df = pd.read_csv('device_parameters.csv', parse_dates=['Event time'])

# Obtain all unique device_numbers and device_names
device_numbers = df['Device number'].drop_duplicates()
device_names = df['Device name'].drop_duplicates()

# Iterate over all unique combinations
for device_number in device_numbers:
    for device_name in device_names:
        # Select data for this unique combo
        data = df[(df['Device number'] == device_number) & (df['Device name'] == device_name)]
        if len(data) == 0:
            # Don't push unless there's actual data
            continue

        # Reduce to just the time series data and make the index the timestamp
        data = data.drop(columns=['Device number', 'Device name']).set_index('Event time')

        # Create a metadata DataFrame that will arrange the pushed data into a tree
        metadata = pd.DataFrame({
            'Name': data.columns,
            'Type': 'Signal',
            'Path': f'{device_number} >> {device_name}'
        }, index=[data.columns])

        spy.push(metadata=metadata, data=data)

 

Edited May 24 by Mark Derbecker