Get substring into a new column

kdb substring
extract substring from column in r
sql find substring
pandas extract substring from column
get substring of column python
mysql substring index
new column substring pandas

I have a table that contains a column that has data in the following format - lets call the column "title" and the table "s"

title

ab.123
ab.321 
cde.456
cde.654
fghi.789
fghi.987

I am trying to get a unique list of the characters that come before the "." so that i end up with this:

ab
cde
fghi

I have tried selecting the initial column into a table then trying to do an update to create a new column that is the position of the dot using "ss".

something like this:

t: select title from s
update thedot: (title ss `.)[0] from t

i was then going to try and do a 3rd column that would be "N" number of characters from "title" where N is the value stored in "thedot" column.

All i get when i try the update is a "type" error.

Any ideas? I am very new to kdb so no doubt doing something simple in a very silly way.

the reason why you get the type error is because ss only works on string type, not symbol. Plus ss is not vector based function so you need to combine it with each '.

q)update thedot:string[title] ss' "." from t
title    thedot
---------------
ab.123   2
ab.321   2
cde.456  3
cde.654  3
fghi.789 4

There are a few ways to solve your problem:

q)select distinct(`$"." vs' string title)[;0] from t
x
----
ab
cde
fghi
q)select distinct(` vs' title)[;0] from t
x
----
ab
cde
fghi

You can read here for more info: http://code.kx.com/q/ref/casting/#vs

kdb - Get substring into a new column, the reason why you get the type error is because ss only works on string type, not symbol. Plus ss is not vector based function so you need to combine it with  I have a db where one column contains 2 pieces of data, e.g. first and last name. The format is roughly ABC-1D23-4F34. I want to copy and insert the first 3 letters, the ABC, into a new column. Lets call these columns [full_id] and [ref_id] From reading it looks like substring is able to do this but I am doing something wrong here. INSERT INTO

An alternative is to make use of the 0: operator, to parse around the "." delimiter. This operator is especially useful if you have a fixed number of 'columns' like in a csv file. In this case where there is a fixed number of columns and we only want the first, a list of distinct characters before the "." can be returned with:

exec distinct raze("S ";".")0:string title from t
`ab`cde`fghi

OR:

distinct raze("S ";".")0:string t`title
`ab`cde`fghi

Where "S " defines the types of each column and "." is the record delimiter. For records with differing number of columns it would be better to use the vs operator.

How to access substrings in pandas column and store it into new , In my opinion you are using a very complicated approach to get what you want. Replace your code with this. It should give you what you are  str.slice function is used to get the substring of the column in pandas dataframe python. Let’s see an Example of how to extract a substring from column of pandas dataframe and store it in new column. Substring of column in pandas python: Substring of column in pandas data frames can be achieved by using str.slice function.

A variation of WooiKent's answer using each-right (/:) :

q)exec distinct (` vs/:x)[;0] from t
`ab`cde`fghi

MySQL SUBSTRING() Function, Extract a substring from a string (start at position 5, extract 3 characters): Extract a substring from the text in a column (start at position 2, extract 5 characters):. In Query Editor, select the date column and click “Split Column By Number of Characters - 2”. Repeat step 1 for the separated column. Select all three columns and then right click to choose “Merge Columns” with custom separator ‘-’.

Extract substring of the column in R dataframe, To extract the substring of the column in R we use functions like substr() , str_sub(​) or str_extract() function. Let's see how to get the substring column. Select the cells where you have the text. Go to Data –> Data Tools –> Text to Columns. In the Text to Column Wizard Step 1, select Delimited and press Next. In Step 2, check the Other option and enter @ in the box right to it.

Python, Pandas str.find() method is used to search a substring in each string present in a The returned series is stored in a new column so that the indexes can be  The first column reflects the street number substring and the second the street name substring. You can then resort the list based on the street name and street number. info If the addresses are simple, you could probably use the Text to Columns wizard .

pandas.Series.str.slice, Slice substrings from each element in the Series or Index. Series.str.​slice_replace: Replace a slice with a string. Series.str.get: Return element at position. Extract substring from start of string (LEFT) To extract text from the left of a string, you use the Excel LEFT function: LEFT(text, [num_chars]) Where text is the address of the cell containing the source string, and num_chars is the number of characters you want to extract.