This blog post is available in the YouTube short form if you’d rather hear it in 59 seconds.
If you were moved to tears by the 2023 John Lewis Christmas commercial “Snapper, the perfect tree” there are 6 hidden and not so obvious reasons the copywriters were able to tug so hard on your heartstrings.
The retailer has produced some of the most iconic Christmas ads of this century employing themes of friendship, generosity, tolerance, sharing and imagination but this sophisticated ad manages to cleverly combine all of these.
In a household with no obvious male influence the boy, like Elliott in Spielberg’s ET, is both nurtured by and nurturer, growing to understand fatherhood by becoming the protector and friend of this lonely and ostracised alien creature.
John Lewis Christmas commercials often uses cover versions of well known pieces to shift perspectives on the familiar and Andrea Bocelli’s new piece Festa uses theatrical repetition of the lyrics “la vita è una festa” – meaning “life is a celebration” – to provide an inspiring composition.
The retailer’s theme this year is the fusion of old and new festive traditions with the Saatchi strapline ‘Let Your Traditions Grow’, celebrating evolving and multicultural customs, finding joy with loved ones, whatever your traditions.
The plant is born helpless and needy but becomes an inspiration. This echoes the Christian nativity story. And the boy’s innocent recognition of goodness in the unusual is uncoloured by prejudice or preconceptions.
Popular opinion is mixed with right wing media commentary focusing on the lack of tradition and woke agendas but more liberal sources on the heartwarming celebration of difference – you can see below how different the comments are against the YouTube videos released by The Guardian and GBNews.
At a time when the UK has become more intolerant of strangers and difference than ever, with nationalism and exceptionalism having taken centre stage this seems like the perfect antidote and glimmer of hope of a welcome return to the sanity of the politically liberal mutual understanding and acceptance – like that of a boy and a carnivorous plant.
The best ads resonate, inspire and stay with us. I feel this one just might.
Transferring 10,000 photos from Windows to Android
Having recently upgraded to a new phone (Samsung S21 FE 5G 128GB) I wanted to transfer around 10,000 photos (30 GB) from my old phone to it.
My old phone had completely died and wouldn’t even turn on but my photos were happily safe on a micro SD card – a removable storage medium that the latest Samsung S series premium phones unfortunately no longer support.
I put the micro SD card into a a full size SD card adapter and then into a SD card reader which I plugged into my laptop’s USB 3 port and could see the photos on the card.
At the same time I plugged my new phone, via a USB C to USB A cable, into the laptop and could see the phone’s internal storage, where I had previously created a new folder (using the phone’s Files app) to transfer the files into.
As an aside, I created that new folder from the phone itself rather than from Windows File Explorer since when doing it the other way round I couldn’t see the newly created directory from the phone!
Unfortunately, when selecting the files in Windows (I sorted them in Windows File Explorer by file type and then picked just .jpg rather than the even more space hungry video files) and copy pasting them over to the Android folder the transfer was taking forever! The estimated time started at 2 hours and increased to over 6 hours.
It took so long that the connection to the phone dropped and the copy only got part way through, meaning I had to see how far through the copy had got, select the remaining files on my laptop and start the copy process again.
I did this a few times over a couple of hours before deciding to search for another more reliable approach!
There are numerous reports on forums and Q&A sites of this issue, like
with either no solution or just a suggestion that the slowness is due to limitations of the USB port or USB cable or file storage device or the file transfer protocol.
Files, typically 1 to 3 MB in size, were taking around 3 seconds each to copy over so transfer speed was around 40MB per minute or 2 GB per hour, far below the theoretical speed of the USB port and cable – even for USB 2 this is 70 GB per hour (and 4x that for USB 3).
With 30 GB of files, we were looking at around 15 hours! ..and that’s without the connection to the phone being lost, which was happening consistently around an hour into copying and therefore requiring my manual monitoring.
I noticed that some much larger 30 MB panoramic photo files were taking not much longer to copy than the much smaller typical 2 MB photo files, indicating a potential throughput of 10MB per second (600MB per minute) which was much more like it!
This confirmed that my USB cable’s throughput, which I was beginning to suspect (even though I had previously copied files the other way round, from my old phone to laptop, in reasonable time frames) was not the limiting factor, but rather something was happening that was related to the sheer number of files.
Maybe each file was being virus scanned by the phone or the file allocation table in the phone or indexing or integrity checking was the bottleneck but, whatever the reason, transferring via a zip file seemed a potential solution to reduce the number of individual files needing to be copied to the phone.
As it turned out zipping the files and transferring the zip file turned out to be exponentially faster – though there were a few gotchas!
Zipping around 1000 files into a 5 GB zip file took 10 minutes (it would have been much faster had I not run several zips of photos from different years at the same time) and then it took just 5 minutes to transfer the zip to the phone against the hour plus it would have taken to transfer them as as individual files.
So the last hurdle was unzipping them on the phone.
I tried the “Files By Google” app however this gave a disheartening error when trying to extract the files from the zip, making me think all was lost.
I googled the failure and came across the ZArchiver Android app which happily came to the rescue and was quickly and successfully able to extract all the files from the zip in around a minute.
So, say 15 minutes in total to zip, transfer and unzip 1000 files rather than the hour it was previously taking, a 4x speed increase in transferring multiple small files!
In fact I copied over the remaining 20 GB of files I had left to transfer in around half an hour rather than the 10 hours it would have taken so a 20x transfer speed increase.
Other options for transferring files from PC to phone include wireless ones like
uploading to a Cloud file storage service like Google Drive or OneDrive – if you don’t have enough storage free you can upload in batches
using file sharing apps like Google’s own Nearby Share – which uses WiFi and Bluetooth but requires installing and configuring software.
Yes, they were a bedrock metric that you based your KPIs on but they are no longer.
In UA (GA3) Google told us “unique pageview, as seen in the Content Overview report, aggregates pageviews that are generated by the same user during the same session. A unique pageview represents the number of sessions during which that page was viewed one or more times.”
In GA4 Google tells us simply there is no GA4 equivalent of the GA3 (UA) Unique Pageview, helpfully putting a ‘N/A’ in the GA4 column of the UA to GA4 mapping table, without so much as a by your leave.
So what metric do you use to see which pages get the most individuals looking at them?
Well looks like there is a ‘Users’ figure against pages which Google explains is “The number of distinct users who visited your website or app.”
I’ve found this new GA4 ‘users’ metric to show a significantly lower figure than the old UA ‘unique pageviews’ (UPV) figure, which may be because the latter was counting every user session where the page was viewed rather than just the unique users behind those sessions.
Who knows?
You can go to the Explore section on the left hand side of the GA4 menu and hand craft your own report of ‘sessions’ against pages but I found that yielded a figure similar to ‘users’ and markedly lower than the UPV figure I’d recorded for previous months.
Some will tell you there is a way to get those old UPV figures by combining user and session data using BigQuery. Easy huh?!
Erm, no. It’s gotten hard to get a UPV figure. But is that because Google wants you to spend time and money doing your own analysis of the data about your website they go to considerable lengths to collect and present? Or because it’s a dated concept they’d rather you didn’t use anymore?
My simple take is that at the end of the day, Google captures certain information during website page (and now app screen too) interactions and tries to present this to us in digestible, useable and familiar terms – so, for example, a user isn’t really a user in the sense of an individual person, it’s some recorded activity of pages being visited by a browser, storing a cookie on the device hosting it, within a certain amount of time since the last visiting of pages by that same browser.
Now as Google gets better at identifying real life actual users (e.g. through verified sign-ins to your own site, or even Google accounts) you may see an apparent decrease in users of your website, when in reality you are just seeing the consequence of Google Analytics more accurately counting users.
This is the quandary of data quality. Improving our data quality may mean we look like we’re doing worse in terms of the amount of data we’re collecting. So why try harder to improve data quality? I mean never mind the quality, feel the width. Unless that quality can ultimately lead you to better decisions..
Well, musings aside, I’m going with dropping UPV from my metrics in favour of the easier to report on users figure for now – but if you have a better understanding leave a comment and enlighten us all!
Excel function to generate a numeric unique ID (UID) for a person’s name.
This simple Excel function magically generates a numeric unique identifier (UID) for each name in a data set.
It’s not just creating a random number for each name because the same name generates the same number each time.
And it’s not just assigning each name one of a handful of numbers because there are as many different numbers as there are names – as proven by the counts in D1 and E1 of the worksheet below.
Checking UIDs are in fact unique for your data set.
But where’s the documentation for this magic Excel “uid” function? And why would we even need it? Well let’s get into it.
Sometimes we need to store or present data about people without revealing any of their personally identifiable information (PII) like name, date of birth (DoB), address, email address, phone number, etc. but at the same time presenting a consistent and true picture of each person’s data. For example person 1 is a 6 foot 35 year old male from London and person 2 a 5 foot 82 year old woman from Edinburgh. This is commonly referred to as pseudonymous data, rather than anonymous data.
Pseudonymous data is data that has been de-identified from the data’s subject but can be re-identified as needed. Anonymous data is data that has been changed so that reidentification of the individual is impossible. Pseudonymisation is the process of replacing identifying information with codes, which can be linked back to the original person with extra information, whereas anonymisation is the irreversible process of rendering personal data non-personal, and not subject to the GDPR.
So we could create a simple mapping table with a number assigned to each individual person but that requires:
creating and then storing the mapping
ensuring we map just the unique individuals in our data so we have just one number per individual
maintaining and adding to the table every time a new person is added.
All rather cumbersome and time consuming.
What we could really do with is some function that magically converts their full name (or full name plus whatever other piece of data we hold about them, like DoB, to uniquely identify an individual) to a unique identifier – ideally numeric, which is easy to store and look up.
So here’s an Excel user-defined function that creates a reasonably unique numeric id or more accurately hash for a person’s full name.
I say reasonably because it’s not perfect and collisions are possible, by which I mean two different names could result in the same numeric ID being generated.
You create it using VBA (the programming language that comes as standard with Excel) and can then use the function in a cell just like any other built in Excel function. It can look as simple as this:
Excel UID user-defined function.
This function encodes each character of the name, together with the position of that character in the string. Each position would ideally generate numbers of a different scale so when added up they won’t interfere with the numbers from other character positions.
So to try to visualise this, if we used a 2-digit number (e.g. A=65) to represent each letter of the name then we could have the number for the name’s first letter occupy the units and tens columns of our UID, multiply the number for the name’s second letter by a hundred so it occupies the hundreds and thousands columns of the UID, multiply the number for the third letter by ten thousand and so on.
This idea is not novel e.g. an algorithm like this is considered here: Stackoverflow.com
The problem with this is that in Excel we can only record 15 digits of precision in a number so quickly run out of numbers meaning we can only record names up to 7 letters long.
By 15 digits of precision I mean that, if you have a number over 15 digits long not all of its digits are actually stored. For example, after 1 quadrillion (1,000,000,000,000,000) you lose the ability to store units so 1,000,000,000,000,001 is actually treated and stored in Excel exactly the same as 1,000,000,000,000,002 which increases the chance of collisions. You can see this for yourself by typing each of these numbers in different cells and entering a formula to subtract one from the other. You will see a difference of 0. Yes, Excel can lie to you!
To address this we use a smaller multiplier so we can encode a longer sequence of letters within the 1 quadrillion limit, however it is then possible for a character at one position to be encoded as exactly the same number as a character in a different position so there isn’t in fact a different total for every single permutation of letters one could encode, although the chance of two names in one’s actual dataset sharing the same total is low.
For example _A (a space followed by A) generates 32 + 2*65 = 162 but so does b_ (b followed by a space).
Now this UID function could in fact be used with any string but there is a limit to the length of the string before collisions become more likely and it is designed for text up to around 30 characters i.e. the typical maximum length of a person’s full name (first name and surname).
You could experiment with increasing the character position multiplier to reduce or eliminate the chance of collisions but that results in the generated ID number requiring more than 15 digits of precision more quickly as names get longer, which then increases the possibility of collisions so it’s swings and roundabouts.
Clearly there is a trade off between the length of string you can generate a UID for and the risk of collisions.
With real person names and datasets in the low hundreds of names I’ve found in its stock form it does a pretty good job of coming up with a different ID for every different name, though there is lots of room for further optimisation – possibly at the expense of more complicated code.
For example, you could imagine simply using an if else clause to assign a different number for every single real world known name. There are apparently around 30,000,000 different surnames and around 30,000,000 firstnames and hence 30,000,000 squared (9e+14) combos of first and last name – so with 15 digits we could perfectly encode every single one of those combos. The code for such a function would however be horrendous!
If you don’t need a purely numeric identifier then generating an alphanumeric identifier (i.e. including letters as well as numbers) would of course allow even more numerous representations within a given code length, however, again at the expense of more complicated code.
If you come up with a more efficient code generator that deals with longer names or reduces collisions please do leave a comment.
With this simple, and hence quick to run, function, although a unique ID is not guaranteed for each different name, you can easily and quickly test whether the function does in fact create unique IDs for your specific data set by running it for each person it contains (simply fill the function down as shown in the first screenshot above) and seeing whether you get the same number of unique IDs as you have unique individuals.
Just do an advanced filter on unique records for firstly the names of the individuals and then the generated IDs of those individuals and check whether there is the same number of rows of each – or alternatively use the Excel COUNTA and UNIQUE functions in combination as shown above (note that we use COUNTA rather than COUNT since COUNT only works with cells containing numbers and dates but not text).
If it doesn’t then you should be able to tweak the function’s code slightly so it does generate a unique ID for each name in your particular data set.
Now, considering your original data set, if your person full names, by themselves, don’t uniquely identify individuals you can concatenate to their name another piece of information about them, for example their DoB, and then run the function against that concatenated string.
Finally you can add a constant of your choosing to the number generated by the function to make all the IDs a consistent length and further obfuscate the relationship between name and ID.
Note: In VBA if we declare a variable without specifying the data type then the Variant data type is assigned by default (which requires more memory resources than most other variables). Types also have a shorthand and # is the shorthand for double. For more details see Data Type Summary.
You can change the function to make it easier to find individuals through their pseudonymised ID e.g. adding their initials before the generated number to make it easier to find a specific individual across various data sets. As a bonus, this also increases the uniqueness of the generated ID though of course the addition of alpha characters means that a numeric field can no longer be used to store the ID.
Just remember that if you change the function you then need to manually recalculate any cells that have already used your user defined function, by pressing ctrl-alt-f9 to force a workbook-wide recalculation. Pressing f9 alone is not enough, since that only recalculates cells marked as ‘dirty’ i.e. which refer to cells whose value has changed since the last calculation.
So, there you go. An approach to creating a simple Excel function that generates a unique number for every person name in your dataset.
Enjoy – and for more tech and wellbeing at the lowest price please subscribe 🙏
Last week a time-saving Python program I had been running successfully for several months in the Google Colab Jupyter notebook environment suddenly failed with a curious error.
The cause – an upgrade to a package (SQLAlchemy) that a library I was using (pandasql) depended upon had broken the library.
This was rather concerning since I had written a huge volume of code using the library and refactoring the code to do things a different way would have been a not insignificant undertaking.
Happily there are a couple of fixes. One is proposed in the link above which is to downgrade the package the broken library depends on before installing the library:
This is not entirely satisfactory since one can imagine a time when that downgraded version is no longer supported.
Ideally a fix would be released to pandasql itself but despite its popularity and widespread use it seems no changes have been made to it for several years so what are the chances of that happening..
Regardless of such stylistic, philosophical and aesthetic considerations, SQL remains one of the most established and popular languages for querying tabular data and rewriting existing code to use alternative methods, like the pandas query function can be time-consuming and will certainly require retesting.
I’ve experienced these unexpected failures of previously working code on occasion before e.g. where a package was completely removed from the base Colab distribution.
Happily the community developed a fix in that instance too.
So, running Python programs on Google Colab’s ever changing foundation remains a rather nerve wracking experience with unexpected work being required from time to time when things suddenly break and you need to research, implement and test fixes. If you know a better approach to managing the relentless and inevitable changes to Colab please leave a comment!
Being overweight actually keeps us feeling unsatisfied and overweight
Inflammation increases with weight gain, which leads to insulin resistance and leptin resistance. So, if you’re looking to lose weight, reducing inflammation is key. You can do this by avoiding processed foods and added sugars, eating more anti-inflammatory foods, getting enough sleep and decreasing stress levels. Reducing the amount of inflammation in your body will also lower your risk for diseases like cancer, heart disease and diabetes.
There is more to long term health and weight maintenance success than calories in and calories out. Check out these conversations by 2 London hospital doctors.
In a nutshell these resources help us understand the need to put good foods in (leafy greens, broccoli/cauliflower, nuts, fatty fish) as much as avoid bad foods.
How to lose weight
I wonder whether we may ‘self-medicate’ and become fat exactly in order to reduce our metabolism, feel tired and hence reduce troublesome thoughts.
Which TV will have the best picture quality in 2023? There is a lot of talk about the headline flagship sets from the consumer electronics arms of the OLED panel manufacturers, LG and Samsung, with Sony’s A95K QD-OLED successor yet to be announced, but the truth of the matter may just have been staring us in the face all along.
Let’s summarise what some of the biggest TV YouTubers and Audio-visual websites have to say before reaching a conclusion.
Best TVs of CES 2023 (Caleb Denison)
Samsung S90C / S95C has potential for greater colour brightness and luminance if pushed
LG G3 META Micro Lens Array improves brightness but not necessarily colour brightness because of the white subpixel however he found colours to be brighter perceptually
Panasonic MZ2000 processing and picture quality made it one of the best at CES 2023
MLA just focuses existing light better rather than increases light energy so doesn’t increase burn in risk
MLA peaks at 2100 nits so a ‘150% increase’ (2.5x original or 1.5x original?)
QD-OLED2 colour brightness is measurably purer since no white subpixel but perceptually MLA may appear little different
LG G3 is 70% brighter (Vincent Teoh)
LG Brightness Booster Max light control architecture (MLA) and light boosting algorithms increase brightness by up to 70% but just on 55, 65, 77 inch models (not 83)
S95C
Samsung puts Micro LED top of its range with, OLED bottom and Neo QLED in the middle.
S90/95C in 55, 65, 77 inch sizes
Measured first MLA
MLA is brighter across all window sizes from 1% to full screen white giving more depth and punch and brighter colours
Panasonic MZ2000 1500 nits (cd/m2) peak light output on a 10% window
Graph shows 50% brightness boost over previous year’s LZ2000 at 10% window size but reducing to just 20% brightness boost at 100% window size.
Though brighter MZ2000 clears image retention quicker than LZ2000
Some scenes look identical in brightness on old LZ2000 and new MZ2000
With ambient light blacks look less black and more pink on the new MZ2000
Panasonic MZ2000 MLA
MLA design uses 27 billion lenses to redirect out of the screen light previously reflected inwards
Brightness is 50% greater than last year’s model
MLA increases colour volume
“True to the film maker’s vision” slogan – even though film makers take into account typical end user equipment
QD-OLED 2nd generation
30% brighter over 2022 QD-OLED due to higher intensity light and less internal absorption
Blacks will be truer than the greys of QD-OLED v1 due to optimisation of the top layers of the panel
CES 23 Best Tvs
LG MLA and Samsung QD-OLED2 are the main technologies impacting top end picture quality for 2023.
TCL Micro LED promises the best picture quality but is 5 years plus away
Panasonic MZ2000 with MLA is vote for best of show with a visible difference against last year’s model
Wireless HDMI is on the LG Signature OLED M model
LG G3 vs Samsung S95C matchup (FOMO)
G3 Micro Lens Array (MLA) will achieve the best full screen brightness
S95C QD-OLED has a brighter blue OLED material and a new anti reflective layer will reflect less light and so achieve truer blacks in a brighter room than its predecessor (OLED v1 also used by Sony A95K)
LG G2 issues were full screen brightness, uniformity (magenta tint) and colour accuracy
Samsung S95B issues were lifted blacks under ambient light, low bit rate content processing and bent panels
The competition will be about high APL brightness and lower luminance colour accuracy
LG MLA G3 vs Samsung 2023 QD-OLED | Really 2000 Nits? (Classy)
Marketing materials don’t specify the brightness window sizes
Panel may be capable of 2000 nits but the implementation in the models may be less
Forbes states QD-OLED v2 is 1500 nits at 10% and a 30% improvement over the previous year.
QD-OLED issues remain: colour fringing with coloured lines in bright scenes and near black smearing.
LG G3 1500 nits on 10% window and 2000 on 3%
Both Sony A95K (QD OLED with heat sync) and Samsung S95B (without heat sync) holds sustained brightness better though does not get so bright as LG G2.
Panel consistency and lack of pink tinting are the main advantages of QD-OLED over OLED.
G3 v S95C (Brian)
Samsung updates in 2022, presumably to keep the panel safe, meant the S95B no longer offered its original brightness by the end of the year
QD-OLED2 is trying to be brighter than MLA (2100 v 2000 nits)
LG Evo G2 panel had a pink tint uniformity issue and Samsung QD-OLED S95B panel had a software update trust issue
Second generation OLED (KG)
At CES 2023 the Samsung booth demonstrated QD-OLED v2 as being perceptibly brighter with even more saturated colours than 2022 QD-OLED.
RTINGS.com provides measurements for several popular TVs and top of the line LEDs measure and are perceived as much brighter and more impressive with bright full screen scenes than top of the line OLED TVs, whether WRGB OLED or QD-OLED.
For 10% peak and 100% sustained brightness respectively, RTINGS.com gives
OLEDs: LG G2 (evo) 450, 190; Sony A95K (QD-OLED) 410, 148; LG G1 (2021) 406, 161
Mini LEDs: Samsung QN95B 1,934, 569; Sony X95K 1223, 633;
Full Array Local Dimming: Sony X95H (2020) 1085, 625
So to summarise, the latest 2023 OLEDs are 30% to 50% brighter than last year for sub 10% white windows but for full screen white are perhaps just 20% brighter so for viewing in a daytime room or bright high APL scenes with, for example, sunshine, snow or sports both of the new OLED technologies, QD-OLED v2 and MLA (META), remain much less impactful than LED designs.
Micro LED achieves the holy grail of professional reference mastering monitor black and bright performance like the 31 inch Sony BVM-HX310 but is too expensive a technology for mass market consumer sets for now and several years to come, with lower energy consumption being one of the technical challenges needing to be overcome.
Each bin defines the absolute maximum number the bin can contain. A bin of 10 will contain numbers up to 10 so 9 and 10 but not 10.1. In other words, the bin contents are less than or equal to (<=) the current bin and greater than (>) the previous bin.
In short a bin contains numbers up to and including the bin’s number but not a fraction over the bin’s number as shown in the examples above.
People usually give their age as rounded down and age limits operate in that way too so a 17.9 year old can’t vote in the UK. If we want to find the number of people aged say 15-18 we need to use the Excel ROUNDDOWN function to make sure someone who is 18.3 or 18.9 appears in a 15-18 bin defined as 18. The previous bin in this example would have to be 14 to capture 14 year olds but not 15 year olds.
