Question 90565 in PostgreSQL Question posted in
The official documentation can be found here.

Count the frequency of users occuring in a list using python – Postgresql

I have a table in postgresql database. From that table I have extracted data using the sql statement mentioned below:

sql_statement = """ select a.slno, a.clientid, a.filename, a.user1_id, b.username, a.user2_id, c.username as username2, a.uploaded_ts, a.status_id
                from masterdb.xmlform_joblist a
                left outer join masterdb.auth_user b
                on a.user1_id = b.id 
                left outer join masterdb.auth_user c
                on a.user2_id = c.id
                """
cursor.execute(sql_statement)
result = cursor.fetchall()

From this code I accessed the database and extracted data from each fields and added the data into list using the below code:

date = []
username1 = []
username2 = []
user1_id = []
user2_id = []
status_id = []
cient_id = []
filename = []

#extracting specific data from specified fields in the database
for row in result:
    date.append(row[7])
    username1.append(row[4])
    username2.append(row[6])
    status_id.append(row[8])
    cient_id.append(row[1])
    filename.append(row[2])
    #creating log file for the extracted fields 
    logger.info("Date | {} , username1 | {} , username2 | {} , status_id | {} , client_id | {} , filename | {} ".format(row[7], row[4], row[6], row[8], row[1], row[2]))

Now I want to check the frequency of usernames from the data I have collected.

username1 looks like:

username1 = ['Dean','Sarah','Sarah','Alan','Dean',......'Alan']

I want to know the count of the users in the list
Expected result:

Dean = 10
Sarah = 6
Alan = 2

Is it possible to achieve this result using python.
I have tried Pandas but it isn’t working as my database is postgresql.

Tags:

2

Answers


  1. 0 
    users = {}  
for user in username1:  
  users.setdefault(user, 0)  
  users[user] += 1  

for user, count in users.items():  
  print(f"{user} = {count}")
    Login or Signup to reply.
  2. 0 
    username1 = ['Dean','Sarah','Sarah','Alan','Dean','Alan']
for x in set(username1):
    print('{0} = {1}'.format(x,username1.count(x)))
  

#Dean = 10
#Sarah = 6
#Alan = 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search