如何在pivot_wider中配对列

fzwojiic 于 2023-06-03 发布在其他

关注(0)|答案(2)|浏览(263)

我有一个df如下

structure(list(ProductAccountNumber = c(17397841L, 17397841L, 
                                        17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 
                                        17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 
                                        17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 
                                        17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 
                                        17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 17397841L, 
                                        17397841L), OriginalDate = c("2009-04-14", "2009-04-14", "2009-04-14", 
                                                                     "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", 
                                                                     "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", 
                                                                     "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", 
                                                                     "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", 
                                                                     "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", 
                                                                     "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14", "2009-04-14"
                                        ), OriginalFICO = c(810L, 810L, 810L, 810L, 810L, 810L, 810L, 
                                                            810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 
                                                            810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 810L, 
                                                            810L, 810L, 810L, 810L), RefreshFICODate = structure(c(1350273600, 
                                                                                                                   1373860800, 1428292800, 1439524800, 1444363200, 1462939200, 1468900800, 
                                                                                                                   1476676800, 1487653200, 1493265600, 1515646800, 1517893200, 1524024000, 
                                                                                                                   1531800000, 1541131200, 1550034000, 1555300800, 1568088000, 1575435600, 
                                                                                                                   1579669200, 1587441600, 1595563200, 1604293200, 1611723600, 1618459200, 
                                                                                                                   1626926400, 1634961600, 1642050000, 1650859200, 1657944000, 1666584000, 
                                                                                                                   1674018000, 1681272000), class = c("POSIXct", "POSIXt"), tzone = ""), 
               RefreshFICO = c(831L, 767L, 809L, 827L, 838L, 827L, 829L, 
                               829L, 829L, 840L, 829L, 836L, 848L, 827L, 836L, 836L, 848L, 
                               813L, 848L, 824L, 818L, 818L, 840L, 857L, 805L, 826L, 826L, 
                               832L, 826L, 826L, 826L, 899L, 864L), RefreshFICODate2 = structure(c(1341460800, 
                                                                                                   1365739200, 1373860800, 1428292800, 1439524800, 1444363200, 
                                                                                                   1462939200, 1468900800, 1476676800, 1487653200, 1493265600, 
                                                                                                   1515646800, 1517893200, 1524024000, 1531800000, 1541131200, 
                                                                                                   1550034000, 1555300800, 1568088000, 1575435600, 1579669200, 
                                                                                                   1587441600, 1595563200, 1604293200, 1611723600, 1618459200, 
                                                                                                   1626926400, 1634961600, 1642050000, 1650859200, 1657944000, 
                                                                                                   1666584000, 1674018000), class = c("POSIXct", "POSIXt"), tzone = ""), 
               RefreshFICO2 = c(827L, 815L, 767L, 809L, 827L, 838L, 827L, 
                                829L, 829L, 829L, 840L, 829L, 836L, 848L, 827L, 836L, 836L, 
                                848L, 813L, 848L, 824L, 818L, 818L, 840L, 857L, 805L, 826L, 
                                826L, 832L, 826L, 826L, 826L, 899L)), row.names = c(NA, 33L
                                ), class = "data.frame")

我想转换它，使所有的日期从OriginalDate/RefreshFICODateX成为列名和列的值来自OriginalFICO/RefreshFICOX。
| 产品帐号|2009年4月14日|2012-10-15 2012-10-15 2012-10-15| 2012-07-05 2012-07-05|
| - -----|- -----|- -----|- -----|
| 17397841|八一零|八三一|八二七|
我可以一次用一组原始/刷新相当容易地做到这一点，但有10个，必须有一个更简单的方法。是否可以告诉pivot_wider之类的东西在某种意义上“配对”列。

r

来源：https://stackoverflow.com/questions/76342796/how-to-pair-columns-in-pivot-wider

2条答案

按热度按时间

nnsrf1az1#

df %>%
  pivot_longer(-ProductAccountNumber, 
               names_to = c("grp", ".value"),
               names_pattern = "(Original|Refresh).*(Date|FICO)",
               values_transform =list(Date = as.Date)) %>%
  distinct() %>%
  pivot_wider(id_cols = 1, names_from = Date, values_from = FICO)

# A tibble: 1 × 37
  Produ…¹ 2009-…² 2012-…³ 2012-…⁴ 2013-…⁵ 2013-…⁶ 2015-…⁷ 2015-…⁸ 2015-…⁹ 2016-…˟ 2016-…˟ 2016-…˟ 2017-…˟ 2017-…˟
    <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>
1  1.74e7     810     831     827     767     815     809     827     838     827     829     829     829     840
# … with 23 more variables:

赞(0）回复(0）举报 2023-06-03

2admgd592#

类似@Onyambu的方法：

#make uniform column names
names(fico) <- c("ProductAccountNumber", "Date_1", "FICO_1",  "Date_2", "FICO_2", "Date_3", "FICO_3")
#get all columns in a similar data format
fico$Date_1 <- as.Date(fico$Date_1, "%Y-%m-%d" )

#Pivot longer then wider
long <- pivot_longer(fico, -ProductAccountNumber, names_sep = "_", names_to = c(".value", "count"), values_to = "value")
pivot_wider(long, id_cols = "ProductAccountNumber", names_from = "Date", values_from = "FICO", values_fn = mean )

  ProductAccountNumber `2009-04-14` `2012-10-15` `2012-07-05` `2013-07-15` `2013-04-12` `2015-04-06` `2015-08-14` `2015-10-09` `2016-05-11` `2016-07-19`
                 <int>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>        <dbl>
1             17397841          810          831          827          767          815          809          827          838          827          829

赞(0）回复(0）举报 2023-06-03

我来回答

如何在pivot_wider中配对列

2条答案

相关问题

热门标签

最新问答