R - Reading a horizontally arranged csv file, replacing values and saving again

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

R - Reading a horizontally arranged csv file, replacing values and saving again

ranjanmano167
I have a horizontally arranged CSV file
<https://www.dropbox.com/s/0ukyuifvpq1olqk/samplefile.csv?dl=0> (
samplefile.csv) with headers are in the first column. Also, each row has a
different number of columns. I want to read this CSV file, replace one of
the cell value and save again as a CSV file with the same format as the
original file with exactly same number of columns and rows. It sounds like
a simple task, but I am struggling to find a way. I tried to do this with
the help of this
<https://stackoverflow.com/questions/17288197/reading-a-csv-file-organized-horizontally>
 and this
<https://stackoverflow.com/questions/20308621/how-do-i-write-a-csv-file-in-r-where-my-input-is-written-to-the-file-as-row>
posts,
but still, I can't get the output the way I want. Can somebody help me here?

My attempt using the answer in this post
<https://stackoverflow.com/questions/17288197/reading-a-csv-file-organized-horizontally>
to
read the CSV file (samplefile.csv) gives me the following output where
headers are kinda messed up and empty cells are replaced with NA values
which is not what I want

aaa <- read.tcsv("samplefile.csv")
aaa

     Header.1 Header.2..units. Header.3..units. Header.3..units..11
Some text           0.0625                0             2648962
<NA>           0.0625             1200             6647473        <NA>
          0.0625             1380                  14        <NA>
     0.2500             1500                  15        <NA>
0.6250             1620                 NA6        <NA>
1.3125             1740                 NA7        <NA>
2.4375             1860                 NA8        <NA>
3.5625             1980                 NA9        <NA>
4.6250             2100                 NA10       <NA>
5.0000             2220                 NA11       <NA>
5.0000             2340                 NA12       <NA>
4.6250             2460                 NA13       <NA>
3.5625             2580                 NA14       <NA>
2.4375             2700                 NA15       <NA>
1.3125             2820                 NA16       <NA>
0.6250             2940                 NA17       <NA>
0.2500             3060                 NA18       <NA>
0.0625             3180                 NA19       <NA>
0.0000             3300                 NA20       <NA>
0.0000            18000                 NA

Also, I am not sure how to go back to original format when I save the file
again after a modification (for example after replacing a cell value)

I tried saving the file again by using t (transpose) as given below

write.csv(t(aaa), file ="samplefile_e.csv", row.names=T)

but still, there are following issues in the saved file

   1. messed up headers
   2. empty cells are replaced with NA
   3. when I open the file in a text editor all the values are shown as
   characters

Thanks,
Mano

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: R - Reading a horizontally arranged csv file, replacing values and saving again

R help mailing list-2
Read the help file for write.table
   ?write.table
and look at the descriptions of its arguments.
  > df <- data.frame(Text=c("Abe","Ben",NA,"David"), Age=c(19, NA, 12, 10))
  > write.table(file=stdout(), t(df), sep=",")
  "V1","V2","V3","V4"
  "Text","Abe","Ben",NA,"David"
  "Age","19",NA,"12","10"
  > write.table(file=stdout(), t(df), quote=FALSE, na="", sep=",")
  V1,V2,V3,V4
  Text,Abe,Ben,,David
  Age,19,,12,10




Bill Dunlap
TIBCO Software
wdunlap tibco.com

On Mon, Oct 1, 2018 at 2:24 AM, Manoranjan Muthusamy <
[hidden email]> wrote:

