CSV data manipulation: Difference between revisions

=={{header|11l}}==

I L.index == 0

line ‘’= ‘,SUM’

E

line ‘’= ‘,’sum(line.split(‘,’).map(Int))

{{out}}

Ada has no build-in or predefined functions to read or write CSV tables. We thus define a (very simplistic) package CSV, which allows to read a row (function Line), to step from column to column (function Next), and to get the items in the column (function Item):

type Row(<>) is tagged private;

Sep: Character;

end record;

The implementation of the package is

function Line(S: String; Separator: Character := ',')

end Next;

Finally, the main program which uses the package CSV:

procedure CSV_Data_Manipulation is

end;

end loop;

{{out}}

=={{header|Aime}}==

read_csv(list t, text path)

{

return 0;

{{out}}

<pre>C1,C2,C3,C4,C5,SUM

=={{header|ALGOL 68}}==

PROC char count = (CHAR c, STRING str) INT:

BEGIN

print ((join (fields, ","), new line))

OD;

{{out}}

<pre>

=={{header|Arturo}}==

;

; C1,C2,C3,C4,C5

prints pad column 6

print ""

=={{header|AutoHotkey}}==

{

i := A_Index

Output .= (i=A_Index && i!=1 ? A_LoopField**2 : A_LoopField) (A_Index=5 ? "`n" : ",")

}

'''Output:'''

<pre>C1,C2,C3,C4,C5

=={{header|AWK}}==

adds a column sum to a csv table

BEGIN { FS = OFS = "," }

NR==1 {

}

print $0, sum

<pre>awk -f csv_data_manipulation.awk data.csv

C1,C2,C3,C4,C5,SUM

=={{header|BaCon}}==

Load the data, change a value and add a column with totals. Then save and print.

OPTION DELIM ","

SAVE total$ TO "data.csv"

PRINT total$

<pre>

C1,C2,C3,C4,C5,SUM

{{works with|QuickBasic|4.5}}

{{trans|FreeBASIC}}

OPEN "manip2.csv" FOR OUTPUT AS #2

CLOSE #1, #2

{{out}}

<pre>

=={{header|BQN}}==

csvstr ← ⟨"C1,C2,C3,C4,C5", "1,5,9,13,17", "2,6,10,14,18", "3,7,11,15,19", "4,8,12,16,20"⟩

sums ← ⟨"SUMS"⟩ ∾+´˘ 1↓ intdata

done ← sums ∾˜˘ intdata

=={{header|C}}==

#define TITLE "CSV data manipulation"

return 0;

}

{{out|Output (in <tt>tmp/csv-data-manipulation.result.csv</tt>)}}

<pre>

=={{header|C sharp|C#}}==

using System.Linq;

}

}

{{out|Output (in <tt>test_out.csv</tt>)}}

<pre>

=={{header|C++}}==

#include <vector>

#include <iostream>

oCSV.save( "test_out.csv" );

return 0;

{{out|Output (in <tt>test_out.csv</tt>)}}

<pre>

=={{header|Clojure}}==

(require '[clojure.data.csv :as csv]

'[clojure.java.io :as io])

(with-open [out-file (io/writer "test_out.csv")]

(csv/write-csv out-file out-data)))))

{{out|Output (in <code>test_out.csv</code>)}}

Using tech.ml.dataset

(require '[tech.v3.dataset :as ds]

'[tech.v3.datatype.functional :as dfn])

(ds/write! (add-sum (ds/->dataset "resources/input.csv")) "resources/output.csv")

=={{header|COBOL}}==

IDENTIFICATION DIVISION.

PROGRAM-ID. CSV.

END-PROGRAM.

=={{header|Common Lisp}}==

Used only built-in functions which are in the standard. There are widespread libraries for working with csv (which can be easily loaded via quicklisp). As another example, I didn't use a split-string function, even though it is available in some implementations and in many compatibility layers and libraries. Instead, I formatted the csv file into s-expressions for the reader to understand it. Also, it deserves a mention that Common Lisp has built-in arrays, but for so little data it is easier to use nested lists.

(defun csvfile-to-nested-list (filename delim-char)

"Reads the csv to a nested list, where each sublist represents a line."

result-header (nested-list-to-csv data-list ",")))))

