拆分一个单元格中多行字符到不同单元格

有如下格式excel数据：
Number           ID                Assignment
1                     A                       AS1                    
                       B                       AS2
                       C                       AS3
2                     A                       AS5
                       D                       AS2
3                     E                       AS6
...


以Number为1的这个观察值来说，ID：A，B，C在同一个excel单元格里的不同行，同理Assigment：AS1，AS2，AS3也在同一个excel单元格里的不同行。

现想用stata将同一个单元格中不同行的string转入不同观察值，如下格式所示：
Number         ID                   Assignment
1                    A                          AS1
1                    B                          AS2
1                    C                          AS3
2                    A                          AS5
2                    D                          AS2
3                    E                          AS6
...

哪位高手有什么好的解决方法么？
非常感谢！
Example.xls (480.5 KB)

2013-6-25 00:51:20 上传

我已将voodoo提供的方法评为最佳答案，思路清晰，程序简洁明了。sungmoo提供的方案也很好，但是正如voodoo所说，excel copy+paste到stata产生错行，用Stata编程解决错行，并达成最终目的，这恐怕只有像sungmoo一样具备极高超技能才能解决，一般人不太容易理解。

但是voodoo的方法离完美还差那么一点点，问题就在于第一步excel的预处理，这里还要感谢h3327156提供的excel预处理方法。但是即便excel能够替换“换行或回车”为指定的字符串，我仍觉得在尽可能的情况下用Stata提供一站式解决方案最好。


那么现在的问题就转换为如何用Stata处理导入excel带来的换行问题。Dr Nick Cox编写的charlist（需要ssc install）可以查看variable中包含的ASCII码，但是很遗憾，经我试验，这个命令并不显示“换行”字符。经Statalist上Joseph Coveney的提示，char(10)和 char(13)分别代表line feed和carriage return。于是我用subinstr解决字符替换问题。最后结合voodoo的代码后，我编写完整程序如下：

***import data***

clear

import excel "Example.xlsx", sheet("sheet1") firstrow

***replace line feed with "///"***   

gen ids=subinstr(ID,char(10),"///",.)

gen assignments=subinstr(Assignment,char(10),"///",.)

***split each ID and Assignment into different observations***

split ids, gen(ID) parse("///")

split assignments, gen(Assignment) parse("///")

drop ID Assignment ids assignments

***reshape to long data***

reshape long ID Assignment, i(Number) j(num)

drop if ID==""&Assignment==""

针对上述程序，sungmoo又提出了让代码更简洁的修改建议

