How to split a dataframe string column into two columns?
There might be a better way, but this here's one approach:
row
0 00000 UNITED STATES
1 01000 ALABAMA
2 01001 Autauga County, AL
3 01003 Baldwin County, AL
4 01005 Barbour County, AL
df = pd.DataFrame(df.row.str.split(' ',1).tolist(),
columns = ['fips','row'])
fips row
0 00000 UNITED STATES
1 01000 ALABAMA
2 01001 Autauga County, AL
3 01003 Baldwin County, AL
4 01005 Barbour County, AL
split one column into multiple columns usining delimiter
Use str.split to split
df[['date', 'date2', 'date3']] = df['date'].replace('NULL', np.nan).str.split('+', expand=True)
and count to count
df['number of dates'] = df[['date', 'date2', 'date3']].count(axis=1)
print(df)
ID date date2 date3 number of dates
0 3009 2016 2017 None 2
1 129 2015 None None 1
2 119 2014 2019 2020 3
3 120 2020 None None 1
4 121 NaN NaN NaN 0
split data in a single column into multiple columns in oracle
Use ROW_NUMBER and then PIVOT:
SELECT item_1,
item_2,
item_3,
item_4
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY ROWNUM) AS rn
FROM table_name t
)
PIVOT (
MAX(value) FOR name IN (
'item_1' AS item_1,
'item_2' AS item_2,
'item_3' AS item_3,
'item_4' AS item_4
)
)
Which, for the sample data:
CREATE TABLE table_name (Name, Value) AS
SELECT 'item_1', 'AB' FROM DUAL UNION ALL
SELECT 'item_2', '2' FROM DUAL UNION ALL
SELECT 'item_3', 'B1' FROM DUAL UNION ALL
SELECT 'item_1', 'CD' FROM DUAL UNION ALL
SELECT 'item_1', 'EF' FROM DUAL UNION ALL
SELECT 'item_2', '3' FROM DUAL UNION ALL
SELECT 'item_3', 'B2' FROM DUAL UNION ALL
SELECT 'item_4', 'ZZ' FROM DUAL;
Outputs:
ITEM_1 ITEM_2 ITEM_3 ITEM_4 AB 2 B1 ZZ CD 3 B2 null EF null null null
Split multiple values from a string in one column, into multiple columns using SQL Server
With a bit of JSON and assuming you have a known or maximum number of tags
Select A.CompanyName
,A.CompanyNumber
,Tag1 = JSON_VALUE(S,'$[0]')
,Tag2 = JSON_VALUE(S,'$[1]')
,Tag3 = JSON_VALUE(S,'$[2]')
From YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)Splitting a single column into multiple columns in R
A possible solution, based on
tidyverse:library(tidyverse)
df %>%
filter(table != "_________________________________________________" ) %>%
mutate(table = str_trim(table)) %>%
separate(table, sep = "\\s+(?=\\d+)",
into = c("Characteristic", "Urban", "Rural", "Total"), fill = "right") %>%
filter(Characteristic != "") %>%
slice(-1)
#> # A tibble: 54 × 4
#> Characteristic Urban Rural Total
#> <chr> <chr> <chr> <chr>
#> 1 Electricity <NA> <NA> <NA>
#> 2 Yes 99.8 94.4 98.9
#> 3 No 0.2 5.6 1.1
#> 4 Total 100.0 100.0 100.0
#> 5 Source of drinking water <NA> <NA> <NA>
#> 6 Piped into residence 97.1 81.4 94.4
#> 7 Public tap 0.0 0.3 0.1
#> 8 Well in residence 1.1 3.7 1.6
#> 9 Public well 0.0 0.4 0.1
#> 10 Spring 0.0 2.3 0.4
#> # … with 44 more rowsHow to split a column value at comma into multiple columns and rename them as its number of column as suffix
You can use
str.splitto split the strings in the column and then attach the resulting DataFrame to the original DataFrame, assigning column names using its width.temp = df['List_of_Order_Id'].str.split(',', expand=True).applymap(lambda x: np.nan if x is None else x)
df[['Order_Id_'+str(i) for i in range(1,temp.shape[1] + 1)]] = temp
Mobile ... List_of_Order_Id Order_Id_1 Order_Id_2 \
0 9.163820e+08 ... 21810 21810 NaN
1 9.179049e+08 ... 23387 23387 NaN
2 9.183748e+08 ... 21767 21767 NaN
3 9.186110e+08 ... 23457 23457 NaN
4 9.187790e+08 ... 23117,23163 23117 23163
.. ... ... ... ... NaN
353 9.970647e+09 ... 21549 21549 NaN
354 9.971940e+09 ... 22753 22753 NaN
355 9.994742e+09 ... 21505,21836,22291,22539,22734 21505 21836
356 9.994964e+09 ... 22348 22348 NaN
357 9.994997e+09 ... 21100,21550 21100 21550
Order_Id_3 Order_Id_4 Order_Id_5
0 NaN NaN NaN
1 NaN NaN NaN
2 NaN NaN NaN
3 NaN NaN NaN
4 NaN NaN NaN
.. NaN NaN NaN
353 NaN NaN NaN
354 NaN NaN NaN
355 22291 22539 22734
356 NaN NaN NaN
357 NaN NaN NaNsplit one column values into multiple column based another column value
This is one way to get the exact result you want, but it is confusing why you'd want all three rows to be returned with values you've already transposed to the first row only...
;WITH cte AS
(
SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY Material_ID ORDER BY UOM),
bupc = CASE WHEN UOM = 'ZF2' THEN UPC END,
pupc = CASE WHEN UOM = 'ZF1' THEN UPC END,
cupc = CASE WHEN UOM = 'ZF3' THEN UPC END
FROM dbo.SomeTableName AS s
),
agg AS
(
SELECT Material_ID,
bupc = MAX(bupc),
pupc = MAX(pupc),
cupc = MAX(cupc)
FROM cte GROUP BY Material_ID
)
SELECT cte.Material_ID, cte.UOM,
[Bottle UPC] = COALESCE(agg.bupc, ''),
[Pack UPC] = COALESCE(agg.pupc, ''),
[Case UPC] = COALESCE(agg.cupc, '')
FROM cte
LEFT OUTER JOIN agg ON agg.Material_ID = cte.Material_ID
AND cte.rn = 1;
- Example db<>fiddle
Related Topics
Varchar Variable Is Not Working in Where Clause
Closing Connection When Using Dapper
How Exactly Does Using or in a MySQL Statement Differ With/Without Parentheses
Window Functions or Common Table Expressions: Count Previous Rows Within Range
I Keep Getting the Error "Relation [Table] Does Not Exist"
Composite Primary Keys:Good or Bad
How to Remove Time from Datetime
Delete Parent If It's Not Referenced by Any Other Child
How to Alter the Position of a Column in a Postgresql Database Table
Extracting Hours from a Datetime (SQL Server 2005)
How Does This Case Expression Reach the Else Clause
SQL Server 2008 - Help Writing Simple Insert Trigger