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

2013-6-22 23:18:23

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

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

复制代码

Stata命令的.do文档以及手工预处理过程中生成的.docx和.csv文档：

Example.zip
大小:(490.59 KB)

马上下载

本附件包括：

Example.do
Example.docx
Example.csv

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-24 01:33:18

*（最多只需一步手工）把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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-24 09:58:31

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

use a, ...

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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

h3327156

2013-6-24 12:17:38

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

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

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

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

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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

蓝色

2013-6-24 13:13:39

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

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

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

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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

点击查看更多内容…

2013-6-24 22:09:36

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

复制代码

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-25 11:33:47

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

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

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

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

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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

蓝色

2013-6-25 12:39:34

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

这个真不错啊

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-25 14:42:34

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

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

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-25 21:05:12

sungmoo 发表于 2013-6-25 14:42
就excel数据而言，若某一单元格的字符数不幸超过244（并且其中仍有分行），怎么办

对于您的第一个问题，excel字符限制的问题，如果确实有超过244的情况，那确实没办法，还好这个case里面还没这个问题。另外，想向您咨询一下，insheet导入csv文件，是不是也有这个限制呢？

对于您说的第二个问题，如果您把我的代码跑一遍的话，得到的结果就是我一开始要求的结果。之所以把voodoo的答案评为最佳答案，是因为他的总体思路解决了问题，这个问题核心是split和reshape，我只是加上了前面换行符替换的subinstr和char(10)。如果我按照您说的那个“如何查找或替换各类文件中的换行符（不可打印字符）”来提问，那只不过是我最开始的subinstr命令而已。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-25 21:14:52

sungmoo 发表于 2013-6-25 11:33
个人理解：stata的“粘贴错行”并不是任意的，也是有规则的。而这种“错行”，其实“错”也并不在stata， ...

其实每次看您的代码，总感觉很有启发，您化繁为简、多角度分析的能力特别强。但是您这种“先切豆腐，再挪豆腐，再挤掉水分/删除空行”的思路，确实不是一般人可以很容易想到的，看似简单的步骤，要求的是相当深的功底。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

dxystata

2013-6-25 22:54:34

学习一下！sungmoo功底很深厚，景仰！

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-26 04:19:58

sungmoo 发表于 2013-6-25 14:42
就excel数据而言，若某一单元格的字符数不幸超过244（并且其中仍有分行），怎么办

对于您说的第一个问题，字符数超过244的问题，目前确实有问题。但是就这个case而言，还好，单元格中字符不太可能超过244。如果真超过了，我会换别的方法。

对于您第二个问题，我觉得您可能没有试验我的代码，我最后的代码的结果完全符合我问题的要求。我之所以把vodoo的答案设置为最佳答案，是因为他的思路还是很清晰的，对于编程水平一般的人也便于理解。这个解决方案的核心是split和reshape，我只不过是在他核心代码的基础上加上了换行符替换，从而解决了excel预处理的问题。我如果按照您说的“如何查找或替换各类文件中的换行符（不可打印字符）”提问，那完全是另一个问题了，您说的这个问题只不过是我一开始用subinstr和char(10)解决的问题而已，而我最初问题的核心是由voodoo的思路解决的。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

dxystata

2013-6-26 14:15:21

sungmoo 发表于 2013-6-24 01:33

*（最多只需一步手工）把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

学习一下！

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-26 14:56:12

我最后的代码的结果完全符合我问题的要求。

楼主在主楼中上传了两份excel文件。请撤掉不适用于你命令的那个（一份有sheet1，一份有Deals10）。

我觉得您可能没有试验我的代码，我最后的代码的结果完全符合我问题的要求。

采用主楼中所给出的有sheet1的那个excel文件，再采用以下主楼中所给出的代码（已不存在默认目录问题）：

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==""

会发现生成了ID1-ID11这些变量，这些变量是楼主所要的？且最后一个命令显示type mismatch。请问楼主用的是哪份数据与哪些命令？

<另>主楼中的命令改作以下即可：

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==""

本方法优点是：完全自动，命令行少（从而容易理解）；缺点是：可能生成长字符串变量及大量中间变量（从而多占内存），运行时间稍长。

个人倾向（或者习惯）少生成中间变量，特别是处理大数据库时。数据处理速度上似乎是怕宽（变量多）不怕长（观测值数多）。

