Create a new CSV file with row and column numbers from each cell

Community Help
1

I have a csv file, without the header information. Each cell contains some values.
Goal: Save the cell values, as well as, the corresponding row and column numbers, in a separate csv file.

Demo data look like:

Screenshot 2023-04-07 20.46.26
Screenshot 2023-04-07 20.46.26756×251 18.9 KB

Expected outcome:

row     column            value

1            1             0.704191104

1            2             0.424829561

1            3             0.009920801

....

12           1             0.337513255

12           2             0.435984561

....

12           7              0.546245968

I tried like below, but didnot work.

RawData = d3.csvParse(
  await FileAttachment("datafile.csv").text(),
  d3.autoType
)
d3 = require('d3-dsv')

Any help will be highly appreciated.

2

Try csvParseRows instead of csvParse, it returns an array of arrays of column values. Then you can use flatMap to get the result:

d3.csvParseRows(text)
  .flatMap((row, i) => row.map((value, j) => ({ i, j, value })))

Capture d’écran 2023-04-08 à 09.47.42
Capture d’écran 2023-04-08 à 09.47.421130×734 60.4 KB

2 Likes
3

It worked. Thanks a lot!

4

@Fil Hi Fil,
Thank you so much for your help. It worked well. BTW I have some additional queries.
(a) I am using the following functions to save the array into CSV. But the problem is it is not showing the first row and column value. For example, Row:0, Col:0 value is missing. Could you please suggest what could be the reason?

csvText_final = {
  let csvText = "";

  data.forEach((row, ind) => {
    if (!ind) {
      return (csvText += `${[ "Row", "Col", "value"].join(",")}\r\n`);
    }

    const properValues = [row.Row, row.Col, row.value];

    return (csvText += `${properValues.join(",")}\r\n`);
  });

  //console.log(csvText);
  return csvText;
}

function saveAs(text, filename){
  var pom = document.createElement('a');
  pom.setAttribute('href', 'data:text/plain;charset=urf-8,'+encodeURIComponent(text));
  pom.setAttribute('download', filename);
  pom.click();
};

Please suggest if there are any other ways to save the array into CSV.

(b) I also want row and column count should be start at 1 not 0. For example, Row: 1, Col:1, instead of Row:0, Col:0.

(c) I want to include an additional column such as Row-Col. For example,

Row     Col   Row-Col      value
1       1       1-1         abc
1       2       1-2         cde
1       3       1-3         fgh

Any help will be highly appreciated.

5

This is really a completely different question. It would probably be better to ask a new one. That gives the new question greater visibility and doesn’t place the onus on someone who thought about this material a month or so ago.

Having said that, if you’re trying to create a download button for your end user, you should probably use d3.csvParse and pass that result to DOM.download. Assuming your data is a list of objects, that might look like so:

download_button =  {
  let text = d3.csvFormat(data);
  let file = new File([text], "file.csv");
  return DOM.download(file, "file.csv", "Download CSV file");
}

On the other hand, if you’re just trying to grab some data as a CSV out of your notebook, you could use the “Download CSV” option from the vertical three dot menu on the left hand side of the cell.

2 Likes
6

@mcmcclur Thanks so much for your response. My first question is now solved. For the next two questions, I’ll ask a new question. Thank you once again.

1 Like