(main)

{{out|Output (in <code>results.txt</code>)}}

=={{header|D}}==

import std.stdio, std.csv, std.file, std.typecons, std.array,

std.algorithm, std.conv, std.range;

fout.writef("%(%(%d,%)\n%)", rows.dropOne

.map!(r => r.csvReader!int.front.map!(x => x + 1)));

{{out|Output (in <code>csv_data_out.csv</code>)}}

<pre>C1,C2,C3,C4,C5

{{libheader| System.Types}}

{{Trans|C#}}

program CSV_data_manipulation;

end;

TFile.WriteAllLines(FILENAME, Input);

{{out}}

<pre>C1,C2,C3,C4,C5,SUM

4,8,12,16,20,60</pre>

=={{header|EchoLisp}}==

;; CSV -> LISTS

(define (csv->row line) (map (lambda(x) (or (string->number x) x)) (string-split line ",")))

{{out}}

(define file.csv #<<

C1,C2,C3,C4,C5

3,7,11,15,19,55

4,8,12,16,20,60"

=={{header|ECL}}==

MyFileLayout := RECORD

STRING Field1;

MyNewDataset := PROJECT(MyDataset,Appended(LEFT));

{{Out}} (contents of Rosetta::myNewCSVFile):

<pre>C1x,C2y,C3z,C4a,C5b

=={{header|Elixir}}==

defmodule Csv do

defstruct header: "", data: "", separator: ","

|> Csv.append_column("SUM", Csv.sums_of_rows(csv))

|> Csv.to_file("out.csv")

{{Out}}

=={{header|Erlang}}==

-module( csv_data ).

split( 1, List ) -> {[], List};

split( N, List ) -> lists:split( N - 1, List ).

{{out}}

=={{header|Euphoria}}==

--- with trace

-- trace(0)

main()

{{out}}

=={{header|F_Sharp|F#}}==

[<EntryPoint>]

File.WriteAllLines ("test_out.csv", output)

0

{{out|Output (in <tt>test_out.csv</tt>)}}

<pre>

=={{header|Factor}}==

The <code>csv</code> vocabulary provides words for working with csv files, strings, and streams.

IN: rosetta-code.csv-manipulation

[ 0 = [ "SUM" suffix ] [ append-sum ] if ] map-index ;

{{out}}

Contents of <code>example.csv</code>

=={{header|Forth}}==

\ USAGE:

\ gforth-fast csvsum.fs -e "stdout stdin csvsum bye" <input.csv >output.csv

THEN

2DROP 2DROP

{{out}}

<pre>

It's fairly easy to read arbitrary lines using allocatable character strings, available since Fortran 2003.

implicit none

character(:), allocatable :: line, name, a(:)

array(p) = line(k:n)

end subroutine

=== Old Fortran ===

Another F90 feature is the SUM function that adds the elements of an array span. Even though for the example the first column looks rather like a record number, all five columns will be added, but otherwise the statement would be SUM(X(2:N)). Other modifications can be made without much difficulty, if desired. The output format is I0 rather than say I2, as it provides only the needed number of characters to present the integer's value. There is no corresponding F format code, and free-format output would roll out many spaces as padding in case of large numbers, that are not present here. It would be needed for a more general solution, but for this example, I0 will do.

Copies a file with 5 comma-separated values to a line, appending a column holding their sum.

INTEGER N !Instead of littering the source with "5"

10 CLOSE (IN) !All done.

END !That's all.

Output could of course be written to a disc file instead of a screen, but here it is:

<pre>

=={{header|FreeBASIC}}==

Open "manip.csv" For Input As #1 ' existing CSV file

Close #1

{{out}}

=={{header|FunL}}==

data Table( header, rows )

t = addColumn( read('test.csv'), 'SUM', r -> r('SUM') = sum(int(v) | (_, v) <- r if v != null) )

write( t, 'test_out.csv' )

{{out}}

=={{header|Gambas}}==

Dim sData As String = File.Load("data.csv")

Dim sLine, sTemp As String

File.Save(User.home &/ "CSVData.csv", sData)

Output:

<pre>

=={{header|Go}}==

import (

log.Fatal(err)

}

{{in|sample.csv}}

<pre>

=={{header|Groovy}}==

def loadCsv = { source -> source.splitEachLine(/,/) { csv << it.collect { it } } }

def saveCsv = { target -> target.withWriter { writer -> csv.each { writer.println it.join(',') } } }

csv[0][0] = 'Column0'

(1..4).each { i -> csv[i][i] = i * 100 }

csv_out.txt:

Array-based solution:

import Data.List (intercalate)

import Control.Monad (when)

main = do

a <- fieldsFromFile "example.txt"

'''Solution 2'''

List-based solution, heavily using functors and lenses

TypeFamilies,

NoMonomorphismRestriction #-}

\2, 6, 10, 14, 18\n\

\3, 7, 11, 15, 19\n\

Examples:

3. Construction and combination

<pre>λ> sampleSum

This version only works in Unicon, but can be easily adapted to work in Icon.

procedure main(A)

write(encodeCSV(csv))

}

Sample run:

Like other languages it is not necessary to use the csv utilities to accomplish this task.

data=: (<'"spam"') (<2 3)} data NB. amend cell in 3rd row, 4th column (0-indexing)

Using the [[j:Addons/tables/dsv|delimiter-separated-values utilities]] (of which <code>tables/csv</code> is a special case) will handle more complex csv constructs:

data=: makenum readcsv 'rc_csv.csv' NB. read data and convert cells to numeric where possible

data=: (<'spam') (2 3;3 0)} data NB. amend 2 cells

Adding a column with the sum of the rows:

'hdr data'=: split readcsv 'rc_csv.csv' NB. read data, split the header & data

hdr=: hdr , <'SUM' NB. add title for extra column to header

data=: <"0 (,. +/"1) makenum data NB. convert to numeric, sum rows & append column

Tacit version of above:

=={{header|Java}}==

===Roll Your Own===

import java.awt.Point;

import java.util.HashMap;

}

}

