<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
Step1:  Twitter
Step2:  Databases can do a lot, but there are somethings that are more easily acheived through throught the flexibility of a general-purpose programming language like Python. In this notebook, we are going run through running a query, returning the results to a pandas DataFrame object, and performing some operations over the data frame in order to analyze our data. Let's go ahead and  create a data frame.
<span style="background-color
Step4:  We have made substantial progress in the preparation of the calculating the linguistic diversity of these different cities. However, we want to use a single metric that can give us a summary of each city. For this, we're going to borrow from the biological literature and use Shannon's Diversity Index $H$. This is a sort of entropy index that is generally used to measure the biological richness of an ecosystem.
We needn't get into the weeds about this measure but just so we know it takes into account both the number of unique languages as well as how balanced those languages are in a city. Therefore, max Shannon would be an equal count for every language of a city. 
We can go ahead and see how this works by reading in the function form the scikit-bio library.
Step6:  This shannon function operates on counts. Conveniently, we have pulled out language counts for per city, so we should be able to run it over this column per city to calculate the index for each city.
Let's return to the Columbia, MO for this example. We can run our final query from the second Twitter notebook, convert this to a data frame, and then run shannon over the count column. This is what that would look like
Step8:  <span style="background-color
Step9:  This isn't all we want though. One city is boring if we can't compare to others. This is where pandas comes in handy. We can chain together methods to return results that we are looking for. 
Below, there is a sample data frame that contains language counts for three different cities. We can use the groupby method to find the shannon index for each city on the count column. Let's take a look...
Step10:  Here, we start with the the count column as this is the column we want to find shannon on. Then we groupby city in order to find shannon per city. Finally, we run the apply method because shannon is a function and not a pandas method. Therefore, apply will apply the shannon function to every grouping of the count column.
<span style="background-color
Step11:  We can also sort the values
Step12:  <span style="background-color
<ASSISTANT_TASK:>
Python Code:
# BE SURE TO RUN THIS CELL BEFORE ANY OF THE OTHER CELLS
import psycopg2
import pandas as pd
# put your code here
# ------------------
statement = 
SELECT DISTINCT iso_language, job_id,COUNT(*)
FROM
(SELECT
  DISTINCT ON (from_user, iso_language)
  *
FROM (SELECT * FROM twitter.tweet WHERE iso_language != 'und' AND job_id >=  255 LIMIT 100000) as T
ORDER BY from_user, iso_language) as UNIQ
GROUP BY iso_language, job_id;
try:
    connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
    # use our connection values to establish a connection
    conn = psycopg2.connect(connect_str)
    cursor = conn.cursor()
    cursor.execute(statement)
    
    column_names = [desc[0] for desc in cursor.description]
    rows = cursor.fetchall()
except Exception as e:
    print("Uh oh, can't connect. Invalid dbname, user or password?")
    print(e)
    
