Importing transcripts into Excel

When I use a YouTube video - such as an interview - in the course of my work, I often grab the transcript. This enables me to accurately cite what was said. All good so far.

The trick is that (a) if we access the transcript (i.e. click the view transcript link in the video description to pop it out alongside the video), and (b) copy it, what we get is a text string of the time stamp, a hard return, and then the text. Rinse, repeat.

I can paste my copied transcript into a table in Word. However, a one hour interview becomes a long document with less than optimal searchability and filtering. For example, say I am looking for the word "19" in the transcript. While, I can search for "19" in Word, that is going to pick up every 19 second time stamp, every 19 minute time stamp, and every other time 19 is mentioned... it can be a long list of hits.

For better quality searchability and filtering, I prefer to use Excel. But when I copy my transcript into Excel, I get alternate rows of the time stamp, then the text. I need a way to separate these two without disconnecting the time stamp from the text, in case I want to quote from the interview. 

There is an easy work around: copy the data once beginning in B2, then again into C2 (we leave A2 blank for now). Now click on the single cell B2, and using the File | Delete function, click the "Delete cell" | "Move cell up" option. This will delete the first time stamp from the C column, so we get a time stamp showing in column B, and the text that relates to it in column C.

Now we shuffle over to column A, and enter the following wee formula (Trifuntov, 2023) into cell A2:

=ISODD(ROW(2:2))

What this does - thank you Alexander Trifuntov! - is to return a "TRUE" if the row is an odd numbered row; and a "FALSE" if the row is even numbered. We drag this formula down our page. It will consecutivise row numbers as it goes (another new word I have just invented!).

Our second-to-last step is to pop some headings into the spreadsheet: I use Odd rows; timestamp; and transcript to prepare for our auto-filter. 

For our last step we highlight our data, and select auto-filter. Now we can (a) filter column A to "False", which then gives us our timestamp and text side-by-side in columns B and C (and as you can see by the image accompanying this post); and we can now search for "contains" in column C.

Sam

References:

Trifuntov, A. (2023, July 19). How to select every other row or every nth row in Excel. Able Bits. https://www.ablebits.com/office-addins-blog/select-every-other-row-excel/

read more "Importing transcripts into Excel"

Micro tasks to digest crises

We often think that we need to block out an entire day to get the 'job' done: whatever that job is. That we need to have 'clear space to think' , or an uninterrupted day.

And if we could only have that time, we could complete what we need to get complete.

I think we may indulge in the wrong metaphors: that we need a grand action for a grand task, instead of diligence, and the accumulation of many actions. Instead, we could think of ourselves as Aslan's mice. If any of you have read the Lion The Witch and the Wardrobe (as illustrated with this post by Pauline Baynes; Lewis, 1950, p. 147), you may remember that the mice came and nibbled away at the great lion's bonds during the night. With so many small creatures, and the aggregation of many small efforts, all the bonds were removed.

Tara Brabazon said in a blog post earlier this year that "there's a kind of bravery and courage that you just cannot see. It's a bravery that you have to choose for yourself. You choose it in little seemingly insignificant choices, [which] you make every single day. You keep making these tiny good choices, over, and over, and over; until you realize that your entire life is different" (2021, 20:27). Tara was quoting Brittany Burgunder - a Californian woman who has fought her own demons with anorexia, compulsive eating, and bulimia. Brittany has lived this in her world; tiny action by tiny action.

This process of chunking things down to their component, small, even micro-decisions is powerful. It is not the big, grand gestures that get the work done; it is in the nibbling away of the small, relentless tide that allows us to suddenly experience the expanse of the sea, now the mountain of work is at our back.

We can break through any bonds that bind us, providing we make one small decision after another. It is the cumulative effect of the decisions which get us there.

Something to remember.

Sam

References:

• Brabazon, T. (1 May 2021). Vlog 267 - Rescue Yourself [video]. Office of Graduate Research Flinders University. https://youtu.be/AgVgq48vHDU
• Lewis, C. S. (1950). The Lion, The Witch and the Wardrobe (9th reprint, 1969). Puffin Books.

read more "Micro tasks to digest crises"

Planning time to write

