代码之家 › 专栏 › 技术社区 › ashurtz

如何将一组列复制到新行中?

spss reshape vba excel r

ashurtz · 技术社区 · 6 年前

抱歉,如果之前已经回答过,我甚至不知道如何搜索它。我对R、VBA或SPSS中的任何自动解决方案都很满意。

我有大量的人口统计数据如下:

ID <- c(1, 2, 3, 4, 5)
State <- c("FL", "FL", "FL", "FL", "FL")
County <- c("Lake", "Lake", "Lake", "Orange", "Orange")
Household <- c (2, 1, 3, 2, 1)
First.Gender <- c("Male", "Female", "Male", "Female", "Male")
Second.Gender <- c("Male", "-", "Female", "Female", "-")
Third.Gender <- c("-", "-", "Male", "-", "-")

Gender_Example <- data.frame(ID, State, County, Household, First.Gender, Second.Gender, Third.Gender)

我想找到一种基于列中的内容创建新行的方法(不创建空行)。如下所示:

ID_i <- c(1, 1, 2, 3, 3, 3, 4, 4, 5) # _i designates my ideal set
State_i <- c("FL", "FL", "FL", "FL", "FL", "FL", "FL", "FL", "FL")
County_i <- c("Lake", "Lake", "Lake", "Lake", "Lake", "Lake", "Orange", "Orange", "Orange")
Household_i <- c(2, 2, 1, 3, 3, 3, 2, 2, 1)
Gender_i <- c("Male", "Male", "Female", "Male", "Female", "Male", "Female", "Female", "Male")

Gender_ideal <- data.frame(ID_i, State_i, County_i, Household_i, Gender_i)

如果已经有人问过这个问题,那么我很高兴有一个链接。非常感谢。

2 回复 | 直到 6 年前

A5C1D2H2I1M1N2O1R2T1 6 年前

R

在R中,您的最佳选择是 melt 从“data.table”(它允许您使用“patterns”来标识度量变量。使用它,您可以执行以下操作:

library(data.table)
melt(setDT(Gender_Example), measure.vars = patterns("Gender$"))[value != "-"]

或者,还有“tidyverse”方法。

library(tidyverse)
Gender_Example %>%
  gather(variable, value, ends_with("Gender")) %>%
  filter(value != "-")

SPSS

在SPSS中,您需要查看 varstocases . 有一个很好的 writeup here that should help you get started .

擅长

这可能取决于您使用的Excel版本。如果您使用的是2016,则可以对数据使用透视表向导,然后双击总计以访问用于创建表的基础“长”表。

概述了该过程 at this video .

或者,您可以使用 Tableau reshaping tool 如上所述 at this video

niko 6 年前

这可能会有帮助

library(reshape2)

Gender_ideal <- melt(Gender_Example, id=c(names(Gender_Example)[1:4]))
rows.to.remove <- which(Gender_ideal$value == "-")
Gender_ideal <- Gender_ideal[-rows.to.remove,]
Gender_ideal
   ID State County Household      variable  value
1   1    FL   Lake         2  First.Gender   Male
2   2    FL   Lake         1  First.Gender Female
3   3    FL   Lake         3  First.Gender   Male
4   4    FL Orange         2  First.Gender Female
5   5    FL Orange         1  First.Gender   Male
6   1    FL   Lake         2 Second.Gender   Male
8   3    FL   Lake         3 Second.Gender Female
9   4    FL Orange         2 Second.Gender Female
13  3    FL   Lake         3  Third.Gender   Male