Covid 19 Network Graph

Creación de un Grafo para concer que usuario de twitter obtiene mayor repercusión con el #coronavirus
Autores : Luis Domínguez López y Sabrina Gutierrez

Fase 1

Este primer script realiza una descarga de twitter con tweepy y las credenciales obtenidas de API TWITTER El resultado es covid-2.csv – fecha de creación, tweet y el nombre del usuario que realizó el RT o el autor.

Fase 2

En esta fase trabajamos sobre covid-2.csv, buscamos obtener los datos de forma mas clara. Por eso buscamos 2 cosas, 1º sacar de los tweets los autores que han sido RT (¿Por qué? Si obtenemos el autor original y contabilizamos cuantas personas le han realizado un RT podemos obtener dos cosas 1º su alcance en referencia a un tema, 2º tener la relación influencer-usuario y poder analizar que influencer afecta mas a cierto usuario), 2º realizar un CSV llamado personas_nombradas-2.csv que contabiliza por usuario el número de RT que ha obtenido.

conn.execute('''DROP TABLE IF EXISTS NAMES;''')
conn.execute('''CREATE TABLE NAMES (ID TEXT PRIMARY KEY NOT NULL, VALUE INT NOT NULL);''')
with open('covid_limpio-2.csv', mode='w') as employee_file:
    employee_writer = csv.writer(employee_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    with open('covid_limpio_conocidos-2.csv', mode='w') as employee_file:
        employee_limpio = csv.writer(employee_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
        with open('covid-2.csv') as csvfile:
            readCSV = csv.reader(csvfile, delimiter=',')
            i = 0
            for row in readCSV:
                try:
                    if row[1][0:2] == "RT":
                        name = row[1].split(":")[0]
                        employee_writer.writerow([name.split(" ")[1], row[1].split(":")[1], row[0], row[2]])
                        employee_limpio.writerow([name.split(" ")[1], row[1].split(":")[1], row[0], row[2]])
                        name = name.split(" ")[1]
                        try:
                            ## caso nuevo
                            conn.execute("INSERT INTO names (ID,VALUE) VALUES( '" + name + "', 1 );")
                            conn.commit()
                        except:
                            ## seleccionas si hay
                            cursor = conn.execute("SELECT * from names where ID = '" + name + "';")
                            ## incrementas y añades
                            for row in cursor:
                                val = row[1] + 1
                                conn.execute("UPDATE names set VALUE = " + str(val) + " where ID = '" + name + "';")
                                conn.commit()
                    else:
                        employee_writer.writerow(['Unknown', row[1], row[0]])
                except:
                    employee_writer.writerow(['Unknown', row[1], row[0]])
with open('personas_nombradas-2.csv', mode='w') as pf:
    pw = csv.writer(pf, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    cursor = conn.execute("SELECT * from names")
    for row in cursor:
        pw.writerow([row[0], str(row[1])])

Fase 3

Para trabajar con Gephi, , la forma mas fácil de empezar es siguiendo la siguiente estructura, un CSV dedicado a los nodos. Este tiene que seguir como mínimo la estructura [ id, label ] en este caso es [id,label,Categoria,Valor] , (¿Por qué? Cada id permitirá que en el siguiente csv “edges” podamos crear las conexiones entre los diferentes id, la de label será el nombre que aparezca en el nodo, y las otras dos permiten colorear según la categoría o según su valor) edge.csv. tiene la estructura [ id origen, id destino, tipo de conexión, valor de la conexión].

with open('edge.csv', mode='w') as edgefile:
edge = csv.writer(edgefile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
with open('covid_limpio_conocidos-2.csv') as origfile:
    orig = csv.reader(origfile, delimiter=',')
    edge.writerow(["Source", "Target","Type","Weight"])
    for row in orig:
        # influencer
        try:
            ## caso nuevo
            print(row[0])
            conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '" + row[0] + "','Influencer', 1 );")
            conn.commit()
            print("insertado influ")
            cursor = conn.execute("SELECT * from names where NAME = '" + row[0] + "';")
            ## incrementas y añades
            for row2 in cursor:
                varidinf = row2[0]
                print(varidinf)
        except:
            try:
                ## seleccionas si hay
                cursor = conn.execute("SELECT * from names where NAME = '" + row[0] + "';")
                ## incrementas y añades
                for row2 in cursor:
                    varidinf = row2[0]
                    val = row2[3] + 1
                    print(val)
                    print("update influ")
                    print(row2[1])
                    conn.execute("UPDATE names set VALUE = " + str(val) + " where NAME = '" + row2[1] + "';")
                    conn.commit()
            except :
                print("Error: updating")
        # usuario normal
        try:
            ## caso nuevo
            print(row[3].replace('.', ''))
            conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '" + row[3] + "','NormalUser', 1 );")
            conn.commit()
            cursor = conn.execute("SELECT * from names where NAME = '" + row[3] + "';")
            ## incrementas y añades
            for row2 in cursor:
                varuser = row2[0]
                print(varuser)
        except:
            try:
                ## seleccionas si hay
                cursor = conn.execute("SELECT * from names where NAME = '" + row[3] + "';")
                ## incrementas y añades
                for row2 in cursor:
                    varuser = row2[0]
                    val = row2[3] + 1
                    print("update user")
                    conn.execute("UPDATE names set VALUE = " + str(val) + " where NAME = '" + row2[3] + "';")
                    conn.commit()
            except:
                try:
                    ## seleccionas si hay
                    cursor = conn.execute("SELECT * from names where NAME = 'PrivateUserName';")
                    ## incrementas y añades
                    for row2 in cursor:
                        varuser = row2[0]
                        val = row2[3] + 1
                        print("update user")
                        conn.execute("UPDATE names set VALUE = " + str(val) + " where NAME = 'PrivateUserName';")
                        conn.commit()
                except:
                    print("Error: updating")

edge.writerow([varuser,varidinf,"Directed",0.1])
with open('node.csv', mode='w') as node_file:
node = csv.writer(node_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
node.writerow(["ID", "Label", "Category", "Value"])
cursor = conn.execute("SELECT * from names")
for row in cursor:
    node.writerow([str(row[0]), row[1][1:], row[2], str(row[3])])

node.csv

edge.csv

Después de la fase 3 ya es posible ponerse a trabajar con Gephi. Para ello tenemos que crear un nuevo proyecto y, en la ventana de laboratorio de datos, cargar en una misma área de trabajo node.csv y edge.csv

Fase 4

En la fase 4 se pueden jugar con los datos de forma que obtengamos datos más reducidos, pero más útiles. Tratamos de mostrar los datos más reducidos y creamos un nodo valor, de esta forma no es necesario poner a todos los usuarios y se puede conectar al influencer directamente con el valor.

def crearedgesbasicoconvalueinflu():
conn.execute('''DROP TABLE IF EXISTS NAMES;''')
conn.execute('''CREATE TABLE NAMES (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT UNIQUE NOT NULL , 
CATEGORY TEXT NOT NULL, VALUE INT NOT NULL );''')
with open('edge.csv', mode='w') as edgefile:
    edge = csv.writer(edgefile, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    with open('covid_limpio_conocidos-2.csv') as origfile:
        orig = csv.reader(origfile, delimiter=',')
        edge.writerow(["Source", "Target","Type","Weight"])
        for row in orig:
            # influencer
            try:
                ## caso nuevo
                print(row[0])
                conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '" + row[0] + "','Influencer', 1 );")
                conn.commit()
                print("insertado influ")
                cursor = conn.execute("SELECT * from names where NAME = '" + row[0] + "';")
                ## incrementas y añades
                for row2 in cursor:
                    varidinf = row2[0]
                    print(varidinf)
            except:
                try:
                    ## seleccionas si hay
                    cursor = conn.execute("SELECT * from names where NAME = '" + row[0] + "';")
                    ## incrementas y añades
                    for row2 in cursor:
                        varidinf = row2[0]
                        val = row2[3] + 1
                        print(val)
                        print("update influ")
                        print(row2[1])
                        conn.execute("UPDATE names set VALUE = " + str(val) + " where NAME = '" + row2[1] + "';")
                        conn.commit()
                except :
                    print("Error: updating")
            # usuario normal
            try:
                ## caso nuevo
                print(row[3].replace('.', ''))
                conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '" + row[3] + "','NormalUser', 1 );")
                conn.commit()
                cursor = conn.execute("SELECT * from names where NAME = '" + row[3] + "';")
                ## incrementas y añades
                for row2 in cursor:
                    varuser = row2[0]
                    print(varuser)
            except:
                try:
                    ## seleccionas si hay
                    cursor = conn.execute("SELECT * from names where NAME = '" + row[3] + "';")
                    ## incrementas y añades
                    for row2 in cursor:
                        varuser = row2[0]
                        val = row2[3] + 1
                        print("update user")
                        conn.execute("UPDATE names set VALUE = " + str(val) + " where NAME = '" + row2[3] + "';")
                        conn.commit()
                except:
                    try:
                        ## seleccionas si hay
                        cursor = conn.execute("SELECT * from names where NAME = 'PrivateUserName';")
                        ## incrementas y añades
                        for row2 in cursor:
                            varuser = row2[0]
                            val = row2[3] + 1
                            print("update user")
                            conn.execute("UPDATE names set VALUE = " + str(val) + " where NAME = 'PrivateUserName';")
                            conn.commit()
                    except:
                        print("Error: updating")

edge.writerow([varuser,varidinf,"Directed",0.1])
        conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '1','Value',3 );")
        conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '10','Value',9 );")
        conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '100','Value',99 );")
        conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '1000','Value',999 );")
        conn.execute("INSERT INTO names (NAME,CATEGORY,VALUE) VALUES( '2000','Value',1999 );")
        with open('node.csv', mode='w') as node_file:
            node = csv.writer(node_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
            node.writerow(["ID", "Label", "Category", "Value"])
            cursor = conn.execute("SELECT * from names")
            for row in cursor:
                node.writerow([str(row[0]), row[1], row[2], str(row[3])])

def node1a2000():
with open('node-value.csv', mode='w') as nodeg:
    nod = csv.writer(nodeg, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    with open('node.csv') as node_file:
        node = csv.reader(node_file, delimiter=',')
        nod.writerow(["ID", "Label", "Category", "Value"])
        i=0
        for row in node:
            if i != 0:
                if int(row[3]) > 2:
                    nod.writerow([str(row[0]), " "+row[1]," "+ row[2]," "+ str(row[3])])
                    if row[1] == '1':
                        val1 = row[0]
                    if row[1] == '10':
                        val10 = row[0]
                    if row[1] == '100':
                        val100 = row[0]
                    if row[1] == '1000':
                        val1000 = row[0]
                    if row[1] == '2000':
                        val2000 = row[0]
            i = i+1

def edge1a2000():
val1 = 74240
val10 = 74241
val100 = 74242
val1000 = 74243
val2000 = 74244
with open('edge-value.csv', mode='w') as edget:
    edgetwo = csv.writer(edget, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    edgetwo.writerow(["Source", "Target", "Type", "Weight"])
    with open('node.csv') as node_file:
        node = csv.reader(node_file, delimiter=',')
        print("edges")
        i = 0
        for row in node:
            print(row[3])
            if i > 0:
                if int(row[3]) > 2:
                    print(row[3])
                    if 10 > int(row[3]) >= 1:
                        edgetwo.writerow([str(row[0]), val1, "Directed", 0.1])
                    if 100 > int(row[3]) >= 10:
                        edgetwo.writerow([str(row[0]), val10, "Directed", 0.1])
                    if 1000 > int(row[3]) >= 100:
                        edgetwo.writerow([str(row[0]), val100, "Directed", 0.1])
                    if 2000 > int(row[3]) >= 1000:
                        edgetwo.writerow([str(row[0]), val1000, "Directed", 0.1])
                    if int(row[3]) >= 2000:
                        edgetwo.writerow([str(row[0]), val2000, "Directed", 0.1])
            i = i + 1

def node1000a2000():
with open('node-value-2.csv', mode='w') as nodeg:
    nod = csv.writer(nodeg, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    with open('node.csv') as node_file:
        node = csv.reader(node_file, delimiter=',')
        nod.writerow(["ID", "Label", "Category", "Value"])
        i=0
        for row in node:
            if i != 0:
                if int(row[3]) > 99:
                    nod.writerow([str(row[0]), " "+row[1]," "+ row[2]," "+ str(row[3])])
                    if row[1] == '1':
                        val1 = row[0]
                    if row[1] == '10':
                        val10 = row[0]
                    if row[1] == '100':
                        val100 = row[0]
                    if row[1] == '1000':
                        val1000 = row[0]
                    if row[1] == '2000':
                        val2000 = row[0]
            i = i+1

def edge1000a2000():
val100 = 74241
val1000 = 74242
val2000 = 74243
with open('edge-value-2.csv', mode='w') as edget:
    edgetwo = csv.writer(edget, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    edgetwo.writerow(["Source", "Target", "Type", "Weight"])
    with open('node.csv') as node_file:
        node = csv.reader(node_file, delimiter=',')
        print("edges")
        i = 0
        for row in node:
            print(row[3])
            if i > 0:
                if int(row[3]) > 99:
                    print(row[3])
                    if 1000 > int(row[3]) >= 100:
                        edgetwo.writerow([str(row[0]), val100, "Directed", 0.1])
                    if 2000 > int(row[3]) >= 1000:
                        edgetwo.writerow([str(row[0]), val1000, "Directed", 0.1])
                    if int(row[3]) >= 2000:
                        edgetwo.writerow([str(row[0]), val2000, "Directed", 0.1])
            i = i + 1