I think academics can be woefully bad at estimating writing time (myself included). I ran across a couple of old resources recently, which reminded me of an important message that I don't think we tell our supervisees often enough: we must plan time to write. And I don't mean that we have to simply plan, because that is not the problem. The problem is that we have to block out much, much more time than we think a project will take to write, in order to actually complete a research project.

This is because the research - the doing - is a tiny part of the the job. The writing is a gigantic black hole which eats up time and drives us mental ... largely because we have under-planned the actual time it will take.

Doing ≠ writing

We need to plan to start writing up our project the moment we start scoping the project. And we need to plan in the fact that we will probably still be writing it six months after the research - the doing - is complete.

RMIT lecturer Gavin Moodie (2010) uses some sample PhD project timelines in his workshops, where the thesis writing part of PhD candidate projects were fairly consistently 'planned' as two to four weeks at the end of the three year project. That is between 1.5% and 3% of overall project time. Crikey: now there is a communication failure between supervisors and supervisees!

Instead we need to write as we go. Good advice is to "(a) write early and write often; [and] (b) don’t get it right, get it written" (Moodie, 2010, p. 11, citing Delamont, Atkinson & Parry, 1998, p. 121). However, that does not say what percentage of time writing should be factored in at. In reviewing John Wolf's excellent PhD writing advice (1996), it too is silent on how long writing will take.

Turning to my own experience, I feel we should allow at least 25% of our total project time for writing if we are a fast writer: up to 40% of our project time if we are slow. Therefore if we are a fast writer, and aiming to complete a PhD in three years, we would probably factor in 20% of our yearly programme for writing in the first two years (around 4 and a half months for planning and doing), with perhaps a half of the final year in writing (five and a half months for writing during the completing stage). That is a much more realistic plan.

25% of total project time for a fast writer;

up to 40% of total time if slow

My guestimated writing ratio is scaleable. For example, if our project is over five months, we simply divide the months into three parts: six weeks each for planning, doing, and completing. Around 20% each for the first two sections gives us a week and a half for writing in each of the earlier stages, with 2.5 weeks of solid writing to end the last six week block.

I hope that helps!

Sam

References:

• Moodie, G. (2010). Surviving your PhD. https://www.academia.edu/359676/Surviving_your_PhD?email_work_card=view-paper
• Wolf, J. (1996). How to write a PhD thesis. University of New South Wales. https://newt.phys.unsw.edu.au/~jw/thesis.html

read more "Planning time to write"

View Recent Facebook Posts using Bookmarks

Facebook is something I use to keep in touch with friends and family, and I am slightly active on it. However, one thing that continues to annoy me is the lack of linearity - of timeline - in the news feed. About four years ago, FB changed the strucure of the posts to sort it with those posts with the most activity coming first, in descending order of reaction and comment.

A while ago I posted how to change the FB feed order in the side bar (here), but this is not a 'sticky' fix. Each time you go back in, you have to reset the feed back to "Most Recent". Go to another page, come back, reset. Rinse, repeat.

However, I want to see what is going on NOW, not six months ago, for when I do dip in. And there is a fix for both PC and mobile, kindly provided within the Facebook community, by Jahthree Pothole and Qazi Muhammad Asif, respectively:

• "Save this as a bookmark, or as a startup page in your browser. It always starts with 'most recent' https://www.facebook.com/?sk=h_chr (Pothole, 2014). The key is the "?sk=h_chr" ending, which you can append to any page or group address that you already have bookmarked
• "and for mobile user like m.facebook.com" (Asif, 2014)

When you load your page from the bookmark, the suffix will disappear. However, your feed view will be in 'most recent posts first' order.

Hopefully those two tips will work for you, as they worked for me.


Sam

• Reference: Pothole, J. & Asif, Q. M. (2014). How do I get my newsfeed to sort by most recent? Retrieved 6 December 2017 from https://www.facebook.com/help/community/question/?id=814642935216256

read more "View Recent Facebook Posts using Bookmarks"

Effective planning

Rolf Dobelli writes, in his book "The Art of Thinking Clearly", about Roy Baumeister and his team of researchers who explored why the Zeigarnik effect - ie, that having a plan meant you could keep a clear head - worked.

