How to count the number of a label repeated for each row of excel in Python or Excel?

Question

I have an excel file that includes 10K rows, each row has some information for a tweet. For example these columns: Tweet, Date of Tweet, User Name, Retweet Count, ..., User Location, Sentiment(the value in this column is Positive or negative or neutral), State(the value of this column is 50 states of the USA), Abbreviation (the value of this column is the abbreviation of state such as CA, NJ, NY,..), CountofNegative (this column is empty and I wish to write the number of Negative tweets for each state in this column, so this column will have 50 numbers).

in following you can see the screenshot of this dataset:

Problem: count the number of negative tweets for each state or its abbreviation and write in CountofNegative column. following is my code:

import pandas as pd

file=pd.read_excel("C:/Users/amtol/Desktop/Project/filter.xlsx")
UserLocation= file["User Location"]
Sentiment= file["Sentiment"]
CountofNegative= file["CountofNegative"]
State=file["State"]
Abbreviation= file["Abbreviation"]

for i, (loc,sent) in enumerate(zip(UserLocation, Sentiment)):
    count=0
    for j, (state, abbr) in enumerate(zip(State, Abbreviation)):
        if (loc == state or loc == abbr and sent == "Negative"):
            count=count+1
        file.loc[j+1,"CountofNegative"]=count

print(CountofNegative)

file.to_excel("C:/Users/amtol/Desktop/Project/filter.xlsx")

there is no error but when the output file is created, the first 24 values for column "CountofNegative" are zeros and the rest of them are ones(which they are not correct answers). Also, I wanted to test the program by print(CountofNegative), which still nothing happened. (no output). how can I fix my code?

Answer 1

Okay, so if there are no generalities in the abbreviation and the state names then first convert the full names to abbreviation using the dict as in the code. Make some changes in the dict if some names/abbreviations are not proper.

Since we are concerned only with 'Negative' Counts. Convert Negative to 1 and other responses by 0 as below:

#Created sample dataset
 data={'State':['New York','New York','New York','New Jersey','New Jersey','New Jersey','California','California','California','NY','NJ','CA'],
'Sentiment' :['Negative','Positive','Negative','Neutral','Negative','Positive','Positive','Positive','Positive','Negative','Positive','Negative'], }
 df = pd.DataFrame(data, columns = ['State', 'Sentiment'])
 print (df)

#Dictionary of US states and abbreviations 
 di = {
'Alabama': 'AL',
'Alaska': 'AK',
'American Samoa': 'AS',
'Arizona': 'AZ',
'Arkansas': 'AR',
'California': 'CA',
'Colorado': 'CO',
'Connecticut': 'CT',
'Delaware': 'DE',
'District of Columbia': 'DC',
'Florida': 'FL',
'Georgia': 'GA',
'Guam': 'GU',
'Hawaii': 'HI',
'Idaho': 'ID',
'Illinois': 'IL',
'Indiana': 'IN',
'Iowa': 'IA',
'Kansas': 'KS',
'Kentucky': 'KY',
'Louisiana': 'LA',
'Maine': 'ME',
'Maryland': 'MD',
'Massachusetts': 'MA',
'Michigan': 'MI',
'Minnesota': 'MN',
'Mississippi': 'MS',
'Missouri': 'MO',
'Montana': 'MT',
'Nebraska': 'NE',
'Nevada': 'NV',
'New Hampshire': 'NH',
'New Jersey': 'NJ',
'New Mexico': 'NM',
'New York': 'NY',
'North Carolina': 'NC',
'North Dakota': 'ND',
'Northern Mariana Islands':'MP',
'Ohio': 'OH',
'Oklahoma': 'OK',
'Oregon': 'OR',
'Pennsylvania': 'PA',
'Puerto Rico': 'PR',
'Rhode Island': 'RI',
'South Carolina': 'SC',
'South Dakota': 'SD',
'Tennessee': 'TN',
'Texas': 'TX',
'Utah': 'UT',
'Vermont': 'VT',
'Virgin Islands': 'VI',
'Virginia': 'VA',
'Washington': 'WA',
'West Virginia': 'WV',
'Wisconsin': 'WI',
'Wyoming': 'WY'
}

#Match the names in the dictionary to columns using
df=df.replace({"State": di}) 

#Create a function to give weight only to negative comments
def convert_to_int(word):
word_dict = {'Negative':1, 'Positive':0, 'Neutral':0, 0: 0}
return word_dict[word]

#Convert the Sentiment col as per the above function
df['Sentiment'] = df['Sentiment'].apply(lambda x : convert_to_int(x))

#Now the final part of doing the count of negative
df['negative_sum'] = df['Sentiment'].groupby(df['State']).transform('sum')


#My final output

 State  Sentiment   negative_sum
0   NY  1   3
1   NY  0   3
2   NY  1   3
3   NJ  0   1
4   NJ  1   1
5   NJ  0   1
6   CA  0   1
7   CA  0   1
8   CA  0   1
9   NY  1   3
10  NJ  0   1
11  CA  1   1

Now, you also have the option to again convert the Sentiment Column to strings as now we have the column we needed of negative sum. I hope this suffice the purpose.