r - Combine rows and sum their values -


below there fraction of original data frame. need combine rows in specific id repeated in specific season , lic , vessel different. combining need sum qtty , grosston.

please take id 431 in season 1998 example (*).

season   lic     id  qtty   vessel   grosston …    1998    16350   431  40     435       57 1998    16353   431  28     303       22.54 …

the same subject 431 has 2 different lic (16350 & 16353) , 2 different vessels (435 & 303). result expected in specific case is: 

  season     lic       id   qtty  vessel    grosston     …     1998    16350      431   68     435     79.54     …

i don't mind lic , vessel remind in resulting row, want keep season, id , resulting sum of qtty , grosston. in above example manually chose lic 16350 , vessel 435.

to honest have no idea do, i'd appreciate help.

thanks

original data (*= rows combined)

season  lic id  qtty    vessel  grosston 1998    15593   411 40  2643    31.5 1999    27271   411 40  2643    31.5 2000    35758   411 40  2643    31.5 2001    45047   411 50  2643    31.5 2002    56291   411 55  2643    31.5 2003    66991   411 55  2643    31.5 2004    80581   411 55  2643    31.5 2005    95058   411 52  na  na 2006    113379  411 50  10911   4.65 2007    120894  411 50  10911   4.65 2008    130033  411 50  2483    8.5 2009    139201  411 46  2296    50 2010    148833  411 46  2296    50 2011    158395  411 46  2296    50 1998    16350   431 40  435 57    # * 1998    16353   431 28  303 22.54 # * 2000    37491   436 50  2021    19.11 2001    47019   436 50  2021    19.11 2002    57588   436 51  2021    19.11 2003    69128   436 51  2021    19.11 2004    82400   436 52  2021    19.11 2005    95599   436 50  2021    19.11 2006    113126  436 50  2021    19.11 2007    122387  436 50  2021    19.11 2008    131126  436 50  2021    19.11 2009    140417  436 50  2021    19.11 2010    150673  436 50  2021    19.11 2011    159776  436 50  2021    19.11

also need keep previous , following rows have 1 id per season. this: (*=row resulting after being combined)

 season lic id  qtty    vessel  grosston 1998    15593   411 40  2643    31.5 1999    27271   411 40  2643    31.5 2000    35758   411 40  2643    31.5 2001    45047   411 50  2643    31.5 2002    56291   411 55  2643    31.5 2003    66991   411 55  2643    31.5 2004    80581   411 55  2643    31.5 2005    95058   411 52  na  na 2006    113379  411 50  10911   4.65 2007    120894  411 50  10911   4.65 2008    130033  411 50  2483    8.5 2009    139201  411 46  2296    50 2010    148833  411 46  2296    50 2011    158395  411 46  2296    50 1998    16350   431 68  435 79.54 #* 2000    37491   436 50  2021    19.11 2001    47019   436 50  2021    19.11 2002    57588   436 51  2021    19.11 2003    69128   436 51  2021    19.11 2004    82400   436 52  2021    19.11 2005    95599   436 50  2021    19.11 2006    113126  436 50  2021    19.11 2007    122387  436 50  2021    19.11 2008    131126  436 50  2021    19.11 2009    140417  436 50  2021    19.11 2010    150673  436 50  2021    19.11 2011    159776  436 50  2021    19.11

if turn data.frame data.table can make great use of by argument

library(data.table)  dt <- data.table(df)  # df original data

then 1 line: 

dt[, lapply(.sd, sum), by=list(season, lic, id, vessel)]

we can filter 1998 season, if we'd like: '

dt[, lapply(.sd, sum), by=list(season, lic, id, vessel)][season==1998]    season   lic  id vessel qtty grosston 1:   1998 15593 411   2643   40    31.50 2:   1998 16350 431    435   68   114.00 3:   1998 16353 431    303   68    45.08