The research team organised students, all a few months from their final exams, into three groups.

"Group 1 had to focus on a party during the current semester. Group 2 had to concentrate on the exam. Group 3 had to focus on the exam and also create a detailed study plan."

"Then Baumeister asked students to complete words under time pressure. Some students saw ‘pa—’ and filled in ‘panic’, while others thought of ‘party’ or ‘Paris’."

"This was a clever method of finding out what was on each of their minds. As expected, group 1 had relaxed about the upcoming exam, while students in group 2 could think of nothing else. Most astonishing was the result from group 3."

"Although these students also had to focus on the upcoming exam, their minds were clear and free from anxiety. Further experiments confirmed this. Outstanding tasks gnaw at us only until we have a clear idea of how we will deal with them."

So, having a good, step by step, detailed plan written down - a plan that has had plenty of skull-sweat put into it to be realistic, include everything that you need to do, and allows plenty of time - will help us to get the job done and get rid of that nagging feeling of impending doom.

It doesn't matter if we haven't done all the jobs yet: having a good plan allows us to juggle all our tasks and being realistic means we know how long each thing will take. A plan gives us the reassurance that we have everything under control.

No doom.


Sam

• Reference: Dobelli, Rolf (2013). The Art of Thinking Clearly: Better thinking, better decisions. UK: Hodder & Stoughton.

read more "Effective planning"

Building a Research Planning Timeline

I went looking for a plan online - a yeast starter, if you will - for planning my PhD a few weeks ago.

What really surprised me was that it appeared that one had such a plan available online. I was staggered that there did not appear to be something already in the wild.

After a fairly intensive search, I found a few sources to help me compile a planning tool of my own. This included John Wittwer's gantt chart template, but, as his spreadsheet was locked, I didn't explore this much further. I considered some of his headings, but the sample I found online from an unknown author (see below) had also used much of the same functionality as John had.

The two sources which did work - after a fashion - were both spreadsheets, both set up as Gantt charts... but both woefully under-detailed. One was a downloadable creative commons sheet with an unknown author, containing perhaps two percent of the required amount of detail (Unknown, n.d.); the other was an online Gantt chart website with perhaps ten percent of the required detail (Toms Planner, n.d.).

What a great shortcut it would have been to have had a whole PhD research planning timeline list that I could have downloaded, then built on. But it was not to be.

So I sat down myself and planned the first cut of my whole darn project, task by task; with start dates and stop dates; scheduling in known breaks. It was quite a long list by the time that I got to 'enough' to start with.

I had a good look at both the online and downloadable spreadsheet versions, then decided to use the downloadable version, as the online version had a 100 line limitation. This was a good option, as my downloadable spreadsheet currently has 201 lines, and will, I am sure, expand.

I made quite a few modifications, such as using functions to work out min & max dates, functions to add the due dates, summing, auto filters, sub-groups and use of colour to delineate different task deadlines (I have put a key at the top). Unfortunately, I could only fit two years on one Gantt chart in Excel before the spreadsheet started crashing on me, but I think that is enough.

Because of the lack of resources, and in the spirit of the creative commons donation which I used a lot of, I have decided to share what I have created with others who are looking for such a beast (and you will find a note in the Comments box under Advanced properties, acknowledging my creative commons benefactor for the spreadsheet structure).

• Download the entire spreadsheet here
• Download my task list here

Sam

References:

• Unknown (n.d.). PHD research proposal gantt chart.xlsx. Retrieved 30 December 2015 from https://docs.google.com/spreadsheets/d/1cKETo0ysuSrInp0WRL90zlCdXTs32I0Ov2hJHyMpqDU/export?format=xlsx&id=1cKETo0ysuSrInp0WRL90zlCdXTs32I0Ov2hJHyMpqDU
• Toms Planner (2014). Research Project Plan. Retrieved 31 December 2015 from http://www.tomsplanner.com/template-example/research/
• Wittwer, John (n.d.). Gantt Chart Template for Excel. Retrieved 2 January 2016 from http://www.vertex42.com/Files/download2/excel.php?file=gantt-chart_L.xls

read more "Building a Research Planning Timeline"