如何根据另一列中的时间间隔聚合一列中的行？

Question

我有一个包含Reddit数据的数据集。更具体地说，subreddit GME中提到“GME”的所有帖子。请参见下面的内容，了解其外观：

为了便于复制，以下是前25行的词典：

    {'date': {0: '2020-12-30 22:43:25',
  1: '2020-12-30 22:21:04',
  2: '2020-12-30 21:28:31',
  3: '2020-12-30 20:35:29',
  4: '2020-12-30 19:32:09',
  5: '2020-12-30 19:30:35',
  6: '2020-12-30 19:08:54',
  7: '2020-12-30 18:57:17',
  8: '2020-12-30 18:56:47',
  9: '2020-12-30 18:55:03',
  10: '2020-12-30 18:44:48',
  11: '2020-12-30 18:40:32',
  12: '2020-12-30 18:18:24',
  13: '2020-12-30 18:02:20',
  14: '2020-12-30 17:08:10',
  15: '2020-12-30 17:08:00',
  16: '2020-12-30 16:51:25',
  17: '2020-12-30 16:30:10',
  18: '2020-12-30 16:24:00',
  19: '2020-12-30 16:20:09',
  20: '2020-12-30 16:04:59',
  21: '2020-12-30 15:57:02',
  22: '2020-12-30 15:52:34',
  23: '2020-12-30 15:32:27',
  24: '2020-12-30 15:29:35'},
 'post': {0: '​\n\n*Processing video 9xcv9jkbke861...*',
  1: "I kept a summary of my reasoning for getting into GameStop (pictured below) I thought I'd share it with everyone so you can have some solid talking points next time you talk to your rich uncle who hopefully is your wife's boyfriend too so you can hang that over his head and have him go all in on some GameStop.\n\nReasoning for Price Targets using comps:\n\nBBY (Best Buy) currently trades at a market cap / revenue (12 months forward) of 26.25b / 47.45b = 0.55 price / sales.\n\nBecause:\n\n1) GME uses a digital first, omni-channel approach\n\n2) Will have positive free cash flows due to the high demand from XBOX/PS5\n\n3) Has a digital revenue sharing agreement with $MSFT\n\n4) Has data from 55 Power Up Rewards members they can sell and leverage for additional revenue streams\n\n5) Has a new insider, our lord and savior, RC god bless, who took CHWY from ground up to now trade at a price to sales of well above 5\n\nThen:\n\nTaking a proprietary formula spoken to me in a dream by RC while Melvin was getting liquidated, I have arrived at a price / sales of 0.843 which is higher than best buy but much less than Chewy. Using analyst estimates for the 12 months forward revs (5.76b) and solving for share price, we get a fair value of  \\~**69.69 per share**.\n\np / 5.76b = 0.8439\n\nshare price = p / shares outstanding (69.75m)\n\n**Squeeze PT** \n\n**RC** said on 12.15.20 ([13D Amendment Link](https://www.sec.gov/Archives/edgar/data/1326380/000119380520001571/e620151_sc13da-gamestop.htm)) that he is ready to be our savior.  This was exactly 14 days ago as we head into a **new year**.\n\nA year is 365 days **minus** 14 days from when RC defended is 351.\n\n$351 + fair value from earlier = **420.69 per share** lets go baby. \n\n​\n\n[Background GME DD](https://preview.redd.it/z38h0k6x9e861.png?width=1184&format=png&auto=webp&s=fca17f969144a116aa919fc072a22ed94916234b)",
  2: "What happened to nio? Nio day is soon and I dont see any word spoken about it.  Like today nio took a rocket up to the star station.\n\nIs this due to the last mod ending his watch or nio being too much for the new comers?\n\nSeems like the sub is becoming what everyone was freaking out about back in the day. \n\nAlso, didn't the sub get locked or set to private as well or is my memory shot from all the paint chips?",
  3: "Melvin Capital has over 5.2 million puts against GME. \n\nThis is our moment, this is our time, we say, fuck the big banks. Its their time to get fucked in the ass. They keep swinging their strap on big cock around like its theirs. Its not, its ours now. We must prevail through the unprecedented times. We must become Victorious. This is the way to the utopia. By holding ground and advancing on GME. Buy your shares, buy your calls, do not short. \n\nTogether we can send this baby interstellar. \n\nGod bless and good luck\n\nVictory is near\n\n\nI'm not a financial advisor, I am a retard.",
  4: "I'm currently 💎👐 on 440 GME shares (was 420 but had to buy the dip, sorry 🤷) expecting the business to turn around as Covid vaccines get distributed broadly and as the company shifts focus to online retail. With the brand recognition and inventory they have on hand, I see a roughly $3B market cap as a reasonable valuation (especially once they stop being net negative post covid).  \n\nBut I see this playing out over a timeframe in which WSB retards will get distracted by 🚀🚀🚀 left and right 🚀🚀🚀 instead of realizing that even 0.5% gains per week are 25% year over year. \n\nDoes this community need investments to be wild emotional rollercoasters that cause anxiety and fever dreams of 🌚 and \U0001faa2??? Or do we feel empty inside if we have investments that allow us to rest easy and only check the markets quarterly instead of every quarter minute? \n\nTldr; can we keep the meme steam pumping long enough for me to retire a thousandaire?? 🚀🚀🚀$GME🚀🚀🚀💎👐#GameGame",
  5: '[removed]',
  6: 'Within the last hour some shitty EV helicopter SPAC started getting spammed HARD on the daily thread. It’s weird seeing the majority of GME posts vanish only to be replaced by these out of **nowhere.**\n\nIs there a way to tell if these are bots? The pump and dumps happening here have gotten insane lately. WSB used to have some insightful DD about lumber companies and virgins flying in space, but now it’s 50% people pumping garbage.\n\nPositions: Every ARK etf, BABA and JD.\n\ninb4 my account is 4 months old - i’ve been on Reddit for a decade, my mains got banned for saying “retard” here too many times.',
  7: '[removed]',
  8: "G. Kennedy Fuld Jr isn't a real person, the profile pic is a photo of Michael Milken. \n\nI'm betting a ton of the commenters and upvoters are bots. This is clear manipulation and dozens of you dinguses who actually started buying shares are ensuring this shit will happen more often. \n\nAutist Recommendation: Ban everyone in that thread except for the commenters who pointed out what I'm saying.",
  9: '[removed]',
  10: '[removed]',
  11: '[removed]',
  12: 'Everyone is blindly betting. Either on the belief that buying the dip means the stock will eventually go up- or buying a stock that is doing good and hoping it does more good. Others will have the belief that in long term- eventually the average stock will rise so you should diamond hand it. While others believe it is better to take small loss and take back with paper hands. \n\n“There is no STREDGY” - Donald J trump, before the big GUH. \n\nHalf the stock that out preform their earnings are going shit below. For some reason half the steel makers and manufacturers went sky high on a pandemic year when physical labor was unemployed- but the other half went down south. \n\nIT IS GAMBLING. $PLTR won. But $GME is gonna make you retards lose tons of money.',
  13: '[removed]',
  14: '[removed]',
  15: 'I changed my post title into something sexy so u idiot fucks can make a little money front running our GME short counterparty. Why should u listen to me ? I called the short raid yesterday and posted a fucking precient ass warning right after open while GME was at -2% but u bunch of dimwits diamondhanded thru yesterday. Maybe u’ll listen to me now (LMAO no u wont)\n\nIMO Our short counterparty is incentivized to smash gme at year end (12/31) because it makes their year over year performance look better. Also given how illiquid this stonk is it presumably takes very little capital to move it - imagine Melvin, in addition to the 10’s of millions of put positions they have, are also short 200 million dollars worth of GME. Knocking price down 10% will make their year over year look tens of millions better, <1% of their fund but given how idiotic their management seems to be they need every % of performance they can artificially create\n\nIMO the short raid was done yesterday to work around uptick rule, which went into effect yesterday at -10% and stays until eod today. That leaves one last shot 12/31 to smash this shit down. Idk if they do it at open or if they do it throughout the day, i bought these puts and will close some today depending on how yee yee yolo i feel. I’m buying the fucking dip tm 1 hour into open no matter what price action looks like.\n\n\nhttps://imgur.com/gallery/N84lB2L',
  16: '[removed]',
  17: "As a GME holder, been thinking about what will have to happen to cause shorts to change their investment thesis. Part of it could be a change in timeline, if GME lasts 5+ years rather than the 1-2 years shorts expected, that could be enough to change an investment thesis. Another is that regardless of timeline, shorts probably compare GME to Blockbuster and no matter how long, it's gonna be toast. So even though GME is undervalued right now they obviously do need a strategy for a long-term position in the gaming industry with games becoming more digital. \n\nMaybe they create an E-sports gaming hub? Maybe they successfully transition to being an online gaming retailer with a new strategic partnership with Microsoft? With the promising 200+% online sales growth, there's a real chance the 2nd option could happen without a major change. \n\nSo to the GME shorts lurking, what would it take for y'all to change your thesis?  Also what're y'all gonna do if/when the comparison goes from Blockbuster to Netflix?",
  18: '[removed]',
  19: "Does anyone have an idea of roughly what % of a hedge funds assets they would keep freed up to use for margin?  It seems like they would keep most of it invested so that it's being useful and making money.  The thing is as GME's price increases the amount of margin required to keep that short position is getting pretty huge.  If we assume they have to keep 130% of the shares value in margin, at a price point of $22 (about the highest it's gotten recently) that would take \\~2 billion in margin to keep that position open.  Melvin Capital for instance has roughly 20 billion in positions on their 13F-HR.  Now this short position could be spread over a few people, but I think there's a reasonable chance it isn't.  Think about it, as it became clear that gme wasn't going away anyone with a smaller position would have unwound it because they knew melvin would be forced to keep it from mooning.\n\n​\n\nhttps://preview.redd.it/zhtia6axmc861.png?width=1608&format=png&auto=webp&s=617f2455c4e8e07b96cd15d39faff17d8975e021",
  20: "I think most of you already saw [this post](https://www.reddit.com/r/wallstreetbets/comments/k450e6/why_apha_is_set_for_a_50_gain/) by [u/WatAb0utB0b](https://www.reddit.com/user/WatAb0utB0b/) about a month ago. I'm here to tell you what has changed since then. Everything is in these two articles: [Bloomberg](https://www.fool.com/investing/2020/12/30/does-113-for-this-merger-between-two-cannabis-heav/?source=eptyholnk0000202&utm_source=yahoo-host&utm_medium=feed&utm_campaign=article), [The Motley Fool](https://www.fool.com/investing/2020/12/30/does-113-for-this-merger-between-two-cannabis-heav/?source=eptyholnk0000202&utm_source=yahoo-host&utm_medium=feed&utm_campaign=article). Because you autists have the attention span of a chimpanzee, I'll give an executive summary.\n\n1. **Juicy merger for super dank weed company**  \nOn the 16th of December, Aphria and Tilray announced that they will merge to form the 2nd biggest exchange traded company of the cannabis market. They will form under Tilray's ticker, but Aphria stock owners will get a 62% stake in Tilray while current Tilray owners will keep a 38% stake. This will allow them to cut costs and get even stronger financials. The merger is expected to happen in the second quarter of 2021.\n2. **Cannabis is still an attractive growth market**  \nA lot of people are sceptical for cannabis since the last few years the stock has jumped up and down a few times through cycles of hype and disillusion. This time, all the cards are laid out right. Aphria is based in Canada, Tilray in USA: North-America is getting addicted. Boomer orange man has obstructed the market, but Sleepy Joe loves his cannabis so we're good for atleast the next 4 years.   \nIt's not just NA either, in the EU medicinal cannabis use and other legal CBD products are getting more and more popular, which implies an enormous growth opportunity. Tilray is already partnered with AB Inbev for CBD drinks and Aphria had acquired a sweet drink company (see last post) so they will fit together perfectly. Year after year, performance improves and astonishing growth rates can be found in financial reports (also see last post).\n3. **Stock price is cheap, perfect time to get in**  \nI jumped in with that last post a bit prematurely (30 shares @ 8.74), the stock price has gone down more than 20% since then. This is because of [🌈🐻 bears that are shorting the cannabis market massively and losing](https://finance.yahoo.com/news/cannabis-short-sellers-now-down-155011384.html?),  $236.9 million total in short interest for APHA . It's time to beat them to the ground once again, like WSB is doing right now with GME.  \nCurrently APHA is noted at around $7, [analysts](https://money.cnn.com/quote/forecast/forecast.html?symb=apha) predict a median price target of $8.64 in the next year (+24%). None of the analysts are giving negative advice, it's all positive with most of them on a 'buy' advice. All of these advices are pretty conservative, weed stock is pretty volatile so the minute good news comes in through the merger or Biden administration you should expect big gains. I would project the price to reach 2017/2018 prices again if you guys diamond hand the fuck out of this stock.\n\n**TL:DR** I'm passing you guys the boof on this massive gain opportunity. APHA is cheap atm and has good forecasts in the future with upcoming merger and Biden administration (+ worldwide growth opportunities). Let's reach another dimension with this stock. Dedicated emoji's: 😮\u200d💨🥦🚀🚀🚀🚀🚀🚀🚀🚀",
  21: '[removed]',
  22: '[removed]',
  23: 'I ordered a Blacks Ops 4 Pro Edition which retails for $100 for only $29.97 on GameStop. They then sent me a pre owned copy. I requested an exchange and sent back the game.  Two days later I received a refund. Yesterday I received a new copy of the game with a packing slip for earlier this month. This is the second time in 3 months this has happened. How is this company even financially stable when they are giving out free money. \n\nThanks GameStop 🤣',
  24: '[removed]'},
 'score': {0: 1,
  1: 1,
  2: 1,
  3: 1,
  4: 1,
  5: 1,
  6: 1,
  7: 1,
  8: 1,
  9: 1,
  10: 1,
  11: 1,
  12: 1,
  13: 1,
  14: 1,
  15: 1,
  16: 1,
  17: 1,
  18: 1,
  19: 1,
  20: 1,
  21: 1,
  22: 1,
  23: 1,
  24: 1},
 'id': {0: 'kncovu',
  1: 'knc9kn',
  2: 'knb9a9',
  3: 'kna85b',
  4: 'kn8yok',
  5: 'kn8xl4',
  6: 'kn8i8y',
  7: 'kn89wn',
  8: 'kn89jy',
  9: 'kn88ch',
  10: 'kn816j',
  11: 'kn7xzf',
  12: 'kn7i3j',
  13: 'kn76na',
  14: 'kn6428',
  15: 'kn63xs',
  16: 'kn5s37',
  17: 'kn5d77',
  18: 'kn5928',
  19: 'kn56fn',
  20: 'kn4wck',
  21: 'kn4qpb',
  22: 'kn4nzq',
  23: 'kn4ahn',
  24: 'kn48lo'}}

