条件平均和分组加 Dataframe 排序

xghobddn  于 2023-04-27  发布在  其他
关注(0)|答案(2)|浏览(73)

用以下数据

structure(list(year = c(1990, 1991, 1992, 1993, 1994, 1995, 1996, 
1997, 1998, 1999, 2000, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 
1997, 1998, 1999, 2000, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 
1997, 1998, 1999, 2000, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 
1997, 1998, 1999, 2000), id = c("sector1", "sector1", "sector1", 
"sector1", "sector1", "sector1", "sector1", "sector1", "sector1", 
"sector1", "sector1", "sector2", "sector2", "sector2", "sector2", 
"sector2", "sector2", "sector2", "sector2", "sector2", "sector2", 
"sector2", "sector3", "sector3", "sector3", "sector3", "sector3", 
"sector3", "sector3", "sector3", "sector3", "sector3", "sector3", 
"sector4", "sector4", "sector4", "sector4", "sector4", "sector4", 
"sector4", "sector4", "sector4", "sector4", "sector4"), value1 = c(1, 
2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 
36, 37, 38, 39, 40, 41, 42, 43, 44), value2 = c(44, 43, 42, 41, 
40, 39, 38, 37, 36, 35, 34, 33, 32, 31, 30, 29, 28, 27, 26, 25, 
24, 23, 22, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 
8, 7, 6, 5, 4, 3, 2, 1)), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -44L))

我试图得到以下输出，这是给定两个日期范围（1990-95和1996-2000）的value 1和value 2列的平均值，最后两列是整个期间的总平均值。然后我将按value 1的总平均值对其进行排名。结果如下所示：

SectorName <- c("Sector4",  "Sector3",  "Sector2",  "Sector1")
Average199095Value1 <- c(36.5,  25.5,   14.5, 3.5)
Average199095Value2 <- c(42, 31, 20, 9)
Average19962000Value1 <- c(8.5, 19.5, 30.5, 41.5)
Average19962000Value2 <- c(3, 14, 25, 36)
AverageValue1 <- c(39, 28, 17,6)
AverageValue2 <- c(6, 17, 28,39)
df <- data.frame(SectorName, Average199095Value1, Average199095Value2, Average19962000Value1, Average19962000Value2, AverageValue1, AverageValue2)

目前我已经尝试了这个方法，但似乎不起作用。我无法根据给定的条件和SectorName的summaries创建新列

df %>%
  mutate( Average199095Value1 = if_else(fyear >= 1990 & fyear <= 1995, mean(value1), 0))%>%
  group_by(newname)%>%
  summarise(across(c(Average199095Value1)))

这已经不能为每个扇区提供唯一的行。
看看我的原始数据：

