Nested loop in Python: Group by day of date, then by string

Question

I'm working with Salesforce data. I'd like to be able to group by data by sentiment and day.

Example row:

Date                        Provider    Sentiment
2017-09-01T01:11:14.000Z    Facebook    Positive

Issue 1: converting SF data to day and then in the right format:

I thought I could use a function:

date = sentiment_day['CreatedDate']

def day(date):
    return date[:10]

 days = date.apply(day)

 formatted_days = pd.to_datetime(days)

 sentiment_day = sentiment_day.assign(Date = Date.values)

This works correctly, and now i'd like to get each day, and then obtain the sentiment.

New example row:

Date                        Provider    Sentiment   Date
2017-09-01T01:11:14.000Z    Facebook    Positive    2017-09-01

I can write a loop that counts the totals across every day for each sentiment:

num_negative = 0
num_positive = 0
num_neutral = 0

 for sentiment in sentiment_day['Sentiment']:
    if sentiment == "Negative":
        num_negative +=1
    if sentiment == "Positive":
        num_positive +=1
    if sentiment == "Neutral":
        num_neutral +=1

print(num_negative)
print(num_positive)
print(num_neutral)

Which outputs:

242 536 1222

Good!

Issue 2: I'd like to get the count for each day in my dataframe

I believe that this is a nested loop? But I can't find any relevant examples to copy.

Answer 1

Assuming that you have only a finite number of Sentiment states and providers, for example;

DateTime,   Provider,   Sentiment
2023-05-10T12:11:12,    Facebook,   Positive
2023-05-11T09:09:55,    Twitter,    Positive
2023-05-25T02:00:52,    Twitter,    Negative
2023-05-26T00:48:22,    Facebook,   Positive
2023-05-30T15:40:37,    Facebook,   Positive
2023-06-14T18:24:38,    Facebook,   Negative
2023-07-02T09:17:39,    Facebook,   Positive
2023-07-14T11:25:05,    Twitter,    Positive
2023-07-15T08:01:35,    Twitter,    Negative
2023-07-22T22:49:52,    Facebook,   Positive

In Python, I would use a single for to populate different lists with the separated date and time along with the sentiment. You can then count the number of values in each. Personally, I would have thought this would be easier to do in SQL, as you would then simply be able to do something like the following;

Select * where Sentiment = "Positive" and Provider = "Facebook"