Sorting data.frame datewise in a descending order and geting datewise subtotl

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

Sorting data.frame datewise in a descending order and geting datewise subtotl

veepsirtt
This post has NOT been accepted by the mailing list yet.
Hi,,
How to Sort the  given data.frame date wise in a descending order and getting date wise subtotal
and month wise sub total ?

SLNO.        DATE        SCRIP        PROFIT
60063 02/07/2014 Aluminium  -1000
60057 02/07/2014 Copper  900
60056 02/07/2014 LEAD       -1250
60029 01/07/2014 Crude Oil 6400
60034 01/07/2014 LEAD        1500
60025 01/07/2014 Nickel        1500
59980 30/06/2014 Nickel          475
59984 30/06/2014 Natural Gas 3000
59972 30/06/2014 Crude Oil 2600
59971 30/06/2014 Copper 3750
59970 30/06/2014 Natural Gas 1500
59924 27/06/2014 Aluminium    500
59922 27/06/2014 LEAD        2250
59920 27/06/2014 Copper 1100
59918 27/06/2014 Natural Gas 3000


thanks
veepsirtt
Reply | Threaded
Open this post in threaded view
|

Re: Sorting data.frame datewise in a descending order and geting datewise subtotl

arun kirshna

Hi veepsirtt,


If `dat` is the dataset

library(dplyr)
 dat %>%

group_by(DATE) %>%

summarize(PROFIT=sum(PROFIT)) %>%
 arrange(desc(as.Date(DATE,format="%d/%m/%Y")))
Source: local data frame [4 x 2]

        DATE PROFIT
1 02/07/2014  -1350
2 01/07/2014   9400
3 30/06/2014  11325
4 27/06/2014   6850


If you just wanted to have a new variable subTotalPROFIT and not summarize the dataset


 dat %>%

 group_by(DATE) %>%

 mutate(subTotalPROFIT=sum(PROFIT)) %>%

arrange(desc(as.Date(DATE,format="%d/%m/%Y")))

A.K.



On Wednesday, July 2, 2014 4:03 AM, Velappan Periasamy <[hidden email]> wrote:



Hi A.K,
How to Sort the  given data.frame date wise in a descending order
 and getting date wise subtotal

SLNO.        DATE        SCRIP        PROFIT 
6006302/07/2014Aluminium       -1000
6005702/07/2014Copper  900
6005602/07/2014LEAD       -1250
6002901/07/2014Crude Oil6400
6003401/07/2014LEAD        1500
6002501/07/2014Nickel        1500
5998030/06/2014Nickel          475
5998430/06/2014Natural Gas3000
5997230/06/2014Crude Oil2600
5997130/06/2014Copper3750
5997030/06/2014Natural Gas1500
5992427/06/2014Aluminium    500
5992227/06/2014LEAD        2250
5992027/06/2014Copper1100
5991827/06/2014Natural Gas3000

Thanks
veepsirtt

______________________________________________
[hidden email] mailing list
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: Sorting data.frame datewise in a descending order and geting datewise subtotl

veepsirtt
Hi A.K