# create dictionary from the rows and column names   
cities = {}
for i in list(range(len(column_names))):
     cities['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
pd.DataFrame(cities)
Explanation: Twitter: An Analysis of Linguistic Diversity
Part III
RECAP: In the second Twitter notebook, we left off with a query that provided us with the language counts for the city of Columbia, MO. But remember, we also had to do some prep work before we actually could find these counts, primarily, restricting it to one user per language and then removing the "und" language rows.
<span style="background-color: #FFFF00">YOUR TURN</span>
Today we are going to pick up where we left off except this time we aren't going to limit it on one city. Run a query that returns the language counts for each city. Remember to restrict it to one user per language (per city) and to remove the 'und' iso_language rows. LIMIT ROWS TO 100,000! City is job_id and not all jobs correspond to the geographic location of the tweet. Therefore, make sure you query only those jobs that are equal to or greater than job 255.
End of explanation
# put your code here
# ------------------
df = pd.DataFrame(cities)
Explanation: Databases can do a lot, but there are somethings that are more easily acheived through throught the flexibility of a general-purpose programming language like Python. In this notebook, we are going run through running a query, returning the results to a pandas DataFrame object, and performing some operations over the data frame in order to analyze our data. Let's go ahead and  create a data frame.
<span style="background-color: #FFFF00">YOUR TURN</span>
All the way back in the first Twitter notebook we covered how to create a dictionary from our returned query and subsequently how we can turn that dictionary into a data frame using pandas. From the query above, turn the language counts per city into a data frame. Call this data frame df.
End of explanation
from skbio.diversity.alpha import shannon
pop1 = [10,10,10]
pop2 = [10,20,30]
pop3 = [100,200,300]
print("pop1 ({}) is more diverse that pop2 ({}), which is as diverse as pop3({})".format(
    shannon(pop1),shannon(pop2),shannon(pop3)))
pop1 = [10,10,10]
pop2 = [10,10,10,10]
print(Also, more languages but equal eveness means greater diversity. 
For example, pop2 ({}) is more diverse than pop1 ({}).format(shannon(pop2),shannon(pop1)))
Explanation: We have made substantial progress in the preparation of the calculating the linguistic diversity of these different cities. However, we want to use a single metric that can give us a summary of each city. For this, we're going to borrow from the biological literature and use Shannon's Diversity Index $H$. This is a sort of entropy index that is generally used to measure the biological richness of an ecosystem.
We needn't get into the weeds about this measure but just so we know it takes into account both the number of unique languages as well as how balanced those languages are in a city. Therefore, max Shannon would be an equal count for every language of a city. 
We can go ahead and see how this works by reading in the function form the scikit-bio library.
End of explanation
statement = 
SELECT DISTINCT iso_language, COUNT(*)
FROM
(SELECT
  DISTINCT ON (from_user, iso_language)
  *
FROM (SELECT * FROM twitter.tweet WHERE job_id = 261 AND iso_language != 'und' LIMIT 10000) as T
ORDER BY from_user, iso_language) as UNIQ
GROUP BY iso_language;
try:
    connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
    # use our connection values to establish a connection
    conn = psycopg2.connect(connect_str)
    cursor = conn.cursor()
    cursor.execute(statement)
    
    column_names = [desc[0] for desc in cursor.description]
    rows = cursor.fetchall()
except Exception as e:
    print("Uh oh, can't connect. Invalid dbname, user or password?")
    print(e)
    
# create dictionary from the rows and column names   
como = {}
for i in list(range(len(column_names))):
     como['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
columbia = pd.DataFrame(como)
print("The shannon index for Columbia is {}".format(shannon(columbia['count'])))
Explanation: This shannon function operates on counts. Conveniently, we have pulled out language counts for per city, so we should be able to run it over this column per city to calculate the index for each city.
Let's return to the Columbia, MO for this example. We can run our final query from the second Twitter notebook, convert this to a data frame, and then run shannon over the count column. This is what that would look like:
End of explanation
# put your code here
# ------------------
# query database
statement = 
SELECT DISTINCT iso_language, COUNT(*) 
FROM (
    SELECT iso_language 
    FROM twitter.tweet 
    WHERE job_id = 261 
    LIMIT 10000) AS users 
GROUP BY iso_language
ORDER BY count;
try:
    connect_str = "dbname='twitter' user='dsa_ro_user' host='dbase.dsa.missouri.edu'password='readonly'"
    # use our connection values to establish a connection
    conn = psycopg2.connect(connect_str)
    cursor = conn.cursor()
    cursor.execute(statement)
    
    column_names = [desc[0] for desc in cursor.description]
    rows = cursor.fetchall()
except Exception as e:
    print("Uh oh, can't connect. Invalid dbname, user or password?")
    print(e)
    
# create dictionary from the rows and column names   
job_261 = {}
for i in list(range(len(column_names))):
     job_261['{}'.format(column_names[i])] = [x[i] for x in rows]
# turn dictionary into a data frame
not_clean = pd.DataFrame(job_261)
shannon(not_clean['count'])
Explanation: <span style="background-color: #FFFF00">YOUR TURN</span>
Remember there was a reason that we did all of that preparation in the last notebook. There were some users who were high volume tweeters making the language counts less even. What do you think would happen if we ran the shannon function over language counts without restricting it to one user per language? Is Columbia more or less diverse according to this measure?
Run a query prior to our preparation steps for Columbia, MO. Is Columbia more or less diverse before clean up?
End of explanation
samp = pd.DataFrame({'lang': ['en','es','fr','en','es','fr','en','es','fr','ru'], 
                     'count': [30,20,10,11,10,9,30,1,1,1],
                     'city': [1,1,1,2,2,2,3,3,3,3]})
samp
samp['count'].groupby(samp['city']).apply(shannon)
Explanation: This isn't all we want though. One city is boring if we can't compare to others. This is where pandas comes in handy. We can chain together methods to return results that we are looking for. 
Below, there is a sample data frame that contains language counts for three different cities. We can use the groupby method to find the shannon index for each city on the count column. Let's take a look...
End of explanation
# put your code here
# ------------------
df['count'].groupby(df['job_id']).apply(shannon)
Explanation: Here, we start with the the count column as this is the column we want to find shannon on. Then we groupby city in order to find shannon per city. Finally, we run the apply method because shannon is a function and not a pandas method. Therefore, apply will apply the shannon function to every grouping of the count column.
<span style="background-color: #FFFF00">YOUR TURN</span>
Now run shannon over the language counts of df. Be sure to group by city (job_id).
End of explanation
samp['count'].groupby(samp['city']).apply(shannon).sort_values()
Explanation: We can also sort the values:
End of explanation
# put your code here
# ------------------
df['count'].groupby(df['job_id']).apply(shannon).sort_values()
Explanation: <span style="background-color: #FFFF00">YOUR TURN</span>
Now sort the shannon indexes. What is the least linguistically diverse city according to this dataset? The most?
End of explanation
<END_TASK>