Insert multiple rows based on a specific condition

[Anna Pak]

Dear All,


I am currently experiencing some difficulties in adding rows to my dataset. I would greatly appreciate it if you can give me any suggestions or advice.

Here is information on key variables in my dataset:
ch is the number of weeks the song charted
mweek_th is the week the song charted (week id)
id is song id

I want to add rows based on the value of ch. For example, song 1 first hit the chart in week 85 and had charted for 16 weeks. I want to add additional 15 rows with the value of mweek_th increasing by 1 as follows:

ch	mweek_th	id
16	85	1
16	86	1
16	87	1
16	88	1
16	89	1
16	90	1
16	91	1
16	92	1
16	93	1
16	94	1
16	95	1
16	96	1
16	97	1
16	98	1
16	99	1
16	100	1
32	2134	2
32	2135	2

The rows highlighted in red are what I want to add to the existing dataset.

I have not been able to come up with an efficient solution for this. Any suggestion would be greatly appreciated. Thank you so much!



Anna

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte ch float(mweek_th id)
16   85 1
32 2134 2
39 2444 3
21 1993 4
45 1900 5
20 1965 6
14 2747 7
23 2909 8
 1  433 9
end

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte ch float(mweek_th id)
16   85 1
32 2134 2
39 2444 3
21 1993 4
45 1900 5
20 1965 6
14 2747 7
23 2909 8
 1  433 9
end

expand ch
bys id (mweek_th): replace mweek_th= mweek_th[_n-1]+1 if _n>1

Res.:

. l, sepby(id)

     +--------------------+
     | ch   mweek_th   id |
     |--------------------|
  1. | 16         85    1 |
  2. | 16         86    1 |
  3. | 16         87    1 |
  4. | 16         88    1 |
  5. | 16         89    1 |
  6. | 16         90    1 |
  7. | 16         91    1 |
  8. | 16         92    1 |
  9. | 16         93    1 |
 10. | 16         94    1 |
 11. | 16         95    1 |
 12. | 16         96    1 |
 13. | 16         97    1 |
 14. | 16         98    1 |
 15. | 16         99    1 |
 16. | 16        100    1 |
     |--------------------|
 17. | 32       2134    2 |
 18. | 32       2135    2 |
 19. | 32       2136    2 |
 20. | 32       2137    2 |
 21. | 32       2138    2 |
 22. | 32       2139    2 |
 23. | 32       2140    2 |
 24. | 32       2141    2 |
 25. | 32       2142    2 |
 26. | 32       2143    2 |
 27. | 32       2144    2 |
 28. | 32       2145    2 |
 29. | 32       2146    2 |
 30. | 32       2147    2 |
 31. | 32       2148    2 |
 32. | 32       2149    2 |
 33. | 32       2150    2 |
 34. | 32       2151    2 |
 35. | 32       2152    2 |
 36. | 32       2153    2 |
 37. | 32       2154    2 |
 38. | 32       2155    2 |
 39. | 32       2156    2 |
 40. | 32       2157    2 |
 41. | 32       2158    2 |
 42. | 32       2159    2 |
 43. | 32       2160    2 |
 44. | 32       2161    2 |
 45. | 32       2162    2 |
 46. | 32       2163    2 |
 47. | 32       2164    2 |
 48. | 32       2165    2 |
     |--------------------|
 49. | 39       2444    3 |
 50. | 39       2445    3 |
 51. | 39       2446    3 |
 52. | 39       2447    3 |
 53. | 39       2448    3 |
 54. | 39       2449    3 |
 55. | 39       2450    3 |
 56. | 39       2451    3 |
 57. | 39       2452    3 |
 58. | 39       2453    3 |
 59. | 39       2454    3 |
 60. | 39       2455    3 |
 61. | 39       2456    3 |
 62. | 39       2457    3 |
 63. | 39       2458    3 |
 64. | 39       2459    3 |
 65. | 39       2460    3 |
 66. | 39       2461    3 |
 67. | 39       2462    3 |
 68. | 39       2463    3 |
 69. | 39       2464    3 |
 70. | 39       2465    3 |
 71. | 39       2466    3 |
 72. | 39       2467    3 |
 73. | 39       2468    3 |
 74. | 39       2469    3 |
 75. | 39       2470    3 |
 76. | 39       2471    3 |
 77. | 39       2472    3 |
 78. | 39       2473    3 |
 79. | 39       2474    3 |
 80. | 39       2475    3 |
 81. | 39       2476    3 |
 82. | 39       2477    3 |
 83. | 39       2478    3 |
 84. | 39       2479    3 |
 85. | 39       2480    3 |
 86. | 39       2481    3 |
 87. | 39       2482    3 |
     |--------------------|
 88. | 21       1993    4 |
 89. | 21       1994    4 |
 90. | 21       1995    4 |
 91. | 21       1996    4 |
 92. | 21       1997    4 |
 93. | 21       1998    4 |
 94. | 21       1999    4 |
 95. | 21       2000    4 |
 96. | 21       2001    4 |
 97. | 21       2002    4 |
 98. | 21       2003    4 |
 99. | 21       2004    4 |
