Posts

Active users stats on LeoFinance - January Month - DATA and CHARTS + Python codes

avatar of @amr008
25
MR
@amr008
·
·
0 views
·
3 min read

Good morning to everyone , I hope you are having a great day.
Today's post will be about how many posts were made using #leofinance or 'hive-167922' tag.

How many users have posted over 100 comments , over 10 posts and much more .

For those who doesn't care about the code , just right to the last heading - DATA and CHARTS .
For nerds like me :) Please cross check the code and see if I can improve the code.

Basic codes

import json 
import pandas as pd 
import pyodbc  
conn = pyodbc.connect('Driver={SQL Server};' 
                      'Server=vip.hivesql.io;' 
                      'Database=DBHive;' 
                      'uid=Hive-amr008;' 
                      'pwd=--hidden--;' 
                      'Trusted_Connection=no;') 
 
cursor = conn.cursor()

The above code is to just establish connection with HiveSQL .

Test_Query = pd.read_sql_query('''select * from TxComments where timestamp > GETDATE()-30 ORDER BY ID DESC ''',conn)

I am getting all the posts + comments for past 30 days and storing it in DataFrame Test_Query .

Output of the Test_Query looks like this -

Posts/ Comments count with LeoFinance tag

posts_count=0 
comments_count=0 
 
complete_list=[] 
 
for i in range(0,len(Test_Query)): 
        json_tags=json.loads(Test_Query['json_metadata'][i]) 
        if 'tags' in json_tags: 
            if('hive-167922' in json_tags['tags'] or 'leofinance' in json_tags['tags']): 
                if(Test_Query['parent_author'][i]==''): 
                    posts_count+=1 
                    if 'app' in json_tags: 
                        complete_list.append([Test_Query['author'][i],pd.to_datetime(Test_Query['timestamp'][i]).date(),'post',json_tags['app']]) 
                    else: 
                        complete_list.append([Test_Query['author'][i],pd.to_datetime(Test_Query['timestamp'][i]).date(),'post','None']) 
                         
                else: 
                    comments_count+=1 
                    if 'app' in json_tags: 
                        complete_list.append([Test_Query['author'][i],pd.to_datetime(Test_Query['timestamp'][i]).date(),'comment',json_tags['app']]) 
                    else: 
                        complete_list.append([Test_Query['author'][i],pd.to_datetime(Test_Query['timestamp'][i]).date(),'comment','None']) 
                 
    
print(posts_count,comments_count,posts_count+comments_count)

Steps -

  1. Go through each row ( post/comment)
  2. check if 'hive-167922' or 'leofinance' tag is used in that post/comment
  3. if yes , check if it is a post or comment
  4. if post increase the post count otherwise increase the comment count.
  5. Store all in 'complete_list' list.
df_data=pd.DataFrame(complete_list) 
df_data.columns=['Author','Date','Type','Front-end'] 
 
 
df_posts= df_check[df_check['Type']=='post'] 
df_posts.columns=['Author','Date','posts','Front-end'] 
 
df_comments = df_check[df_check['Type']=='comment'] 
df_comments.columns=['Author','Date','comments','Front-end']

Steps-

  1. Take the whole list and convert to DataFrame

  2. Store only posts in df_posts

  3. Store only comments in df_comments

df_posts_count=df_posts.groupby('Author').count() 
df_comments_count=df_comments.groupby('Author').count()

I have just grouped it by Author to get the count . This will give me how much a particular user has posted and commented .

Output-

count_1to10_posts=0 
count_10to30_posts=0 
count_30_posts=0 
 
count_1to100_c=0 
count_101to250_c=0 
count_251_c=0 
 
 
for i in range(0,len(df_posts_count)): 
    if df_posts_count['posts'][i] >0 and df_posts_count['posts'][i] <=10: 
        count_1to10_posts +=1       
    elif df_posts_count['posts'][i] >10 and df_posts_count['posts'][i] <=30: 
        count_10to30_posts +=1 
    else: 
        count_30_posts +=1 
 
for i in range(0,len(df_comments_count)): 
    if df_comments_count['comments'][i] > 0 and df_comments_count['comments'][i] <=100: 
        count_1to100_c +=1 
    elif df_comments_count['comments'][i] > 100 and df_comments_count['comments'][i] <=250: 
        count_101to250_c +=1 
    else: 
        count_251_c +=1 
 
         
print('Number of users who have -') 
 
print('Posted 1 - 10 posts:'+str(count_1to10_posts)) 
print('Posted 11 - 30 posts:'+str(count_10to30_posts)) 
print('Posted over 30 posts:'+str(count_30_posts)) 
 
print('\n') 
 
print('Posted 1- 100 comments:'+str(count_1to100_c)) 
print('Posted 101 - 250 comments:'+str(count_101to250_c)) 
print('posted 251 or above comments:'+str(count_251_c))

Steps -

  1. I have just gone through each row and have segregated based on posts/ comments count .

  2. I am just increasing the count for posts/ comments if the condition holds true.

DATA and CHARTS

To all those who skipped the above code part , this is what you need -

Output -
Number of users who have - Posted 1 - 10 posts:1103 Posted 11 - 30 posts:261 Posted over 30 posts:105

Posted 1- 100 comments:1745 Posted 101 - 250 comments:69 posted 251 or above comments:33

To get better picture , let me show the same data in charts -

Posts

So the users who have made just 1-10 posts are huge ( 1103 ) followed by 11-30 ( 261 ) .
I have taken 30 as the dividing point because 30 days = 30 posts = 1 post per day and its good to see 105 users here .

Comments

Those who have commented over 250 times with leofinance tag = just 33 :(

Engage more people , engage moree.

Date wise posts+comments with LeoFinance tag.

Average is 1960 posts + comments per day with leofinance tag . That is quite amazing :)

Top 20 Authors ( Posts count )

AccountPost_count
@playpoker332
@playhighcard332
@rollandthomas161
@cryptospa125
@taskmaster4450124
@taskmaster4450le117
@wiseagent107
@joshuaslane92
@cryptopoints84
@aljif780
@jrcornel76
@jondoe75
@break-out-trader71
@playcard68
@chronocrypto64
@klausklaus63
@edicted61
@monsterbuster61
@honeysaver60
@inabsentia59

Top 20 Authors ( Comments count )

AccountComment_count
@taskmaster4450le1724
@filotasriza3913
@acesontop864
@hykss825
@erikah726
@bozz635
@amr008537
@maarnio498
@wiseagent464
@alexvan432
@cmmemes407
@marvinix404
@eirik385
@jfang003373
@chekohler361
@coyotelation358
@shortsegments351
@badbitch342
@onealfa.leo341
@jmsansan.leo322

Let me know if you want your data in the comments, I will get it for you .

df_posts_count[df_posts_count['Author']=='amr008'] 
df_comments_count[df_comments_count['Author']=='amr008']

Output =
20 posts :)
537 comments .

Credits:@mariosfame

Follow me on noise.cash where I promote Leo/Hive posts - https://noise.cash/u/AMR Follow me on twitter - https://twitter.com/NaveshSapad

Regards,
MR.

Posted Using LeoFinance Beta