structure(list(newname = c("Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "Construction", "Construction", 
"Construction", "Construction", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerDurables", 
"ConsumerDurables", "ConsumerDurables", "ConsumerDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "ConsumerNonDurables", "ConsumerNonDurables", 
"ConsumerNonDurables", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Healthcare", "Healthcare", 
"Healthcare", "Healthcare", "Healthcare", "Hitec", "Hitec", "Hitec", 
"Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", 
"Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", 
"Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", 
"Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", 
"Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", "Hitec", 
"Hitec", "Hitec", "Hitec", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Manufacturing", 
"Manufacturing", "Manufacturing", "Manufacturing", "Other", "Other", 
"Other", "Other", "Other", "Other", "Other", "Other", "Other", 
"Other", "Other", "Other", "Other", "Other", "Other", "Other", 
"Other", "Other", "Other", "Other", "Other", "Other", "Other", 
"Other", "Other", "Other", "Other", "Other", "Other", "Other", 
"Other", "Other", "Other", "Other", "Other", "Other", "Other", 
"Other", "Other", "Other", "Other", "Telecom", "Telecom", "Telecom"
), fyear = c(1980L, 1981L, 1982L, 1983L, 1984L, 1985L, 1986L, 
1987L, 1988L, 1989L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 
1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 
2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 
2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 1980L, 1981L, 
1982L, 1983L, 1984L, 1985L, 1986L, 1987L, 1988L, 1989L, 1990L, 
1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 
2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 
2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 
2018L, 2019L, 2020L, 1980L, 1981L, 1982L, 1983L, 1984L, 1985L, 
1986L, 1987L, 1988L, 1989L, 1990L, 1991L, 1992L, 1993L, 1994L, 
1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 
2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 
2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 1980L, 
1981L, 1982L, 1983L, 1984L, 1985L, 1986L, 1987L, 1988L, 1989L, 
1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 
1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 
2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 
2017L, 2018L, 2019L, 2020L, 1980L, 1981L, 1982L, 1983L, 1984L, 
1985L, 1986L, 1987L, 1988L, 1989L, 1990L, 1991L, 1992L, 1993L, 
1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 2002L, 
2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 2011L, 
2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 2020L, 
1980L, 1981L, 1982L, 1983L, 1984L, 1985L, 1986L, 1987L, 1988L, 
1989L, 1990L, 1991L, 1992L, 1993L, 1994L, 1995L, 1996L, 1997L, 
1998L, 1999L, 2000L, 2001L, 2002L, 2003L, 2004L, 2005L, 2006L, 
2007L, 2008L, 2009L, 2010L, 2011L, 2012L, 2013L, 2014L, 2015L, 
2016L, 2017L, 2018L, 2019L, 2020L, 1980L, 1981L, 1982L, 1983L, 
1984L, 1985L, 1986L, 1987L, 1988L, 1989L, 1990L, 1991L, 1992L, 
1993L, 1994L, 1995L, 1996L, 1997L, 1998L, 1999L, 2000L, 2001L, 
2002L, 2003L, 2004L, 2005L, 2006L, 2007L, 2008L, 2009L, 2010L, 
2011L, 2012L, 2013L, 2014L, 2015L, 2016L, 2017L, 2018L, 2019L, 
2020L, 1980L, 1981L, 1982L), aqcflow = c(0.0327011090166374, 
0.0891084825787633, 0.0312081332316508, 0.182650601141221, 0.135408752018235, 
0.322781507327902, 0.557661348472675, 0.688192692714978, 0.0435128867651673, 
0.077104602426249, 0.0546116652537045, 0.0567717752756549, 0.0190204778986921, 
0.0422513858442951, 0.111582408843812, 0.138835889840082, 0.21339824217947, 
0.729583536573735, 3.70529170989959, 0.262144246780356, 1.76215378216399, 
0.45194783420955, 0.652770897344236, 0.175103869777577, 0.180827165111433, 
0.149986045752354, 0.078250630530394, -0.503692306815624, -0.0518757710132951, 
-0.7720261821134, 0.230585043415605, 0.238286642410781, 0.320867035994737, 
0.341897601437444, 1.01554348624006, 0.172666837990277, 0.195659415321763, 
0.275250666778578, 0.240727984134981, 0.120414986190179, 0.0499784682483759, 
0.472245297040454, 0.104676655828936, 0.0287722856085376, 0.0176405265599653, 
0.112742846133144, 0.536727671958966, 0.158628864629525, 0.249053357333556, 
0.0735945532304827, 0.220491560262896, 0.10065761739229, 0.106868350960601, 
0.0351261294462886, 0.024756402336487, 0.0500968227281999, 0.100621587712314, 
0.0994099839974206, 0.117422191328254, 0.196255904156231, 0.615740932499413, 
0.348900979430292, 0.6107802364305, 0.0711385744032832, 3.17434200160807, 
0.0770698571013883, 0.044781542425963, 0.0651289848871228, 0.522960777598145, 
0.0427368404505195, 0.0903472080918216, 0.0473480285521694, 0.0281690261202573, 
0.0437622507066264, 0.322846003344956, 0.249486005657788, 0.503910152388906, 
0.0922307421183483, 0.263683395313148, 0.112119581078771, 0.0771939700683992, 
0.0637837548667107, 0.0640645693989714, 0.115946896317165, 0.29285940668396, 
0.0819871921740071, 0.158225125109308, 1.47631790027665, 0.577337176451445, 
0.423030779686888, 0.583091780117988, 1.00631979209326, 0.249119378200487, 
0.0716808492041745, 0.125279429940534, 0.163728742042447, 0.141221312072172, 
0.227120730245417, 0.198268755638632, 0.178148560521089, 0.339700546788499, 
0.214932412610536, 0.846113543014675, 0.223683683260404, 0.25649590589947, 
0.20236288790552, 0.11449271907732, 0.34619179324506, 0.206588417811379, 
0.394153477424554, 0.139115738686944, 0.189847747578118, 0.409289820623734, 
0.105218979094217, 0.284981282281443, 0.185167183221172, 0.361676940451129, 
0.559006646817415, 0.411404702562887, 0.295589191726131, 0.466995747332065, 
0.0894203756610949, 0.10355319995995, 0.4528190349516, 1.2655722754218, 
0.413971025528504, 0.543815917154582, 0.3172523479049, 0.773505951446099, 
0.561232676847249, 0.0992567715411651, 0.0840863133063806, 0.155912772149331, 
0.386280498238424, 0.0964309305233621, 0.162406255439231, 0.324928301064751, 
0.747364334045819, 0.549962601182661, 0.780023161941572, 0.896321598958778, 
1.18397071677158, 0.4727385788393, 0.177869163908826, 0.344182472155594, 
0.185164266544749, 0.44325585361233, 0.478099517855447, 0.607626988528037, 
-5.42649506785895, 3.1974326206803, 0.210793888907286, 0.140132335912957, 
0.472193097965167, 0.375627777035389, 0.508787375520285, 0.257200409426199, 
0.51719993185793, 2.79975974916284, 0.373411675990249, 0.684439213942028, 
2.04893124152904, 0.249987037103979, 0.177936667892041, 0.0220059931985778, 
0.0368562868535418, 0.0139190300366638, 0.091264485018916, 0.0556375678109474, 
0.0574937686426698, 0.390702648133074, 0.161149919773346, 0.39071659195072, 
0.0911729736970256, 0.114122132445457, 0.059809385947336, 0.0690692822599624, 
0.147811597643456, 0.115206149482706, 0.220770909022354, 0.248060875311897, 
0.249599912989314, 0.295877163890425, 0.348918551990022, 0.401759441211746, 
0.357858675252015, 0.228734159861055, 0.177813408668261, 0.287670744460532, 
0.438257023888881, 0.577477248729184, 0.837542611539598, 0.368761845273496, 
0.194352392695252, 0.272894298426757, 0.377946613438861, 0.238380003601906, 
0.185350190234652, 0.400378066121696, 0.299319438421733, 0.846576987087597, 
0.428370894416693, 0.226304146697809, 0.390695644509826, 0.229708396137127, 
0.0611118816068114, 0.91457876988625, 0.095728312113845, 0.127681889262652, 
0.171824536213337, 0.403187707384889, 0.610042614504908, 0.312782338902465, 
0.244507783868094, 0.292719782293294, 0.268424089506829, -0.0194580339144448, 
0.0621316009701274, 0.16577592852773, 0.0687944942297927, 0.116483602629686, 
0.202753513321355, 0.169852461185515, 0.324193448122668, 0.571457369107324, 
1.02889746719941, 0.139785407048946, 0.182884727472834, 0.157731967042524, 
0.200148966355485, 0.187937681843115, 0.235740291955067, 0.299155619122651, 
0.450898347130522, 0.148582472164081, 0.151191356355559, 0.33502190907248, 
0.575308699339121, 0.116996496002553, 0.203937341691483, 0.201436878951163, 
0.211030344201144, 0.203406695986912, 0.422125040111421, 0.328151702287586, 
0.133383049502796, 0.0956029932414235, 0.0863348202021317, 0.171115079827406, 
0.0853988668263664, 0.381825222952706, 0.194666573181134, 0.340069988814446, 
0.265499688132498, 0.15553279496298, 0.175343652727323, 0.112702820053556, 
0.0747946305673376, 0.0898398268792042, 0.0992149751285673, 0.204679037575275, 
0.167387166933518, 0.199528198653895, 0.24917480383037, 0.697945489270061, 
0.418690870280069, 0.204814351431434, 0.333062633853687, 0.306854462429052, 
0.177225703485381, 0.210984212304347, 0.204743538636171, 0.382421365654316, 
0.353070831474964, 0.293958028013819, 0.23629692414499, 0.128709394571526, 
0.196379060018921, 0.209558893447537, 0.155932728321773, 0.204303270220047, 
0.51148777347979, 0.313643914120864, 0.212889643234266, 0.158634768395137, 
0.166974195513155, 0.557811007874943, 0.0201939780431773, 0.0268439367731074, 
0.031904654031849), capxflow = c(0.674669042001147, 2.32133189958842, 
0.686442381021085, 1.00547273633307, 3.23156579447241, 4.91713765135634, 
1.0908904958252, 1.37078867028165, 0.426656498401432, 0.228689312327981, 
0.160728845116356, 0.176293151455044, 0.111195338790864, 0.140997778877136, 
0.374374258561577, 0.645488665177115, 0.215701467802282, 1.10556419989591, 
2.22874778388712, 0.238793013969754, 1.00569451534229, 0.519553617406389, 
0.172625655775836, 0.416448249820551, 0.261297196729412, 0.165055768723438, 
0.272405345600507, -0.433717123539418, -0.104096139783908, 0.243861805348052, 
0.126392595263221, 0.229517848917083, 0.31078089034867, 0.203678303370509, 
0.381532973155766, 0.239483046205334, 0.166123962732639, 0.207447209595197, 
0.133600834065348, 0.110044077691042, 0.0887780386615917, 5.51175709962473, 
2.50371151819297, 1.17951238894775, 0.499215752037754, 0.69263872037833, 
1.07739772621495, 1.42869796454031, 1.03021096293871, 0.423396068788589, 
0.506745907345859, 0.766947897011993, 0.976178173886395, 0.554080433961121, 
0.467584227274847, 0.726135913507101, 0.940829766556758, 1.07483856660241, 
1.03787766823289, 1.46291579773822, 1.3724655551272, 1.47084599517601, 
4.0838441460566, 1.3539836627449, 16.8678636157103, 0.616962157332745, 
0.599720125999097, 0.891768242845906, 2.13638937787776, 0.929667771359825, 
0.405933299879614, 0.183726824245412, 0.293626718751842, 0.371173922886383, 
1.23895483354113, 1.38721076308122, 3.30001673278291, 1.90576748942574, 
2.47719353932462, 1.01119013981864, 0.675775607106753, 0.986980188626616, 
0.713523919931288, 0.708252113238862, 0.698405657024002, 0.488331511855499, 
0.571334807171476, 1.03710443945491, 0.584836713221358, 0.563431326449192, 
0.439254589106128, 0.629678073933394, 0.380448816178494, 0.360535103413359, 
0.406366607488468, 0.348812514778937, 0.392474014107413, 0.419684428963084, 
0.430244064852589, 0.362116539130246, 0.453295845983665, 0.382023288560576, 
0.417785970686493, 0.263879710033133, 0.327328861982949, 0.251256262229488, 
0.227220574230611, 0.263129663585824, 0.276021756477099, 0.341093555077119, 
0.399498863309831, 0.248973931802461, 0.336266740287336, 0.277382454280861, 
0.363774067788193, 0.25385858675691, 0.329694080210248, 0.340367277617789, 
0.368930038689754, 0.296526654961732, 0.235724971991443, 0.241121089887318, 
0.247619989132648, 1.24466190229879, 2.09625225176416, 1.29007348721071, 
1.43126172609954, 1.19287112003181, 1.54034260277211, 1.06251507729047, 
0.541057865233277, 0.493814370120483, 0.59441961313532, 0.483215197819897, 
0.430679890785408, 0.491461253874902, 0.632768903323093, 0.709428204772584, 
0.673085227393191, 0.681439166529088, 0.819562683043544, 0.839339132748499, 
0.617837952600833, 0.378170434824411, 0.456842781443454, 0.389315581556786, 
0.535373435106512, 0.508728514362511, 0.576609203741427, -3.94909973075138, 
1.36006151958819, 0.32526836123384, 0.270697117771131, 0.293280942295825, 
0.277745889337558, 0.398249060378554, 0.335669204524696, 0.378430005265991, 
1.16231281864664, 0.419425102342797, 0.332720566222572, 0.537822517316877, 
0.27196435282653, 0.239279662080572, 0.955579376514124, 0.92866830024385, 
0.868650438208687, 0.763587732292253, 0.905135875300624, 0.971456518335978, 
0.852560349555115, 0.729541316396448, 0.911429967096383, 0.765957600441541, 
0.619336730531254, 0.570482633841601, 0.489944947191041, 0.583522218561656, 
0.469402323820123, 0.607868841336602, 0.777793455924236, 0.682660641353534, 
0.690155340400227, 0.656596996633975, 1.05783352096639, 0.937543952820873, 
0.482388854541262, 0.3139470072256, 0.303934589782443, 0.34968482103963, 
0.420626456466545, 0.511635240589572, 0.320027050952749, 0.233955542979911, 
0.235783049741251, 0.290311760472029, 0.277144178770627, 0.298513259188479, 
0.377949175920778, 0.356397154597461, 0.495020670298752, 0.481316255645237, 
0.309455602402415, 0.36960684365732, 0.310318351897849, 1.03965428781311, 
1.7191894629249, 0.993220150465076, 0.657446870607463, 0.744361124116906, 
0.974289339033479, 0.89961744581222, 0.732329704561562, 0.740097606079667, 
1.08581503769204, 0.947885357013187, 0.686169978038163, 0.707827757275949, 
0.615805512066598, 0.508140028738697, 0.523626340545269, 0.601216882172947, 
0.562343004158574, 0.501856816802463, 0.542936101140767, 0.582605131551534, 
0.346380425478488, 0.357981787441717, 0.3122039966258, 0.274524024144509, 
0.344216108189033, 0.372694787174678, 0.47353876540056, 0.577649428129592, 
0.326918738776082, 0.233759869309685, 0.346765496832562, 0.439849106790811, 
0.341037496760665, 0.384028909911226, 0.439735139324212, 0.392772960896002, 
0.33804899760656, 0.352259052819956, 0.396760541160109, 0.708504882988932, 
1.11290264464168, 1.25809180832258, 1.31673456555969, 0.830741078855709, 
1.03338905633568, 0.907480643489563, 1.05684894379057, 0.774346476629271, 
0.759611370579782, 0.820246770900165, 0.83091691719578, 0.860888859825977, 
0.784997323885253, 0.72754850400878, 0.76486361323333, 0.739249627643551, 
0.743779020062453, 0.909249759341854, 1.40299849912686, 1.04994249870924, 
0.714103888196281, 0.939569093752098, 0.854252856776188, 0.588275751001638, 
0.543237264889554, 0.588265408395136, 0.765723668134315, 0.816084993616015, 
0.946277356430408, 0.667741917786016, 0.526502419227616, 0.652011505847518, 
0.891190397113626, 0.775990718268226, 0.9920654527098, 1.30471998217398, 
0.667838751750224, 0.5203979864599, 0.578548101670324, 0.624671856242137, 
1.00795183710696, 1.47758625093801, 1.19445812943161, 0.963913137374029
), payoutflow = c(0.118670387832306, 0.41425340205421, 0.183569676608199, 
0.166122726857993, 0.604535687149777, 0.631849178761906, 0.228367429225639, 
0.473728736451051, 0.23526557037835, 0.226882959399828, 0.196464281802805, 
0.0409517849282157, 0.0401179257999956, 0.0770073887516303, 0.280835690727294, 
0.272939522506465, 0.0692786230932961, 0.5045630308662, 0.958304397911748, 
0.162149658318999, 1.18362543247688, 0.417439236571484, 0.241487779206468, 
0.627506727862352, 0.345445759749153, 0.426806333052808, 0.903406610403315, 
-0.677117613035761, -0.0690310751889791, 0.081726643852221, 0.153753736038494, 
0.470950932093954, 0.266686978867725, 0.237039475024309, 0.568424982161016, 
0.878458505975849, 0.3934807272676, 0.448694983730159, 0.396668092148076, 
0.24977438177234, 0.240060320595776, 0.857203829055667, 0.350805995761216, 
0.186023701612354, 0.136895363720284, 0.189141407351954, 0.327729328069999, 
0.396267233855378, 0.500257572805394, 0.218218538638185, 0.188244892347315, 
0.194655154974607, 0.154009492875113, 0.100210777110154, 0.0767138842088544, 
0.0984015166020163, 0.257153842893199, 0.224204312715533, 0.281998874560534, 
0.462836966245217, 0.359898424608257, 0.35534199293624, 0.68509968986253, 
0.136687633977363, 1.84463075548471, 0.112670992503081, 0.104582031245567, 
0.152529681959348, 0.679848615291084, 0.213862084885452, 0.0386990548405042, 
0.059955934523449, 0.0599083798786307, 0.161973050915757, 0.538046125889393, 
0.865936444959124, 1.39350203105922, 0.740005573577454, 0.887871975371469, 
0.360217848565089, 0.194754266576631, 0.204330953534539, 0.304709538839604, 
0.320084954337527, 0.381754244528755, 0.315610190903717, 0.535418349146399, 
0.80344294859558, 0.57664415825856, 0.632542554085599, 0.443193599517348, 
0.587365336325132, 0.297258947201002, 0.319325013424532, 0.399919590342257, 
0.337602633713024, 0.402530513945828, 0.502349043025434, 0.539020008878608, 
0.524726609415782, 0.69281335764835, 0.639208730776798, 0.659400140169773, 
0.487550641543517, 0.58299889455976, 0.455048881304417, 0.446579070545945, 
0.544846357192936, 0.549475704344164, 0.8614181548844, 0.922695609498144, 
0.453815400251471, 0.840018382859402, 0.621409151879466, 0.874520820982227, 
0.642519007303733, 0.915166735426287, 0.883816237144334, 1.15530682731815, 
0.693040935935544, 0.546010232565771, 0.529299275693657, 0.559119575945112, 
0.292454071704792, 0.448702216322864, 0.401916987841568, 0.329179533494738, 
0.329001856099344, 0.467934928317715, 0.494379864856572, 0.227756520640882, 
0.274439059141163, 0.360190770800592, 0.266572693051812, 0.167706972260498, 
0.289525262524534, 0.256762077355597, 0.365782635807428, 0.305702123966848, 
0.309932505414207, 0.491662226489692, 0.414664639392058, 0.418742740756241, 
0.134872977653523, 0.236942941764083, 0.217104189094048, 0.42462384331737, 
0.509404101528062, 0.527496512143664, -9.35042299612067, 1.90000699039433, 
0.244861503770169, 0.262385488604234, 0.501301795314069, 0.413976486139711, 
0.644927718077783, 0.484011145872932, 0.521042001706226, 1.52463115100589, 
0.5041332748177, 0.417314890613121, 0.492562896588324, 0.257477560013847, 
0.207639725632275, 0.289148223658119, 0.249258465365325, 0.247826237223113, 
0.242982072161697, 0.291204514738391, 0.310847706385749, 0.455668077954126, 
0.488846334492026, 0.463495945937185, 0.386351511890368, 0.252791290077945, 
0.204653048974644, 0.199899508010732, 0.213817077188427, 0.162388185242991, 
0.248095669355316, 0.387081456817727, 0.433074736586545, 0.545357613633948, 
0.572912146731243, 0.768210935666996, 0.601747820808336, 0.487899464791981, 
0.421002941380517, 0.517510407562216, 1.07527648551673, 1.06424429280093, 
1.42527478440828, 0.718431301499033, 0.371636038321229, 0.498132453908202, 
0.616152811905293, 0.496826583777895, 0.682716508683874, 0.95982815813481, 
0.923705303406893, 1.0240846150811, 1.02741612454431, 0.90505595451174, 
0.95379859417449, 0.791033750340594, 0.284943826776339, 0.482883926859129, 
0.344740184203914, 0.238274206677599, 0.257862346661672, 0.393131874839924, 
0.393845108454458, 0.419351333260321, 0.306188578526756, 0.664527482794998, 
0.314029522887539, 0.20325165333506, 0.23720118622711, 0.265866567513767, 
0.203502038753787, 0.424634270047101, 0.330157929906979, 0.377820508566789, 
0.387436460151236, 0.517928575077592, 0.444779524637836, 0.318433311772051, 
0.27366962238141, 0.231011996456106, 0.290153890896768, 0.509197539475541, 
0.442799545883804, 0.746098848749629, 0.661616268180989, 0.270774693448042, 
0.27043489642781, 0.444605782234643, 0.401483573867151, 0.497528309804018, 
0.720469715088756, 0.899651381466097, 0.644264758670221, 0.604524628769428, 
0.636800889975366, 0.518906682911425, 0.831808142864483, 0.264098153423631, 
0.285644228249799, 0.352551368236518, 0.26910441665994, 0.4392798051296, 
0.465503949390099, 0.566723167416552, 0.408982592731701, 0.378911407206789, 
0.347137180754846, 0.357012887758946, 0.308697247889278, 0.299259218921417, 
0.306200065476286, 0.311331397262831, 0.311073615960404, 0.303349325617434, 
0.393660918969593, 0.715560799432053, 0.490202631336747, 0.343033891951275, 
0.403971769530315, 0.439010214833115, 0.343951942840524, 0.337347981382213, 
0.441076220210045, 0.61692946155064, 0.659196104867674, 0.563647306586165, 
0.323767465938854, 0.274823044897713, 0.414668435005828, 0.502337539156556, 
0.470729309410993, 0.583599288504489, 0.913204213591575, 0.582904881756153, 
0.413310971066658, 0.583542058373007, 0.560640477796128, 1.09285900001746, 
0.346152216543242, 0.30407276219861, 0.29281629613067)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -290L), groups = structure(list(
   newname = c("Construction", "ConsumerDurables", "ConsumerNonDurables", 
   "Healthcare", "Hitec", "Manufacturing", "Other", "Telecom"
   ), .rows = structure(list(1:41, 42:82, 83:123, 124:164, 165:205, 
       206:246, 247:287, 288:290), ptype = integer(0), class = c("vctrs_list_of", 
   "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -8L), .drop = TRUE))

下面的代码是：

df3 %>% 
  group_by(year_range = case_when(fyear %in% 1980:1990 ~ 1980, year %in% 1991:2001 ~ 1990, year %in% 2002:2007 ~ 2000, year %in% 2008:2020 ~ 2020), id) %>% 
  summarize(across(c(aqcflow,capxflow),  ~ mean(.), .names = "Average_{.col}"), .by = c(id, year_range)) %>% 
  pivot_wider(names_from = year_range, values_from = c(aqcflow, capxflow) %>% 
  left_join(df3 %>% 
              group_by(id) %>% 
              summarize(across(c(aqcflow, capxflow), mean, .names = "Average_total_{.col}")), 
            by = "id") %>% 
  arrange(desc(Average_total_value1))
r

2条答案

按热度按时间
i7uq4tfw

i7uq4tfw1#

首先需要group_by相关列。在summarise(across(...))中，需要为其提供一个函数，并在.names参数的帮助下，可以设置创建的新列名。然后使用pivot_wider将“long”整形为“wide”，以每个id生成一行。最后，left_join通过id和arrange将额外的两列与所有年份的平均值绑定在一起，并通过总值1的平均值绑定在一起。

library(tidyverse)

df %>% 
  group_by(year_range = ifelse(year %in% 1990:1995, 1995, 1996), id) %>% 
  summarize(across(value1:value2, mean, .names = "Average_{.col}")) %>% 
  pivot_wider(names_from = year_range, values_from = Average_value1:Average_value2) %>% 
  left_join(df %>% 
              group_by(id) %>% 
              summarize(across(value1:value2, mean, .names = "Average_total_{.col}")), 
            by = "id") %>% 
  arrange(desc(Average_total_value1))

# A tibble: 4 × 7
  id      Average_value1_1995 Average_value1_1996 Average_value2_1995 Average_value2_1996 Average_total_value1 Average_total_value2
  <chr>                 <dbl>               <dbl>               <dbl>               <dbl>                <dbl>                <dbl>
1 sector4                36.5                  42                 8.5                   3                   39                    6
2 sector3                25.5                  31                19.5                  14                   28                   17
3 sector2                14.5                  20                30.5                  25                   17                   28
4 sector1                 3.5                   9                41.5                  36                    6                   39

编辑更新请求

df3 %>% 
  group_by(year_range = case_when(fyear %in% 1980:1990 ~ 1980, fyear %in% 1991:2001 ~ 1990, fyear %in% 2002:2007 ~ 2000, fyear %in% 2008:2020 ~ 2020), newname) %>% 
  summarize(across(c(aqcflow,capxflow),  ~ mean(.), .names = "Average_{.col}")) %>% 
  pivot_wider(names_from = year_range, values_from = c(Average_aqcflow, Average_capxflow)) %>% 
  left_join(df3 %>% 
              group_by(newname) %>% 
              summarize(across(c(aqcflow, capxflow), mean, .names = "Average_total_{.col}")), 
            by = "newname") %>% 
  arrange(desc(Average_total_aqcflow))

# A tibble: 8 × 11
  newname             Average_aqcflow_1980 Average_aqcflow_1990 Average_aqcflow_2000 Average_aqcflow_2020 Average_capxflow_1980 Average_capxflow_1990 Average_capxflow_2000 Average_capxflow_2020 Average_total_aqcflow Average_total_capxflow
  <chr>                              <dbl>                <dbl>                <dbl>                <dbl>                 <dbl>                 <dbl>                 <dbl>                 <dbl>                 <dbl>                  <dbl>
1 Healthcare                        0.459                 0.521              -0.0858                0.678                 1.09                  0.612               -0.0965                 0.403                0.466                   0.570
2 ConsumerNonDurables               0.457                 0.248               0.253                 0.277                 0.620                 0.385                0.281                  0.303                0.314                   0.407
3 Construction                      0.201                 0.681               0.122                 0.183                 1.46                  0.615                0.142                  0.180                0.313                   0.636
4 Manufacturing                     0.318                 0.257               0.211                 0.268                 0.958                 0.562                0.356                  0.406                0.270                   0.588
5 Hitec                             0.130                 0.229               0.425                 0.343                 0.843                 0.684                0.397                  0.335                0.267                   0.574
6 ConsumerDurables                  0.189                 0.210               0.659                 0.149                 1.42                  1.29                 3.74                   1.17                 0.251                   1.64 
7 Other                             0.188                 0.249               0.273                 0.257                 0.973                 0.876                0.693                  0.781                0.239                   0.845
8 Telecom                           0.0263               NA                  NA                    NA                     1.21                 NA                   NA                     NA                    0.0263                  1.21
赞(0）分享  回复(0）举报  2023-04-27
c2e8gylq

c2e8gylq2#

以下是另一种tidyverse方法：
我们必须在按id, period)分组后使用summarise拆分一次，然后按id分组一次，然后使用bind_cols将它们放在一起：

library(dplyr) # >= 1.1.0
library(tidyr)

df %>% 
  mutate(period = ifelse(year <= 1995, "1990-1995", "1996-2000")) %>%
  summarise(across(c(value1, value2), ~mean(.), .names = "Average_{.col}"), .by = c(id, period)) %>% 
  pivot_wider(names_from = period, values_from = c(Average_value1, Average_value2)) %>% 
  arrange(SectorName) %>% 
  bind_cols(df %>%
              summarise(across(-year, mean), .by=id) %>% 
              arrange(desc(id)) %>% 
              select(-id)
            )
id      `Average_value1_1990-1995` `Average_value1_1996-2000` `Average_value2_1990-1995` `Average_value2_1996-2000` value1 value2
  <chr>                        <dbl>                      <dbl>                      <dbl>                      <dbl>  <dbl>  <dbl>
1 sector4                       36.5                         42                        8.5                          3     39      6
2 sector3                       25.5                         31                       19.5                         14     28     17
3 sector2                       14.5                         20                       30.5                         25     17     28
4 sector1                        3.5                          9                       41.5                         36      6     39
赞(0）分享  回复(0）举报  2023-04-27
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com