Split one row into multiple rows based on comma-separated string column
Use unnest
on the array returned by split
.
SELECT a,split_b
FROM tbl
CROSS JOIN UNNEST(SPLIT(b,',')) AS t (split_b)
Split (explode) pandas dataframe string entry to separate rows
How about something like this:
In [55]: pd.concat([Series(row['var2'], row['var1'].split(','))
for _, row in a.iterrows()]).reset_index()
Out[55]:
index 0
0 a 1
1 b 1
2 c 1
3 d 2
4 e 2
5 f 2
Then you just have to rename the columns
Split comma delimited cell into multiple rows, keeping Original Rows as it is?
Ok got the Simple solution.
- Just Split All Rows by Comma Delimiter 'using power query'. we get "
After Split Rows
" - Then Paste All Original Rows Above "
After Split Rows
" - Add the filter to numbers/id row & use "
Sort smallest to largest
"
and Done.
We get the Expected Result with the Original Rows & Split Rows Below them, as shown in above question.
How to split comma delimited data from one column into multiple rows
If you are on SQL Server 2016 or better, you can use OPENJSON()
to split up the code values instead of cumbersome string operations:
SELECT t.Employee_FullName,
Code = LTRIM(j.value),
Hours = MAX(CASE j.[key]
WHEN 0 THEN RegularTime
WHEN 1 THEN DoubleTime
WHEN 2 THEN Overtime END)
FROM dbo.MyTable AS t
CROSS APPLY OPENJSON('["' + REPLACE(t.Code,',','","') + '"]') AS j
GROUP BY t.Employee_FullName, LTRIM(j.value);
- Example db<>fiddle
Splitting a comma separated value into separate rows in Pandas
Here is one way using explode
:
df.Col1 = df.Col1.str.split(',')
df.explode('Col1')
Output:
Col1 Col2 Col3
0 1 0.034 0.1
1 2 1.234 0.2
1 3 1.234 0.2
1 4 1.234 0.2
2 5 0.300 1.3
Related Topics
Terms of a Sum in a R Expression
Creating a Prng Engine for <Random> in C++11 That Matches Prng Results in R
Converting Multiple Boolean Columns to Single Factor Column
Control Padding of Grobs Added to Patchwork
Replacing for Loop with Foreach Loop
How to Filter Rows Based on the Previous Row and Keep Previous Row Using Dplyr
Removing Row with Duplicated Values in All Columns of a Data Frame (R)
Split a Column to Multiple Columns
Web Scraping Data Table with R Rvest
Object 'C_Stri_Join' Not Found - Using Knitr in Rstudio
Total Mean & Mean by Groups in R with Dplyr
Rank Vector with Some Equal Values
How to Get a Minimum Value by Group
R Convert String Date (E.G. "October 1, 2014") to Date Format
Removing Unicode Symbols from Column Names
Create All Subvectors of a Certain Length (Moving Window)
How to Read Large Numbers Precisely in R and Perform Arithmetic on Them