{{out|Output (in <tt>test_out.csv</tt>)}}

<pre>

{{libheader|Apache commons-csv}}

Using the [http://commons.apache.org/proper/commons-csv/ Apache commons-csv] library.

import java.util.*;

}

}

{{in}} data/csvtest_in.csv

<pre>

===uniVocity-parsers===

Using the [http://www.univocity.com/pages/parsers-tutorial uniVocity-parsers] library.

public static void main(String[] args) throws IOException {

writer.writeRows(new ArrayList<List<Object>>());

}

=={{header|JavaScript}}==

As an embedded scripting language which evolved in browsers carefully isolated from local file systems, JavaScript has no standard file IO libraries. The readFile() and writeFile() functions used in this example are written for JS embedded in macOS as 'JavaScript for Automation'. Other embeddings will require other definitions of these functions, and in some JS contexts it will not be possible to write them at all.

'use strict';

);

{{Out}}

Below is a toy example to add new columns to an CSV file. Other manipulations, i.e. adding new rows, modifying existing values and so forth, can be accomplished very easily.

// formats for the data parameter in the function below: {col1: array | function, col2: array | function}

return idx;

}

{{output}}

The writing out of each line is facilitated by jq's @csv builtin. We must "slurp in" the file (using the -s option) so the header line can be handled specially.

def read_csv:

split("\n")

([]; ($line|map(tonumber)) as $line | . + [$line + [$line|add]]))[] ] ;

{{Out}}

