kdbq

Automate the process of data cleaning and saving tables into CSV

I just wanted to clean and add columns to the 2 csv files I had in the "filePaths" variable in exactly the same way as I've defined below (you can ignore the process), then save those data into new csv files.

The 2 problems here are mainly "how to group bunch of filepath into a single variable named "filepaths" and "how to use the "each" function inn KDB/Q? to loop through the process"

Thanks in advance!

// Define the list of file paths
filePaths: (`"$/Users/yuanhanlim/Desktop/KDBQ/Final_Project/MSFT_orderbook.csv",
        `"/Users/yuanhanlim/Desktop/KDBQ/Final_Project/AAPL_orderbook.csv";)

// Loop through each file path
{
    filePath;
    formatString: "IIIIIIIIIIIIIIIIIIII";
    // Read data from CSV
    data_QTE: (formatString; enlist ",") 0: filePath;

    // Calculate MidPrice
    data_QTE: update MidPrice: (Bid_Price1 + Ask_Price1) % 2 from data_QTE;

    // Compute total bid volume
    data_QTE: update Nbt: sum (Bid_Vol1; Bid_Vol2; Bid_Vol3; Bid_Vol4; Bid_Vol5) from data_QTE;

    // Compute total ask volume
    .
    . 
    // Remove the first row
    new_qte: delete from new_qte where i = 0;

    // Save cleaned data to CSV
    savePath: `$"/Users/yuanhanlim/Desktop/KDBQ/Final_Project/cleaned_" , last filePath;
    : savePath 0: csv 0: new_qte;
  } each filePaths;


First error I got: 
valuation error:



[0]  filePaths: (`"/Users/yuanhanlim/Desktop/KDBQ/Final_Project/MSFT_orderbook.csv",
          `"/Users/yuanhanlim/Desktop/KDBQ/Final_Project/AAPL_orderbook.csv";)
          ^
2nd error I got; 
evaluation error:

type

[2]  
   // Save cleaned data to CSV
   savePath: `$"/Users/yuanhanlim/Desktop/KDBQ/Final_Project/cleaned_" , last filePath;
             ^
   : savePath 0: csv 0: new_qte;

[1]  (.q.each)


[0]  
  : savePath 0: csv 0: new_qte;
} each filePaths;
  ^
Solution

  • The path to your file needs to be a file descriptor, https://code.kx.com/q/ref/file-text/#load-csv not a symbol. change it to either `$":path" or hsym `$"path" https://code.kx.com/q/ref/hsym/