现在，我的目标是根据这些帖子发布的30分钟时间间隔汇总这些帖子（使其适合时间序列分析）。例如：

我认为用熊猫做这件事应该是可行的，但在这一点上，我完全不知道从哪里开始。非常感谢您的任何意见

Answer 1

您可以将日期列转换为datetime，然后将pd.Grouper与groupby一起使用，如下所示：

# Covnert to datetime
df.date = pd.to_datetime(df.date)

# Grouped per 30 min aggregation
new = df.groupby(pd.Grouper(key='date', freq='30min')).agg({'post':lambda x: ','.join(list(x)),
                                                            'score':'sum'})

将为您提供：

Out[58]: 
                                                                  post  score
date                                                                         
2020-12-30 15:00:00                                          [removed]      1
2020-12-30 15:30:00  I ordered a Blacks Ops 4 Pro Edition which ret...      3
2020-12-30 16:00:00  I think most of you already saw [this post](ht...      3
2020-12-30 16:30:00  As a GME holder, been thinking about what will...      2
2020-12-30 17:00:00  I changed my post title into something sexy so...      2
2020-12-30 17:30:00                                                         0
2020-12-30 18:00:00  [removed],Everyone is blindly betting. Either ...      2
2020-12-30 18:30:00  [removed],[removed],[removed],G. Kennedy Fuld ...      5
2020-12-30 19:00:00  Within the last hour some shitty EV helicopter...      1
2020-12-30 19:30:00  [removed],I'm currently 💎👐 on 440 GME shares (...      2
2020-12-30 20:00:00                                                         0
2020-12-30 20:30:00  Melvin Capital has over 5.2 million puts again...      1
2020-12-30 21:00:00  What happened to nio? Nio day is soon and I do...      1
2020-12-30 21:30:00                                                         0
2020-12-30 22:00:00  I kept a summary of my reasoning for getting i...      1
2020-12-30 22:30:00  ​\n\n*Processing video 9xcv9jkbke86...      1