Ben Ben - 7 months ago 19
SQL Question

How to convert this SQL statement into Python: MID? SUBSTRING?

I am trying to filter a Python DataFrame by substring. It is very simple and straightforward to do this in SQL, but I have had great difficulty translating this logic into Python code.

This is the SQL code:

SELECT * FROM [oe.data.0.Current]
WHERE substring([series_id],5,2) = '50'
OR substring([series_id],5,5) = '72400';


So essentially I want to filter the 'series_id' column by specific character values in the 5th and 6th character position and the 5th through 9th character position.

I don't understand how MID or SUBSTRING translates into DataFrames.

How do I acheive the results of this SQL query in a DataFrame?

Answer

You can just map it and use lambda function:

df[df['series_id'].map(lambda x: x[4:6]=='50' or x[4:9]=='72400')]

There might be a better solution, but this is what I use in similar cases.

Comments