Repeat Rows N Times According to Column Value
You could do that with a recursive CTE using UNION ALL
:
;WITH cte AS
(
SELECT * FROM Table1
UNION ALL
SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
FROM cte INNER JOIN Table1 t
ON cte.[ID] = t.[ID]
WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1
Here's a working SQLFiddle.
Here's a longer explanation of this technique.
Since your input is too large for this recursion, you could use an auxillary table to have "many" dummy rows and then use SELECT TOP([Order])
for each input row (CROSS APPLY
):
;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
SELECT TOP(t.[Order]) N
FROM E16) ca
ORDER BY 1
(The auxillary table is borrowed from here, it allows up to 65536 rows per input row and can be extended if required)
Here's a working SQLFiddle.
Repeat Rows N Times According to Column Value, Without Limit in Repeating
Finlay I found the solution. We can not use "OPTION(MAXRECURSION 0)" in CTE structure but we can use our query as a function and use "OPTION(MAXRECURSION 0)" in calling and running Function likes following:
Create fnCreateIndex
(
@Pr1 Int
)
RETURNS TABLE
AS
RETURN
(
WITH Numbers(Num) AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Numbers c
WHERE c.Num < @Pr1),
CTE as
(
SELECT partname, qty, num
FROM supplylist
JOIN Numbers ON supplylist.qty >= Numbers.Num
)
Select * from cte
)
Finaly we can use this for getting the resuls:
select * from fnCreateIndex (50000) order by partname, num OPTION(MAXRECURSION 0)
I found solution according to: https://stackoverflow.com/a/7428903/4885037
Repeat Rows in Data Frame n Times
Use a combination of pd.DataFrame.loc
and pd.Index.repeat
test.loc[test.index.repeat(test.times)]
id times
0 a 2
0 a 2
1 b 3
1 b 3
1 b 3
2 c 1
3 d 5
3 d 5
3 d 5
3 d 5
3 d 5
To mimic your exact output, use reset_index
test.loc[test.index.repeat(test.times)].reset_index(drop=True)
id times
0 a 2
1 a 2
2 b 3
3 b 3
4 b 3
5 c 1
6 d 5
7 d 5
8 d 5
9 d 5
10 d 5
Repeat dataframe rows n times according to the unique column values and to each row repeat create a new column with different values
One solution is to convert 'Cs'
to a Categorical. Then use GroupBy
+ first
:
df['Cs'] = df['Cs'].astype('category')
res = df.groupby(['Samp', 'Cs']).first().reset_index()
res['Age'] = res.groupby('Samp')['Age'].transform('first').astype(int)
Result
Samp Cs Age
0 A cin 51
1 A ebv 51
2 A gs 51
3 A msi 51
4 B cin 62
5 B ebv 62
6 B gs 62
7 B msi 62
8 C cin 55
9 C ebv 55
10 C gs 55
11 C msi 55
12 D cin 70
13 D ebv 70
14 D gs 70
15 D msi 70
16 E cin 56
17 E ebv 56
18 E gs 56
19 E msi 56
How to repeat the rows n times
Like this?
SQL> with test (a, b, c) as
2 (select 1, 2, 3 from dual union all
3 select 2, 3, 4 from dual
4 ),
5 temp as
6 (select a, b, c,
7 row_number() over (order by column_value, a) rn
8 from test cross join table(cast(multiset(select level from dual
9 connect by level <= 9
10 ) as sys.odcinumberlist))
11 )
12 select a, b, c
13 from temp
14 where rn <= 9
15 order by rn ;
A B C
---------- ---------- ----------
1 2 3
2 3 4
1 2 3
2 3 4
1 2 3
2 3 4
1 2 3
2 3 4
1 2 3
9 rows selected.
SQL>
What does it do?
- lines #1 - 4 represent your sample data
- CTE
temp
(lines #5 - 11) created all those rows;row_number
is used to "rank" them, ordered bycolumn_value
(think of it as of alevel
pseudocolumn, if it is closer to you) and thea
column value (why? Your sample output suggests so) - final query (lines #12 - 15) selects the result for
rn <= 9
(as you wanted to get 9 rows)
Repeat each value n times as rows in SQL
Try this:
select * from names
cross join (select rownum n from dual
connect by level <= (select max(repeat) from names))
where n <= repeat
order by name
replicate rows by n times in python
Another method could be:
df.assign(Times = df.Times.apply(lambda x: range(1, x + 1))).explode('Times')
Out[]:
String Times
0 a 1
0 a 2
1 b 1
1 b 2
1 b 3
2 c 1
2 c 2
2 c 3
2 c 4
2 c 5
pandas - Copy each row 'n' times depending on column value
Use Index.repeat
, DataFrame.loc
, DataFrame.assign
and DataFrame.reset_index
new_df = df.loc[df.index.repeat(df['orig_qty'])].assign(fifo_qty=1).reset_index(drop=True)
[output]
date orig_qty price fifo_qty
0 2019-04-08 4 115.0 1
1 2019-04-08 4 115.0 1
2 2019-04-08 4 115.0 1
3 2019-04-08 4 115.0 1
4 2019-04-09 2 103.0 1
5 2019-04-09 2 103.0 1
Repeat each Row in a Dataframe different N times according to the difference between two value in the Time Column
Create another column to hold the difference in the values of columns, for repetition reference and then do the operation like this:
import pandas as pd
# Sample dataframe
df = pd.DataFrame({
'id' : ['a', 'b', 'c', 'd'],
'col1' : [4, 5, 6, 7],
'col2' : [3, 2, 4, 3]
})
# Create a new column to hold the difference in column values
# i.e. the number of times the row repition is required.
df['times'] = df.col1 - df.col2
# create the finalDf with repeated rows
finalDf = df.loc[df.index.repeat(df.times)].reset_index(drop=True)
print(finalDf.head())
The output of print
statement looks like:
id col1 col2 times
0 a 4 3 1
1 b 5 2 3
2 b 5 2 3
3 b 5 2 3
4 c 6 4 2
Related Topics
How to Delete the Top 1000 Rows from a Table Using SQL Server 2008
Timestamp Difference in Hours for Postgresql
Regular Expression to Match All Comments in a T-SQL Script
MySQL Full Text Search with Partial Words
SQL Add Filter Only If a Variable Is Not Null
For JSON Path Returns Less Number of Rows on Azure SQL
Export Table Data from One SQL Server to Another
How Do Null Values Affect Performance in a Database Search
How to Format Date and Time on Ssrs Report
How to Find All Ids of Children Recursively
The Transaction Log for the Database Is Full
SQL Query to Obtain Value That Occurs More Than Once
SQL Server Unique Composite Key of Two Field with Second Field Auto-Increment
How to Search All Columns in a Table
Check If Null Exists in Postgres Array
Backup a Single Table with Its Data from a Database in SQL Server 2008