文章/答案/技术大牛

发布

社区首页 >问答首页 >在R中重新格式化数据

问在R中重新格式化数据
EN

Stack Overflow用户

提问于 2019-11-18 19:19:51

回答 2查看 106关注 0票数 1

我有多个科尔，并希望重新格式化的数据，以便有更少的科尔。

这是我的黄瓜df：

# Dataframe
df <- data.frame(
    ~Location, ~Product_Name, ~Category, ~Machine1, ~Machine2 ~Machine1_adds, ~Machine2_adds, ~Sales1, ~Saless2, Spoils1, Spoils2
    A,  "Snickers",   Candy,  0, 1,  $2.5, $3, 2, 1
    A,  "Kitcat",   Candy,  0, 1,  $3, $3, 2, 1
    A,  "Pepsi",   Bev,  1, 1,  $5, $4, 3, 0
    B,  "Coke",   Bev,  1, 0, $5,  $6.45, 1, 1
    B,  "Gatoraid",   Bev,  0, 1, $4,  $4.45, 1, 0
    B,  "Sprite",   Bev,  1, 1,  $8, $6, 1, 0
)
df

我想将数据帧重新格式化为机器是一台，销售是另一种，而宠坏是最后一种，每一行调整为新的。参考产出：

# Dataframe
new_df <- data.frame(
    ~Location, ~Product_Name, ~Category, ~Machine, ~Machine_adds, ~Sales, Spoils
    A,  "Snickers",   Candy, 1,  0,  $2.5, 2
    A,  "Kitcat",   Candy, 1,  0,  $3, 2
    A,  "Pepsi",   Bev,  1, 1,  $5, 3
    B,  "Coke",   Bev,  2, 1, $6.45, 1
    B,  "Gatoraid",   Bev, 2,  0,  $4.45, 1
    B,  "Sprite",   Bev,  2, 1,  $8, 0
)
new_df


I implemented the melt function but I am not getting the 'Machine' Column to represent which machine I am pulling from (either Machine 1, 2, 3 etc..).

dplyr

melt

dataframe

回答 2

Stack Overflow用户

回答已采纳

发布于 2019-11-18 19:38:53

下面是melt来自data.table的一个选项

library(data.table)
melt(setDT(df), measure = patterns("^Machine", "^Sales", "^Spoils"), 
      value.name = c("Machine_adds", "Sales", "Spoils"))[, variable := NULL][]
#    Location Product_Name Category Machine_adds Sales Spoils
# 1:        A     Snickers    Candy       0  $2.5      2
# 2:        A       Kitcat    Candy       0    $3      2
# 3:        A        Pepsi      Bev       1    $5      3
# 4:        B         Coke      Bev       1    $5      1
# 5:        B     Gatoraid      Bev       0    $4      1
# 6:        B       Sprite      Bev       1    $8      1
# 7:        A     Snickers    Candy       1    $3      1
# 8:        A       Kitcat    Candy       1    $3      1
# 9:        A        Pepsi      Bev       1    $4      0
#10:        B         Coke      Bev       0 $6.45      1
#11:        B     Gatoraid      Bev       1 $4.45      0
#12:        B       Sprite      Bev       1    $6      0

更新

根据OP的更新示例，如果有'Machine‘和Machine_adds’列，我们可以将patterns稍微更改为

# creating new columns in the dataset
df[c('Machine1', 'Machine2')] <- df[c("Machine1_adds", "Machine2_adds")]
melt(setDT(df), measure = patterns("^Machine\\d+$", 
         "^Machine\\d+_adds$", "^Sales", "^Spoils"), 
   value.name = c("Machine", "Machine_adds", "Sales", "Spoils"))[, 
           variable := NULL][]

或者使用来自tidyr的tidyr

library(dplyr)
library(tidyr)
library(stringr)
df %>% 
     rename_at(3:ncol(.), ~
           str_replace(., "(\\d+)_?.*", "_\\1")) %>% 
     pivot_longer(cols =  matches("^(Machine|Sales|Spoils)"),
        names_to = c(".value", "group"), names_sep = "_") %>%
     select(-group)
