如何旋转R中带有节和子节的列组成的数据框 [英] How to pivoting dataframe consisting column with section and sub section In R
问题描述
我有一个下面提到的数据框:
I have a below-mentioned dataframe:
structure(
list(ID = c("P-1", " P-1", "P-1", "P-2", "P-3", "P-4", "P-5", "P-6", "P-7",
"P-8"),
Date = c("2020-03-16 12:11:33", "2020-03-16 13:16:04",
"2020-03-16 06:13:55", "2020-03-16 10:03:43",
"2020-03-16 12:37:09", "2020-03-16 06:40:24",
"2020-03-16 09:46:45", "2020-03-16 12:07:44",
"2020-03-16 14:09:51", "2020-03-16 09:19:23"),
Status = c("SA", "SA", "SA", "RE", "RE", "RE", "RE", "XA", "XA", "XA"),
Flag = c("L", "L", "L", NA, "K", "J", NA, NA, "H", "G"),
Value = c(5929.81, 5929.81, 5929.81, NA, 6969.33, 740.08, NA, NA, 1524.8,
NA),
Flag2 = c("CL", "CL", "CL", NA, "RY", "", NA, NA, "", NA),
Flag3 = c(NA, NA, NA, NA, "RI", "PO", NA, "SS", "DDP", NA)),
.Names=c("ID", "Date", "Status", "Flag", "Value", "Flag2", "Flag3"),
row.names=c(NA, 10L), class="data.frame")
我正在使用以下代码:
df %>% mutate(L = ifelse(Flag == "L",1,0),
K = ifelse(Flag == "K",1,0),
# etc for Flag) %>%
mutate(sub_status = NA) %>%
mutate(sub_status = ifelse(!is.na(Flag2) & Flag3 == 0, "a", sub_status),
sub_status = ifelse(is.na(Flag2) & Flag3 != 0, "b", sub_status),
# etc for sub-status) %>%
mutate(value_class = ifelse(0 <= Value & Value <= 15000, "0-15000",
"15000-50000")) %>%
group_by(Date, status, sub_status, value_class) %>%
summarise(L = sum(L),
K = sum(K),
# etc
count = n())
向我提供以下输出:
Date Status sub_status value_class G H I J K L NA Count
2020-03-20 SA a 0-15000 0 0 0 0 1 1 0 2
2020-03-20 SA b 0-15000 0 0 0 0 1 0 0 1
................
................
我想使用<$ c $获得以下输出c> DF ,其中 Status
列具有不同的3个值,而 Flag2
具有值或[null]或NA,最后 Flag3
列具有不同的7 [null]或NA的值。对于一个不同的 ID
,我们可以多次输入 Flag3
列。
I want to get the following output using the DF
, where the Status
column has distinct 3 values and Flag2
has either values or [null] or NA and finally Flag3
column has distinct 7 values with [null] or NA. For one distinct ID
we have multiple entry of Flag3
column.
我需要通过创建一个基于 Value
的3组(如0-15000、15000-50000)来创建以下数据框。
I Need to create the following dataframe, by creating a 3 group based on Value
like 0-15000, 15000-50000.
- 如果对于不同的ID,
标志2
的值不是0或[null] / NA,但Flag3
的值为0或[null] / NA,则它将为a
。 - 如果唯一ID
Flag3
的值不是0或[null] / NA,但Flag2
的值是0或[null ] / NA,则为b
- 如果对于唯一ID,两个
Flag2
&Flag3
的值不是0或[Null] / NA,那么它将是c
- 如果对于唯一ID,则同时
Flag2
&Flag3
的值为0或[Null] / NA,将是d
- If for a distinct ID
Flag2
has some value other than 0 or [null]/NA butFlag3
has value 0 or [null]/NA then it would bea
. - If for a distinct ID
Flag3
has some value other than 0 or [null]/NA butFlag2
has value 0 or [null]/NA then it would beb
- If for a distinct ID both
Flag2
&Flag3
has some value other than 0 or [Null]/NA then it would bec
- If for a distinct ID both
Flag2
&Flag3
has value 0 or [Null]/NA the it would bed
我要在以下结构中使用%
和 Total
列。
I want to arrange the above mentioned datafrmae in the following structure with percent
and Total
column.
我提到过像 2/5
这样的百分比,它可以表明状态除以总数,而 sub_status
除以它们各自的 Status
。
I have mentioned the percentage like 2/5
to show that status would be divided by the Total whereas sub_status
would be divided by their respective Status
.
16/03/2020 0 - 15000 15000 - 50000
Status count percent L K J H G [Null] count percent L K J H G [Null] Total
SA 1 1/8 (12.50%) 1 0 0 0 0 0 0 - 0 0 0 0 0 0 1
a 1 1/1(100.00%) 1 0 0 0 0 0 0 - 0 0 0 0 0 0 1
b 0 - 0 0 0 0 0 0 0 - 0 0 0 0 0 0 0
c 0 - 1 0 0 0 0 0 0 - 0 0 0 0 0 0 0
d 0 - 0 0 0 0 0 0 0 - 0 0 0 0 0 0 0
RE 4 50.00% 0 1 1 0 0 2 0 - 0 0 0 0 0 0 4
a 0 - 0 0 0 0 0 0 0 - 0 0 0 0 0 0 0
b 1 25.00% 0 0 1 0 0 1 0 - 0 0 0 0 0 0 1
c 1 25.00% 0 1 0 0 0 1 0 - 0 0 0 0 0 0 1
d 2 50.00% 0 0 0 0 0 2 0 - 0 0 0 0 0 0 2
XA 3 37.50% 0 0 0 1 1 1 0 - 0 0 0 0 0 0 3
a 0 - 0 0 0 0 0 0 0 - 0 0 0 0 0 0 0
b 2 66.67% 0 0 0 1 0 1 0 - 0 0 0 0 0 0 2
c 0 - 0 0 0 0 0 0 0 - 0 0 0 0 0 0 0
d 1 33.33% 0 0 0 0 1 0 0 - 0 0 0 0 0 0 1
Total 8 100.00% 1 1 0 0 1 3 0 - 0 0 0 0 0 0 8
我已经提到了如果数据框没有按照 startdate
的最新日期,则基于最新日期16/03/2020的必需输出,将所有值0保留在输出数据框中。百分比列仅供参考,将计算百分比值。
I have mentioned the required output based on the latest date which is 16/03/2020, if the dataframe doesn't have the latest date as per startdate
keep all the value 0 in the output dataframe. The percentage column is just for the reference there will be calculated percentage values,.
此外,我想保持结构静态。例如,如果一天中不存在任何参数,则输出结构将与0值相同。
Also, I want to keep the structure static. For Example, if for any of the parameter are not present for a day the output structure would be same with 0 value.
例如,假设日期 17/03/2020
没有状态为 SA
或sub_status c
该占位符将出现在输出中,值为 0
。
For Example, Suppose date 17/03/2020
don't have any row with status SA
or sub_status c
the place holder for that will be there in the output with value as 0
.
推荐答案
希望这足以使您入门,进一步,我需要一个看起来像R的预期输出,并进一步说明如何计算变量。
Hopefully that'll be enough to get you started, to go further, I'll need an expected output that looks like it comes from R, and further explanations as to how variables are computed.
library(tidyverse)
df <- structure(
list(ID = c("P-1", " P-1", "P-1", "P-2", "P-3", "P-4", "P-5", "P-6", "P-7",
"P-8"),
Date = c("2020-03-16 12:11:33", "2020-03-16 13:16:04",
"2020-03-16 06:13:55", "2020-03-16 10:03:43",
"2020-03-16 12:37:09", "2020-03-16 06:40:24",
"2020-03-16 09:46:45", "2020-03-16 12:07:44",
"2020-03-16 14:09:51", "2020-03-16 09:19:23"),
Status = c("SA", "SA", "SA", "RE", "RE", "RE", "RE", "XA", "XA", "XA"),
Flag = c("L", "L", "L", NA, "K", "J", NA, NA, "H", "G"),
Value = c(5929.81, 5929.81, 5929.81, NA, 6969.33, 740.08, NA, NA, 1524.8,
NA),
Flag2 = c("CL", "CL", "CL", NA, "RY", "", NA, NA, "", NA),
Flag3 = c(NA, NA, NA, NA, "RI", "PO", NA, "SS", "DDP", NA)),
.Names=c("ID", "Date", "Status", "Flag", "Value", "Flag2", "Flag3"),
row.names=c(NA, 10L), class="data.frame")
df2 <- df %>%
mutate(
# add variables
Value = ifelse(0 <= Value & Value <= 15000, "0-15000", "15000-50000"),
substatus = case_when(
!is.na(Flag2) & is.na(Flag3) ~ "a",
!is.na(Flag3) & is.na(Flag2) ~ "b",
!is.na(Flag3) & !is.na(Flag2) ~ "c",
TRUE ~ "d"),
# make Date an actual date rather than a timestamp
Date = as.Date(Date),
# remove obsolete columns
Flag2 = NULL,
Flag3 = NULL,
ID = NULL,
# renames NAs into the name of the desired column
Flag = ifelse(is.na(Flag), "[Null]", Flag),
# create column of 1 for pivot
temp = 1,
# and row id
id = row_number()
) %>%
# create new columns L K etc, this also drops the Flag col
pivot_wider(names_from = "Flag", values_from = "temp", values_fill = list(temp=0)) %>%
# move `[Null]` column to the end
select(everything(), -`[Null]`, `[Null]`) %>%
mutate(
id = NULL,
count = 1,
Total = rowSums(select(., L:`[Null]`)))
df2
#> # A tibble: 10 x 12
#> Date Status Value substatus L K J H G `[Null]`
#> <date> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2020-03-16 SA 0-15~ a 1 0 0 0 0 0
#> 2 2020-03-16 SA 0-15~ a 1 0 0 0 0 0
#> 3 2020-03-16 SA 0-15~ a 1 0 0 0 0 0
#> 4 2020-03-16 RE <NA> d 0 0 0 0 0 1
#> 5 2020-03-16 RE 0-15~ c 0 1 0 0 0 0
#> 6 2020-03-16 RE 0-15~ c 0 0 1 0 0 0
#> 7 2020-03-16 RE <NA> d 0 0 0 0 0 1
#> 8 2020-03-16 XA <NA> b 0 0 0 0 0 1
#> 9 2020-03-16 XA 0-15~ c 0 0 0 1 0 0
#> 10 2020-03-16 XA <NA> d 0 0 0 0 1 0
#> # ... with 2 more variables: count <dbl>, Total <dbl>
# As you didn't tell what to do with NA values so I left them as NA
bind_rows(
df2 %>%
# add missing combinations of abcd
complete(nesting(Date, Status, Value), substatus) %>%
group_by(Date, Value, Status, substatus) %>%
summarize_all(~sum(., na.rm=TRUE)) %>%
group_by(Status, Value) %>%
mutate(percent = paste(round(100 * Total / sum(Total), 2), "%")) %>%
ungroup(),
df2 %>%
mutate(substatus = Status, Status = paste0(Status, "_")) %>%
group_by(Date, Value, Status, substatus) %>%
mutate(count = n()) %>%
group_by(count, add = TRUE) %>%
summarize_all(~sum(., na.rm=TRUE)) %>%
group_by(Value) %>%
mutate(percent = paste(round(100 * Total / sum(Total), 2), "%"))
) %>%
arrange(Date, Value, desc(Status)) %>%
mutate(Status = NULL) %>%
rename(Status = substatus) %>%
print(n=Inf)
#> # A tibble: 25 x 12
#> Date Value Status L K J H G `[Null]` count Total
#> <date> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2020-03-16 0-15~ XA 0 0 0 1 0 0 1 1
#> 2 2020-03-16 0-15~ a 0 0 0 0 0 0 0 0
#> 3 2020-03-16 0-15~ b 0 0 0 0 0 0 0 0
#> 4 2020-03-16 0-15~ c 0 0 0 1 0 0 1 1
#> 5 2020-03-16 0-15~ d 0 0 0 0 0 0 0 0
#> 6 2020-03-16 0-15~ SA 3 0 0 0 0 0 3 3
#> 7 2020-03-16 0-15~ a 3 0 0 0 0 0 3 3
#> 8 2020-03-16 0-15~ b 0 0 0 0 0 0 0 0
#> 9 2020-03-16 0-15~ c 0 0 0 0 0 0 0 0
#> 10 2020-03-16 0-15~ d 0 0 0 0 0 0 0 0
#> 11 2020-03-16 0-15~ RE 0 1 1 0 0 0 2 2
#> 12 2020-03-16 0-15~ a 0 0 0 0 0 0 0 0
#> 13 2020-03-16 0-15~ b 0 0 0 0 0 0 0 0
#> 14 2020-03-16 0-15~ c 0 1 1 0 0 0 2 2
#> 15 2020-03-16 0-15~ d 0 0 0 0 0 0 0 0
#> 16 2020-03-16 <NA> XA 0 0 0 0 1 1 2 2
#> 17 2020-03-16 <NA> a 0 0 0 0 0 0 0 0
#> 18 2020-03-16 <NA> b 0 0 0 0 0 1 1 1
#> 19 2020-03-16 <NA> c 0 0 0 0 0 0 0 0
#> 20 2020-03-16 <NA> d 0 0 0 0 1 0 1 1
#> 21 2020-03-16 <NA> RE 0 0 0 0 0 2 2 2
#> 22 2020-03-16 <NA> a 0 0 0 0 0 0 0 0
#> 23 2020-03-16 <NA> b 0 0 0 0 0 0 0 0
#> 24 2020-03-16 <NA> c 0 0 0 0 0 0 0 0
#> 25 2020-03-16 <NA> d 0 0 0 0 0 2 2 2
#> # ... with 1 more variable: percent <chr>
这篇关于如何旋转R中带有节和子节的列组成的数据框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!