setwd('D:/Users/GBD数据')
library(dplyr)
library(ggplot2)
EC <- read.csv('GBD_2021_DATAPI.csv',header = T)
region_order <- read.csv('region_order.csv',header = F)
EC$location <- factor(EC$location,
levels=region_order$V1,
ordered=TRUE)
#计算2021年ASPR
EC_2021_ASPR <- subset(EC,EC$year==2021 &
EC$age_name=='Age-standardized' &
EC$metric_name== 'Rate' &
EC$measure_name=='Prevalence')
EC_2021_ASPR$val <- round(EC_2021_ASPR$val,1)
EC_2021_ASPR$lower <- round(EC_2021_ASPR$lower,1)
EC_2021_ASPR$upper <- round(EC_2021_ASPR$upper,1)
EC_2021_ASPR$lower_to_upper <- paste(EC_2021_ASPR$lower,EC_2021_ASPR$upper,sep = ' to ')
EC_2021_ASPR$lower_to_upper <- paste('(',EC_2021_ASPR$lower_to_upper,')',sep = '')
EC_2021_ASPR$ASPR <- paste(EC_2021_ASPR$val,EC_2021_ASPR$lower_to_upper,sep = ' ')
#提取EAPC值
EC_1990to2021_ASPRchange <- read.csv('EAPC_outcome(Prevalence).csv',header = T)
#汇总
EC_2021_ASPR_Table1 <- EC_2021_ASPR[,c(4,18)]
EC_1990to2021_ASPRchange_Table1 <- EC_1990to2021_ASPRchange[,c(2,8)]
EC_Prelavence_Table1 <- left_join(EC_2021_ASPR_Table1,EC_1990to2021_ASPRchange_Table1,by="location")
write.csv(EC_Prelavence_Table1,"Prelavence_Table1.csv")
Hi @yuyu11
You will need to read the Posting Guide for this forum, and then post a reprex (a reproducible example) to get detailed help.
Thank you.Do you think it's okay to upload data like this?I want this data to be sorted by region. What should I do？
location ASPR
1 Global 116 (84.6 to 153.6)
2 East Asia 269.6 (195 to 362.1)
3 Southeast Asia 189.6 (130.4 to 262.4)
4 Oceania 136.6 (92.2 to 189.1)
5 Central Asia 127.6 (91.1 to 172.6)
6 Central Europe 181.7 (129.4 to 246.2)
7 Australasia 513.4 (371.5 to 682.5)
8 High-income Asia Pacific 110.3 (76.1 to 153.6)
9 Eastern Europe 203.6 (153.7 to 264.8)
10 Western Europe 187.8 (135.2 to 251.5)
11 Southern Latin America 72.4 (49.9 to 99.3)
12 High-income North America 334.2 (249.5 to 432.4)
13 Caribbean 51.2 (35.9 to 70.6)
14 Central Latin America 50.4 (35 to 69.2)
15 Tropical Latin America 174.8 (121.6 to 239.1)
16 Andean Latin America 68.9 (47.6 to 95.2)
17 North Africa and Middle East 37.1 (26.4 to 50.5)
18 South Asia 11.6 (8.1 to 15.9)
19 Central Sub-Saharan Africa 38.7 (26.7 to 54.3)
20 Eastern Sub-Saharan Africa 36.3 (25.4 to 51)
21 Southern Sub-Saharan Africa 113.5 (80.8 to 152.2)
22 Western Sub-Saharan Africa 33.7 (23.6 to 47.8)
23 High-middle SDI 185.5 (133.6 to 250.5)
24 High SDI 237.4 (177.5 to 305.3)
25 Low-middle SDI 40.7 (28.6 to 55.9)
26 Low SDI 30.1 (21 to 42.3)
27 Middle SDI 142.2 (102.8 to 192.4)
I pasted your data into a csv file and made an example of sorting it on the column location.
As an aside, it would be far easier to help you if you pasted your data as the output of the dput() function. If your data frame is named DF, run dput(DF) and paste the output of that into your post. The output of the dput function is code that others can use to reproduce the data.
library(tidyverse)
DF <- read_csv("~/R/Play/Dummy.csv")
#> Rows: 27 Columns: 2
#> ── Column specification ────────────────────────────────────────────────────────
#> Delimiter: ","
#> chr (2): location, ASPR
#>
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
DF
#> # A tibble: 27 × 2
#> location ASPR
#> <chr> <chr>
#> 1 Global 116 (84.6 to 153.6)
#> 2 East Asia 269.6 (195 to 362.1)
#> 3 Southeast Asia 189.6 (130.4 to 262.4)
#> 4 Oceania 136.6 (92.2 to 189.1)
#> 5 Central Asia 127.6 (91.1 to 172.6)
#> 6 Central Europe 181.7 (129.4 to 246.2)
#> 7 Australasia 513.4 (371.5 to 682.5)
#> 8 High-income Asia Pacific 110.3 (76.1 to 153.6)
#> 9 Eastern Europe 203.6 (153.7 to 264.8)
#> 10 Western Europe 187.8 (135.2 to 251.5)
#> # ℹ 17 more rows
DF <- DF |> arrange(location)
DF
#> # A tibble: 27 × 2
#> location ASPR
#> <chr> <chr>
#> 1 Andean Latin America 68.9 (47.6 to 95.2)
#> 2 Australasia 513.4 (371.5 to 682.5)
#> 3 Caribbean 51.2 (35.9 to 70.6)
#> 4 Central Asia 127.6 (91.1 to 172.6)
#> 5 Central Europe 181.7 (129.4 to 246.2)
#> 6 Central Latin America 50.4 (35 to 69.2)
#> 7 Central Sub-Saharan Africa 38.7 (26.7 to 54.3)
#> 8 East Asia 269.6 (195 to 362.1)
#> 9 Eastern Europe 203.6 (153.7 to 264.8)
#> 10 Eastern Sub-Saharan Africa 36.3 (25.4 to 51)
#> # ℹ 17 more rows
Created on 2024-11-30 with reprex v2.1.1
thank you very much.You used this example, but it wasn't sorted in the order I expected. Here are my stats and my order.
> dput(EC_Prelavence_Table1)
structure(list(location = c("Global", "East Asia", "Southeast Asia",
"Oceania", "Central Asia", "Central Europe", "Australasia", "High-income Asia Pacific",
"Eastern Europe", "Western Europe", "Southern Latin America",
"High-income North America", "Caribbean", "Central Latin America",
"Tropical Latin America", "Andean Latin America", "North Africa and Middle East",
"South Asia", "Central Sub-Saharan Africa", "Eastern Sub-Saharan Africa",
"Southern Sub-Saharan Africa", "Western Sub-Saharan Africa",
"High-middle SDI", "High SDI", "Low-middle SDI", "Low SDI", "Middle SDI"
), ASPR = c("116 (84.6 to 153.6)", "269.6 (195 to 362.1)", "189.6 (130.4 to 262.4)",
"136.6 (92.2 to 189.1)", "127.6 (91.1 to 172.6)", "181.7 (129.4 to 246.2)",
"513.4 (371.5 to 682.5)", "110.3 (76.1 to 153.6)", "203.6 (153.7 to 264.8)",
"187.8 (135.2 to 251.5)", "72.4 (49.9 to 99.3)", "334.2 (249.5 to 432.4)",
"51.2 (35.9 to 70.6)", "50.4 (35 to 69.2)", "174.8 (121.6 to 239.1)",
"68.9 (47.6 to 95.2)", "37.1 (26.4 to 50.5)", "11.6 (8.1 to 15.9)",
"38.7 (26.7 to 54.3)", "36.3 (25.4 to 51)", "113.5 (80.8 to 152.2)",
"33.7 (23.6 to 47.8)", "185.5 (133.6 to 250.5)", "237.4 (177.5 to 305.3)",
"40.7 (28.6 to 55.9)", "30.1 (21 to 42.3)", "142.2 (102.8 to 192.4)"
), EAPC_CI = c("-2.05\n(-2.2 to -1.89)", "-2.31\n(-2.63 to -1.99)",
"-0.16\n(-0.23 to -0.1)", "-0.01\n(-0.03 to 0.02)", "0.35\n(0.32 to 0.37)",
"0.66\n(0.6 to 0.73)", "-0.33\n(-0.71 to 0.05)", "-0.07\n(-0.12 to -0.02)",
"0.02\n(-0.19 to 0.24)", "0.34\n(0.25 to 0.44)", "-0.1\n(-0.22 to 0.01)",
"-0.04\n(-0.89 to 0.81)", "-0.16\n(-0.34 to 0.02)", "0.39\n(0.08 to 0.7)",
"-0.21\n(-0.3 to -0.12)", "0.24\n(0.2 to 0.27)", "0.44\n(0.34 to 0.54)",
"0.12\n(0.09 to 0.14)", "0.07\n(0.06 to 0.09)", "0.02\n(0 to 0.03)",
"-0.48\n(-0.81 to -0.15)", "-0.08\n(-0.12 to -0.05)", "-1.64\n(-1.82 to -1.46)",
"0.04\n(-0.28 to 0.36)", "-0.37\n(-0.41 to -0.32)", "0.1\n(0.08 to 0.12)",
"-2.48\n(-2.7 to -2.26)")), class = "data.frame", row.names = c(NA,
-27L))
>
```Next is the order I want to be arranged
> dput(region_order)
structure(list(V1 = c("Global", "Low SDI", "Low-middle SDI",
"Middle SDI", "High-middle SDI", "High SDI", "Andean Latin America",
"Australasia", "Caribbean", "Central Asia", "Central Europe",
"Central Latin America", "Central Sub-Saharan Africa", "East Asia",
"Eastern Europe", "Eastern Sub-Saharan Africa", "High-income Asia Pacific",
"High-income North America", "North Africa and Middle East",
"Oceania", "South Asia", "Southeast Asia", "Southern Latin America",
"Southern Sub-Saharan Africa", "Tropical Latin America", "Western Europe",
"Western Sub-Saharan Africa")), class = "data.frame", row.names = c(NA,
-27L))
>
If you create "region" as an ordered factor, you can sort (=order) the dataframe as you require:
library(stringr)
#dput(EC_Prelavence_Table1)
EC_Prelavence_Table1 <- structure(list(location = c("Global", "East Asia", "Southeast Asia",
"Oceania", "Central Asia", "Central Europe", "Australasia", "High-income Asia Pacific",
"Eastern Europe", "Western Europe", "Southern Latin America",
"High-income North America", "Caribbean", "Central Latin America",
"Tropical Latin America", "Andean Latin America", "North Africa and Middle East",
"South Asia", "Central Sub-Saharan Africa", "Eastern Sub-Saharan Africa",
"Southern Sub-Saharan Africa", "Western Sub-Saharan Africa",
"High-middle SDI", "High SDI", "Low-middle SDI", "Low SDI", "Middle SDI"
), ASPR = c("116 (84.6 to 153.6)", "269.6 (195 to 362.1)", "189.6 (130.4 to 262.4)",
"136.6 (92.2 to 189.1)", "127.6 (91.1 to 172.6)", "181.7 (129.4 to 246.2)",
"513.4 (371.5 to 682.5)", "110.3 (76.1 to 153.6)", "203.6 (153.7 to 264.8)",
"187.8 (135.2 to 251.5)", "72.4 (49.9 to 99.3)", "334.2 (249.5 to 432.4)",
"51.2 (35.9 to 70.6)", "50.4 (35 to 69.2)", "174.8 (121.6 to 239.1)",
"68.9 (47.6 to 95.2)", "37.1 (26.4 to 50.5)", "11.6 (8.1 to 15.9)",
"38.7 (26.7 to 54.3)", "36.3 (25.4 to 51)", "113.5 (80.8 to 152.2)",
"33.7 (23.6 to 47.8)", "185.5 (133.6 to 250.5)", "237.4 (177.5 to 305.3)",
"40.7 (28.6 to 55.9)", "30.1 (21 to 42.3)", "142.2 (102.8 to 192.4)"
), EAPC_CI = c("-2.05\n(-2.2 to -1.89)", "-2.31\n(-2.63 to -1.99)",
"-0.16\n(-0.23 to -0.1)", "-0.01\n(-0.03 to 0.02)", "0.35\n(0.32 to 0.37)",
"0.66\n(0.6 to 0.73)", "-0.33\n(-0.71 to 0.05)", "-0.07\n(-0.12 to -0.02)",
"0.02\n(-0.19 to 0.24)", "0.34\n(0.25 to 0.44)", "-0.1\n(-0.22 to 0.01)",
"-0.04\n(-0.89 to 0.81)", "-0.16\n(-0.34 to 0.02)", "0.39\n(0.08 to 0.7)",
"-0.21\n(-0.3 to -0.12)", "0.24\n(0.2 to 0.27)", "0.44\n(0.34 to 0.54)",
"0.12\n(0.09 to 0.14)", "0.07\n(0.06 to 0.09)", "0.02\n(0 to 0.03)",
"-0.48\n(-0.81 to -0.15)", "-0.08\n(-0.12 to -0.05)", "-1.64\n(-1.82 to -1.46)",
"0.04\n(-0.28 to 0.36)", "-0.37\n(-0.41 to -0.32)", "0.1\n(0.08 to 0.12)",
"-2.48\n(-2.7 to -2.26)")), class = "data.frame", row.names = c(NA,
-27L))
# Next is the order I want to be arranged
region_order <- structure(list(V1 = c("Global", "Low SDI", "Low-middle SDI",
"Middle SDI", "High-middle SDI", "High SDI", "Andean Latin America",
"Australasia", "Caribbean", "Central Asia", "Central Europe",
"Central Latin America", "Central Sub-Saharan Africa", "East Asia",
"Eastern Europe", "Eastern Sub-Saharan Africa", "High-income Asia Pacific",
"High-income North America", "North Africa and Middle East",
"Oceania", "South Asia", "Southeast Asia", "Southern Latin America",
"Southern Sub-Saharan Africa", "Tropical Latin America", "Western Europe",
"Western Sub-Saharan Africa")), class = "data.frame", row.names = c(NA,
-27L))
str(EC_Prelavence_Table1)
#> 'data.frame': 27 obs. of 3 variables:
#> $ location: chr "Global" "East Asia" "Southeast Asia" "Oceania" ...
#> $ ASPR : chr "116 (84.6 to 153.6)" "269.6 (195 to 362.1)" "189.6 (130.4 to 262.4)" "136.6 (92.2 to 189.1)" ...
#> $ EAPC_CI : chr "-2.05\n(-2.2 to -1.89)" "-2.31\n(-2.63 to -1.99)" "-0.16\n(-0.23 to -0.1)" "-0.01\n(-0.03 to 0.02)" ...
# Use the region order to create an ordered factor
EC_Prelavence_Table1$region <- factor(EC_Prelavence_Table1$location,
ordered=TRUE,
levels = region_order$V1)
str(EC_Prelavence_Table1)
#> 'data.frame': 27 obs. of 4 variables:
#> $ location: chr "Global" "East Asia" "Southeast Asia" "Oceania" ...
#> $ ASPR : chr "116 (84.6 to 153.6)" "269.6 (195 to 362.1)" "189.6 (130.4 to 262.4)" "136.6 (92.2 to 189.1)" ...
#> $ EAPC_CI : chr "-2.05\n(-2.2 to -1.89)" "-2.31\n(-2.63 to -1.99)" "-0.16\n(-0.23 to -0.1)" "-0.01\n(-0.03 to 0.02)" ...
#> $ region : Ord.factor w/ 27 levels "Global"<"Low SDI"<..: 1 14 22 20 10 11 8 17 15 26 ...
new <- EC_Prelavence_Table1[order(EC_Prelavence_Table1$region), ]
new <- new[,1:3]
new$EAPC_CI <- str_replace(new$EAPC_CI, "\\\n", " ")
new
#> location ASPR EAPC_CI
#> 1 Global 116 (84.6 to 153.6) -2.05 (-2.2 to -1.89)
#> 26 Low SDI 30.1 (21 to 42.3) 0.1 (0.08 to 0.12)
#> 25 Low-middle SDI 40.7 (28.6 to 55.9) -0.37 (-0.41 to -0.32)
#> 27 Middle SDI 142.2 (102.8 to 192.4) -2.48 (-2.7 to -2.26)
#> 23 High-middle SDI 185.5 (133.6 to 250.5) -1.64 (-1.82 to -1.46)
#> 24 High SDI 237.4 (177.5 to 305.3) 0.04 (-0.28 to 0.36)
#> 16 Andean Latin America 68.9 (47.6 to 95.2) 0.24 (0.2 to 0.27)
#> 7 Australasia 513.4 (371.5 to 682.5) -0.33 (-0.71 to 0.05)
#> 13 Caribbean 51.2 (35.9 to 70.6) -0.16 (-0.34 to 0.02)
#> 5 Central Asia 127.6 (91.1 to 172.6) 0.35 (0.32 to 0.37)
#> 6 Central Europe 181.7 (129.4 to 246.2) 0.66 (0.6 to 0.73)
#> 14 Central Latin America 50.4 (35 to 69.2) 0.39 (0.08 to 0.7)
#> 19 Central Sub-Saharan Africa 38.7 (26.7 to 54.3) 0.07 (0.06 to 0.09)
#> 2 East Asia 269.6 (195 to 362.1) -2.31 (-2.63 to -1.99)
#> 9 Eastern Europe 203.6 (153.7 to 264.8) 0.02 (-0.19 to 0.24)
#> 20 Eastern Sub-Saharan Africa 36.3 (25.4 to 51) 0.02 (0 to 0.03)
#> 8 High-income Asia Pacific 110.3 (76.1 to 153.6) -0.07 (-0.12 to -0.02)
#> 12 High-income North America 334.2 (249.5 to 432.4) -0.04 (-0.89 to 0.81)
#> 17 North Africa and Middle East 37.1 (26.4 to 50.5) 0.44 (0.34 to 0.54)
#> 4 Oceania 136.6 (92.2 to 189.1) -0.01 (-0.03 to 0.02)
#> 18 South Asia 11.6 (8.1 to 15.9) 0.12 (0.09 to 0.14)
#> 3 Southeast Asia 189.6 (130.4 to 262.4) -0.16 (-0.23 to -0.1)
#> 11 Southern Latin America 72.4 (49.9 to 99.3) -0.1 (-0.22 to 0.01)
#> 21 Southern Sub-Saharan Africa 113.5 (80.8 to 152.2) -0.48 (-0.81 to -0.15)
#> 15 Tropical Latin America 174.8 (121.6 to 239.1) -0.21 (-0.3 to -0.12)
#> 10 Western Europe 187.8 (135.2 to 251.5) 0.34 (0.25 to 0.44)
#> 22 Western Sub-Saharan Africa 33.7 (23.6 to 47.8) -0.08 (-0.12 to -0.05)
Created on 2024-12-01 with reprex v2.1.1
1 Like
very grateful!It worked
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
If you have a query related to it or one of the replies, start a new topic and refer back with a link.