<jupyter_start><jupyter_text>GET DRIVER ENTITIES<jupyter_code>tweets['driver_id'] = tweets.apply(lambda tweets: getDrivers(tweets), 1)
driver_assign = tweets[['tweet_id', 'driver_id']]
driver_assign = driver_assign.explode('driver_id')
driver_assign = driver_assign.dropna(axis=0)
driver_assign['driver_id'] = driver_assign['driver_id'].astype(int)
driver_assign.to_sql('driver_entities', engine, index=False, if_exists='replace')
driver_assign.sort_values('tweet_id')
driver_assign<jupyter_output><empty_output><jupyter_text>GET TWEET META DATA<jupyter_code>import requests
import os
import json

# To set your enviornment variables in your terminal run the following line:
# export 'BEARER_TOKEN'='<your_bearer_token>'


def auth():
    return os.environ.get("BEARER_TOKEN")


def create_url(tweet_id):
#     tweet_fields = "tweet.fields=lang,author_id"
    tweet_fields = 'tweet.fields=public_metrics,geo'
    # Tweet fields are adjustable.
    # Options include:
    # attachments, author_id, context_annotations,
    # conversation_id, created_at, entities, geo, id,
    # in_reply_to_user_id, lang, non_public_metrics, organic_metrics,
    # possibly_sensitive, promoted_metrics, public_metrics, referenced_tweets,
    # source, text, and withheld
    id = "ids={}".format(tweet_id)
    # You can adjust ids to include a single Tweets.
    # Or you can add to up to 100 comma-separated IDs
    url = "https://api.twitter.com/2/tweets?{}&{}".format(id, tweet_fields)
    return url


def create_headers(bearer_token):
    headers = {"Authorization": "Bearer {}".format(bearer_token)}
    return headers


def connect_to_endpoint(url, headers):
    response = requests.request("GET", url, headers=headers)
    print(response.status_code)
    if response.status_code != 200:
        raise Exception(
            "Request returned an error: {} {}".format(
                response.status_code, response.text
            )
        )
    return response.json()


def main():
    bearer_token = auth()
    url = create_url(tweets['tweet_id'][0])
    headers = create_headers(bearer_token)
    json_response = connect_to_endpoint(url, headers)
    print(json_response, "\n\n")
    print(json.dumps(json_response, indent=4, sort_keys=True))


if __name__ == "__main__":
    main()
import math
batch = 0
batch_size = 100
batches = math.ceil(len(data_df)/batch_size)
ids = str(list(data_df['tweet_id'][batch*batch_size:batch*batch_size+batch_size]))\
    .replace("'", "").replace("[", "").replace("]", "").replace(" ", "")
batches
batch_size = 100
for batch in range(batches):   
    try:
        ids = str(list(data_df['tweet_id'][batch*batch_size:batch*batch_size+batch_size-1]))\
            .replace("'", "").replace("[", "").replace("]", "").replace(" ", "")
        bearer_token = auth()
        url = create_url(ids)
        headers = create_headers(bearer_token)
        json_response = connect_to_endpoint(url, headers)
        col.insert_many(json_response['data'])
        print(batch)
    except:
        print(batch)
#     print(batch*batch_size, batch*batch_size+batch_size-1)
ids
for batch in range(442, batches)
ids = str(list(data_df['tweet_id'][442*batch_size:]))\
    .replace("'", "").replace("[", "").replace("]", "").replace(" ", "")