$ jq -s -R -r -f CSV_data_manipulation.jq input.csv

=={{header|Julia}}==

ifn = "csv_data_manipulation_in.dat"

df.SUM = sum.(eachrow(df))

CSV.write(ofn, df)

<pre>

$ cat csv_data_manipulation_out.dat

=={{header|Kotlin}}==

import java.io.File

File("example2.csv").writeText(text) // write to new file

println(text) // print to console

{{out}}

=={{header|Lingo}}==

-- Simplified CSV parser (without escape character support etc.).

-- First line is interrepted as header with column names.

delete char (str.length-delim.length+1) to str.length of str

return str

eol = numtochar(10)

3,7,11,15,19,55

4,8,12,16,20,60

=={{header|Lua}}==

Adds a SUM column.

for line in io.lines('file.csv') do

table.insert(csv, {})

local file=io.open('file.csv', 'w')

file:write(newFileData)

{{out}}

=={{header|M2000 Interpreter}}==

Module Checkit {

Function Sum {

}

Checkit

=={{header|Maple}}==

Entire script:

M(..,6) := < "Total", seq( add(M[i,j], j=1..5), i=2..5 ) >;

ExportMatrix("data_out.csv",M,target=csv);

Running this script showing interactive results:

["C1" "C2" "C3" "C4" "C5"]

[ ]

> ExportMatrix("data_out.csv",M,target=csv);

96

=={{header|Mathematica}} / {{header|Wolfram Language}}==

Mathematica's Import and Export functions support CSV files.

->{{"C1","C2","C3","C4","C5"},{1,5,9,13,17},{2,6,10,14,18},{3,7,11,15,19},{4,8,12,16,20}}

iCSV = Transpose@

Append[Transpose[iCSV], Join[{"Sum"}, Total /@ Drop[iCSV, 1]]];

iCSV // MatrixForm

{{out}}

<pre>(C1 C2 C3 C4 C5 Sum

=={{header|MATLAB}} / {{header|Octave}}==

=== Using file manipulation ===

fid = fopen(filename);

header = fgetl(fid);

fprintf(fid,"%i\n",sum(X(k,:)));

end;

=== Using <code>table</code> ===

data = readtable(filename);

data.SUM = sum([data{:,:}],2);

=={{header|Nanoquery}}==

Nanoquery was created to parse and manipulate data files, with CSV being the first targeted format. As a result, it has a number of operators for retrieving data from a CSV file, including the record operator '#', the column operator '@', and the lookup operator '~'

sum = 0

end for

=={{header|NetRexx}}==

{{libheader|Apache commons-csv}}

Using the [http://commons.apache.org/proper/commons-csv/ Apache commons-csv] library.

options replace format comments java crossref symbols

end

return lineOut

{{in}} data/csvtest_in.csv

<pre>

Nim's standard library contains a robust CSV parser, but for this simple document that's not necessary.

let

outf.writeLine(line)

{{out}}

Objeck has a CSV parser with built-in functions.

use Data.CSV;

}

}

{{out}}

Using the '''csv''' module available in '''Opam''':

List.rev (this :: (List.rev lst))

) data

in

{{out}}

{{Works with|PARI/GP|2.7.4 and above}}

\\ CSV data manipulation

\\ 10/24/16 aev

\\ Testing:

processCsv("c:\\pariData\\test");

{{in}} data/test.csv file

{{works with|Free Pascal}}

In Pascal you can use TStringList CommaText property to work with CSV.

program CSV_Data_Manipulation;

uses Classes, SysUtils;

ts.Free;

end.

{{in}} input.csv file

=={{header|Perl}}==