> I have a horizontally arranged CSV file
> <https://www.dropbox.com/s/0ukyuifvpq1olqk/samplefile.csv?dl=0> (
> samplefile.csv) with headers are in the first column. Also, each row has a
> different number of columns. I want to read this CSV file, replace one of
> the cell value and save again as a CSV file with the same format as the
> original file with exactly same number of columns and rows. It sounds like
> a simple task, but I am struggling to find a way. I tried to do this with
> the help of this
> <https://stackoverflow.com/questions/17288197/reading-a-
> csv-file-organized-horizontally>
>  and this
> <https://stackoverflow.com/questions/20308621/how-do-i-
> write-a-csv-file-in-r-where-my-input-is-written-to-the-file-as-row>
> posts,
> but still, I can't get the output the way I want. Can somebody help me
> here?
>
> My attempt using the answer in this post
> <https://stackoverflow.com/questions/17288197/reading-a-
> csv-file-organized-horizontally>
> to
> read the CSV file (samplefile.csv) gives me the following output where
> headers are kinda messed up and empty cells are replaced with NA values
> which is not what I want
>
> aaa <- read.tcsv("samplefile.csv")
> aaa
>
>      Header.1 Header.2..units. Header.3..units. Header.3..units..11
> Some text           0.0625                0             2648962
> <NA>           0.0625             1200             6647473        <NA>
>           0.0625             1380                  14        <NA>
>      0.2500             1500                  15        <NA>
> 0.6250             1620                 NA6        <NA>
> 1.3125             1740                 NA7        <NA>
> 2.4375             1860                 NA8        <NA>
> 3.5625             1980                 NA9        <NA>
> 4.6250             2100                 NA10       <NA>
> 5.0000             2220                 NA11       <NA>
> 5.0000             2340                 NA12       <NA>
> 4.6250             2460                 NA13       <NA>
> 3.5625             2580                 NA14       <NA>
> 2.4375             2700                 NA15       <NA>
> 1.3125             2820                 NA16       <NA>
> 0.6250             2940                 NA17       <NA>
> 0.2500             3060                 NA18       <NA>
> 0.0625             3180                 NA19       <NA>
> 0.0000             3300                 NA20       <NA>
> 0.0000            18000                 NA
>
> Also, I am not sure how to go back to original format when I save the file
> again after a modification (for example after replacing a cell value)
>
> I tried saving the file again by using t (transpose) as given below
>
> write.csv(t(aaa), file ="samplefile_e.csv", row.names=T)
>
> but still, there are following issues in the saved file
>
>    1. messed up headers
>    2. empty cells are replaced with NA
>    3. when I open the file in a text editor all the values are shown as
>    characters
>
> Thanks,
> Mano
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: R - Reading a horizontally arranged csv file, replacing values and saving again

Amit Mittal
You can just transpose in xl and read the regular form. Also use na.omit(data) and is.na() to replace na with 0  or remove rows with na data items

------------
Amit Mittal
PhD in Finance and Accounting (tbd)
IIM Lucknow
http://ssrn.com/author=2665511
*Top 10%, downloaded author since July 2017
------------
Sent from my Outlook for Android
https://aka.ms/ghei36

________________________________
From: R-help <[hidden email]> on behalf of William Dunlap via R-help <[hidden email]>
Sent: Monday, October 1, 2018 9:26:36 PM
To: Manoranjan Muthusamy
Cc: [hidden email]
Subject: Re: [R] R - Reading a horizontally arranged csv file, replacing values and saving again

Read the help file for write.table
   ?write.table
and look at the descriptions of its arguments.
  > df <- data.frame(Text=c("Abe","Ben",NA,"David"), Age=c(19, NA, 12, 10))
  > write.table(file=stdout(), t(df), sep=",")
  "V1","V2","V3","V4"
  "Text","Abe","Ben",NA,"David"
  "Age","19",NA,"12","10"
  > write.table(file=stdout(), t(df), quote=FALSE, na="", sep=",")
  V1,V2,V3,V4
  Text,Abe,Ben,,David
  Age,19,,12,10




Bill Dunlap
TIBCO Software
wdunlap tibco.com

On Mon, Oct 1, 2018 at 2:24 AM, Manoranjan Muthusamy <
[hidden email]> wrote:

> I have a horizontally arranged CSV file
> <https://www.dropbox.com/s/0ukyuifvpq1olqk/samplefile.csv?dl=0> (
> samplefile.csv) with headers are in the first column. Also, each row has a
> different number of columns. I want to read this CSV file, replace one of
> the cell value and save again as a CSV file with the same format as the
> original file with exactly same number of columns and rows. It sounds like
> a simple task, but I am struggling to find a way. I tried to do this with
> the help of this
> <https://stackoverflow.com/questions/17288197/reading-a-
> csv-file-organized-horizontally>
>  and this
> <https://stackoverflow.com/questions/20308621/how-do-i-
> write-a-csv-file-in-r-where-my-input-is-written-to-the-file-as-row>
> posts,
> but still, I can't get the output the way I want. Can somebody help me
> here?
>
> My attempt using the answer in this post
> <https://stackoverflow.com/questions/17288197/reading-a-
> csv-file-organized-horizontally>
> to
> read the CSV file (samplefile.csv) gives me the following output where
> headers are kinda messed up and empty cells are replaced with NA values
> which is not what I want
>
> aaa <- read.tcsv("samplefile.csv")
> aaa
>
>      Header.1 Header.2..units. Header.3..units. Header.3..units..11
> Some text           0.0625                0             2648962
> <NA>           0.0625             1200             6647473        <NA>
>           0.0625             1380                  14        <NA>
>      0.2500             1500                  15        <NA>
> 0.6250             1620                 NA6        <NA>
> 1.3125             1740                 NA7        <NA>
> 2.4375             1860                 NA8        <NA>
> 3.5625             1980                 NA9        <NA>
> 4.6250             2100                 NA10       <NA>
> 5.0000             2220                 NA11       <NA>
> 5.0000             2340                 NA12       <NA>
> 4.6250             2460                 NA13       <NA>
> 3.5625             2580                 NA14       <NA>
> 2.4375             2700                 NA15       <NA>
> 1.3125             2820                 NA16       <NA>
> 0.6250             2940                 NA17       <NA>
> 0.2500             3060                 NA18       <NA>
> 0.0625             3180                 NA19       <NA>
> 0.0000             3300                 NA20       <NA>
> 0.0000            18000                 NA
>
> Also, I am not sure how to go back to original format when I save the file
> again after a modification (for example after replacing a cell value)
>
> I tried saving the file again by using t (transpose) as given below
>
> write.csv(t(aaa), file ="samplefile_e.csv", row.names=T)
>
> but still, there are following issues in the saved file
>
>    1. messed up headers
>    2. empty cells are replaced with NA
>    3. when I open the file in a text editor all the values are shown as
>    characters
>
> Thanks,
> Mano
>
>         [[alternative HTML version deleted]]
>
> ______________________________________________
> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/
> posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: R - Reading a horizontally arranged csv file, replacing values and saving again

ranjanmano167
In reply to this post by R help mailing list-2
Thanks for the short but informative answer, Bill. But still, each row has
four columns..right? Although the *NA* is replaced by a blank cell,
because of the extra comma it still is a four column row. Is there any way
to avoid/remove the extra comma when NA is replaced which will make it a
three column row?



On Mon, Oct 1, 2018 at 4:56 PM William Dunlap <[hidden email]> wrote:

> Read the help file for write.table
>    ?write.table
> and look at the descriptions of its arguments.
>   > df <- data.frame(Text=c("Abe","Ben",NA,"David"), Age=c(19, NA, 12, 10))
>   > write.table(file=stdout(), t(df), sep=",")
>   "V1","V2","V3","V4"
>   "Text","Abe","Ben",NA,"David"
>   "Age","19",NA,"12","10"
>   > write.table(file=stdout(), t(df), quote=FALSE, na="", sep=",")
>   V1,V2,V3,V4
>   Text,Abe,Ben,,David
>   Age,19,,12,10
>
>
>
>
> Bill Dunlap
> TIBCO Software
> wdunlap tibco.com
>
> On Mon, Oct 1, 2018 at 2:24 AM, Manoranjan Muthusamy <
> [hidden email]> wrote:
>
>> I have a horizontally arranged CSV file
>> <https://www.dropbox.com/s/0ukyuifvpq1olqk/samplefile.csv?dl=0> (
>> samplefile.csv) with headers are in the first column. Also, each row has a
>> different number of columns. I want to read this CSV file, replace one of
>> the cell value and save again as a CSV file with the same format as the
>> original file with exactly same number of columns and rows. It sounds like
>> a simple task, but I am struggling to find a way. I tried to do this with
>> the help of this
>> <
>> https://stackoverflow.com/questions/17288197/reading-a-csv-file-organized-horizontally
>> >
>>  and this
>> <
>> https://stackoverflow.com/questions/20308621/how-do-i-write-a-csv-file-in-r-where-my-input-is-written-to-the-file-as-row
>> >
>> posts,
>> but still, I can't get the output the way I want. Can somebody help me
>> here?
>>
>> My attempt using the answer in this post
>> <
>> https://stackoverflow.com/questions/17288197/reading-a-csv-file-organized-horizontally
>> >
>> to
>> read the CSV file (samplefile.csv) gives me the following output where
>> headers are kinda messed up and empty cells are replaced with NA values
>> which is not what I want
>>
>> aaa <- read.tcsv("samplefile.csv")
>> aaa
>>
>>      Header.1 Header.2..units. Header.3..units. Header.3..units..11
>> Some text           0.0625                0             2648962
>> <NA>           0.0625             1200             6647473        <NA>
>>           0.0625             1380                  14        <NA>
>>      0.2500             1500                  15        <NA>
>> 0.6250             1620                 NA6        <NA>
>> 1.3125             1740                 NA7        <NA>
>> 2.4375             1860                 NA8        <NA>
>> 3.5625             1980                 NA9        <NA>
>> 4.6250             2100                 NA10       <NA>
>> 5.0000             2220                 NA11       <NA>
>> 5.0000             2340                 NA12       <NA>
>> 4.6250             2460                 NA13       <NA>
>> 3.5625             2580                 NA14       <NA>
>> 2.4375             2700                 NA15       <NA>
>> 1.3125             2820                 NA16       <NA>
>> 0.6250             2940                 NA17       <NA>
>> 0.2500             3060                 NA18       <NA>
>> 0.0625             3180                 NA19       <NA>
>> 0.0000             3300                 NA20       <NA>
>> 0.0000            18000                 NA
>>
>> Also, I am not sure how to go back to original format when I save the file
>> again after a modification (for example after replacing a cell value)
>>
>> I tried saving the file again by using t (transpose) as given below
>>
>> write.csv(t(aaa), file ="samplefile_e.csv", row.names=T)
>>
>> but still, there are following issues in the saved file
>>
>>    1. messed up headers
>>    2. empty cells are replaced with NA
>>    3. when I open the file in a text editor all the values are shown as
>>    characters
>>
>> Thanks,
>> Mano
>>
>>         [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>>
>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: R - Reading a horizontally arranged csv file, replacing values and saving again

R help mailing list-2
Using read.table to read data with a variable number of entries per row is
probably a mistake - data.frames (which read.table returns) are not meant
for this sort of data.  You want to store the data in R as a list with
names.  E.g.,

readYourFormat <- function(connection) {
    # connection is a file name or connection object referring
    # to text of the form
    # Header A,Some text
    # Header B,<number>,<number>,<number>
    # Header C,<number>
    # Header D,<number>,<number>,<number>,<number>
    lines <- readLines(connection)
    lines <- lines[ nchar(lines)>0 ] # omit empty lines
    fields <- strsplit(lines, ",") # split lines by commas into fields
    headers <- vapply(fields, function(x)x[1], FUN.VALUE="") # first entry
is header
    names(fields) <- headers
    contents <- lapply(fields, function(x)x[-1]) # remaining entries are
contents
    contents[-1] <- lapply(contents[-1], as.numeric) # convert all but
first line's entries to numeric
    contents
}

After reading the data and fiddling with it (but keeping it a named list),
you can write it out in a similar format with

writeYourFormat <- function (data, connection = stdout())
{
    stopifnot(is.list(data), !is.null(names(data)))
    cat(file = connection, sep = "\n", paste(sep = ",", names(data),
        vapply(data, paste, collapse = ",", FUN.VALUE = "")))
}


Bill Dunlap
TIBCO Software
wdunlap tibco.com

On Wed, Oct 3, 2018 at 3:05 AM, Manoranjan Muthusamy <
[hidden email]> wrote:

> Thanks for the short but informative answer, Bill. But still, each row has
> four columns..right? Although the *NA* is replaced by a blank cell,
> because of the extra comma it still is a four column row. Is there any way
> to avoid/remove the extra comma when NA is replaced which will make it a
> three column row?
>
>
>
> On Mon, Oct 1, 2018 at 4:56 PM William Dunlap <[hidden email]> wrote:
>
>> Read the help file for write.table
>>    ?write.table
>> and look at the descriptions of its arguments.
>>   > df <- data.frame(Text=c("Abe","Ben",NA,"David"), Age=c(19, NA, 12,
>> 10))
>>   > write.table(file=stdout(), t(df), sep=",")
>>   "V1","V2","V3","V4"
>>   "Text","Abe","Ben",NA,"David"
>>   "Age","19",NA,"12","10"
>>   > write.table(file=stdout(), t(df), quote=FALSE, na="", sep=",")
>>   V1,V2,V3,V4
>>   Text,Abe,Ben,,David
>>   Age,19,,12,10
>>
>>
>>
>>
>> Bill Dunlap
>> TIBCO Software
>> wdunlap tibco.com
>>
>> On Mon, Oct 1, 2018 at 2:24 AM, Manoranjan Muthusamy <
>> [hidden email]> wrote:
>>
>>> I have a horizontally arranged CSV file
>>> <https://www.dropbox.com/s/0ukyuifvpq1olqk/samplefile.csv?dl=0> (
>>> samplefile.csv) with headers are in the first column. Also, each row has
>>> a
>>> different number of columns. I want to read this CSV file, replace one of
>>> the cell value and save again as a CSV file with the same format as the
>>> original file with exactly same number of columns and rows. It sounds
>>> like
>>> a simple task, but I am struggling to find a way. I tried to do this with
>>> the help of this
>>> <https://stackoverflow.com/questions/17288197/reading-a-
>>> csv-file-organized-horizontally>
>>>  and this
>>> <https://stackoverflow.com/questions/20308621/how-do-i-
>>> write-a-csv-file-in-r-where-my-input-is-written-to-the-file-as-row>
>>> posts,
>>> but still, I can't get the output the way I want. Can somebody help me
>>> here?
>>>
>>> My attempt using the answer in this post
>>> <https://stackoverflow.com/questions/17288197/reading-a-
>>> csv-file-organized-horizontally>
>>> to
>>> read the CSV file (samplefile.csv) gives me the following output where
>>> headers are kinda messed up and empty cells are replaced with NA values
>>> which is not what I want
>>>
>>> aaa <- read.tcsv("samplefile.csv")
>>> aaa
>>>
>>>      Header.1 Header.2..units. Header.3..units. Header.3..units..11
>>> Some text           0.0625                0             2648962
>>> <NA>           0.0625             1200             6647473        <NA>
>>>           0.0625             1380                  14        <NA>
>>>      0.2500             1500                  15        <NA>
>>> 0.6250             1620                 NA6        <NA>
>>> 1.3125             1740                 NA7        <NA>
>>> 2.4375             1860                 NA8        <NA>
>>> 3.5625             1980                 NA9        <NA>
>>> 4.6250             2100                 NA10       <NA>
>>> 5.0000             2220                 NA11       <NA>
>>> 5.0000             2340                 NA12       <NA>
>>> 4.6250             2460                 NA13       <NA>
>>> 3.5625             2580                 NA14       <NA>
>>> 2.4375             2700                 NA15       <NA>
>>> 1.3125             2820                 NA16       <NA>
>>> 0.6250             2940                 NA17       <NA>
>>> 0.2500             3060                 NA18       <NA>
>>> 0.0625             3180                 NA19       <NA>
>>> 0.0000             3300                 NA20       <NA>
>>> 0.0000            18000                 NA
>>>
>>> Also, I am not sure how to go back to original format when I save the
>>> file
>>> again after a modification (for example after replacing a cell value)
>>>
>>> I tried saving the file again by using t (transpose) as given below
>>>
>>> write.csv(t(aaa), file ="samplefile_e.csv", row.names=T)
>>>
>>> but still, there are following issues in the saved file
>>>
>>>    1. messed up headers
>>>    2. empty cells are replaced with NA
>>>    3. when I open the file in a text editor all the values are shown as
>>>    characters
>>>
>>> Thanks,
>>> Mano
>>>
>>>         [[alternative HTML version deleted]]
>>>
>>> ______________________________________________
>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide http://www.R-project.org/
>>> posting-guide.html
>>> and provide commented, minimal, self-contained, reproducible code.
>>>
>>
>>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Reply | Threaded
Open this post in threaded view
|