the entire result output looks this: 

    season    lic  id vessel qtty grosston  1:   1998  15593 411   2643   40    31.50  2:   1999  27271 411   2643   40    31.50  3:   2000  35758 411   2643   40    31.50  4:   2001  45047 411   2643   50    31.50  5:   2002  56291 411   2643   55    31.50  6:   2003  66991 411   2643   55    31.50  7:   2004  80581 411   2643   55    31.50  8:   2005  95058 411     na   52       na  9:   2006 113379 411  10911   50     4.65 10:   2007 120894 411  10911   50     4.65 11:   2008 130033 411   2483   50     8.50 12:   2009 139201 411   2296   46    50.00 13:   2010 148833 411   2296   46    50.00 14:   2011 158395 411   2296   46    50.00 15:   1998  16350 431    435   68   114.00 16:   1998  16353 431    303   68    45.08 17:   1999  28641 431    303   68    45.08 18:   1999  28644 431    435   68   114.00 19:   2000  37491 436   2021   50    19.11 20:   2001  47019 436   2021   50    19.11 21:   2002  57588 436   2021   51    19.11 22:   2003  69128 436   2021   51    19.11 23:   2004  82400 436   2021   52    19.11 24:   2005  95599 436   2021   50    19.11 25:   2006 113126 436   2021   50    19.11 26:   2007 122387 436   2021   50    19.11 27:   2008 131126 436   2021   50    19.11 28:   2009 140417 436   2021   50    19.11 29:   2010 150673 436   2021   50    19.11 30:   2011 159776 436   2021   50    19.11     season    lic  id vessel qtty grosston

Comments

Post a Comment

Popular posts from this blog

php - mySql Join with 4 tables -

select * web_user e inner join web_audio ce on ce.uid = e.uid inner join web_videos c on c.uid = e.uid inner join web_images d on d.uid = e.uid e.uid = '1' we have scenario have 4 tables , want fetch records on basis of uid web_user table rest of tables fetch records on basis of web_user uid. have 6 records database. returning 8 time. below response of mysql query. uid ufname ulname uemail username upassword uaddress ucity ustate uzipcode uphone uimage usertype ugenre website biography uverifycode featured activate ustatus upaid aid uid a_filename a_uploadname a_genre a_singername a_approve a_price vid uid v_filename v_uploadname v_short_desc v_singername v_approve v_price iid uid upload_file i_approve ========================== 1 muhammad mohsin mohsin@balianti.com mohsin 213123 test addresss 0 1 40100 333-2335-5555 fds_896_autumn-mist.jpg 1 6 null null 202cb962ac59075b964b07152d234b70 1 1 1 1 1 1 asd muhammad_709.mp3 5 asd 0 0.00 1 1 test video muhammad_578.flv ...
Read more

css - Text drops down with smaller window -

i've adapted css use in website , can't quite figure out why 1 aspect of page acting way acting. basically, when shrink window below size horizontally, of text "drops down". becomes issue if user viewing website through ipad vertical orientation. can view issue here . i'm hoping make if window gets smaller, placement of text remains intact. the problem area not have enough space put both side navigation , content on 1 line. make automatically adjust page's width when exceeds decrements of width. this code example, , highly unlikely work. if see css more clearly, provide exact code. @media , (max-width:960px) { /* assuming #wrapper 960px across */ #content {width:560px;} /* opposed 720px */ } edit the content drops down @ 980px, have shrink width after there. @media , (max-width:980px) { #container {width:520px;} }
Read more

c# - DetailsView in ASP.Net - How to add another column on the side/add a control in each row? -

i have detailsview control on page used edit various fields of record, works in respect. i looking way add 1 column (and if works, why not more) right, absolutely read-only, show same fields of record comparison purposes. i aware there no obvious way such thing out of box detailsview . have looked other controls (transposed gridview , recommended formview , listview ), nothing satisfies. have special data binding setup using detailsview , can't out of without losing features. anyone on how "hack in" additional columns (for display only) on detailsview ? the solution have now, use second detailsview , visible set false in aspx. in code, make sure databind hidden detailsview hosts data third column first, initial detailsview named itemdetails . and in item created event, pass third column html rendering of hidden controls (in last code block) : protected void itemdetails_itemcreated(object sender, eventargs e) { if (dataite...
Read more