如何使用R来操作数据

Question

我正在尝试进行一项预测分析，其中x年的指标将预测年份x+1。

如果我有这样的数据，我想使用R在SQL中执行相当于更新查询的操作：

x <- c("Randy Watson", "Cleo McDowell", "Darryl Jenks", "Jaffe Joffer",
       "Randy Watson", "Cleo McDowell", "Darryl Jenks", "Jaffe Joffer",
       "Randy Watson", "Cleo McDowell", "Darryl Jenks", "Jaffe Joffer")
y <- c("2012", "2012", "2012", "2012", 
       "2013", "2013", "2013", "2013", 
       "2014", "2014", "2014", "2014")
z <- c(100, 50, 75, 0, 
       110, 75, 0, 25, 
       125, 25, 10, 50)

df <- data.frame(x, y, z)

colnames(df) <- c("Name", "Year", "Sales")

print(df)

            Name Year Sales
1   Randy Watson 2012   100
2  Cleo McDowell 2012    50
3   Darryl Jenks 2012    75
4   Jaffe Joffer 2012     0
5   Randy Watson 2013   110
6  Cleo McDowell 2013    75
7   Darryl Jenks 2013     0
8   Jaffe Joffer 2013    25
9   Randy Watson 2014   125
10 Cleo McDowell 2014    25
11  Darryl Jenks 2014    10
12  Jaffe Joffer 2014    50

我希望最终输出如下所示：

print(df)
           Name YearX YearX1
1  Randy Watson   100   110
2 Cleo McDowell    50    75
3  Darryl Jenks    75     0
4  Jaffe Joffer     0    25

...

我怎样才能在R中做到这一点？我知道如何在SQL中这样做(尽管我不想使用sqldf，除非这是最好的方法)。

谢谢。

编辑：，下面的解决方案不是我想要的。虽然它有效，如果只有两年，我的数据有10年。我不需要名字，Year1，Year2，Year3等等。我只想知道名字，YearX，YearX+1，对不起，如果我不清楚的话。

Answer 1

像这样

# I took the liberty of rearranging your working example a bit   
df <- data.frame(
           Name = c("Randy Watson", "Cleo McDowell", "Darryl Jenks", "Jaffe Joffer",
                    "Randy Watson", "Cleo McDowell", "Darryl Jenks", "Jaffe Joffer"),
           Year = c("2013", "2013", "2013", "2013", "2014", "2014", "2014", "2014"),
           Sales = c(100, 50, 75, 0, 110, 75, 0, 25))

reshape(df, idvar = "Name", timevar = "Year", direction = "wide")

           Name Sales.2013 Sales.2014
1  Randy Watson        100        110
2 Cleo McDowell         50         75
3  Darryl Jenks         75          0
4  Jaffe Joffer          0         25

或者密切关注你的问题

df_wide <- reshape(df, idvar = "Name", timevar = "Year", direction = "wide")

colnames(df_wide) <- c("Name", "Year0", "Year1") 

print(df_wide)
           Name Year0 Year1
1  Randy Watson   100   110
2 Cleo McDowell    50    75
3  Darryl Jenks    75     0
4  Jaffe Joffer     0    25

一些替代方法应该给出相同的结果。

library(reshape)
cast(df, Name ~ Year)

xtabs(Sales  ~  Name + Year, data = df)