Re: R - Reading a horizontally arranged csv file, replacing values and saving again

ranjanmano167
That's exactly what I was looking for. Never used readLines before which is
why I was struggling.  Many thanks. I asked the same question in Stack
Overflow as well. But no answers yet. I will post the link to your answer.
Thanks again.

On Wed, Oct 3, 2018 at 5:51 PM William Dunlap <[hidden email]> wrote:

> Using read.table to read data with a variable number of entries per row is
> probably a mistake - data.frames (which read.table returns) are not meant
> for this sort of data.  You want to store the data in R as a list with
> names.  E.g.,
>
> readYourFormat <- function(connection) {
>     # connection is a file name or connection object referring
>     # to text of the form
>     # Header A,Some text
>     # Header B,<number>,<number>,<number>
>     # Header C,<number>
>     # Header D,<number>,<number>,<number>,<number>
>     lines <- readLines(connection)
>     lines <- lines[ nchar(lines)>0 ] # omit empty lines
>     fields <- strsplit(lines, ",") # split lines by commas into fields
>     headers <- vapply(fields, function(x)x[1], FUN.VALUE="") # first entry
> is header
>     names(fields) <- headers
>     contents <- lapply(fields, function(x)x[-1]) # remaining entries are
> contents
>     contents[-1] <- lapply(contents[-1], as.numeric) # convert all but
> first line's entries to numeric
>     contents
> }
>
> After reading the data and fiddling with it (but keeping it a named list),
> you can write it out in a similar format with
>
> writeYourFormat <- function (data, connection = stdout())
> {
>     stopifnot(is.list(data), !is.null(names(data)))
>     cat(file = connection, sep = "\n", paste(sep = ",", names(data),
>         vapply(data, paste, collapse = ",", FUN.VALUE = "")))
> }
>
>
> Bill Dunlap
> TIBCO Software
> wdunlap tibco.com
>
> On Wed, Oct 3, 2018 at 3:05 AM, Manoranjan Muthusamy <
> [hidden email]> wrote:
>
>> Thanks for the short but informative answer, Bill. But still, each row
>> has four columns..right? Although the *NA* is replaced by a blank cell,
>> because of the extra comma it still is a four column row. Is there any way
>> to avoid/remove the extra comma when NA is replaced which will make it a
>> three column row?
>>
>>
>>
>> On Mon, Oct 1, 2018 at 4:56 PM William Dunlap <[hidden email]> wrote:
>>
>>> Read the help file for write.table
>>>    ?write.table
>>> and look at the descriptions of its arguments.
>>>   > df <- data.frame(Text=c("Abe","Ben",NA,"David"), Age=c(19, NA, 12,
>>> 10))
>>>   > write.table(file=stdout(), t(df), sep=",")
>>>   "V1","V2","V3","V4"
>>>   "Text","Abe","Ben",NA,"David"
>>>   "Age","19",NA,"12","10"
>>>   > write.table(file=stdout(), t(df), quote=FALSE, na="", sep=",")
>>>   V1,V2,V3,V4
>>>   Text,Abe,Ben,,David
>>>   Age,19,,12,10
>>>
>>>
>>>
>>>
>>> Bill Dunlap
>>> TIBCO Software
>>> wdunlap tibco.com
>>>
>>> On Mon, Oct 1, 2018 at 2:24 AM, Manoranjan Muthusamy <
>>> [hidden email]> wrote:
>>>
>>>> I have a horizontally arranged CSV file
>>>> <https://www.dropbox.com/s/0ukyuifvpq1olqk/samplefile.csv?dl=0> (
>>>> samplefile.csv) with headers are in the first column. Also, each row
>>>> has a
>>>> different number of columns. I want to read this CSV file, replace one
>>>> of
>>>> the cell value and save again as a CSV file with the same format as the
>>>> original file with exactly same number of columns and rows. It sounds
>>>> like
>>>> a simple task, but I am struggling to find a way. I tried to do this
>>>> with
>>>> the help of this
>>>> <
>>>> https://stackoverflow.com/questions/17288197/reading-a-csv-file-organized-horizontally
>>>> >
>>>>  and this
>>>> <
>>>> https://stackoverflow.com/questions/20308621/how-do-i-write-a-csv-file-in-r-where-my-input-is-written-to-the-file-as-row
>>>> >
>>>> posts,
>>>> but still, I can't get the output the way I want. Can somebody help me
>>>> here?
>>>>
>>>> My attempt using the answer in this post
>>>> <
>>>> https://stackoverflow.com/questions/17288197/reading-a-csv-file-organized-horizontally
>>>> >
>>>> to
>>>> read the CSV file (samplefile.csv) gives me the following output where
>>>> headers are kinda messed up and empty cells are replaced with NA values
>>>> which is not what I want
>>>>
>>>> aaa <- read.tcsv("samplefile.csv")
>>>> aaa
>>>>
>>>>      Header.1 Header.2..units. Header.3..units. Header.3..units..11
>>>> Some text           0.0625                0             2648962
>>>> <NA>           0.0625             1200             6647473        <NA>
>>>>           0.0625             1380                  14        <NA>
>>>>      0.2500             1500                  15        <NA>
>>>> 0.6250             1620                 NA6        <NA>
>>>> 1.3125             1740                 NA7        <NA>
>>>> 2.4375             1860                 NA8        <NA>
>>>> 3.5625             1980                 NA9        <NA>
>>>> 4.6250             2100                 NA10       <NA>
>>>> 5.0000             2220                 NA11       <NA>
>>>> 5.0000             2340                 NA12       <NA>
>>>> 4.6250             2460                 NA13       <NA>
>>>> 3.5625             2580                 NA14       <NA>
>>>> 2.4375             2700                 NA15       <NA>
>>>> 1.3125             2820                 NA16       <NA>
>>>> 0.6250             2940                 NA17       <NA>
>>>> 0.2500             3060                 NA18       <NA>
>>>> 0.0625             3180                 NA19       <NA>
>>>> 0.0000             3300                 NA20       <NA>
>>>> 0.0000            18000                 NA
>>>>
>>>> Also, I am not sure how to go back to original format when I save the
>>>> file
>>>> again after a modification (for example after replacing a cell value)
>>>>
>>>> I tried saving the file again by using t (transpose) as given below
>>>>
>>>> write.csv(t(aaa), file ="samplefile_e.csv", row.names=T)
>>>>
>>>> but still, there are following issues in the saved file
>>>>
>>>>    1. messed up headers
>>>>    2. empty cells are replaced with NA
>>>>    3. when I open the file in a text editor all the values are shown as
>>>>    characters
>>>>
>>>> Thanks,
>>>> Mano
>>>>
>>>>         [[alternative HTML version deleted]]
>>>>
>>>> ______________________________________________
>>>> [hidden email] mailing list -- To UNSUBSCRIBE and more, see
>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>> PLEASE do read the posting guide
>>>> http://www.R-project.org/posting-guide.html
>>>> and provide commented, minimal, self-contained, reproducible code.
>>>>
>>>
>>>
>

        [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list -- To UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.