VTT_Transcript_To_XLSX_Table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b6d551c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Simple VTT File To XLSX Table Conversion\n",
    "\n",
    "#Choose a filename from the folder where this IPYNB will be run\n",
    "\n",
    "filename=\"SampleWorkshopTranscript.vtt\"\n",
    "import webvtt\n",
    "start=[]\n",
    "end=[]\n",
    "text=[]\n",
    "speaker=[]\n",
    "for caption in webvtt.read(filename):\n",
    "    start.append(caption.start)\n",
    "    end.append(caption.end)\n",
    "    text.append(caption.text)\n",
    "    speaker.append(caption.raw_text)\n",
    "    \n",
    "#If you get an error after running this, it may be that the formatting is wrong so jump to next line of code....."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2cb1ada8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WEBVTT\n",
      "\n",
      "000:00:00.000--> 000:00:13.370\n",
      "<v Speaker 1>Analytics and the mental level analytics is not about making a report. It's not about making a dashboard. It's about asking great questions and questions that matter what the business. Think about this workshop. When we started this workshop, the intent is to understand.</v>\n",
      "\n",
      "000:00:13.850 --> 000:00:33.520\n",
      "<v Speaker 1>Uh, where do we need to go? How do we work as a team? What are you guys thinking about? You know what's on your mind. What your priority is. Do we know each other? I mean, there's a lot of questions here, but ultimately, the outcome that we want is we understand each other better. We really get to feel, you know, the diversity in the team. What we're good at, how we leverage each other.</v>\n",
      "\n",
      "000:00:34.250 --> 000:01:04.140\n",
      "<v Speaker 1>Where we need to move forward to, you know, and and it's a whole bunch of things. So remember that high level frame terms of the question before we go into the details because if not you get lost. So any analytics tasks with the why, what is it you're trying to do? Why is it matter now having said that, there are a few things you might notice that I've been doing things that I've been linking things together and you know I've this is something that I did on the fly right balance is linked to traveling.</v>\n",
      "\n",
      "000:01:04.540 --> 000:01:28.810\n",
      "<v Speaker 1>Link to you know you wanna clean inbox so that you would like balance you. We should go back to sports. Sports is linked to health. Health is linked to family. This is also health related. So I'm in a way trying to codify that information and it's kind of manual. So it's just see trying to take information and solve it out so that we see whether there are thematic things that occur or stand out as a picture similarly.</v>\n",
      "\n",
      "000:01:30.130 --> 000:01:57.420\n",
      "<v Speaker 1>Opportunity to invite innovate, you know, so sometimes there's no deadlines because we try to understand. There are things around where team and you know, making links between different ideas and concepts. So this is quite manual. So think about analytics perspective. How did he do this? It took a lot of effort from Z flying about trying to make arrows and connecting it and then moving boxes so that there was a story to tell where stakeholders related to be distributed to this.</v>\n",
      "\n",
      "000:01:58.200 --> 000:01:58.790\n",
      "<v Speaker 1>Now.</v>\n",
      "\n",
      "000:01:59.710 --> 000:02:01.290\n",
      "<v Speaker 1>What if I told you that?</v>\n",
      "\n",
      "000:02:02.040--> 000:02:06.180\n",
      "<v Speaker 1>The there's a way to actually see so one of the questions that to ask was?</v>\n",
      "\n",
      "000:02:07.030--> 000:02:10.220\n",
      "<v Speaker 1>What kind of things do we talk about and then I can start reverse?</v>\n",
      "\n",
      "000:02:11.210 --> 000:02:16.200\n",
      "<v Speaker 1>So rendered and jumped to a dashboard. First thing you do is you leverage what is existing.</v>\n",
      "\n",
      "000:02:17.570 --> 000:02:21.360\n",
      "<v Speaker 1>And you can do things like this. So did we talk about the learner mindset?</v>\n",
      "\n",
      "000:02:23.800 --> 000:02:28.230\n",
      "<v Speaker 1>We didn't talk about learner mindset. Did we talk about learning?</v>\n",
      "\n",
      "000:02:29.750 --> 000:02:41.860\n",
      "<v Speaker 1>So we have one item that talked about learning, but it gives you a way of actually scanning through. It's not comprehensive, but you have that kind of ability to see the future or let's say, do we talk about stakeholders.</v>\n",
      "\n",
      "000:02:42.870 --> 000:02:57.720\n",
      "<v Speaker 1>Who talks about stakeholders? She has ruined Julius and Venu all mentioned stakeholders, who clearly is a topic that's important, you know, and we talked about conflict. General talked about conflict. So, you know, gives you a way to navigate and get some sense of what we're talking about.</v>\n",
      "\n",
      "000:02:58.940 --> 000:03:28.720\n",
      "<v Speaker 1>What's also quite useful is you can also see who made the most contributions in terms of stickies, and it's not a contest, by the way, but you can see that Julius had quite a lot of stickies, and actually you can visualize it very quickly because you know the section. So where did Julius make the most amount of contributions you see that he spent quite a number of stickies in this area, and you get a bit of a sense. So again, this is all semi manual, but you're using existing tools before you even do anything else.</v>\n",
      "\n",
      "000:03:29.170 --> 000:03:40.390\n",
      "<v Speaker 1>And it's all in service of the question. You know, do we know where we're going? Do we know where we're strong? Who is caught? Are people engaged because you need people to be engaged to answer those questions now.</v>\n",
      "\n",
      "000:03:41.820 --> 000:04:11.560\n",
      "<v Speaker 1>You remember about the data piece? Jello Jello said. Can I enter it later? Or maybe I don't need to enter it? Think about the work that we do, all the stakeholders out there. I don't need to put this in a rebar or maybe I can skip it or leave it empty field. I'll put it that that's exactly what happened. So we digitalize first digitize first because you want to make sure that things are captured. So think about the work whenever you hear someone say I'll put it outside or it's OK, it becomes hidden. You can't see. So that's the point. I said. Remember this moment when you said that.</v>\n",
      "\n",
      "000:04:11.830 --> 000:04:18.450\n",
      "<v Speaker 1>And think about the work that we do in relates and it's not criticism against India, but you know, just consider that think about the fact that.</v>\n",
      "\n",
      "000:04:19.650 --> 000:04:36.140\n",
      "<v Speaker 1>The end was trying to share some information in this kind of format, which is OK you can sort of see it, but the idea was you know if we are able to connect that information.</v>\n",
      "\n",
      "000:04:37.070--> 000:04:38.800\n",
      "<v Speaker 1>You actually have that data.</v>\n",
      "\n",
      "000:04:39.620 --> 000:04:48.210\n",
      "<v Speaker 1>And that data was captured in a structured manner because it was true forms and you can actually view it, you know, and by putting a link.</v>\n",
      "\n",
      "000:04:49.350 --> 000:04:59.510\n",
      "<v Speaker 1>You know, you both actually observed when you create that connectivity between different tools and different systems. Think about the work that we do, Ariba data, ERP data, how do we Marshall it together?</v>\n",
      "\n",
      "000:05:00.590 --> 000:05:02.640\n",
      "<v Speaker 1>It relates to the work that we do as well.</v>\n",
      "\n",
      "000:05:04.390 --> 000:05:17.840\n",
      "<v Speaker 1>And the the other bit around, uh. So as we do this, I asked the question what can we see from all this information about what do we want our hopes and wishes.</v>\n",
      "\n",
      "000:05:19.100 --> 000:05:35.940\n",
      "<v Speaker 1>Our reflections and what I just did is very manual. You know, I picked it up as V1 more time with family, you know, gonna be more efficient. We wanna be impactful with our stakeholders. I can do that as well. But I wanted to show you. Did you know that you can actually download the information as a CSV?</v>\n",
      "\n",
      "000:05:37.070--> 000:05:41.950\n",
      "<v Speaker 1>And if you can download CSV, you can actually have all that information.</v>\n",
      "\n",
      "000:05:42.690 --> 000:05:46.440\n",
      "<v Speaker 1>Download it in this form you know who put that information in.</v>\n",
      "\n",
      "000:05:47.210 --> 000:05:49.220\n",
      "<v Speaker 1>You know which section is related to.</v>\n",
      "\n",
      "000:05:50.070--> 000:06:17.680\n",
      "<v Speaker 1>You can see where this is going and it's tagged properly, so the tagging methods so you see that I ask you, can you put this in the box? So can you move that box? Because if it is in one box, it will absorb the tag. Think about the work that we do reference mapping standard mappings for any data managing or P&L V PSHIP mapping. This is an analogy of that because if you don't have a good mapping, you can't do these kind of things. If you can take this kind of information.</v>\n",
      "\n",
      "000:06:18.350 --> 000:06:19.760\n",
      "<v Speaker 1>And you have it available.</v>\n",
      "\n",
      "000:06:20.710 --> 000:06:24.380\n",
      "<v Speaker 1>Where I can also see what do we need help with. So this is the section.</v>\n",
      "\n",
      "000:06:25.100 --> 000:06:33.610\n",
      "<v Speaker 1>That is all the things that we need help with as a team and we're able to get it. Now. This leads to if we can get that information.</v>\n",
      "\n",
      "000:06:34.500 --> 000:06:38.840\n",
      "<v Speaker 1>In a way that is standardized, this is not for this workshop. I couldn't do it fast enough.</v>\n",
      "\n",
      "000:06:39.690 --> 000:06:42.830\n",
      "<v Speaker 1>You can believe. Sorry. Is that? Yeah.</v>\n",
      "\n",
      "000:06:43.330 --> 000:06:45.910\n",
      "<v Speaker 2>Yeah, I was just laughing that you can do it fast enough soon.</v>\n",
      "\n",
      "000:06:46.490 --> 000:06:47.040\n",
      "<v Speaker 1>Why?</v>\n",
      "\n",
      "000:06:47.620 --> 000:06:49.740\n",
      "<v Speaker 2>Yeah. Because normally you'll say fast.</v>\n",
      "\n",
      "000:06:50.730 --> 000:06:55.940\n",
      "<v Speaker 1>Yeah, but the point is you can take all this information. This is for a different workshop, by the way.</v>\n",
      "\n",
      "000:06:56.650 --> 000:06:58.300\n",
      "<v Speaker 1>You can take all that information.</v>\n",
      "\n",
      "000:06:59.150 --> 000:07:22.220\n",
      "<v Speaker 1>And in Power BI there are things that we don't explore. Did you know that you can do something called extract key phrases or score sentiment where you can do things like you can see whether or not the statement is negative or positive. So hit becomes a low number. I'm happy becomes a high number because somebody wrote their algorithm. You can do things like extract key phrases.</v>\n",
      "\n",
      "000:07:23.180 --> 000:07:23.810\n",
      "<v Speaker 1>And.</v>\n",
      "\n",
      "000:07:24.580 --> 000:07:53.430\n",
      "<v Speaker 1>These are all existing over the box features, so I want you to think about this. Have you really explored what you can do without all the high tech fancy things? This is just power BI you use this day in day out other buttons that you never see. Have you been demonstrating the curiosity? Just to say I wonder, you know can this and not just wonder openly wonder in reference to your objective your why your why we're doing this. Why does it matter? And if you do that you can actually.</v>\n",
      "\n",
      "000:07:54.650 --> 000:08:23.830\n",
      "<v Speaker 1>Able to pull up information where I haven't done it again, but you can actually get information like based on all the sticky notes. This is not for our meeting, by the way. Another meeting. You can see things like day one. What did we discuss? And this was for the digital workshop on the road map that we want to do earlier this year. So we see the first day it was business needs digital very quickly. You can pick it up as a team. Second day it's about.</v>\n",
      "\n",
      "000:08:25.440 --> 000:08:42.640\n",
      "<v Speaker 1>To do digital, we need more data and the data needs to be right then the third day we invited some of our. We started talking about planning and you can see what is the year ahead, what is the adoption and it gives you a very rich way to explore that information.</v>\n",
      "\n",
      "000:08:44.040--> 000:08:49.270\n",
      "<v Speaker 1>And it's, I hope, myself doing this. You can see that it is important.</v>\n",
      "\n",
      "000:08:50.140 --> 000:08:55.410\n",
      "<v Speaker 1>To think about what you want out of the end related back to the question.</v>\n",
      "\n",
      "000:08:56.840 --> 000:08:59.580\n",
      "<v Speaker 1>Before you do all this, but if you structure it right.</v>\n",
      "\n",
      "000:09:00.400 --> 000:09:09.630\n",
      "<v Speaker 1>You organize that data in a way that helps you answer this question, not knowing what the full question is, but knowing the idea of the kind of things that you would like to explore.</v>\n",
      "\n",
      "000:09:10.550 --> 000:09:14.050\n",
      "<v Speaker 1>You can digitize inputs from these kind of workshops.</v>\n",
      "\n",
      "000:09:14.830 --> 000:09:28.360\n",
      "<v Speaker 1>Pull it into something that you can explore and visualize very quickly when you say what is the most common word that comes out of freezes that come up in pairs and these are just default things in power BI you can save.</v>\n",
      "\n",
      "000:09:29.540 --> 000:09:51.410\n",
      "<v Speaker 1>Such by who said what. And before you jump to this, you should actually already see what is available in the default tool here as mural, but it could also be rather than jump straight to power BI. What's really available in ERP's. What's really available in the standard reports before you try to be too fancy, but the whole intent is we are the only team.</v>\n",
      "\n",
      "000:09:52.120 --> 000:09:55.640\n",
      "<v Speaker 1>Or we at the team that has this ability to think this way.</v>\n",
      "\n",
      "000:09:56.290 --> 000:10:13.920\n",
      "<v Speaker 1>You know, we always sit uh, our job is to take EKPO and the EKPO and the LFA one tables. And it's so much more. I mean, data is all around this. What would be quite manual in terms of trying to find things.</v>\n",
      "\n",
      "000:10:14.620 --> 000:10:33.210\n",
      "<v Speaker 1>Uh, you can turn it. Make it into something easy to understand. Where what I had to do here is I had to pick up the story manually, but if you digitize things properly, you set the right rules around or make sure that everything is in the box because the sticky notes will absorb the box that is in.</v>\n",
      "\n",
      "000:10:34.730 --> 000:11:03.520\n",
      "<v Speaker 1>And you tell people one idea per sticky. You remember I said that because if you don't do that, then you get more than one idea and sticky. Think about that from a perspective of data quality. People use the field wrongly. Then obviously it it wouldn't help to sort of answer the question that you want and the ultimately it allows you to pull up a picture that you can look to see people talking about what are people talking about. Does it matter you can zoom in.</v>\n",
      "\n",
      "000:11:03.950 --> 000:11:06.710\n",
      "<v Speaker 1>Transactions and use it to inform decision making.</v>\n",
      "\n",
      "000:11:07.370 --> 000:11:30.310\n",
      "<v Speaker 1>And shape what we want to do, because we're using that information and take that answer and then use it to even enrich it further. So it's almost like a ladder. You know, you start with some data, you get some insights, the insights you take, some action, you generate more data because of the discussions around that and you continue to enrich it. Now it's not about this workshop kind of you anymore. It is about the way we do our work.</v>\n",
      "\n",
      "000:11:31.050--> 000:11:33.690\n",
      "<v Speaker 1>We do a lot more than this, building dashboards and reports.</v>\n",
      "\n",
      "000:11:34.330 --> 000:11:40.900\n",
      "<v Speaker 1>And I'm doing this to share not because I want you to repeat this, but to just think about.</v>\n",
      "\n",
      "000:11:41.540 --> 000:11:50.540\n",
      "<v Speaker 1>You know, do you think a data view guided by purpose? What is the question that the business wants answered? What matters that the business?</v>\n",
      "\n",
      "000:11:51.230 --> 000:12:10.530\n",
      "<v Speaker 1>Uh, and step back a little bit to try to understand where can I get the information? Is using the existing tool like what I'm doing here where you can just search things here. Is that good enough? Do I need something more special? Do I not even need a tool? I just need to eyeball it and then just see it because it's so simple.</v>\n",
      "\n",
      "000:12:11.950 --> 000:12:18.090\n",
      "<v Speaker 1>And if I need to go for more advanced ways, what tools do I have available and just have an understanding?</v>\n",
      "\n",
      "000:12:19.370 --> 000:12:28.970\n",
      "<v Speaker 1>So I I think this is sort of my general sharing to just relate the way that we can digitize this workshop content.</v>\n",
      "\n",
      "000:12:29.980 --> 000:12:50.970\n",
      "<v Speaker 1>And then use that digitized content to try and infer some degree of insights where we can do like word searches, we can do sentiment analysis, key phrase analysis. You try and understand. Do we have something in common between all of this and the way that we see things and the whole intent is not to just make a chat is to.</v>\n",
      "\n",
      "000:12:51.780 --> 000:12:57.630\n",
      "<v Speaker 1>Tell the story driven by data, and in this case it's data from all this generating it.</v>\n",
      "\n",
      "000:12:58.290 --> 000:13:28.390\n",
      "<v Speaker 1>To help us get better as it be, understand where do we need to rebalance, where do we need to build on our strengths and what are the gaps that we need to cover. So I'll get off my soapbox, but I just wanted to share this with everyone that it's about this and the last thing I wanted to share is if you want to put in multiple stickies and you're lazy to do it online, it's it's also about interoperability of our systems. Did you know that you can also do this?</v>\n",
      "\n",
      "000:13:29.480 --> 000:13:35.590\n",
      "<v Speaker 1>You can take things, do it offline, and then later come into mural and you can paste 4 boxes all the same time.</v>\n",
      "\n",
      "000:13:38.210 --> 000:13:49.940\n",
      "<v Speaker 1>So think about the systems that we use think about the and maybe this is mobile venue, but maybe on social screen for small interoperatibility did you see what I just did? I took excel. I mean Excel talked to mural.</v>\n",
      "\n",
      "000:13:50.700 --> 000:14:21.810\n",
      "<v Speaker 1>You know, so think about it. Can we make S4 talk to Oscar for the data hub in the background? Can we make things join up better? And it goes back to the data thinking we are unique. At least I feel this team where unique in the organization because unlike CP data, they sort of look at, they start from the governance side. We start from the what is the business question. We're trying to answer and then we work backwards to say what data is there, what can we do with the data that's available. But then we still have to go through all these kind of questions so.</v>\n",
      "\n",
      "000:14:23.090--> 000:14:23.860\n",
      "<v Speaker 1>Hopefully.</v>\n",
      "\n",
      "000:14:24.690 --> 000:14:31.920\n",
      "<v Speaker 1>What I'm saying is there's a story around like trying to say so. The idea here was just to sort of share with you different ways of thinking when you approach your work.</v>\n",
      "\n",
      "000:14:32.930 --> 000:14:34.020\n",
      "<v Speaker 1>OK, I'm going to pause.</v>\n",
      "\n",
      "000:14:38.930 --> 000:14:44.760\n",
      "<v Speaker 2>Thank you. See you on that. I think some good insights there. I will stop the.</v>\n",
      "\n",
      "000:14:44.860 --> 000:14:48.170\n",
      "<v Speaker 2>Um recording?</v>\n",
      "\n",
      "000:14:48.690 --> 000:14:48.980\n",
      "<v Speaker 1>Yep.</v>\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open(filename) as f:\n",
    "    text = f.read()\n",
    "\n",
    "print(text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ffb6f617",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(0,10):\n",
    "    j=str(i)\n",
    "    text = text.replace(j+\":\", \"0\"+j+\":\")\n",
    "    text = text.replace(\".\"+j+\" \", \".00\"+j)\n",
    "    text = text.replace(\":\"+j+\".\", \":0\"+j+\".\")\n",
    "    for k in range(0,10):\n",
    "        l=str(k)\n",
    "        text = text.replace(\".\"+j+l+\" \", \".0\"+j+l)\n",
    "        text = text.replace(\".\"+j+l+\"\\n\", \".0\"+j+l+\"\\n\")\n",
    "        text = text.replace(\":\"+j+\"0\"+l+\":\",\":\"+j+l+\":\")\n",
    "\n",
    "for i in range(0,10):\n",
    "    j=str(i)\n",
    "    for k in range(0,10):\n",
    "        l=str(k)\n",
    "        text = text.replace(\":\"+j+\"0\"+l+\":\",\":\"+j+l+\":\")\n",
    "        \n",
    "for i in range(0,10):\n",
    "    j=str(i)\n",
    "    text = text.replace(\".\"+j+\"\\n\",\".00\"+j+\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "14e676fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "cleanedVTT=filename.split(\".\", 1)[0]+\".vtt\"\n",
    "f = open(cleanedVTT, \"w\")\n",
    "f.write(text)\n",
    "f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "94179a0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WEBVTT\n",
      "\n",
      "0000:00:00.000--> 0000:00:13.370\n",
      "<v Speaker 1>Analytics and the mental level analytics is not about making a report. It's not about making a dashboard. It's about asking great questions and questions that matter what the business. Think about this workshop. When we started this workshop, the intent is to understand.</v>\n",
      "\n",
      "0000:00:13.850 --> 0000:00:33.520\n",
      "<v Speaker 1>Uh, where do we need to go? How do we work as a team? What are you guys thinking about? You know what's on your mind. What your priority is. Do we know each other? I mean, there's a lot of questions here, but ultimately, the outcome that we want is we understand each other better. We really get to feel, you know, the diversity in the team. What we're good at, how we leverage each other.</v>\n",
      "\n",
      "0000:00:34.250 --> 0000:01:04.140\n",
      "<v Speaker 1>Where we need to move forward to, you know, and and it's a whole bunch of things. So remember that high level frame terms of the question before we go into the details because if not you get lost. So any analytics tasks with the why, what is it you're trying to do? Why is it matter now having said that, there are a few things you might notice that I've been doing things that I've been linking things together and you know I've this is something that I did on the fly right balance is linked to traveling.</v>\n",
      "\n",
      "0000:01:04.540 --> 0000:01:28.810\n",
      "<v Speaker 1>Link to you know you wanna clean inbox so that you would like balance you. We should go back to sports. Sports is linked to health. Health is linked to family. This is also health related. So I'm in a way trying to codify that information and it's kind of manual. So it's just see trying to take information and solve it out so that we see whether there are thematic things that occur or stand out as a picture similarly.</v>\n",
      "\n",
      "0000:01:30.130 --> 0000:01:57.420\n",
      "<v Speaker 1>Opportunity to invite innovate, you know, so sometimes there's no deadlines because we try to understand. There are things around where team and you know, making links between different ideas and concepts. So this is quite manual. So think about analytics perspective. How did he do this? It took a lot of effort from Z flying about trying to make arrows and connecting it and then moving boxes so that there was a story to tell where stakeholders related to be distributed to this.</v>\n",
      "\n",
      "0000:01:58.200 --> 0000:01:58.790\n",
      "<v Speaker 1>Now.</v>\n",
      "\n",
      "0000:01:59.710 --> 0000:02:01.290\n",
      "<v Speaker 1>What if I told you that?</v>\n",
      "\n",
      "0000:02:02.040--> 0000:02:06.180\n",
      "<v Speaker 1>The there's a way to actually see so one of the questions that to ask was?</v>\n",
      "\n",
      "0000:02:07.030--> 0000:02:10.220\n",
      "<v Speaker 1>What kind of things do we talk about and then I can start reverse?</v>\n",
      "\n",
      "0000:02:11.210 --> 0000:02:16.200\n",
      "<v Speaker 1>So rendered and jumped to a dashboard. First thing you do is you leverage what is existing.</v>\n",
      "\n",
      "0000:02:17.570 --> 0000:02:21.360\n",
      "<v Speaker 1>And you can do things like this. So did we talk about the learner mindset?</v>\n",
      "\n",
      "0000:02:23.800 --> 0000:02:28.230\n",
      "<v Speaker 1>We didn't talk about learner mindset. Did we talk about learning?</v>\n",
      "\n",
      "0000:02:29.750 --> 0000:02:41.860\n",
      "<v Speaker 1>So we have one item that talked about learning, but it gives you a way of actually scanning through. It's not comprehensive, but you have that kind of ability to see the future or let's say, do we talk about stakeholders.</v>\n",
      "\n",
      "0000:02:42.870 --> 0000:02:57.720\n",
      "<v Speaker 1>Who talks about stakeholders? She has ruined Julius and Venu all mentioned stakeholders, who clearly is a topic that's important, you know, and we talked about conflict. General talked about conflict. So, you know, gives you a way to navigate and get some sense of what we're talking about.</v>\n",
      "\n",
      "0000:02:58.940 --> 0000:03:28.720\n",
      "<v Speaker 1>What's also quite useful is you can also see who made the most contributions in terms of stickies, and it's not a contest, by the way, but you can see that Julius had quite a lot of stickies, and actually you can visualize it very quickly because you know the section. So where did Julius make the most amount of contributions you see that he spent quite a number of stickies in this area, and you get a bit of a sense. So again, this is all semi manual, but you're using existing tools before you even do anything else.</v>\n",
      "\n",
      "0000:03:29.170 --> 0000:03:40.390\n",
      "<v Speaker 1>And it's all in service of the question. You know, do we know where we're going? Do we know where we're strong? Who is caught? Are people engaged because you need people to be engaged to answer those questions now.</v>\n",
      "\n",
      "0000:03:41.820 --> 0000:04:11.560\n",
      "<v Speaker 1>You remember about the data piece? Jello Jello said. Can I enter it later? Or maybe I don't need to enter it? Think about the work that we do, all the stakeholders out there. I don't need to put this in a rebar or maybe I can skip it or leave it empty field. I'll put it that that's exactly what happened. So we digitalize first digitize first because you want to make sure that things are captured. So think about the work whenever you hear someone say I'll put it outside or it's OK, it becomes hidden. You can't see. So that's the point. I said. Remember this moment when you said that.</v>\n",
      "\n",
      "0000:04:11.830 --> 0000:04:18.450\n",
      "<v Speaker 1>And think about the work that we do in relates and it's not criticism against India, but you know, just consider that think about the fact that.</v>\n",
      "\n",
      "0000:04:19.650 --> 0000:04:36.140\n",
      "<v Speaker 1>The end was trying to share some information in this kind of format, which is OK you can sort of see it, but the idea was you know if we are able to connect that information.</v>\n",
      "\n",
      "0000:04:37.070--> 0000:04:38.800\n",
      "<v Speaker 1>You actually have that data.</v>\n",
      "\n",
      "0000:04:39.620 --> 0000:04:48.210\n",
      "<v Speaker 1>And that data was captured in a structured manner because it was true forms and you can actually view it, you know, and by putting a link.</v>\n",
      "\n",
      "0000:04:49.350 --> 0000:04:59.510\n",
      "<v Speaker 1>You know, you both actually observed when you create that connectivity between different tools and different systems. Think about the work that we do, Ariba data, ERP data, how do we Marshall it together?</v>\n",
      "\n",
      "0000:05:00.590 --> 0000:05:02.640\n",
      "<v Speaker 1>It relates to the work that we do as well.</v>\n",
      "\n",
      "0000:05:04.390 --> 0000:05:17.840\n",
      "<v Speaker 1>And the the other bit around, uh. So as we do this, I asked the question what can we see from all this information about what do we want our hopes and wishes.</v>\n",
      "\n",
      "0000:05:19.100 --> 0000:05:35.940\n",
      "<v Speaker 1>Our reflections and what I just did is very manual. You know, I picked it up as V1 more time with family, you know, gonna be more efficient. We wanna be impactful with our stakeholders. I can do that as well. But I wanted to show you. Did you know that you can actually download the information as a CSV?</v>\n",
      "\n",
      "0000:05:37.070--> 0000:05:41.950\n",
      "<v Speaker 1>And if you can download CSV, you can actually have all that information.</v>\n",
      "\n",
      "0000:05:42.690 --> 0000:05:46.440\n",
      "<v Speaker 1>Download it in this form you know who put that information in.</v>\n",
      "\n",
      "0000:05:47.210 --> 0000:05:49.220\n",
      "<v Speaker 1>You know which section is related to.</v>\n",
      "\n",
      "0000:05:50.070--> 0000:06:17.680\n",
      "<v Speaker 1>You can see where this is going and it's tagged properly, so the tagging methods so you see that I ask you, can you put this in the box? So can you move that box? Because if it is in one box, it will absorb the tag. Think about the work that we do reference mapping standard mappings for any data managing or P&L V PSHIP mapping. This is an analogy of that because if you don't have a good mapping, you can't do these kind of things. If you can take this kind of information.</v>\n",
      "\n",
      "0000:06:18.350 --> 0000:06:19.760\n",
      "<v Speaker 1>And you have it available.</v>\n",
      "\n",
      "0000:06:20.710 --> 0000:06:24.380\n",
      "<v Speaker 1>Where I can also see what do we need help with. So this is the section.</v>\n",
      "\n",
      "0000:06:25.100 --> 0000:06:33.610\n",
      "<v Speaker 1>That is all the things that we need help with as a team and we're able to get it. Now. This leads to if we can get that information.</v>\n",
      "\n",
      "0000:06:34.500 --> 0000:06:38.840\n",
      "<v Speaker 1>In a way that is standardized, this is not for this workshop. I couldn't do it fast enough.</v>\n",
      "\n",
      "0000:06:39.690 --> 0000:06:42.830\n",
      "<v Speaker 1>You can believe. Sorry. Is that? Yeah.</v>\n",
      "\n",
      "0000:06:43.330 --> 0000:06:45.910\n",
      "<v Speaker 2>Yeah, I was just laughing that you can do it fast enough soon.</v>\n",
      "\n",
      "0000:06:46.490 --> 0000:06:47.040\n",
      "<v Speaker 1>Why?</v>\n",
      "\n",
      "0000:06:47.620 --> 0000:06:49.740\n",
      "<v Speaker 2>Yeah. Because normally you'll say fast.</v>\n",
      "\n",
      "0000:06:50.730 --> 0000:06:55.940\n",
      "<v Speaker 1>Yeah, but the point is you can take all this information. This is for a different workshop, by the way.</v>\n",
      "\n",
      "0000:06:56.650 --> 0000:06:58.300\n",
      "<v Speaker 1>You can take all that information.</v>\n",
      "\n",
      "0000:06:59.150 --> 0000:07:22.220\n",
      "<v Speaker 1>And in Power BI there are things that we don't explore. Did you know that you can do something called extract key phrases or score sentiment where you can do things like you can see whether or not the statement is negative or positive. So hit becomes a low number. I'm happy becomes a high number because somebody wrote their algorithm. You can do things like extract key phrases.</v>\n",
      "\n",
      "0000:07:23.180 --> 0000:07:23.810\n",
      "<v Speaker 1>And.</v>\n",
      "\n",
      "0000:07:24.580 --> 0000:07:53.430\n",
      "<v Speaker 1>These are all existing over the box features, so I want you to think about this. Have you really explored what you can do without all the high tech fancy things? This is just power BI you use this day in day out other buttons that you never see. Have you been demonstrating the curiosity? Just to say I wonder, you know can this and not just wonder openly wonder in reference to your objective your why your why we're doing this. Why does it matter? And if you do that you can actually.</v>\n",
      "\n",
      "0000:07:54.650 --> 0000:08:23.830\n",
      "<v Speaker 1>Able to pull up information where I haven't done it again, but you can actually get information like based on all the sticky notes. This is not for our meeting, by the way. Another meeting. You can see things like day one. What did we discuss? And this was for the digital workshop on the road map that we want to do earlier this year. So we see the first day it was business needs digital very quickly. You can pick it up as a team. Second day it's about.</v>\n",
      "\n",
      "0000:08:25.440 --> 0000:08:42.640\n",
      "<v Speaker 1>To do digital, we need more data and the data needs to be right then the third day we invited some of our. We started talking about planning and you can see what is the year ahead, what is the adoption and it gives you a very rich way to explore that information.</v>\n",
      "\n",
      "0000:08:44.040--> 0000:08:49.270\n",
      "<v Speaker 1>And it's, I hope, myself doing this. You can see that it is important.</v>\n",
      "\n",
      "0000:08:50.140 --> 0000:08:55.410\n",
      "<v Speaker 1>To think about what you want out of the end related back to the question.</v>\n",
      "\n",
      "0000:08:56.840 --> 0000:08:59.580\n",
      "<v Speaker 1>Before you do all this, but if you structure it right.</v>\n",
      "\n",
      "0000:09:00.400 --> 0000:09:09.630\n",
      "<v Speaker 1>You organize that data in a way that helps you answer this question, not knowing what the full question is, but knowing the idea of the kind of things that you would like to explore.</v>\n",
      "\n",
      "0000:09:10.550 --> 0000:09:14.050\n",
      "<v Speaker 1>You can digitize inputs from these kind of workshops.</v>\n",
      "\n",
      "0000:09:14.830 --> 0000:09:28.360\n",
      "<v Speaker 1>Pull it into something that you can explore and visualize very quickly when you say what is the most common word that comes out of freezes that come up in pairs and these are just default things in power BI you can save.</v>\n",
      "\n",
      "0000:09:29.540 --> 0000:09:51.410\n",
      "<v Speaker 1>Such by who said what. And before you jump to this, you should actually already see what is available in the default tool here as mural, but it could also be rather than jump straight to power BI. What's really available in ERP's. What's really available in the standard reports before you try to be too fancy, but the whole intent is we are the only team.</v>\n",
      "\n",
      "0000:09:52.120 --> 0000:09:55.640\n",
      "<v Speaker 1>Or we at the team that has this ability to think this way.</v>\n",
      "\n",
      "0000:09:56.290 --> 0000:10:13.920\n",
      "<v Speaker 1>You know, we always sit uh, our job is to take EKPO and the EKPO and the LFA one tables. And it's so much more. I mean, data is all around this. What would be quite manual in terms of trying to find things.</v>\n",
      "\n",
      "0000:10:14.620 --> 0000:10:33.210\n",
      "<v Speaker 1>Uh, you can turn it. Make it into something easy to understand. Where what I had to do here is I had to pick up the story manually, but if you digitize things properly, you set the right rules around or make sure that everything is in the box because the sticky notes will absorb the box that is in.</v>\n",
      "\n",
      "0000:10:34.730 --> 0000:11:03.520\n",
      "<v Speaker 1>And you tell people one idea per sticky. You remember I said that because if you don't do that, then you get more than one idea and sticky. Think about that from a perspective of data quality. People use the field wrongly. Then obviously it it wouldn't help to sort of answer the question that you want and the ultimately it allows you to pull up a picture that you can look to see people talking about what are people talking about. Does it matter you can zoom in.</v>\n",
      "\n",
      "0000:11:03.950 --> 0000:11:06.710\n",
      "<v Speaker 1>Transactions and use it to inform decision making.</v>\n",
      "\n",
      "0000:11:07.370 --> 0000:11:30.310\n",
      "<v Speaker 1>And shape what we want to do, because we're using that information and take that answer and then use it to even enrich it further. So it's almost like a ladder. You know, you start with some data, you get some insights, the insights you take, some action, you generate more data because of the discussions around that and you continue to enrich it. Now it's not about this workshop kind of you anymore. It is about the way we do our work.</v>\n",
      "\n",
      "0000:11:31.050--> 0000:11:33.690\n",
      "<v Speaker 1>We do a lot more than this, building dashboards and reports.</v>\n",
      "\n",
      "0000:11:34.330 --> 0000:11:40.900\n",
      "<v Speaker 1>And I'm doing this to share not because I want you to repeat this, but to just think about.</v>\n",
      "\n",
      "0000:11:41.540 --> 0000:11:50.540\n",
      "<v Speaker 1>You know, do you think a data view guided by purpose? What is the question that the business wants answered? What matters that the business?</v>\n",
      "\n",
      "0000:11:51.230 --> 0000:12:10.530\n",
      "<v Speaker 1>Uh, and step back a little bit to try to understand where can I get the information? Is using the existing tool like what I'm doing here where you can just search things here. Is that good enough? Do I need something more special? Do I not even need a tool? I just need to eyeball it and then just see it because it's so simple.</v>\n",
      "\n",
      "0000:12:11.950 --> 0000:12:18.090\n",
      "<v Speaker 1>And if I need to go for more advanced ways, what tools do I have available and just have an understanding?</v>\n",
      "\n",
      "0000:12:19.370 --> 0000:12:28.970\n",
      "<v Speaker 1>So I I think this is sort of my general sharing to just relate the way that we can digitize this workshop content.</v>\n",
      "\n",
      "0000:12:29.980 --> 0000:12:50.970\n",
      "<v Speaker 1>And then use that digitized content to try and infer some degree of insights where we can do like word searches, we can do sentiment analysis, key phrase analysis. You try and understand. Do we have something in common between all of this and the way that we see things and the whole intent is not to just make a chat is to.</v>\n",
      "\n",
      "0000:12:51.780 --> 0000:12:57.630\n",
      "<v Speaker 1>Tell the story driven by data, and in this case it's data from all this generating it.</v>\n",
      "\n",
      "0000:12:58.290 --> 0000:13:28.390\n",
      "<v Speaker 1>To help us get better as it be, understand where do we need to rebalance, where do we need to build on our strengths and what are the gaps that we need to cover. So I'll get off my soapbox, but I just wanted to share this with everyone that it's about this and the last thing I wanted to share is if you want to put in multiple stickies and you're lazy to do it online, it's it's also about interoperability of our systems. Did you know that you can also do this?</v>\n",
      "\n",
      "0000:13:29.480 --> 0000:13:35.590\n",
      "<v Speaker 1>You can take things, do it offline, and then later come into mural and you can paste 4 boxes all the same time.</v>\n",
      "\n",
      "0000:13:38.210 --> 0000:13:49.940\n",
      "<v Speaker 1>So think about the systems that we use think about the and maybe this is mobile venue, but maybe on social screen for small interoperatibility did you see what I just did? I took excel. I mean Excel talked to mural.</v>\n",
      "\n",
      "0000:13:50.700 --> 0000:14:21.810\n",
      "<v Speaker 1>You know, so think about it. Can we make S4 talk to Oscar for the data hub in the background? Can we make things join up better? And it goes back to the data thinking we are unique. At least I feel this team where unique in the organization because unlike CP data, they sort of look at, they start from the governance side. We start from the what is the business question. We're trying to answer and then we work backwards to say what data is there, what can we do with the data that's available. But then we still have to go through all these kind of questions so.</v>\n",
      "\n",
      "0000:14:23.090--> 0000:14:23.860\n",
      "<v Speaker 1>Hopefully.</v>\n",
      "\n",
      "0000:14:24.690 --> 0000:14:31.920\n",
      "<v Speaker 1>What I'm saying is there's a story around like trying to say so. The idea here was just to sort of share with you different ways of thinking when you approach your work.</v>\n",
      "\n",
      "0000:14:32.930 --> 0000:14:34.020\n",
      "<v Speaker 1>OK, I'm going to pause.</v>\n",
      "\n",
      "0000:14:38.930 --> 0000:14:44.760\n",
      "<v Speaker 2>Thank you. See you on that. I think some good insights there. I will stop the.</v>\n",
      "\n",
      "0000:14:44.860 --> 0000:14:48.170\n",
      "<v Speaker 2>Um recording?</v>\n",
      "\n",
      "0000:14:48.690 --> 0000:14:48.980\n",
      "<v Speaker 1>Yep.</v>\n",
      "\n"
     ]
    }
   ],
   "source": [
    "with open(cleanedVTT) as f:\n",
    "    cleanedtext = f.read()\n",
    "\n",
    "print(cleanedtext)\n",
    "\n",
    "#Check if formatting of timestamps corrected from 0:0:0.0 to 00:00:00.000 format..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1de76cb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import webvtt\n",
    "start=[]\n",
    "end=[]\n",
    "text=[]\n",
    "speaker=[]\n",
    "for caption in webvtt.read(cleanedVTT):\n",
    "    start.append(caption.start)\n",
    "    end.append(caption.end)\n",
    "    text.append(caption.text)\n",
    "    speaker.append(caption.raw_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "70026c04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>StartTime</th>\n",
       "      <th>EndTime</th>\n",
       "      <th>Text</th>\n",
       "      <th>Speaker</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00:00.000</td>\n",
       "      <td>00:00:13.370</td>\n",
       "      <td>Analytics and the mental level analytics is no...</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00:00:13.850</td>\n",
       "      <td>00:00:33.520</td>\n",
       "      <td>Uh, where do we need to go? How do we work as ...</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00:00:34.250</td>\n",
       "      <td>00:01:04.140</td>\n",
       "      <td>Where we need to move forward to, you know, an...</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00:01:04.540</td>\n",
       "      <td>00:01:28.810</td>\n",
       "      <td>Link to you know you wanna clean inbox so that...</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00:01:30.130</td>\n",
       "      <td>00:01:57.420</td>\n",
       "      <td>Opportunity to invite innovate, you know, so s...</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>00:14:24.690</td>\n",
       "      <td>00:14:31.920</td>\n",
       "      <td>What I'm saying is there's a story around like...</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>00:14:32.930</td>\n",
       "      <td>00:14:34.020</td>\n",
       "      <td>OK, I'm going to pause.</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>00:14:38.930</td>\n",
       "      <td>00:14:44.760</td>\n",
       "      <td>Thank you. See you on that. I think some good ...</td>\n",
       "      <td>Speaker 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>00:14:44.860</td>\n",
       "      <td>00:14:48.170</td>\n",
       "      <td>Um recording?</td>\n",
       "      <td>Speaker 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>00:14:48.690</td>\n",
       "      <td>00:14:48.980</td>\n",
       "      <td>Yep.</td>\n",
       "      <td>Speaker 1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       StartTime       EndTime  \\\n",
       "0   00:00:00.000  00:00:13.370   \n",
       "1   00:00:13.850  00:00:33.520   \n",
       "2   00:00:34.250  00:01:04.140   \n",
       "3   00:01:04.540  00:01:28.810   \n",
       "4   00:01:30.130  00:01:57.420   \n",
       "..           ...           ...   \n",
       "70  00:14:24.690  00:14:31.920   \n",
       "71  00:14:32.930  00:14:34.020   \n",
       "72  00:14:38.930  00:14:44.760   \n",
       "73  00:14:44.860  00:14:48.170   \n",
       "74  00:14:48.690  00:14:48.980   \n",
       "\n",
       "                                                 Text    Speaker  \n",
       "0   Analytics and the mental level analytics is no...  Speaker 1  \n",
       "1   Uh, where do we need to go? How do we work as ...  Speaker 1  \n",
       "2   Where we need to move forward to, you know, an...  Speaker 1  \n",
       "3   Link to you know you wanna clean inbox so that...  Speaker 1  \n",
       "4   Opportunity to invite innovate, you know, so s...  Speaker 1  \n",
       "..                                                ...        ...  \n",
       "70  What I'm saying is there's a story around like...  Speaker 1  \n",
       "71                            OK, I'm going to pause.  Speaker 1  \n",
       "72  Thank you. See you on that. I think some good ...  Speaker 2  \n",
       "73                                      Um recording?  Speaker 2  \n",
       "74                                               Yep.  Speaker 1  \n",
       "\n",
       "[75 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame(list(zip(start,end,text,speaker)), columns = ['StartTime', 'EndTime',\"Text\",\"Speaker\"])\n",
    "listx=df['Speaker'].str.split('>', n=1, expand=True)\n",
    "df[\"Speaker\"]=listx[0]\n",
    "df[\"Speaker\"]=df[\"Speaker\"].str.replace(\"<v \",\"\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "15dcc187",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel (r'cleaned_table.xlsx', index = False, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "354da293",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b5da5f2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}