# A tibble: 12 x 6
#   Location Product_Name Category Machine Sales Spoils
#   <chr>    <chr>        <chr>      <dbl> <chr>  <dbl>
# 1 A        Snickers     Candy          0 $2.5       2
# 2 A        Snickers     Candy          1 $3         1
# 3 A        Kitcat       Candy          0 $3         2
# 4 A        Kitcat       Candy          1 $3         1
# 5 A        Pepsi        Bev            1 $5         3
# 6 A        Pepsi        Bev            1 $4         0
# 7 B        Coke         Bev            1 $5         1
# 8 B        Coke         Bev            0 $6.45      1
# 9 B        Gatoraid     Bev            0 $4         1
#10 B        Gatoraid     Bev            1 $4.45      0
#11 B        Sprite       Bev            1 $8         1
#12 B        Sprite       Bev            1 $6         0

更新

df %>%
   rename_at(vars(matches('^Machine.*adds$')), ~ 
         str_replace(., '(\\d+)_(\\w+)$', '_\\2\\1')) %>% 
   rename_at(3:ncol(.), ~ str_replace(., "(\\d+)_?.*", ":\\1")) %>%
   pivot_longer(cols =  matches("^(Machine|Sales|Spoils)"),
        names_to = c(".value", "group"), names_sep = ":") %>%
   select(-group)

数据

df <- structure(list(Location = c("A", "A", "A", "B", "B", "B"), 
     Product_Name = c("Snickers", 
"Kitcat", "Pepsi", "Coke", "Gatoraid", "Sprite"), Category = c("Candy", 
"Candy", "Bev", "Bev", "Bev", "Bev"), Machine1_adds = c(0, 0, 
1, 1, 0, 1), Machine2_adds = c(1, 1, 1, 0, 1, 1), Sales1 = c("$2.5", 
"$3", "$5", "$5", "$4", "$8"), Sales2 = c("$3", "$3", "$4", "$6.45", 
"$4.45", "$6"), Spoils1 = c(2, 2, 3, 1, 1, 1), Spoils2 = c(1, 
1, 0, 1, 0, 0)), row.names = c(NA, -6L), class = c("tbl_df", 
"tbl", "data.frame"))

票数 1

Stack Overflow用户

发布于 2019-11-18 19:47:11

根据您所拥有的内容，您正在寻找reshape函数，该函数的变量对齐为list(c(4,5),c(6,7),c(8,9))。你可以用：

reshape(df,t(matrix(4:ncol(df),2)),idvar = 1:3,dir="long")

或

reshape(df,list(c(4,5),c(6,7),c(8,9)),idvar = 1:3,dir="long")

要获得您的名称，我将使用v.names参数

reshape(df,list(c(4,5),c(6,7),c(8,9)),idvar = 1:3,dir="long",
         v.names = c("Machine_adds","Sales","Spoils"))[-4]# -4 removes the time variable.
                   Location Product_Name Category Machine_adds Sales Spoils
A.Snickers.Candy.1        A     Snickers    Candy            0  $2.5      2
A.Kitcat.Candy.1          A       Kitcat    Candy            0    $3      2
A.Pepsi.Bev.1             A        Pepsi      Bev            1    $5      3
B.Coke.Bev.1              B         Coke      Bev            1    $5      1
B.Gatoraid.Bev.1          B     Gatoraid      Bev            0    $4      1
B.Sprite.Bev.1            B       Sprite      Bev            1    $8      1
A.Snickers.Candy.2        A     Snickers    Candy            1    $3      1
A.Kitcat.Candy.2          A       Kitcat    Candy            1    $3      1
A.Pepsi.Bev.2             A        Pepsi      Bev            1    $4      0
B.Coke.Bev.2              B         Coke      Bev            0 $6.45      1
B.Gatoraid.Bev.2          B     Gatoraid      Bev            1 $4.45      0
B.Sprite.Bev.2            B       Sprite      Bev            1    $6      0

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/58921462

复制

相似问题

问在R中重新格式化数据
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在R中重新格式化数据EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在R中重新格式化数据
EN