import excel "Example.xls", sh("sheet1") first clear
split ID, p(`=char(10)')
split Assignment, p(`=char(10)')
drop ID Assignment
reshape long ID Assignment, i(Number)
drop if ID==""

最后感谢所有参与这个帖子讨论的人，真的获益良多。

满有趣的题目，也是个数据处理中满常碰见问题。当然还有高的悬赏金额——于是我来啦！

全部利用Stata完成有点困难（但也不是一定不可以，只是最终程序应该会长挺多！）
以下的处理方法结合Excel-Word-Stata共同完成，处理思路和操作相对简单直观，希望楼主喜欢啦。

1. //        #0
   
2. //        在Excel和Word中的手工数据预处理
   
3. /*
   
4.         #0.1 将目标Excel工作表copy+paste至Word中
   
5.         #0.2 在Word中将手工回车符转换为特殊字符串，方便Stata的处理
   
6.                 -> Ctrl-H(即“查找和替换”功能)
   
7.                 -> 查找内容：^l
   
8.                 -> 替换为：_***_
   
9.                 -> 按“替换”键，完成查找替换任务
   
10.         #0.3 将查找替换后的Word表格内容copy+paste到新的Excel工作表中
    
11.         #0.4 将新的Excel工作表另存为“CSV(逗号分隔.csv)”，文件名为Example.csv
    
12. */
    
13. 
    
14. //        #1
    
15. //        Stata中的处理
    
16.         //        #1.1 导入csv文档
    
17. insheet using Example.csv, clear comma names
    
18. 
    
19.         //        #1.2 分隔变量
    
20. split id, gen(nme) parse("_***_")
    
21. split assignment, gen(asg) parse("_***_")
    
22. 
    
23.         //        #1.3 转换为长型数据表并删除空值等
    
24. keep number nme* asg*
    
25. reshape long nme asg, i(number) j(rank)
    
26. assert (!missing(nme) == !missing(asg))
    
27. keep if !missing(nme)
    
28. 
    
29.         //        #1.4 存盘
    
30. save example, replace
    
31. 
    
32. //        Done!

复制代码

Stata命令的.do文档以及手工预处理过程中生成的.docx和.csv文档：
Example.zip (490.59 KB) 本附件包括：

• Example.do
• Example.docx
• Example.csv

2013-6-23 12:17:32 上传

*（最多只需一步手工）把excel数据直接用edit窗口复制到stata中，存成a，可见其中数据排列的特征

use a,clear /*可选*/
g y=real(number)==.
g x=(id!="")&y
replace assignment=id if x
replace x=x[_n-1]+1 if x[_n-1]&y
replace id=number if y&x<2
replace assignment=number if x>1
replace number=number[_n-1] if y
replace y=_n
bys number (y): egen m=max(x)
bys number (y): replace assignment=assignment[_n+m-1] if m
drop if id==""
sort y /*不保留原始顺序，可取消此步*/
drop y x m

sungmoo 发表于 2013-6-24 01:33
*（最多只需一步手工）把excel数据直接用edit窗口复制到stata中，存成a，可见其中数据排列的特征

use a, ...

sungmoo提供的方法最初我也想尝试过，但考虑到直接从Excel中copy+paste到Stata存在严重的错行与错列问题。而要发现错行错列的规律并将这一规律逆向还原为Stata代码，这几乎要求坛友要具备像sungmoo一样高超的技能！至少我目前还不具备这样的能力——本人在阅读sungmoo提供的代码时，感觉其可读性/可理解性并不太好。

voodoo 发表于 2013-6-24 09:58
sungmoo提供的方法最初我也想尝试过，但考虑到直接从Excel中copy+paste到Stata存在严重的错行与错列问题。 ...

如果您是指excle中的查找替代在哪里，请看下图

excle 有关同一单位格【台湾称储存格】，多行变成一行，可以参照下面网址
http://isvincent.blogspot.tw/2011/01/excel_11.html

我当时的想法是，一开始先把这个多行变成一行后【断行处理】，【重要字词与字词间用个符号代掉】
再利用拆字词的方式…
【所以我说我的做法与voodoo相近】

我知道观念很简单，但实务操作上很费时费工，虽然论坛币奖赏很多，
问题是哪几天是星期六日，开车去玩也是很重要的  @_@
所以我…… 当作没看到……

h3327156 发表于 2013-6-24 12:17
如果您是指excle中的查找替代在哪里，请看下图

excle 有关同一单位格【台湾称储存格】，多行变成一行， ...

excel 直接查找替换的方法不错；
一开始没有找到 换行该怎么查找。现在终于知道了。


现在也就先在excel里面查找，替换；
然后再用voodoo的do 文件就解决问题了。

我对于二楼解法的步骤#0还是不太满意，觉得太绕了点。在坛友h3327156的启示下（抱歉链接中的台湾网页很难打开，我并未看到其给出的具体方法），借助google帮助，我终于找到改进步骤#0的方法——这真真印证了“google不是万能的，但没有google万万不能”的说法！

1. //        #0
   
2. //        在Excel中的手工数据预处理
   
3. /*
   
4.         #0.1 在【Excel中】将单元格中的软回车符转换为特殊字符串，方便Stata的处理
   
5.                 -> Ctrl-H(即“查找和替换”功能)
   
6.                 -> 查找内容：【按下Alt键不动，然后连续按数字键盘中的1和0（注：不会显示任何字符）】
   
7.                 -> 替换为：_***_
   
8.                 -> 按“替换”键，完成查找替换任务
   
9.         #0.2 将新的Excel工作表另存为“CSV(逗号分隔.csv)”，文件名为Example.csv
   
10. */
    
11. 
    
12. //        #1
    
13. //        Stata中的处理
    
14.         //        #1.1 导入csv文档
    
15. insheet using Example.csv, clear comma names
    
16. 
    
17.         //        #1.2 分隔变量
    
18. split id, gen(nme) parse("_***_")
    
19. split assignment, gen(asg) parse("_***_")
    
20. 
    
21.         //        #1.3 转换为长型数据表并删除空值等
    
22. keep number nme* asg*
    
23. reshape long nme asg, i(number) j(rank)
    
24. assert (!missing(nme) == !missing(asg))
    
25. keep if !missing(nme)
    
26. 
    
27.         //        #1.4 存盘
    
28. save example, replace
    
29. 
    
30. //        Done!

复制代码

正如voodoo所说，excel copy+paste到stata产生错行，用Stata编程解决错行，并达成最终目的，这恐怕只有像sungmoo一样具备极高超技能才能解决，一般人不太容易理解。

个人理解：stata的“粘贴错行”并不是任意的，也是有规则的。而这种“错行”，其实“错”也并不在stata，而在于原始数据。原始数据如果是“规矩的”，stata不会错行；stata之所以对“不规矩”的原始数据会错行，这正表现stata是按既定规则运行的结果（stata总是守规矩的）。

就本例而言，个人以为“粘贴错行”的特点（或叫规律）并不难发现。do文件调整错行数据的思路其实也很简单（说白了，就是“先切豆腐，再挪豆腐，再挤掉水分/删除空行”，最后合起豆腐）。

本例其实根本没有用上什么高深的技能（从所使用的命令即可看到），各位恭维过重了。

个人的总体思路是，在处理数据时，用stata等软件的重要目的即，尽可能减少人工操作的偶然性失误。而软件出错，只应该是系统性失误。用楼主自己的话说，“在尽可能的情况下用Stata提供一站式解决方案最好”。本题的问题其实是，怎样才算“令人接受的一站式”。

楼主说的“Statalist上Joseph Coveney的提示，char(10)和 char(13)分别代表line feed和carriage return。”

这个真不错啊

import excel "Example.xlsx", sheet("sheet1") firstrow

就excel数据而言，若某一单元格的字符数不幸超过244（并且其中仍有分行），怎么办


********************
另外，没看懂楼主以下最终想要什么样的结果（仅就楼主给出的excel数据）：

最后结合voodoo的代码后，我编写完整程序如下：***import data***
clear
import excel "Example.xlsx", sheet("sheet1") firstrow
***replace line feed with "///"***   
gen ids=subinstr(ID,char(10),"///",.)
gen assignments=subinstr(Assignment,char(10),"///",.)
***split each ID and Assignment into different observations***
split ids, gen(ID) parse("///")
split assignments, gen(Assignment) parse("///")
drop ID Assignment ids assignments
***reshape to long data***
reshape long Advisor Assignment, i(Number) j(num)
drop if Advisor==""&Assignment==""

楼主得到的并不是最初提到的结果：

现想用stata将同一个单元格中不同行的string转入不同观察值，如下格式所示：
Number         ID                   Assignment
1                    A                          AS1
1                    B                          AS2
1                    C                          AS3
2                    A                          AS5
2                    D                          AS2
3                    E                          AS6

根据楼主的思路方向看，楼主一开始只需询问“如何查找或替换各类文件中的换行符（不可打印字符）”，就足够了。