you can use case in this case – SELECT type, sum(case when place=”home” then 1 else 0 end) as Home, sum(case when place=”school” then 1 else 0 end) as school, sum(case when place=”work” then 1 else 0 end) as work, sum(case when place=”cafe” then 1 else 0 end) as cafe, sum(case when place=”friends” then 1 … Read more
So the question is are there any techniques for speeding up these kinds of queries? Well, not really. A column-based storage engine would probably be faster with those SELECT COUNT(*) queries but it would be less performant for pretty much any other query. Your best bet is to maintain a summary table via triggers. It … Read more
So you need value_counts ? df.col_name.value_counts() Out[345]: False 6 True 1 Name: has_cancer, dtype: int64
Here are two ways to count the numbers of times a given substring appears in a string (the first being my preference). Note (as confirmed by the OP) the substring ‘aa’ appears twice in the string ‘aaa’, and therefore five times in: str = “aaabbccaaaaddbab” 1. Use String#scan with a regex that contains a positive … Read more
You could first find if element is NaN or not by isnull() and then take row-wise sum(axis=1) In [195]: df.isnull().sum(axis=1) Out[195]: 0 0 1 0 2 0 3 3 4 0 5 0 dtype: int64 And, if you want the output as list, you can In [196]: df.isnull().sum(axis=1).tolist() Out[196]: [0, 0, 0, 3, 0, 0] … Read more
Usually you can plug a Query’s result (which is basically a table) as the FROM clause source of another query, so something like this will be written: SELECT COUNT(*), SUM(SUBQUERY.AGE) from ( SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age FROM availables INNER JOIN rooms ON availables.room_id=rooms.id WHERE availables.bookdate BETWEEN ‘2009-06-25’ AND date_add(‘2009-06-25’, INTERVAL 4 DAY) … Read more
Here are couple of ways to reshape your data df In [27]: df Out[27]: Col X Col Y 0 class 1 cat 1 1 class 2 cat 1 2 class 3 cat 2 3 class 2 cat 3 1) Using pd.crosstab() In [28]: pd.crosstab(df[‘Col X’], df[‘Col Y’]) Out[28]: Col Y cat 1 cat 2 cat … Read more
For a very quick estimate: SELECT reltuples FROM pg_class WHERE relname=”my_table”; There are several caveats, though. For one, relname is not necessarily unique in pg_class. There can be multiple tables with the same relname in multiple schemas of the database. To be unambiguous: SELECT reltuples::bigint FROM pg_class WHERE oid = ‘my_schema.my_table’::regclass; If you do not … Read more
You can do this with sum, a generator expression, and str.isupper: message = input(“Type word: “) print(“Capital Letters: “, sum(1 for c in message if c.isupper())) See a demonstration below: >>> message = input(“Type word: “) Type word: aBcDeFg >>> print(“Capital Letters: “, sum(1 for c in message if c.isupper())) Capital Letters: 3 >>>