您说的这个问题只不过是我一开始用subinstr和char(10)解决的问题而已

个人觉得（当然可能因人而异），这个思路的核心就是如何（手工或自动）查找或替换不可打印字符而已（也就是让，分行且在同一单元格的内容变成同一个观测值的内容）。（至于后面的部分是水到渠成的，只不过如何压缩运行时间与命令行而已）

另一思路的核心是，（利用stata对分行内容可能具有的处理方式）让分行且在同一单元格的内容直接变成不同观测值的内容。（后面的部分仍是水道渠成的，仍是压缩时间与命令字符数的问题）

简单重复地说，两种思路并在一起考虑，对于“分行单元格”的处理，个人以为不过是，最初要不要查找或替换不可打印字符而已（也就是如何对付已有的分行符），由此引出后面不同的再处理。

若要再说一些不同之处。每个人的思维模式可能不一样，一种是根据后面部分找前面部分的处理方法，一种是根据前面部分找后面部分的处理方法。哪种方法“更有效”，应该没有一般的客观标准。客观的也许只是运行时间与命令字符数。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-26 22:21:59

sungmoo 发表于 2013-6-26 14:56
楼主在主楼中上传了两份excel文件。请撤掉不适用于你命令的那个（一份有sheet1，一份有Deals10）。

不好意思，因为我自己的数据文件和example有些区别，我在自己文件中用的Advisor这个variable name，所以在后面的reshape命令中忘改成ID了，给大家造成不便，抱歉。正确的命令如下：
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==""

另外，我是想删除其中不表要的那个excel示例，但是我不知道在这个论坛上如何操作啊，我编辑的时候看不到多余的那个附件啊。

我的这个程序是会生成ID1...ID11，但是经过reshape，这些中间变量就被转换成long格式了，最后的形式就是我一开始要求的。之所以您运行我的程序出现中间变量没排除，我在一开始解释了，疏忽了，没有改全variable name。

对于您说的大数据处理，最好不要生成过多中间变量的问题，确实如此。对此我也深有体会，以前向您请教过_n-7到_n-365连续求和的问题，我一开始我写了个循环，中间生成了300多中间变量，由于我处理的是大数据，dta文件本身就3G，我电脑32G内存，跑起来也很艰苦。后来您通过sum减sum的方法，很巧妙地解决了问题，运行速度很快。其实我最佩服您的就是用简单方法处理问题，但是，最难的就是如何思考出那个简单的方法，这个没有很深功底，根本办不到。

至于这次的算法，我觉得还是voodoo的方法更直观一些。可能是我本人比较避讳复制粘贴excel的操作。而且，我觉得一般excel文档能装下的就不是什么太大的数据，就我这个case而言，stata生成那点中间变量，不算太大的事。如果本身是个大数据文件，比如是dta的几个G的文件，那确实要注意内存占用的问题。

最后还得感谢您这句命令split Assignment, p(`=char(10)')，又帮我省了不少事。

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

2013-6-27 22:54:51

蓝色发表于 2013-6-25 12:39
楼主说的“Statalist上Joseph Coveney的提示，char(10)和 char(13)分别代表line feed和carriage return。” ...

当时一接手这个问题时，按照楼主的要求我就试图Stata完成全部工作。

我一直都知道Stata中有char()可以处理ascii字符，通过google到的网页http://www.asciitable.com/我也知道line feed的ascii代码为10，但当时不知为什么Stata处理过程中报错了！在调试几次报错后，当时我就得出（错误的）结论——“Stata无法完成特殊字符替换！” 只好退而求其次，用Excel+Word+Stata的处理思路（见2楼）。

可惜，当时的do文档我没留下，但印象中有一个调试版本是：
import excel using Example.xls, firstrow clear
replace ID = subinstr(ID, "`=char(10)'", "_***_", .)
replace Assignment = subinstr(Assignment, "`=char(10)'", "_***_", .)
...
今晚又重新测试了上面的代码，却发现万事OK啦！

当然最终sungmoo发在16楼的帖子蓝色字体部分是最“精简”解！

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

蓝色

2013-6-27 23:06:57

voodoo 发表于 2013-6-27 22:54
当时一接手这个问题时，安装楼主的要求我就试图Stata完成全部工作。

我一直都知道Stata中有char()可以 ...

还是需要坚持不断尝试，改进。
那样，最优答案就是你的了。
哈哈

扫码加我拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