Why i can't get two specifics texts from CSV file and make a dataframe with this?

Question

I have a csv file with many tweets. I trying to get two specifics texts and make a dataframe with this information: date, hashtag

Created At,Text
Fri Jan 06 11:02:14 +0000 2017, #beta #betalab #mg Afiliada da Globo: Apresentador no AM é demitido após criticar governador

I would like to have this result:

Below is one of many ways i tried but no matter, the results isn't what i need.

I tried exactly the code below

import os
os.chdir(r'C:\Users\Documents')
dataset = pd.read_csv('Tweets_Mg.csv', encoding='utf-8')
dataset.drop_duplicates(['Text'], inplace=True)


def Preprocessing(instancia):

    stemmer = nltk.stem.RSLPStemmer()
    
    instancia = re.sub(r"http\S+", "", instancia).lower().replace('?','').replace('!','').replace('.','').replace(';','').replace('-','').replace(':','').replace(')','')
    
    #List of stopwords in portuguese language
    stopwords = set(nltk.corpus.stopwords.words('portuguese'))
    palavras = [stemmer.stem(i) for i in instancia.split() if not i in stopwords]
    
    return (" ".join(palavras))

tweets = [Preprocessing(i) for i in dataset.Text]



def procurar_hashtags(tweet):
 
    return re.findall('(#[A-Za-z]+[A-Za-z0-9-_]+)', tweet)
hashtag_list = [procurar_hashtags(i) for i in tweets] 


def hashtag_top(hashtag_list):
    hashtag_df = pd.DataFrame(hashtag_list)
    hashtag_df = pd.concat([hashtag_df[0],hashtag_df[1],hashtag_df[2],
                           hashtag_df[3],hashtag_df[4],hashtag_df[5],
                           hashtag_df[6],hashtag_df[7],
                           hashtag_df[8]], ignore_index=True)
    
    hashtag_df = hashtag_df.dropna()
    hashtag_df = pd.DataFrame(hashtag_df)
    hashags_unicas = hashtag_df[0].value_counts()
    
    return hashags_unicas

 hashtag_dataframe = hashtag_top(hashtag_list)
 hashtag_dataframe[hashtag_dataframe>=25]

The result is not good, no matter what I do, I can't capture the dates from the hashtags. Like this:

 #timbet                  193
 #glob                    119
 #operacaobetalab         118
 #sigodevolt               77

I doing something wrong...

Answer 1

You can use this as a starting point:

from itertools import product
from pathlib import Path
import csv
import re

hashtag = re.compile('(#\w+)')

csvfile = Path('/path/to/your/file.csv')

tags_by_date = []

for line in csv.reader(csvfile.open()):
    tags = hashtag.findall(line[1])
    if tags:
        for date, tag in product(line[0], tags):
            tags_by_date.append([date, tag])

And here is a small proof of concept (far from a complete solution since you didn't take the time to elaborate your question in a better way):

>>> line
['Fri Jan 06 11:02:14 +0000 2017', ' #beta #betalab #mg Afiliada da Globo: Apresentador no AM é demitido após criticar governador']
>>> hashtag.findall(line[1])
['#beta', '#betalab', '#mg']