I have a large table with a comments column (contains large strings of text) and a date column on which the comment was posted. I created a separate vector of keywords (we'll call this key) and I want to count how many matches there are for each day. This gets me close, however it counts matches across the entire dataset, where I need it broken down by each day. The code:
key <- c('A','B','C','D')
table_mentions <- data.frame(key, colsum(sapply(key, grepl, table_orig$comment)))
This returns the following format:
Key | Count of mentions |
---|---|
A | 85 |
B | 52 |
C | 54 |
D | 54 |
This is close, but it is a count of matches across the entire data set. I need the data broken down in the following format:
comm_date | Key | Count of mentions |
---|---|---|
03-01-21 | A | 4 |
03-01-21 | B | 3 |
03-01-21 | C | 7 |
03-01-21 | D | 5 |
03-02-21 | A | 6 |
03-02-21 | B | 3 |
03-02-21 | C | 2 |
03-02-21 | D | 6 |
03-03-21 | A | 5 |
03-03-21 | B | 6 |
03-03-21 | C | 9 |
03-03-21 | D | 11 |
....and so on from there (I made up the second table's numbers)...
I can't for the life of me figure out how to make this work. I tried splitting the data frame by date, but I'm unsure of how to pass the list of split data frames into the function above and then merge the results back together.
Thank you!
EDIT: Here is the dput(table_orig).
structure(list(comm_date = c("01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21", "01-03-21",
"01-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21",
"02-03-21", "02-03-21", "02-03-21", "02-03-21", "02-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21", "03-03-21",
"03-03-21", "03-03-21", "03-03-21"), comment = c("I know the eyes are on BTC and",
"Isnt it funny how for years te", "Coinbase just made a bunch of ",
"Still no ADA though?", "No", "What color do you associate Ma",
"... Red?", "That was February", "See ya in the next daily discu",
"C u there bro", "Just woke up in all this green", "How long time it\031s taking for ",
"Chill. Check in another 10 mi", "Depends on the coin/network. ",
"Depends on the coin.", "Is it just me or is the Crypto", "Resistance at 50k is tough",
"For now...", "have to wait 3 minutes for new", "Correction over? I feel like w",
"We might start barting for a f", "Knock, knock, knockin' on Heav",
"See you guys tomorrow for more", "Before we get to the next dail",
"Fomo back in!!!!! Gogogogoogoo", "AAVE you beast=\200", "I remember interacting with a ",
"Aave (AAVE), Balancer (BAL), B", "Ah, that's probably part of wh",
"LRC is garbo", "wait... NY residents couldn't ", "Grandma Bitcoin, what mighty b",
"The biggest ones =", "Remember this feeling next tim", "Be a bit weary during Q4 2021 ",
"These AAVE and UNI longs got m", "Very bullish on UNI. UNI v3 sh",
"If it can beat the 50k wall, s", "not trying to jinx it, but I\031m",
"&#x200B;\n\n",
"member when this place was a g", "See you in another life brotha",
"Come with me, Hail Mary \nRun ", "Feeling quite optimistic about",
"I\031m suddenly feeling optimisti", "Glad to see the optimism of so",
"Those three are my biggest hol", "Amazing portfolio !!", "I'm a big fan of LINK",
"Two wrongs make a right?", "How can Coinbase not list the ",
"Agreed. Not sure what they hav", "Classic Coinbase", "Na feb is blue",
"Thanks man I got it\nI fist tra", "Tnx", "Tnx I got it", "I give it 20 mins",
"Yeah annoying. But if BTC brok", "Hay, hay, hay hay hayyy",
"See you buddy!", "I might be wrong, but nobody c", "Since when?",
"We can't even sign up for some", "Are you using TOR?", "I do not. I just use a VPN. D",
"The dip is the light if you ha", "If? Ye of little faith.\n\n52k i",
"We never left. Only noobs got ", "Far out man.\n\nEdit: read sente",
"Pretty much all I saw:\n\n![gif]", "Lmao. =3That\031s about right.",
"The hunt: Showdown", "And until L2\031s or EIP 1559 are", "Exactly",
"I\031ve been in crypto for many y", "I completely understand what y",
"From the first sentence of my ", "This was quality my guy LOL",
"I don't have a whole lot in bu", "Crossing 50K; the gift that ke",
"True enough, I should not be s", "Same i opened a long near the ",
"Approaching liquidation is qui", "Possible, but btc will still d",
"ADA has more HODLers than trad", "Its 24 volume is a higher perc",
"I\031d be curious what that numbe", "Cardo is a vegetable tha is al",
"Reminds me of that episode of ", "Jajajaja, that is literally th",
"No you\031re suppose to dip until", "Everyone laughed at me for buy",
"Laughed? Laughing. Cause we ne", "ATH incoming", "ETH is like that girl in a mov",
"I really should do analysis of", "You do that. If you need me, I",
"So MIR staking APY just went u", "Totally agree. Staking has bee",
"I dont earn any staking income", "Nano dropping out of the top 1",
"I\031m I only retard who invested", "If we close the daily above $4",
"Why 48.3 in particular?", "What are you looking at for sp",
"The ema 50 and 100 are right a", "Perhaps the bitcoin is fueled ",
"Any fresh traction?", "Does anyone have a ticker/dash", "I wrote a script to pull the t",
"Dip into the market, don't dip", "When I dip you dip we dip\n\n&am",
"Me too lol. Got some more ETH ", "Its strange that no-one has me",
"ASIA LESGOOOOOOOOOOO", "Yes Asia go! Except you, F2poo", "How do i sell the Moons i have",
"Sell them on Honeyswap. Transf", "So I\031m sure there\031s a better w",
"Not OP, but thanks for this :-", "Those conversion rates are so ",
"Are they>7<ý\rB\017", "in that site u get for 444 moo", "can anyone confirm how much wa",
"Better comparison is market ca", "Yeah, the eth printer has been",
"Sucks to see all the red but t", "I mean \034just a day\035 seems apt ",
"If XRP is having a lawsuit fro", "The sec isn't the issue, it's ",
"Its based in the US so the SEC", "Very sketchy ladder going up f",
"The Hardy Boyz, The Dudley Boy", "They\031re hitting people with la",
"\n\nyeah, whats next? FLAGS?", "Gemini Earn saved me. I was ge",
"Thats the great hodl feature. ", "Get into a staking coin or 2. ",
"There\031s nothing wrong with usi", "How many of you guys actually ",
"TA in Crypto only works in hin", "I try but man it\031s hard having",
"Tweets from billionaires blows", "Lol isn\031t that a meme word",
"Lol don\031t think so", "More people than actually use ", "Just remembet that this whole ",
"Coinbase also definitely wants", "Nano dropping out of top 100.",
"Why is bat pumping!?", "BOOM BAT OVER 0.50 <‰", "ok so I'm on the verge of pull",
"got assistance from the Pangol", "If I were to trade BTC > ET",
"Markets going crazy again, por", "Nano seems to be ready for nex",
"Reckon nano is a good long ter", "Yes. Not going to shill but ju",
"10$ we come", "Will BTC break 50k or get reje", "We broke it! >s",
"LINK finally moving!", "Let's go, Cardano, show those ", "Go LINK $29 USD, go =\200 =\200 =",
"someone shilled me Ecomi OMI t", "Go LINK $29 USD, go =\200 =\200 =",
"https://vitalik.ca/general/202", "is it gonna be another bleed w",
"Noooooooo Nan", "Any show you recommend? im get", "American Gods",
"Do you like racing? \nDrive to ", "Rupauls Drag Race. Ive seen ev",
"The Expanse", "The Boys", "Have you heard of The Office?", "Woohooo waiting for pay day =H",
"I\031m relatively new to the sub.", "https://i.imgur.com/eTFToC4.jp",
"This is okay.", "I have passed the test", "Does Kraken not have an XLM-&g",
"Me everytime my portfolio goes", "At least now we have less to l",
"Can anyone explain the volume ", " Per definition, volume is mea",
"Payday, crypto day", "Yes! Come on March 15!", "Can't wait for the game Age of",
"btw if you're on steam, whishl", "Isn\031t wish list just a fancy b",
"yes, but steam will feature ga", "Oooh so I\031m literally wishing ",
"When you know, you know", "what if I never know >z", "then you know that you never k",
"^^^woah", " You buy more and figure it ou", "If nano drops out of top 100 I",
"Really? There seems to be fair", "Well spoken! But a lot of coin",
"it's 84 on on coinmarketcap. i", "Don\031t sweat it dude \n\nThere\031s ",
"See y'all on the next thread !", "I'm really deliberating whethe",
"I could definitely see another", "And what about next year?",
"I dunno, man. I wouldn't touch", "ADA is more likely to go from ",
"This", "\n\n\nWe", "Roughly how much did the gas ",
"I didn't even notice nano had ", "1. It's 88th on Coingecko.\n2. ",
"Don't get why you're hostile. ", "This was not hostile but a dir",
"you sound hostile bro. he just", "The 50 has been holding strong",
"I mean daily chart lol", "Appreciate it, I\031ll be looking",
"I went Aave, XLM, XTZ, and add", "RIGHT! To the moon and beyond ",
"Moon is so bearish. To Uranus!", "There's a lot of crypto here t",
"Asian Lesbos you say >\024", "No probs bur someone told me y",
"Mhm I hear you. Thanks for the", "Username checks out", "=\024",
"Does it not balance out with t", "Not really, because on Honeysw",
"But then you lose them transfe", "idk man i haven't look at it y",
"TA is a distant secondary when", "But Coinbase is responsible fo",
"By volume sure. Not by end co", "The silver lining is the accel",
"That would be a spicy market", "Can I swap it back to bitcoin ",
"Yeah it depends on the facilit", "I am not familiar with SDEX, i",
"Yes, it supports several asset", "Lucky to be working, had a few",
"Hmm makes sense but I also pos", "Ahh.. Then all you can do is c",
"I see. Thanks!", "Yeah but I mean can I see that", "Search bar, moon dist",
"Yeah I agree with you, -90% is", "I think you didn't get the Zoo",
"Nope haha I didn't get it, I t", "Yeah I thought it was a legit ",
"Cool. Thanks for your insight", "Hey thanks man I am new here w",
"Pancakeswap isn't offering me ", "I know. It's beefy I'd be worr",
"I've tested a small amount of ", "Holding a big bag of GRT since",
"Let's all support our beloved ", "Anyone can tell me why I\031m not",
"300 BTC is basically all that'", "That's still 15 million usd on",
"Considering how the 50k sell w", "Get into VET now or wait for a",
"Get in now but leave some mone", "Get in right away. You can alw",
"Enjin just quietly goes about ", "they have announced to have th",
"The Agave airdrop is now worth", "Lol damn congrats if you got i",
"I did and its a nice feeling a", "I'll be you next time >#\nWhere",
"Honestly nowhere as I don't th", "Is atom over with?", "What\031s that even mean is it ov",
"Your screen is upside down.", "Nope, it's exploding like an A",
"[deleted]", "Just asking", "I mean it truly is late, but t",
"VET (Vechain) to the moon=\200=\200=", "Well deserved too! =\200",
"Sand continuing to kick ass. A", "Love CMC for airdropping me 25",
"[me_irl](https://imgur.com/2qI", "Thought on going in on MATIC n",
"He will probably keep dipping ", "I went in now because it\031s bee",
"It's up 446% from a month ago ", "AY EL GE OH", "YU GI OH?",
"YU YU HAKUSHO?", "Amazing how hitting the previo", "Here's go Matic again on the m",
"?", "The chart for SLT is fuckkng i", "Yeh I\031ve been watching this on",
"Platform is going live this mo", "I threw some money at it but I",
"They are doing real estate as ", "I will never forget that day w",
"Go VET! Let\031s see if this tren", "Does the Buy/Sell ratio on Coi",
"Yes, but it's not the only mar", "Is people on this sub 100% bul",
"You should be looking at the B", "It's still the silver standard",
"Hella bullish", "I'm bullish on ETH for the lon", "Still massively bullish but ob",
"The bullishness here is defini", "1. How do some people achieve ",
"Not sure on the first point bu", "God damn VET =3", "And ffs VTHO",
"Atom appears to be the flavor ", "See my comment here: https://w",
"The same or better than polkad", "They have a pretty website wit",
"Yeah looking into it more. I\031m", "Anyone have any opinions on Ko",
"He is good. Ilikey", "How many proud ATOM holders do", "Reporting in, great to see ATO",
"[deleted]")), row.names = c(NA, -522L), class = "data.frame")