-----------------------------
library(XML)
URL <-  "http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2013&enddate=6/9/2014&exchange=MCX&sid=1"
doc <- htmlParse(URL)
tableNodes <- getNodeSet(doc, "//table")
dat1 <- readHTMLTable(tableNodes[[4]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", "numeric"),stringsAsFactors=FALSE)
dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
str(dat1)
------------------------------

 
I got this error,while running the above code under RStudio.,
how to correct it?.

> dat1 <- readHTMLTable(tableNodes[[4]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", " ..." ... [TRUNCATED]
Error in readHTMLTable(tableNodes[[4]], colClasses = c("numeric", "character",  :
  error in evaluating the argument 'doc' in selecting a method for function 'readHTMLTable': Error in tableNodes[[4]] : subscript out of bounds


On Thu, Jul 3, 2014 at 12:42 AM, arun kirshna [via R] <[hidden email]> wrote:

Hi veepsirtt,


If `dat` is the dataset

library(dplyr)
 dat %>%

group_by(DATE) %>%

summarize(PROFIT=sum(PROFIT)) %>%
 arrange(desc(as.Date(DATE,format="%d/%m/%Y")))
Source: local data frame [4 x 2]

        DATE PROFIT
1 02/07/2014  -1350
2 01/07/2014   9400
3 30/06/2014  11325
4 27/06/2014   6850


If you just wanted to have a new variable subTotalPROFIT and not summarize the dataset


 dat %>%

 group_by(DATE) %>%

 mutate(subTotalPROFIT=sum(PROFIT)) %>%

arrange(desc(as.Date(DATE,format="%d/%m/%Y")))

A.K.



On Wednesday, July 2, 2014 4:03 AM, Velappan Periasamy <[hidden email]> wrote:



Hi A.K,
How to Sort the  given data.frame date wise in a descending order
 and getting date wise subtotal

SLNO.        DATE        SCRIP        PROFIT 
6006302/07/2014Aluminium       -1000
6005702/07/2014Copper  900
6005602/07/2014LEAD       -1250
6002901/07/2014Crude Oil6400
6003401/07/2014LEAD        1500
6002501/07/2014Nickel        1500
5998030/06/2014Nickel          475
5998430/06/2014Natural Gas3000
5997230/06/2014Crude Oil2600
5997130/06/2014Copper3750
5997030/06/2014Natural Gas1500
5992427/06/2014Aluminium    500
5992227/06/2014LEAD        2250
5992027/06/2014Copper1100
5991827/06/2014Natural Gas3000

Thanks
veepsirtt

______________________________________________
[hidden email] mailing list
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.



To unsubscribe from Sorting data.frame datewise in a descending order and geting datewise subtotl, click here.
NAML

Reply | Threaded
Open this post in threaded view
|

Re: Sorting data.frame datewise in a descending order and geting datewise subtotl

veepsirtt
In reply to this post by arun kirshna
Hi A.K
I modified and got the results
thanks A.K

library(XML)
URL <-  "http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2014&enddate=02/07/2014&exchange=MCX&sid=1"
doc <- htmlParse(URL)
tableNodes <- getNodeSet(doc, "//table")
l=length(tableNodes)
dat1 <- readHTMLTable(tableNodes[[l]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", "numeric"),stringsAsFactors=FALSE)
dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
str(dat1)


On Thu, Jul 3, 2014 at 10:14 AM, Velappan Periasamy <[hidden email]> wrote:
Hi A.K

-----------------------------
library(XML)
URL <-  "http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2013&enddate=6/9/2014&exchange=MCX&sid=1"
doc <- htmlParse(URL)
tableNodes <- getNodeSet(doc, "//table")
dat1 <- readHTMLTable(tableNodes[[4]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", "numeric"),stringsAsFactors=FALSE)
dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
str(dat1)
------------------------------

 
I got this error,while running the above code under RStudio.,
how to correct it?.

> dat1 <- readHTMLTable(tableNodes[[4]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", " ..." ... [TRUNCATED]
Error in readHTMLTable(tableNodes[[4]], colClasses = c("numeric", "character",  :
  error in evaluating the argument 'doc' in selecting a method for function 'readHTMLTable': Error in tableNodes[[4]] : subscript out of bounds


On Thu, Jul 3, 2014 at 12:42 AM, arun kirshna [via R] <[hidden email]> wrote:

Hi veepsirtt,


If `dat` is the dataset

library(dplyr)
 dat %>%

group_by(DATE) %>%

summarize(PROFIT=sum(PROFIT)) %>%
 arrange(desc(as.Date(DATE,format="%d/%m/%Y")))
Source: local data frame [4 x 2]

        DATE PROFIT
1 02/07/2014  -1350
2 01/07/2014   9400
3 30/06/2014  11325
4 27/06/2014   6850


If you just wanted to have a new variable subTotalPROFIT and not summarize the dataset


 dat %>%

 group_by(DATE) %>%

 mutate(subTotalPROFIT=sum(PROFIT)) %>%

arrange(desc(as.Date(DATE,format="%d/%m/%Y")))

A.K.



On Wednesday, July 2, 2014 4:03 AM, Velappan Periasamy <[hidden email]> wrote:



Hi A.K,
How to Sort the  given data.frame date wise in a descending order
 and getting date wise subtotal

SLNO.        DATE        SCRIP        PROFIT 
6006302/07/2014Aluminium       -1000
6005702/07/2014Copper  900
6005602/07/2014LEAD       -1250
6002901/07/2014Crude Oil6400
6003401/07/2014LEAD        1500
6002501/07/2014Nickel        1500
5998030/06/2014Nickel          475
5998430/06/2014Natural Gas3000
5997230/06/2014Crude Oil2600
5997130/06/2014Copper3750
5997030/06/2014Natural Gas1500
5992427/06/2014Aluminium    500
5992227/06/2014LEAD        2250
5992027/06/2014Copper1100
5991827/06/2014Natural Gas3000

Thanks
veepsirtt

______________________________________________
[hidden email] mailing list
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.



To unsubscribe from Sorting data.frame datewise in a descending order and geting datewise subtotl, click here.
NAML


Reply | Threaded
Open this post in threaded view
|

Re: Sorting data.frame datewise in a descending order and geting datewise subtotl

veepsirtt
In reply to this post by arun kirshna
Warning in install.packages :
  package ‘dplyr’ is not available (for R version 2.15.3)
 Is there any alternate way to sorting data.frame datewise in a
descending order?.(not using dplyr)




On Thu, Jul 3, 2014 at 12:48 PM, Velappan Periasamy <[hidden email]> wrote:
Hi A.K
I modified and got the results
thanks A.K

library(XML)
URL <-  "http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2014&enddate=02/07/2014&exchange=MCX&sid=1"

doc <- htmlParse(URL)
tableNodes <- getNodeSet(doc, "//table")
l=length(tableNodes)
dat1 <- readHTMLTable(tableNodes[[l]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", "numeric"),stringsAsFactors=FALSE)

dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
str(dat1)


On Thu, Jul 3, 2014 at 10:14 AM, Velappan Periasamy <[hidden email]> wrote:
Hi A.K

-----------------------------
library(XML)
URL <-  "http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2013&enddate=6/9/2014&exchange=MCX&sid=1"
doc <- htmlParse(URL)
tableNodes <- getNodeSet(doc, "//table")
dat1 <- readHTMLTable(tableNodes[[4]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", "numeric"),stringsAsFactors=FALSE)
dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
str(dat1)
------------------------------

 
I got this error,while running the above code under RStudio.,
how to correct it?.

> dat1 <- readHTMLTable(tableNodes[[4]], colClasses=c("numeric","character", "character", "numeric", "character", "numeric", "numeric", "character", " ..." ... [TRUNCATED]
Error in readHTMLTable(tableNodes[[4]], colClasses = c("numeric", "character",  :
  error in evaluating the argument 'doc' in selecting a method for function 'readHTMLTable': Error in tableNodes[[4]] : subscript out of bounds


On Thu, Jul 3, 2014 at 12:42 AM, arun kirshna [via R] <[hidden email]> wrote:

Hi veepsirtt,


If `dat` is the dataset

library(dplyr)
 dat %>%

group_by(DATE) %>%

summarize(PROFIT=sum(PROFIT)) %>%
 arrange(desc(as.Date(DATE,format="%d/%m/%Y")))
Source: local data frame [4 x 2]

        DATE PROFIT
1 02/07/2014  -1350
2 01/07/2014   9400
3 30/06/2014  11325
4 27/06/2014   6850


If you just wanted to have a new variable subTotalPROFIT and not summarize the dataset


 dat %>%

 group_by(DATE) %>%

 mutate(subTotalPROFIT=sum(PROFIT)) %>%

arrange(desc(as.Date(DATE,format="%d/%m/%Y")))

A.K.



On Wednesday, July 2, 2014 4:03 AM, Velappan Periasamy <[hidden email]> wrote:



Hi A.K,
How to Sort the  given data.frame date wise in a descending order
 and getting date wise subtotal

SLNO.        DATE        SCRIP        PROFIT 
6006302/07/2014Aluminium       -1000
6005702/07/2014Copper  900
6005602/07/2014LEAD       -1250
6002901/07/2014Crude Oil6400
6003401/07/2014LEAD        1500
6002501/07/2014Nickel        1500
5998030/06/2014Nickel          475
5998430/06/2014Natural Gas3000
5997230/06/2014Crude Oil2600
5997130/06/2014Copper3750
5997030/06/2014Natural Gas1500
5992427/06/2014Aluminium    500
5992227/06/2014LEAD        2250
5992027/06/2014Copper1100
5991827/06/2014Natural Gas3000

Thanks
veepsirtt

______________________________________________
[hidden email] mailing list
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.



To unsubscribe from Sorting data.frame datewise in a descending order and geting datewise subtotl, click here.
NAML



Reply | Threaded
Open this post in threaded view
|

Re: Sorting data.frame datewise in a descending order and geting datewise subtotl

arun kirshna


Hi,
Try:

res1 <- aggregate(PROFIT~DATE, data=dat, FUN=sum)
res1[rev(order(as.Date(res1$DATE, format="%d/%m/%Y"))),]
#        DATE PROFIT
#2 02/07/2014  -1350
#1 01/07/2014   9400
#4 30/06/2014  11325
#3 27/06/2014   6850

#if you don't wanted another column in the original dataset with `sum`
 res2 <- transform(dat, sumPROFIT=ave(PROFIT, DATE, FUN=sum))
res2[rev(order(as.Date(res2$DATE,format="%d/%m/%Y"))),]
A.K.



On Thursday, July 3, 2014 4:43 AM, veepsirtt <[hidden email]> wrote:
Warning in install.packages :
  package ‘dplyr’ is not available (for R version 2.15.3)

Is there any alternate way to sorting data.frame datewise in a

descending order?.(not using dplyr)




On Thu, Jul 3, 2014 at 12:48 PM, Velappan Periasamy <[hidden email]>
wrote:

> Hi A.K
> I modified and got the results
> thanks A.K
>
> library(XML)
> URL <-  "
> http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2014&enddate=02/07/2014&exchange=MCX&sid=1
> "
>
> doc <- htmlParse(URL)
> tableNodes <- getNodeSet(doc, "//table")
> l=length(tableNodes)
> dat1 <- readHTMLTable(tableNodes[[l]], colClasses=c("numeric","character",
> "character", "numeric", "character", "numeric", "numeric", "character",
> "numeric"),stringsAsFactors=FALSE)
>
> dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
> str(dat1)
>
>
> On Thu, Jul 3, 2014 at 10:14 AM, Velappan Periasamy <[hidden email]>
> wrote:
>
>> Hi A.K
>>
>> -----------------------------
>> library(XML)
>> URL <-  "
>> http://money.securebank.in/index.php?option=com_dashboard&view=history&Itemid=56&startdate=01/01/2013&enddate=6/9/2014&exchange=MCX&sid=1"
>>
>> doc <- htmlParse(URL)
>> tableNodes <- getNodeSet(doc, "//table")
>> dat1 <- readHTMLTable(tableNodes[[4]],
>> colClasses=c("numeric","character", "character", "numeric", "character",
>> "numeric", "numeric", "character", "numeric"),stringsAsFactors=FALSE)
>> dat1$DATE <- as.Date(dat1$DATE, "%d-%m-%Y")
>> str(dat1)
>> ------------------------------
>>
>>
>> I got this error,while running the above code under RStudio.,
>> how to correct it?.
>>
>> > dat1 <- readHTMLTable(tableNodes[[4]],
>> colClasses=c("numeric","character", "character", "numeric", "character",
>> "numeric", "numeric", "character", " ..." ... [TRUNCATED]
>> Error in readHTMLTable(tableNodes[[4]], colClasses = c("numeric",
>> "character",  :
>>   error in evaluating the argument 'doc' in selecting a method for
>> function 'readHTMLTable': Error in tableNodes[[4]] : subscript out of
>> bounds
>>
>>
>> On Thu, Jul 3, 2014 at 12:42 AM, arun kirshna [via R] <
>> [hidden email]> wrote:
>>
>>>
>>> Hi veepsirtt,
>>>
>>>
>>> If `dat` is the dataset
>>>
>>> library(dplyr)
>>>  dat %>%
>>>
>>> group_by(DATE) %>%
>>>
>>> summarize(PROFIT=sum(PROFIT)) %>%
>>>  arrange(desc(as.Date(DATE,format="%d/%m/%Y")))
>>> Source: local data frame [4 x 2]
>>>
>>>         DATE PROFIT
>>> 1 02/07/2014  -1350
>>> 2 01/07/2014   9400
>>> 3 30/06/2014  11325
>>> 4 27/06/2014   6850
>>>
>>>
>>> If you just wanted to have a new variable subTotalPROFIT and not
>>> summarize the dataset
>>>
>>>
>>>  dat %>%
>>>
>>>  group_by(DATE) %>%
>>>
>>>  mutate(subTotalPROFIT=sum(PROFIT)) %>%
>>>
>>> arrange(desc(as.Date(DATE,format="%d/%m/%Y")))
>>>
>>> A.K.
>>>
>>>
>>>
>>> On Wednesday, July 2, 2014 4:03 AM, Velappan Periasamy <[hidden email]
>>> <http://user/SendEmail.jtp?type=node&node=4693407&i=0>> wrote:



>>>
>>>
>>>
>>> Hi A.K,
>>> How to Sort the  given data.frame date wise in a descending order
>>>  and getting date wise subtotal
>>>
>>> SLNO.        DATE        SCRIP        PROFIT
>>> 6006302/07/2014Aluminium       -1000
>>> 6005702/07/2014Copper  900
>>> 6005602/07/2014LEAD       -1250
>>> 6002901/07/2014Crude Oil6400
>>> 6003401/07/2014LEAD        1500
>>> 6002501/07/2014Nickel        1500
>>> 5998030/06/2014Nickel          475
>>> 5998430/06/2014Natural Gas3000
>>> 5997230/06/2014Crude Oil2600
>>> 5997130/06/2014Copper3750
>>> 5997030/06/2014Natural Gas1500
>>> 5992427/06/2014Aluminium    500
>>> 5992227/06/2014LEAD        2250
>>> 5992027/06/2014Copper1100
>>> 5991827/06/2014Natural Gas3000
>>>
>>> Thanks
>>> veepsirtt
>>>
>>> ______________________________________________
>>> [hidden email] <http://user/SendEmail.jtp?type=node&node=4693407&i=1>
>>> mailing list
>>> 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.
>>>
>>>
>>> ------------------------------
>>>  If you reply to this email, your message will be added to the
>>> discussion below:
>>>
>>> http://r.789695.n4.nabble.com/Sorting-data-frame-datewise-in-a-descending-order-and-geting-datewise-subtotl-tp4693384p4693407.html
>>>  To unsubscribe from Sorting data.frame datewise in a descending order
>>> and geting datewise subtotl, click here
>>> <
>>> .
>>> NAML
>>> <
http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>>>
>>
>>
>




--
View this message in context: http://r.789695.n4.nabble.com/Sorting-data-frame-datewise-in-a-descending-order-and-geting-datewise-subtotl-tp4693384p4693434.html
Sent from the R help mailing list archive at Nabble.com.
    [[alternative HTML version deleted]]

______________________________________________
[hidden email] mailing list
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.

______________________________________________
[hidden email] mailing list
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: Sorting data.frame datewise in a descending order and geting datewise subtotl

veepsirtt
This post has NOT been accepted by the mailing list yet.
In reply to this post by veepsirtt
SLNO.        DATE        SCRIP        PROFIT
60063 02/07/2014 Aluminium  -1000
60057 02/07/2014 Copper  900
60056 02/07/2014 LEAD       -1250
60029 01/07/2014 Crude Oil 6400
60034 01/07/2014 LEAD        1500
60025 01/07/2014 Nickel        1500
59980 30/06/2014 Nickel          475
59984 30/06/2014 Natural Gas 3000
59972 30/06/2014 Crude Oil 2600
59971 30/06/2014 Copper 3750
59970 30/06/2014 Natural Gas 1500
59924 27/06/2014 Aluminium    500
59922 27/06/2014 LEAD        2250
59920 27/06/2014 Copper 1100
59918 27/06/2014 Natural Gas 3000

for the above data I need the plot of date wise cumulative profit for  every day separately.