For simple files, you can use [http://p3rl.org/split split]:

use warnings;

use strict;

print join(',' => @header), "\n";

print join(',' => @$_), "\n" for @rows;

However, if the CSV can contain quoted texts (the type MS Excel produces), you should rather use the [http://p3rl.org/Text::CSV Text::CSV]. Only reading the data and printing the result is different:

use warnings;

use strict;

# Print the output.

=={{header|Phix}}==

Note that error checking is omitted, in particular for scanf, and obviously we use an inline constant for pwa/p2js, but normal file i/o for desktop/Phix.

<span style="color: #008080;">with</span> <span style="color: #008080;">javascript_semantics</span>

<span style="color: #008080;">constant</span> <span style="color: #000000;">tcsv</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">"""

<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>

<span style="color: #7060A8;">puts</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #000000;">lines</span><span style="color: #0000FF;">)</span>

{{out}}

<pre>

=={{header|PHP}}==

<?php

$row++;

}

=={{header|PicoLisp}}==

(prinl (line) "," "SUM")

(while (split (line) ",")

{{Out}}

<pre>C1,C2,C3,C4,C5,SUM

=={{header|PL/I}}==

csv: Proc Options(Main);

/*********************************************************************

End;

{{in}}

<pre>

=={{header|PowerShell}}==

@"

C1,C2,C3,C4,C5

## Display the object in tabular form

$records | Format-Table -AutoSize

{{out}}

<pre>

The following uses SWI-Prolog's csv_read_file_row/3 in order to

demonstrate that it is not necessary to read more than a line at a time.

% augment( +InFileName, +OutFileName)

append(List, [Sum], NewList),

NewTerm =.. [F | NewList].

=={{header|PureBasic}}==

EnableExplicit

CloseConsole()

EndIf

{{out|Output (in output.csv)}}

=== Using <code>fileinput</code> ===

Note that the [http://docs.python.org/3.3/library/csv.html csv module] is not required for such a simple and regular CSV file. Here overwriting is done in place.

changerow, changecolumn, changevalue = 2, 4, '"Spam"'

fields[changecolumn-1] = changevalue

line = ','.join(fields) + '\n'

{{out}}

After this the data file <code>csv_data_manipulation.csv</code> gets changed from that of the task to:

=== Using <code>csv</code>, <code>pathlib</code> and <code>tempfile</code> ===

In this example overwriting is performed ''not'' in place but by using [https://docs.python.org/library/tempfile.html <code>tempfile</code> library] for creating a temporary file and [https://docs.python.org/library/pathlib.html <code>pathlib</code> library] for overwriting the initial file. [http://docs.python.org/library/csv.html <code>csv</code> module] is used to allow easier manipulation with delimiters.

from pathlib import Path

from tempfile import NamedTemporaryFile

temp_file_path = Path(temp_file.name)

temp_file_path.replace(filepath)

{{Out}}

<pre>C1,C2,C3,C4,C5,SUM

=== Using <code>pandas</code> ===

filepath = 'data.csv'

rows_sums = df.sum(axis=1)

df['SUM'] = rows_sums

=={{header|Q}}==

t:update SUM:sum value flip t from t / Add SUM column to t

=={{header|R}}==

df <- read.csv(textConnection(

"C1,C2,C3,C4,C5

df$sum <- rowSums(df)

write.csv(df,row.names = FALSE)

{{out}}

This output can also be saved to a file:

=={{header|Racket}}==

(require (planet neil/csv:1:=7) net/url)

(append row (list (~a (apply + xs))))))

(define (->string row) (string-join row "," #:after-last "\n"))

Example:

"C1, C2, C3, C4, C5

1, 5, 9, 13, 17

4, 8, 12, 16, 20")

{{out}}

<pre>

(formerly Perl 6)

On the face of it this task is pretty simple. Especially given the sample CSV file and the total lack of specification of ''what'' changes to make to the file. Something like this would suffice.

my $fh = open($csvfile, :r);

my @header = $fh.get.split(',');

$out.say((@header,'SUM').join(','));

$out.say((@$_, [+] @$_).join(',')) for @csv;

But if your CSV file is at all complex you are better off using a CSV parsing module. (Complex meaning fields that contain commas, quotes, newlines, etc.)

my $csvfile = './whatever.csv';

my @csv = Text::CSV.parse-file($csvfile);

# modify(@csv); # do whatever;

=={{header|Red}}==

>>data: copy []

>>foreach item filein [append/only data split item ","]

append data/1 "SUM"

][

append data/1 to string!

(to integer! data/1/1) + (to integer! data/1/2) + (to integer! data/1/3) + (to integer! data/1/4) + (to integer! data/1/5)

>> print data

C1, C2, C3, C4, C5, SUM

3, 7, 11, 15, 19, 55

4, 8, 12, 16, 20, 60

=={{header|REXX}}==

===version 1===

* extend in.csv to add a column containing the sum of the lines' elems

* 21.06.2013 Walter Pachl

Do i=1 To i-1

Call lineout csv,l.i

{{out}}

<pre>C1,C2,C3,C4,C5,SUM

Also supported is the ability to specify the fileID of the data file to be specified.

parse arg iFID . /*obtain optional argument from the CL*/

if iFID=='' | iFID=="," then iFID= 'CSV_SUM.DAT' /*Not specified? Then use the default*/

do k=2 for rec-2 /*process all the records just read. */

call lineout iFID,@.k /*write the new CSV record (has SUM). */

{{out|output|text=&nbsp; to the console:}}

<pre>

=={{header|Ring}}==

# Project : CSV data manipulation

fclose(fpout)

see csvend + nl

Output:

<pre>

=={{header|Ruby}}==

# read:

ar = CSV.table("test.csv").to_a #table method assumes headers and converts numbers if possible.

CSV.open("out.csv", 'w') do |csv|

ar.each{|line| csv << line}

{{output}}

<pre>c1,c2,c3,c4,c5,SUM

=={{header|Run BASIC}}==

1,5,9,13,17

2,6,10,14,18

cma$ = ""

print

<pre>C1,C2,C3,C4,C5

1,5,9,13,17

=={{header|Rust}}==

{{libheader|BurntSushi's csv crate}}

use std::num::ParseIntError;

use csv::{Reader, Writer};

writer.flush()?;

Ok(())

{{output}}

<pre>C1,C2,C3,C4,C5,SUM

=={{header|SAS}}==

infile datalines dlm="," firstobs=2;

file "output.csv" dlm=",";

4,8,12,16,20

;

=={{header|Scala}}==

{{libheader|Scala}}

object parseCSV extends App {

*/

=={{header|Seed7}}==

is in the same directory as the program.

const proc: main is func

writeln(join(csvData[line], ","));

end for;

{{out}}

=={{header|SenseTalk}}==

// For test purposes, start by creating (or re-creating) the data file

put {{

put file "myData.csv" -- display the updated file contents

=={{header|Sidef}}==

{{trans|Raku}}

For simple files we can use the ''split'' method.

var csvfile = %f'data.csv';

var fh = csvfile.open_r;

out.say([header..., 'SUM'].join(','));

csv.each { |row| out.say([row..., row.sum].join(',')) };

For complex files, the ''Text::CSV'' library is recommended.

Hash(eol => "\n")

);

[header, rows...].each { |row|

csv.print(out, row);

=={{header|Stata}}==

replace c5=c3+c4

egen sum=rowtotal(c*)

drop if mod(c3,3)==0

=={{header|Tcl}}==

{{tcllib|struct::matrix}}

{{tcllib|csv}}

package require csv

}

{{out|Output (in <tt>example.csv</tt>)}}

<pre>

Although, for this specific small task,

puts "[gets $f],SUM"

while { [gets $f row] > 0 } {

puts "$row,[expr [string map {, +} $row]]"

}

suffices.

=={{header|TUSCRIPT}}==

$$ MODE DATA

$$ csv=*

csv=APPEND(csv,line)

ENDLOOP

{{Out}}

<pre>

=={{header|TXR}}==

@(collect :vars (value sum))

@ (bind sum 0)

@ (end)

@(end)