问题:
I have a dataframe like below:
ColA ColB
djdn- 3
dn-dn 5
ndmc- 8
nd-nd-md 9
Expected Output:
ColA ColB New_Col...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have a dataframe like below:
ColA ColB
djdn- 3
dn-dn 5
ndmc- 8
nd-nd-md 9
Expected Output:
ColA ColB New_Col
djdn- 3 djdn
dn-dn 5 dn-dn
ndmc- 8 ndmc
nd-nd-md 9 nd-nd-md
Using sqldf, I want to remove the "-" at the end of the value if it exists at the end.
This is my attempted code:
library(sqldf)
df_new<- sqldf("select CASE(RIGHT([ColA], 1) = '-', LEFT([ColA], LEN([ColA])-1),
[ColA]) [New_Col] from df")
Error in result_create(conn@ptr, statement) : near "(": syntax error
回答1:
I think you looking for rtrim
library(sqldf)
df_new<- sqldf("select ColB,rtrim(ColA,'-') as ColA from df")
ColB ColA
1 3 djdn
2 5 dn-dn
3 8 ndmc
4 9 nd-nd-md
回答2:
While using rtrim
seems easier, here's a solution using substr
: sqldf uses SQLite, which does not have the RIGHT
or LEFT
function, so use the SUBSTR
function instead, and the LEN
function is LENGTH
.
library(sqldf)
df_new <- sqldf("select df.*,
CASE
WHEN substr(ColA, length(ColA),1) = '-' THEN substr(ColA, 1, length(ColA)-1)
ELSE ColA
END AS New_Col from df")
回答3:
To match “value contains ‘-’ at the end”, use (I'll assume PostgreSQL) a pattern match:
SELECT
col_a
FROM df
WHERE (col_a LIKE '%-')
Then, to get the value without its final character (which you now know is a ‘-’ character), use a string manipulation function:
SELECT
left(col_a, -1) AS col_a_truncated
FROM df
WHERE (col_a LIKE '%-')