bearer_token = auth()
url = create_url(ids)
headers = create_headers(bearer_token)
json_response = connect_to_endpoint(url, headers)
# col.insert_many(json_response['data'])
print(batch)
#     print(batch*batch_size, batch*batch_size+batch_size-1)
len(ids)
json_response['data']
json_response<jupyter_output><empty_output><jupyter_text>ETL META<jupyter_code>df = pd.DataFrame(list(col.find({})))
df = df.drop_duplicates('id', keep='first').reset_index(drop=True)
df
df['retweet_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'retweet_count'), 1)
df['reply_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'reply_count'), 1)
df['like_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'like_count'), 1)
df['quote_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'quote_count'), 1)
# df['tweet_id'] = df.apply(lambda df: parse_json(df, 'data', 'id'), 1)
df = df.rename(index=str, columns={'id': 'tweet_id'})
df = df[['tweet_id','retweet_count','reply_count','like_count','quote_count']]
df
def escapeArray(df, column):
    if len(df[column]) == 0:
        return ''
    else:
        return df[column]

for column in list(df):
    for i, j in zip(df[column], range(len(df))):
        if isinstance(i, np.ndarray):
            df[column][j] = 0
            
df = df.reset_index(drop=True)
df['retweet_count'] = df['retweet_count'].astype(int)
df['reply_count'] = df['reply_count'].astype(int)
df['like_count'] = df['like_count'].astype(int)
df['quote_count'] = df['quote_count'].astype(int)
df.to_sql('meta', engine, index=False, if_exists='replace')<jupyter_output><empty_output><jupyter_text>USER LOOKUP<jupyter_code>import requests
import os
import json

# To set your enviornment variables in your terminal run the following line:
# export 'BEARER_TOKEN'='<your_bearer_token>'


def auth():
    return os.environ.get("BEARER_TOKEN")


def create_url(usernames):
    # Specify the usernames that you want to lookup below
    # You can enter up to 100 comma-separated values.
#     usernames = "69008563"
    user_fields = "user.fields=description,created_at,public_metrics,location,name,username,verified"
    # User fields are adjustable, options include:
    # created_at, description, entities, id, location, name,
    # pinned_tweet_id, profile_image_url, protected,
    # public_metrics, url, username, verified, and withheld
    url = "https://api.twitter.com/2/users?ids={}&{}".format(usernames, user_fields)
    return url


def create_headers(bearer_token):
    headers = {"Authorization": "Bearer {}".format(bearer_token)}
    return headers


def connect_to_endpoint(url, headers):
    response = requests.request("GET", url, headers=headers)
    print(response.status_code)
    if response.status_code != 200:
        raise Exception(
            "Request returned an error: {} {}".format(
                response.status_code, response.text
            )
        )
    return response.json()


def main():
    bearer_token = auth()
    url = create_url()
    headers = create_headers(bearer_token)
    json_response = connect_to_endpoint(url, headers)
    print(json.dumps(json_response, indent=4, sort_keys=True))


# if __name__ == "__main__":
#     main()
author_df = data_df.drop_duplicates('author_id', keep='first').reset_index(drop=True)
author_df
def auth():
    return os.environ.get("BEARER_TOKEN")

def create_headers(bearer_token):
    headers = {"Authorization": "Bearer {}".format(bearer_token)}
    return headers

bearer_token = auth()
headers = create_headers(bearer_token)
ids = '1364879051438002176,1350309702'
user_fields = "user.fields=description,created_at,public_metrics,location,name,username,verified"
r = requests.request("GET", 'https://api.twitter.com/2/users/{}?expansions=pinned_tweet_id&{}'.format(ids, user_fields), headers=headers)
headers = create_headers(bearer_token)
ids = 'ids=1364879051438002176,1350309702'
r = requests.request("GET", 'https://api.twitter.com/2/users?{}&{}'.format(ids, user_fields), headers=headers)
print(json.loads(r.text))
r
ids
batch_size = 100
batches = math.ceil(len(author_df)/batch_size)
for batch in range(batches):   
    try:
        ids = str(list(author_df['author_id'][batch*batch_size:batch*batch_size+batch_size-1]))\
            .replace("'", "").replace("[", "").replace("]", "").replace(" ", "")
        bearer_token = auth()
        url = create_url(ids)
        headers = create_headers(bearer_token)
        json_response = connect_to_endpoint(url, headers)
        col.insert_many(json_response['data'])
        print(batch)
    except Exception:
        print(batch)
#     print(batch*batch_size, batch*batch_size+batch_size-1)
df = pd.DataFrame(list(col.find({})))
df = df[1074:].reset_index(drop=True)
df['public_metrics'][0]
df['followers_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'followers_count'), 1)
df['following_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'following_count'), 1)
df['tweet_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'tweet_count'), 1)
df['listed_count'] = df.apply(lambda df: parse_json(df, 'public_metrics', 'listed_count'), 1)
df['name'] = df.apply(lambda df: getRawText(df, 'name'), 1)
df = df.rename(index=str, columns={'id': 'author_id'})
df = df[[
    'author_id', 'description', 'verified', 'username', 'created_at', 'name',
    'location', 'followers_count', 'following_count','tweet_count', 'listed_count'
]]
engine = create_engine('postgresql://postgres@localhost:5432/frenchgp')
df.to_sql('author', engine, if_exists='replace', index=False)
for i, idx in zip(list(df), range(len(df))):
    for j in df[i]:
        if isinstance(j, dict):
            print(i, j, idx)<jupyter_output>withheld {'country_codes': ['RU']} 7
withheld {'country_codes': ['RU']} 7
{'fraction_non_alphanumeric': 0.15516289342904474, 'fraction_numerical': 0.014467145223633352, 'mean_word_length': 5.017275747508306, 'pattern_counts': {'":': 5, '<': 18, '<?xml version=': 0, '>': 18, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '98636', 'license': 'MIT', 'n_tokens_mistral': 3181, 'n_tokens_neox': 3085, 'n_words': 591, 'path': 'src/python/ipynb/updater.ipynb', 'repo_name': 'ilyakhruschev/f1', 'size': '9055'}