100. | 21       2005    4 |
101. | 21       2006    4 |
102. | 21       2007    4 |
103. | 21       2008    4 |
104. | 21       2009    4 |
105. | 21       2010    4 |
106. | 21       2011    4 |
107. | 21       2012    4 |
108. | 21       2013    4 |
     |--------------------|
109. | 45       1900    5 |
110. | 45       1901    5 |
111. | 45       1902    5 |
112. | 45       1903    5 |
113. | 45       1904    5 |
114. | 45       1905    5 |
115. | 45       1906    5 |
116. | 45       1907    5 |
117. | 45       1908    5 |
118. | 45       1909    5 |
119. | 45       1910    5 |
120. | 45       1911    5 |
121. | 45       1912    5 |
122. | 45       1913    5 |
123. | 45       1914    5 |
124. | 45       1915    5 |
125. | 45       1916    5 |
126. | 45       1917    5 |
127. | 45       1918    5 |
128. | 45       1919    5 |
129. | 45       1920    5 |
130. | 45       1921    5 |
131. | 45       1922    5 |
132. | 45       1923    5 |
133. | 45       1924    5 |
134. | 45       1925    5 |
135. | 45       1926    5 |
136. | 45       1927    5 |
137. | 45       1928    5 |
138. | 45       1929    5 |
139. | 45       1930    5 |
140. | 45       1931    5 |
141. | 45       1932    5 |
142. | 45       1933    5 |
143. | 45       1934    5 |
144. | 45       1935    5 |
145. | 45       1936    5 |
146. | 45       1937    5 |
147. | 45       1938    5 |
148. | 45       1939    5 |
149. | 45       1940    5 |
150. | 45       1941    5 |
151. | 45       1942    5 |
152. | 45       1943    5 |
153. | 45       1944    5 |
     |--------------------|
154. | 20       1965    6 |
155. | 20       1966    6 |
156. | 20       1967    6 |
157. | 20       1968    6 |
158. | 20       1969    6 |
159. | 20       1970    6 |
160. | 20       1971    6 |
161. | 20       1972    6 |
162. | 20       1973    6 |
163. | 20       1974    6 |
164. | 20       1975    6 |
165. | 20       1976    6 |
166. | 20       1977    6 |
167. | 20       1978    6 |
168. | 20       1979    6 |
169. | 20       1980    6 |
170. | 20       1981    6 |
171. | 20       1982    6 |
172. | 20       1983    6 |
173. | 20       1984    6 |
     |--------------------|
174. | 14       2747    7 |
175. | 14       2748    7 |
176. | 14       2749    7 |
177. | 14       2750    7 |
178. | 14       2751    7 |
179. | 14       2752    7 |
180. | 14       2753    7 |
181. | 14       2754    7 |
182. | 14       2755    7 |
183. | 14       2756    7 |
184. | 14       2757    7 |
185. | 14       2758    7 |
186. | 14       2759    7 |
187. | 14       2760    7 |
     |--------------------|
188. | 23       2909    8 |
189. | 23       2910    8 |
190. | 23       2911    8 |
191. | 23       2912    8 |
192. | 23       2913    8 |
193. | 23       2914    8 |
194. | 23       2915    8 |
195. | 23       2916    8 |
196. | 23       2917    8 |
197. | 23       2918    8 |
198. | 23       2919    8 |
199. | 23       2920    8 |
200. | 23       2921    8 |
201. | 23       2922    8 |
202. | 23       2923    8 |
203. | 23       2924    8 |
204. | 23       2925    8 |
205. | 23       2926    8 |
206. | 23       2927    8 |
207. | 23       2928    8 |
208. | 23       2929    8 |
209. | 23       2930    8 |
210. | 23       2931    8 |
     |--------------------|
211. |  1        433    9 |
     +--------------------+

.

[Romalpa Akzo]

The second line could be a bit more direct and concise.

expand ch
bys id: replace mweek_th = mweek_th + _n-1

[Andrew Musau]

Thanks Romalpa. I had the feeling that the code could be written more concisely.

[Anna Pak]

Dear Andrew and Romalpa,


Thank you so much for your help. I have never used the command, expand, before, and I am glad that the command allows me to get the outcome I want. Thank you both again for your help.



Anna