0
$\begingroup$

I am collecting metrics on 6 REST services from a microservices architecture. For each instant collected, I extract two CSV from each service. One CSV contains three latency metrics (99th quantile, 50th quantile, average). And another CSV has the number of responses per second that the service returned with the HTTP codes 200 and 400.

Examples of each of the CSVs:

Latency:

"Time", "99th quantile", "50th quantile", "Mean", "IsError"
2023-02-23 15:20:30,2.45,0.577,0.602,True
2023-02-23 15:21:00,0.939,0.424,0.457,True
2023-02-23 15:21:30,0.740,0.417,0.456,True
2023-02-23 15:22:00,0.965,0.396,0.443,True
2023-02-23 15:22:30,2.34,0.438,0.547,True

QPS:

"Time","2xx","4xx/5xx","IsError"
2023-02-22 21:18:30,216,0,False
2023-02-22 21:19:00,280,0,False
2023-02-22 21:19:30,242,0,False
2023-02-22 21:20:00,311,0,False

In addition to the metrics, it has a column with the Time and a column with the label whether it is a boolean value.

The CSV file names always start with the service name and have the word "latency" in the case of latency metrics and "QPS" in the case of wanted per second.

Example:

Cart latency-data-as-seriestocolumns-2023-02-23 15_53_38.csv
Cart QPS-data-2023-02-23 15_53_26.csv
Catalogue latency-data-as-seriestocolumns-2023-02-23 15_53_20.csv
Catalogue QPS-data-2023-02-23 15_53_13.csv
Frontend latency-data-as-seriestocolumns-2023-02-23 15_54_54.csv
Frontend QPS-data-as-seriestocolumns-2023-02-23 15_54_48.csv
Orders latency-data-as-seriestocolumns-2023-02-23 15_53_54.csv
Orders QPS-data-2023-02-23 15_53_47.csv
Payment latency-data-as-seriestocolumns-2023-02-23 15_54_10.csv
Payment QPS-data-2023-02-23 15_54_00.csv
Shipping latency-data-as-seriestocolumns-2023-02-23 15_54_24.csv
Shipping QPS-data-2023-02-23 15_54_17.csv
User latency-data-as-seriestocolumns-2023-02-23 15_54_40.csv
User QPS-data-as-seriestocolumns-2023-02-23 15_54_32.csv

I wanted to make a dataset where it reads all the CSV from a paste and creates the whole dataset for training and validation.

In the end, I would have a dataset with the following format:

"Time","99th quantile","50th quantile","Mean","2xx","4xx/5xx","IsError","Service"
2023-02-06 16:13:00,0.0970,0.00402,0.00771,254,0,True,Orders
2023-02-06 16:13:30,0.0700,0.00377,0.00614,267,0,True,Orders
2023-02-06 16:14:00,0.0208,0.00328,0.00388,251,0,True,Orders
2023-02-06 16:14:30,0.0971,0.00349,0.00655,273,0,True,Orders
2023-02-06 16:15:00,0.0232,0.00323,0.00443,276,0,True,Orders
2023-02-06 16:15:30,0.00995,0.00309,0.00380,69,0,True,Orders
2023-02-06 16:16:00,0.00957,0.00283,0.00316,171,0,True,Orders

Is it possible to put all this information together in a single DataFrame? Knowing that a collected moment is represented by two CSVs and that in the same folder, I will have several collection time periods.

$\endgroup$
2
  • 1
    $\begingroup$ Sounds fun! Yes, after loading all the files into their own dataframes (however you think is best), it sounds like you can merge on the Time column in all dataframes in pandas using merge, join, or concat (again, depending on what you think is best). $\endgroup$
    – m13op22
    Commented Mar 3, 2023 at 17:45
  • $\begingroup$ How do you plan to join the tables? The times don't seem to match between the Latency and the QPS? And they contain different number of rows. And if "IsError" is true for Latency and false for QPS (or other combinations), should IsError" be true or false in the combined file? $\endgroup$
    – Filip
    Commented May 2, 2023 at 19:48

1 Answer 1

0
$\begingroup$

If I understand your question correctly, this should give you what you are looking for:

from glob import glob

dfs = {'latency': [], 'qts': []}

for file_path in glob('*.csv'):
    service, data_type = file_path.split('_')[0:2]
    df = pd.read_csv(file_path)
    df['Service'] = service
    df = df.rename(columns={'IsError': f'IsError_{data_type}'})
    dfs[data_type].append(df)

# combine all dataframes for latency and qts
latency, qts = pd.concat(dfs['latency']), pd.concat(dfs['qts'])

# join latency and qts on Service and Timestamp
dataset = qts.join(latency, on=['Service', 'Timestamp'], how='inner').reset_index()

# this assumes that it is an error if either latency or qts is an error
dataset['IsError'] = dataset['IsError_latency'] | dataset['IsError_qts']
dataset = dataset.drop(columns=['IsError_latency', 'IsError_qts'])
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.