[R] reshape
arun
smartpink111 at yahoo.com
Thu Mar 14 03:44:28 CET 2013
Hi Elisa,
You need to check your data. For some 'st', the data is repeated/duplicated (I am assuming, didn't check it) especially for a particular year.
dat1<-read.csv("elisa.csv",sep="\t")
dat1$st<- as.character(dat1$st)
str(dat1)
#'data.frame': 506953 obs. of 5 variables:
# $ st : chr "AGOMO" "AGOMO" "AGOMO" "AGOMO" ...
# $ year : int 2004 2004 2004 2004 2004 2004 2004 2004 2004 2004 ...
# $ month : int 1 1 1 1 1 1 1 1 1 1 ...
# $ day : int 1 2 3 4 5 6 7 8 9 10 ...
# $ discharge: num 6.75 7.6 5.58 5.02 4.8 ...
library(reshape2)
dat2<- do.call(rbind,lapply(split(dat1,list(dat1$st,dat1$year)),function(x) {x$day<-seq_along(x$day);x}))
row.names(dat2)<-1:nrow(dat2)
str(dat2)
#'data.frame': 506953 obs. of 5 variables:
# $ st : chr "TANNU" "TANNU" "TANNU" "TANNU" ...
# $ year : int 1935 1935 1935 1935 1935 1935 1935 1935 1935 1935 ...
# $ month : int 1 1 1 1 1 1 1 1 1 1 ...
# $ day : int 1 2 3 4 5 6 7 8 9 10 ...
# $ discharge: num 8.06 7.65 7.3 7.3 6.95 6.95 6.6 6.25 5.9 5.61 ...
res1<-lapply(split(dat2,dat2$st),function(x) dcast(x,day~year,mean,value.var="discharge"))
length(res1)
#[1] 124
which(lapply(res1,nrow)>366) #this is what I mentioned
#CURVO GHIST READO TERCA UZZCO
# 34 52 82 115 118
nrow(res1$CURVO)
#[1] 730
nrow(res1$CURVO)
#[1] 730
tail(res1$CURVO)
# day 2005 2006 2007 2008 2010
#725 725 NaN NaN NaN NaN 1.2552028
#726 726 NaN NaN NaN NaN 1.1714796
#727 727 NaN NaN NaN NaN 0.8065988
#728 728 NaN NaN NaN NaN 0.9375889
#729 729 NaN NaN NaN NaN 1.1693797
#730 730 NaN NaN NaN NaN 1.2124010
If the data is duplicated, then you can try this to sort out:
res1[lapply(res1,nrow)>366]<-lapply(res1[lapply(res1,nrow)>366],function(x) x[1:366,])
which(lapply(res1,nrow)>366)
#named integer(0)
tail(res1$TERCA)
# day 2004 2005 2006 2010
#361 361 4.451136 0.1457738 0.7695212 1.2963822
#362 362 4.539952 0.1371580 0.7409566 1.3431055
#363 363 2.133320 0.1179060 0.7325064 1.3905679
#364 364 1.499514 0.1238591 0.7223855 1.3318688
#365 365 1.136719 0.1312868 0.7449733 1.3561590
#366 366 1.004139 NaN NaN 0.5610225
A.K.
________________________________
From: eliza botto <eliza_botto at hotmail.com>
To: "smartpink111 at yahoo.com" <smartpink111 at yahoo.com>
Sent: Wednesday, March 13, 2013 7:04 PM
Subject: reshape
Dear Arun,
[text file is attached in case the format of file is change.......... excel file contains the data.
Few days ago, i asked you a question about reshaping of data, of the file which is attached with this email
By using the following command
library(reshape2)
res<-lapply(split(dat1,dat1$st),function(x) dcast(x,month~year,mean,value.var="discharge"))
The data was converted to following shape
$VERRO
month 2008 2009 2010
1 1 1.4737028 2.314878 2.672661
2 2 1.6700918 2.609722 2.112421
3 3 3.2387775 7.305766 6.939536
4 4 6.7063592 18.745256 13.278218
5 5 13.4666085 21.061198 12.185597
6 6 9.7578872 8.642275 6.973769
7 7 2.2772154 3.436705 3.357440
8 8 1.1911175 2.300386 1.994471
9 9 0.8528279 2.653862 1.242462
10 10 0.7581712 1.956000 1.753606
11 11 3.6661192 3.406465 10.984185
12 12 2.2877993 3.736377 2.312527
$VOBIC
month 2005 2006 2008 2009
1 1 1.7360776 0.8095275 1.6369044 0.8195241
2 2 0.6962079 3.8510720 0.4319758 2.3304495
3 3 1.0423625 2.7687266 0.2904245 0.7015527
4 4 2.4158326 1.2315324 1.4287387 1.5701019
5 5 1.6852624 0.8981553 0.2336609 2.4217805
6 6 0.7311802 0.5769895 0.5988320 1.0135255
7 7 0.4366588 0.5744698 0.2393858 0.6352815
8 8 0.2259192 1.0504790 0.1727715 0.4938100
9 9 0.3061264 0.7786805 0.1607744 0.5704136
10 10 0.1007935 0.1449439 1.3299596 0.6592695
11 11 0.4050701 0.2634931 3.4369699 2.8625800
12 12 2.5665737 3.5129255 3.7440045 6.7098572
I now want to reshape the data so that i instead of month column, there is a "day" column and corresponding to that column there are values of discharge against each day.
Point to remember is that there can be leap years in the data. Therefore, in the day column there should be 366 rows and the discharge value against 366th row of "day" column should be NA for normal years.
The output should slightly look like the following
day2007200820092012
11.06481450.739607160.709476751.0910787
21.03883460.721533780.642139050.98537076
30.994321350.724910180.604172220.92753464
40.964183460.713164880.626546960.93985833
51.01195460.721533780.601655151.0182268
60.990267020.70683050.618238961.01867
70.96763580.627783840.624279221.0029522
80.984760260.589403720.675053341.0979852
90.992942280.596523180.63785911.1139438
100.960866430.623609280.631065041.1213348
110.961336750.685543460.632059741.0227273
120.910802230.692296270.679805130.99130809
130.898210330.695432570.625013880.98488831
140.90451450.697288760.652906231.0115222
150.971476140.721533780.631314180.97627949
160.972398850.737912840.615216920.94556519
171.00774530.676293650.622519120.94295091
181.02916630.697929810.644660540.93701747
191.02837050.659599590.651216180.91748959
200.969657780.608771360.668747160.95011925
210.968335920.661707260.655964571.0267864
221.00641240.619956180.577547541.0067704
231.13065080.614961340.635842971.0029563
241.06462430.628076090.615495770.99962462
250.928952190.615668930.592897730.98986724
260.942263680.653836540.558965290.95461753
271.02668740.617186840.572011930.88800036
280.94771740.555822150.535633291.0005777
290.981320330.629388560.522046891.0308107
300.992216490.589182910.573018751.0172452
311.05236710.646848410.578798270.91537055
321.0265770.58529220.681835260.91131135
330.933984210.584033440.7828520.96773502
340.88977990.616603250.782104830.94601239
350.892958190.633329670.704205790.98362671
360.895142570.563174440.769290951.0820029
370.896331620.584054430.818266421.0334544
380.953632950.579567980.842612740.96225166
390.983145180.604592630.764479490.91343272
400.98407340.645256820.71450390.9234311
410.956092520.696100550.748946850.98913102
420.996894260.685934740.694914190.85918895
430.997123210.670523270.689887450.87369491
440.983603080.693200160.775263010.94079462
451.01018540.675746250.681081990.94199814
460.97868190.706308330.649400530.96485538
470.991059310.698306030.615985821.0220053
480.993048560.703287150.659961981.0572528
490.979516030.70447730.614225721.0563196
500.966466220.591025260.641630911.0131615
510.943271660.593649720.626780170.91487339
520.951434220.613215470.645127060.91821118
530.933120710.583458750.678795931.0396076
540.946182930.58163910.700427170.98816825
550.969606440.553955920.675280461.0243786
560.952475780.534918280.659219811.001266
570.922747010.543278220.68861811.0076981
580.894430040.513272440.685622180.92648276
590.92652730.55081720.695660260.96099836
600.881879870.575617960.840092360.94934403
610.853577260.578525550.836073960.95625094
620.847754730.574117410.865484220.94170779
630.752322230.633223450.961532550.92981229
640.805179230.5818721.01116060.97984979
650.846797790.61297290.899303021.0105809
660.925805770.583721540.881288511.0515365
670.822347070.565243150.825528690.99106288
680.806379790.585820310.856195921.017468
690.775416040.611130560.866708181.015052
700.787507740.610304760.847902521.0300831
710.742507070.599864740.805210920.98531088
720.781721070.67169050.845400360.99889496
730.810744350.564842950.881530860.98009904
740.801071010.494853230.890004740.95679038
750.79091670.56518690.837582691.0355801
760.774696460.604315990.852634431.0908412
770.833467740.545913990.925448671.0836892
780.8445920.500468440.968060821.1196524
790.780972150.524489630.982251211.0815244
800.757262190.521005490.885294761.0881958
810.803240420.545886410.900113731.1234666
820.781228150.556052210.867759271.0836817
830.723385220.52704270.928950671.101074
840.745660560.572055240.851884861.1711372
850.707430640.610067080.861676681.3422045
860.64938610.537263620.882443011.146363
870.643327320.574083911.09494311.0501232
880.633222230.589973871.15035121.1187078
890.638528490.548038891.07272751.1463537
900.639437960.580587711.05522111.0277322
910.579675230.617089931.36677171.0434552
920.523858640.581591181.50668371.0381919
930.502897370.56697661.55489691.0872603
940.552531090.579787081.50904171.1263416
950.543698380.519883851.47954130.99869602
960.50948560.544044061.47229990.98555417
970.517196780.596260331.46011931.0158522
980.537638180.542616541.43274880.99869549
990.526522060.575883281.32307290.9967576
1000.507632660.618973761.44819221.0196349
1010.494634890.624457831.53571680.98960951
1020.48530960.615046441.42877021.0153303
1030.514013480.524271651.39415150.93344699
1040.505479540.520285731.44491780.98603459
1050.489847060.564647411.51582030.96893531
1060.509766640.615625191.7243610.92914199
1070.528582410.589434391.63495940.92845841
1080.539694270.677070821.49664010.99246688
1090.525429790.708300871.62886431.0001392
1100.552199470.692685841.59141090.96535717
1110.57489360.704544511.5839230.89322917
1120.581341970.95039531.59469980.96774321
1130.580231970.870118941.58721721.0703388
1140.570996090.850895271.60779591.0396314
1150.611240360.857834891.53126121.0232001
1160.596745180.853992681.64969791.0236788
1170.542354320.850160092.95830831.0019835
1180.478948690.866258834.22728221.0129579
1190.513190720.875382143.06184361.0569999
1200.533892050.912875632.46327631.1196854
1210.575013681.11644792.19263891.3541186
1220.652808750.947994442.11810861.8258313
1230.572470140.889800132.05124521.5870326
1240.645089420.842806532.02151491.6699538
1250.55869680.919142051.92608542.9393732
1260.530147881.37553281.83618812.5174096
1270.499938951.04813391.84277891.9632441
1280.544228351.17479321.95604261.6715986
1290.477599621.04810331.97988641.6251918
1300.472808651.02870631.98105481.7246901
1310.508133381.03708421.95205572.2090389
1320.491969511.08176281.97757252.134945
1330.580330951.06359531.94970042.1593739
1342.72760931.21894551.99369142.078446
1352.3409551.19119642.10516521.8117349
1360.653795341.01430492.12064411.6135186
1370.674405841.72366932.04421371.5115738
1380.494603462.42139372.02971921.4475275
1390.494041382.59001762.04374121.4044078
1400.542445153.26251542.01515361.3867691
1410.573050244.3120012.1568951.3498345
1420.554512134.78249232.76127511.3576969
1430.533292355.07245793.72252541.3243373
1440.521651034.83804234.66425761.3231542
1450.549035335.07087485.15899061.3634215
1460.625000754.58860556.11495811.5063462
1470.664636316.89224396.13105351.4863485
1481.74460577.0276825.02013831.4015488
1490.9893138211.113274.31413951.3755733
1500.7485418.5194484.35305811.3336467
1510.7877564817.8661033.72399831.4434756
1520.9671787510.6184633.1817371.3319598
1530.838063999.49790213.43566431.2871763
1540.767686629.10266754.24232851.2521469
1550.713488338.05928693.6136811.2438042
1560.691580477.38667114.56377591.2676442
1570.755100927.456215910.5612871.2905136
1580.786538357.93885276.91756951.3912844
1590.693669176.62166575.69888981.3522234
1600.86008193.27049817.14140912.8149149
1610.773236536.01129496.16770342.4243438
1620.802740226.14409245.93353323.4082182
1630.775161227.82397825.78761326.1901435
1640.853297577.97278545.98386646.4392565
1650.755804217.69552246.09740445.200522
1665.43650317.10237946.38980855.3154358
1674.67327877.01319597.11186176.0050122
1682.71813246.93724317.04393327.397882
1691.68299066.98617698.02083355.2847531
1701.09545286.85807389.06670064.3840506
1711.1832026.61854629.11420153.5813312
1721.54395246.45544718.24071142.8464327
1731.66295946.0023777.15631042.308585
1741.39110896.02015345.9965922.2683034
1751.0137966.08015164.51635142.2232726
1761.22203255.83708364.11013631.8545508
1771.39074885.55399244.16503531.7834044
1781.04934135.52670764.12196851.7679803
1790.817993035.65157875.97276222.2616463
1800.770582686.15258077.82814082.6211916
1810.781056596.30688596.40658462.5053509
1820.815826126.29605585.5316082.5193545
1830.872425396.62221186.08146782.1911263
1840.799617737.33735544.26058942.4818411
1850.74638478.34599554.71059152.4199169
1860.674591517.87512715.75189462.3712093
1870.692568157.06783475.58827462.287974
1880.679422927.18847145.02895982.0957046
1890.677472376.8622313.68996272.006909
1900.700974126.3338292.98369381.9475101
1910.631158316.09406432.90733281.861646
1920.615122676.08883662.81579571.898311
1930.564320416.24429532.83587361.9261693
1940.594892257.10070852.80102412.058995
1950.642937737.53796762.83925981.7427533
1960.620940666.66205162.98212351.7049205
1970.597350326.02718452.71624041.5980379
1980.584379845.99023854.57428681.7239156
1990.579452165.98941317.91891861.4384137
2000.557736436.10517024.33473291.2943278
2010.552432116.09860782.78877781.4182289
2020.565459145.39031172.70727281.3331344
2030.534671865.80665172.57246521.8001603
2040.623344675.20840422.60017652.1845565
2050.647746996.0602162.56937051.3000448
2060.516276996.04092432.5359381.1315809
2070.541215595.86390432.18347811.0953713
2080.542103965.90491762.04957271.1153756
2090.546852954.52234552.15498041.113491
2100.525649475.65755092.08905011.0848855
2110.497719215.93350971.83736131.0765594
2120.495674635.8513461.97941721.0522506
2130.546131125.85084481.89801571.1027664
2140.566462015.95377832.35460991.1728034
2150.543097265.76733942.2220551.0670922
2160.508683934.38068571.82851221.0639146
2170.562784195.4972781.76906731.1427799
2180.565930615.68401851.76321341.0827335
2190.578437764.52610071.7283421.0536919
2200.669033453.84358922.26787481.1587576
2210.568170093.8455411.80112631.1373135
2220.521121593.76844251.57930111.0949174
2230.538162.71063561.66561491.0756004
2240.555363624.80099221.63285351.207592
2250.588774065.62892251.45032461.1277703
2260.58639936.47543691.4356031.6840457
2270.604196435.32173831.34201351.781441
2280.598778232.78827231.21323541.5170789
2290.600894484.86793372.02606111.3124227
2300.585118772.28809412.804861.1344342
2310.590046454.57215122.80999011.1697087
2320.597775354.7464592.74010881.1334919
2330.637527054.55994832.697911.1111067
2340.635632434.51849952.62538081.1990097
2350.575525414.66521862.59458491.1742317
2360.579450214.65962322.51177591.1356492
2370.569736662.16199812.49691231.1258631
2380.585889012.62385662.78611581.192101
2390.609870432.67228073.06652551.1678017
2400.623637014.5182022.70350611.137551
2410.632970444.41402031.83183691.0741609
2420.620964014.10900271.14647931.118947
2430.552160424.28036691.13755491.0494053
2440.590046451.59845331.12076981.0879782
2450.563294493.09942741.13428741.1720783
2460.61047974.53809061.13333131.1377645
2470.592820384.72700551.09277281.1797031
2480.530186935.2568121.01961561.2226005
2490.545983745.54867970.975800781.2140205
2500.531234576.84242241.02819161.2295129
2510.536805849.25181511.03240861.776022
2520.564948845.83119191.06125191.2755068
2530.53707735.11100451.01309961.2357103
2540.536425825.12136471.04169421.1249365
2550.583900224.98415231.05619351.1568394
2560.5749425.10465510.955761171.1756604
2570.543061816.46399111.01087371.1308558
2580.61556293.69223851.15831921.1994919
2590.631837915.6415911.65108971.2125883
2600.58739265.19401281.11135961.201392
2610.542103965.14355580.929441311.1801834
2620.475198934.70238160.971550861.1253904
2630.437805485.26005990.925933371.1392003
2640.45938285.08802880.876984051.1458752
2650.50789263.85138630.845129811.1549251
2660.536775165.00613430.846605051.1766139
2670.501543214.96272160.773306771.2988562
2680.596485254.86256210.78737691.202592
2690.56466784.83720140.775040941.1735086
2700.563396844.75583790.755956391.1339521
2710.549014234.71595320.743660371.1677927
2720.574253022.19107190.744161721.1658874
2730.563907144.73606070.735867921.2144972
2740.540802324.55288990.606441181.2426181
2750.53552534.77890.585577711.2464341
2760.573130074.75410840.569231121.2497697
2770.596745184.56068240.580050011.4482825
2780.580841254.44825650.627301451.8033021
2790.626929381.93210970.604698061.267868
2800.578420044.52826670.579060161.153984
2810.54476573.54968650.58557351.1547042
2820.563882414.18384520.637097911.1916279
2830.59005462.50331610.592356141.2268987
2840.575775772.72352860.568240121.1381103
2850.563897562.84181870.589358081.1799644
2860.533632112.76659660.664483771.1942531
2870.586921132.59373530.687623841.1403985
2880.559350532.60992120.68786321.2066456
2890.575032832.53971660.633318891.1940065
2900.553301322.77268820.620001541.2190238
2910.546059444.20025430.597894741.1966297
2920.5426144.26992490.607707021.1284872
2930.640978691.81936750.656951361.1890011
2940.657623873.92327250.687366671.1706587
2950.64708563.9776240.760466561.2159274
2960.587912482.64156860.657989051.1978153
2970.612910483.55556160.617528151.2218845
2980.577927462.48638810.610478081.2300012
2990.612658690.72315320.615495771.1916206
3000.604580450.733281070.604432961.2125899
3010.600169260.752397240.615479951.2009135
3020.599399030.920076340.633578221.1694554
3030.59647711.74806610.664495191.1930494
3040.562273891.67684590.672792291.3572232
3050.617793151.37202620.491908091.607067
3060.579962461.44510330.548915341.3662671
3070.571017191.36503260.676857571.3090836
3080.574090442.58785350.534060711.2557163
3090.5836582.80449410.544105791.2852644
3100.560606085.48401750.523028831.235702
3110.552459414.84027740.520768431.3412731
3120.516637854.18517840.549393281.3353075
3130.574763323.74814190.529795151.3048115
3140.588187323.2100330.518794361.3100371
3150.512517843.5472950.500718251.3160079
3160.542384993.75870940.522520621.3421985
3170.516626333.58472910.536853131.2838422
3180.541324153.57778280.534817691.3045738
3190.53550423.35959780.514719111.9267165
3200.496396473.35783310.567230812.3659179
3210.487181693.25621570.54588891.8051146
3220.511769013.14976060.528047821.5654158
3230.529401543.18054030.537074531.4482199
3240.565549973.29508620.548655761.3445983
3250.58145393.23145720.490620561.4227541
3260.605180153.21146790.473070491.4379519
3270.685190272.91427490.49441241.4153684
3280.659594182.54029770.476607591.345072
3290.623627432.73438960.518763921.3488929
3300.555334372.79225410.532821591.2912246
3310.532713752.68137860.517741341.2831074
3320.559100172.60560890.550174431.3193334
3330.527032362.09290280.514539041.2671856
3340.533593291.79151780.610986941.2688212
3350.545320631.80898350.603675481.3162515
3360.585359561.84327850.535335611.2349898
3370.589315041.69081750.5240031.239525
3380.582094481.63142260.534317891.2433287
3390.578428191.68804670.504931211.2140239
3400.589662761.74403810.512727861.2735857
3410.558370181.69379680.530304011.3171954
3420.587716671.48395720.536951051.3329288
3430.539252281.48130470.531327091.2845565
3440.545580571.54729030.527087561.2519107
3450.531226681.6891020.477120661.2157007
3460.538169571.76408230.486610881.1773483
3470.556785891.70956520.523534131.243337
3480.548505881.72212760.480363141.2531033
3490.577640231.74494420.503439761.2233239
3500.611482581.50531090.542105481.2197364
3510.611898031.27470640.522578091.1778031
3520.607936170.992936310.526601071.2237897
3530.615849310.901547170.517067581.2573928
3540.609808250.875874920.450816731.2619249
3550.581130180.861689090.470100211.2805232
3560.595805650.889211450.569526991.3658136
3570.639680170.967412790.626543971.3741412
3580.653238340.9698370.577797381.4408326
3590.672597851.01570010.510511011.1875855
3600.630985260.983311390.475601991.2623963
3610.650834850.854476390.575284651.2876546
3620.655925110.762893030.531543131.255007
3630.673184110.762216840.571492171.2836178
3640.645256280.849442810.58004581.2781226
3650.655882610.918182910.579793481.2814531
366NANANA1.546798
More information about the R-help
mailing list