issue in python and sql query

Question

I have three datasets in csv files.
One csv contains type which is small_ airport, Large_airport and medium_airport. other csv contains id, airport_ref, airport_ident, type, description, frequency_mhz. Now I am doing join each category, large_airport, medium_airport, small airport to the communication frequencies ‘frequency_mhz’. the code is

ps.sqldf('select airport_ident, airport_freq.type, description, frequency_mhz from airport_freq\
         join airports on airport_freq.airport_ref = airports.id where airports.type = "large_airport"')

Similar to what I did for small and medium airport, now I want to produce the mean, mode and median for the ‘frequency_mhz’ for each large_airport and frequencies more than 100 mhz. I used:

ps.sqldf('select max(frequency_mhz), min(frequency_mhz), avg(frequency_mhz) from airport-frequencies').

It gives an error:

PandaSQLException: (sqlite3.OperationalError) near "-": syntax error
[SQL: select max(frequency_mhz), min(frequency_mhz), avg(frequency_mhz) from airport-frequencies]
(Background on this error at: http://sqlalche.me/e/e3q8)

how can I calculate the mean, mode and median for the ‘frequency_mhz’ For each large_airport and frequencies more than 100 mhz?

"its give error": please edit your question to include the error that you get. — Luke Woodward
– Luke Woodward, Commented Apr 17, 2022 at 19:07

prateek bandi · Accepted Answer · 2022-04-17 22:05:39Z

1

Please rename 'airport-frequencies' to 'airport_frequencies', your query will work fine. SQL does not deal very well with "-", so avoid using it.

answered Apr 17, 2022 at 22:05

prateek bandi

512 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hYg-Cain · Accepted Answer · 2022-04-17 21:32:51Z

0

ps.sqldf("select max(frequency_mhz), min(frequency_mhz), avg(frequency_mhz) from 'airport-frequencies'").

I guess this table does not exist, did you mean airport_freq? Or if it does you have to handle the "-"

edited Apr 17, 2022 at 21:32

answered Apr 17, 2022 at 21:01

hYg-Cain

4084 silver badges12 bronze badges

5 Comments

Maryam Anwer Over a year ago

the table is available when i see, pd.read_csv('../input/airports-data/from airport-frequencies.csv') and these are column name ( id airport_ref airport_ident type description frequency_mhz)

hYg-Cain Over a year ago

then try adding quotes --> from "airport-frequencies"

Maryam Anwer Over a year ago

its give SyntaxError: EOL while scanning string literal. Other than SQL query, what technique can i use to get mean, medium and mode?

hYg-Cain Over a year ago

since you're already in quotes you'd have to espace them, or try as edited in my given answer.

hYg-Cain Over a year ago

In regards of other solutions: You could select all required values and calculate the max, min, avg in python, but since there is the option to do it when retrieving the data you should use that option since it is better from performance and readability point of view. Also you should try and avoid names that require escaping like " - " does.

Collectives™ on Stack Overflow

issue in python and sql query

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related