31:Tade,explainer = html`

What I did to the data

Andy sent me a downloaded from Whatsapp text file with a list of all messages paired with date/time stamps. I took that file and:

Tasks	Platforms	Languages
Split the data out into useful columns inlcuding sender, message, timestamp, word count, character count, etc. Filtered out members who contributed very little and became inactive post 2015. These were Chris, George and Adam. Filtered out personally identifiable information such as full name and message. Basic visualisation and experimentation with the data i.e. topic modelling and sentiment analysis. Decided to go for Google Cloud Platform to do both as wanted to harnass their deep learning based tools and have little benefit from doing my own custom topic modelling at this stage.
Processed the data through the Google Natural Language API (GNLAPI) to obtain sentiment , topic category and extract entities (the parts of each message most salient to its meaning).
Explored and experimented with the data through interactive visualisation. Created narrative and refined visualisations.

Let's have a quick look at the resulting data. Make a selection at the filters below and you'll see a random post that matches the criteria + 10 follow-up posts in sequence...

Note, for the purpose of data protection:

It does not have the actual message

All senders are referred to by first name only

${viewof senders_copy2_input}

${viewof engagement_category_input}

`32:T78d,function main_file_function(parsed_file, fathers_array = false) { let parsed_file_deep_copy = JSON.parse(JSON.stringify(parsed_file)); let pf_with_source = parsed_file_deep_copy.map(function(el,ind) { //add previous sender let date_split = el['date'].split("/"); //[0] day, [1] month, [2] year let datetime_asdate = new Date(`${date_split[2]}-${date_split[1]}-${date_split[0]}T${el['time']}:00-06:00`); let monthyear_asdate = new Date(`${date_split[2]}-${date_split[1]}-01T${el['time']}:00-06:00`); el['previous sender'] = 'first post'; if (ind > 0) { el['previous sender'] = parsed_file[ind - 1]['sender']; if (Number(el['time diff (minutes)']) > 180) { el['previous sender'] = 'max reply time exceed'; } if (parsed_file[ind - 1]['sender'] == parsed_file[ind]['sender']) { el['previous sender'] = 'not reply'; } } if(fathers_array) { el['father'] = 'no'; if (fathers_array.some(function(d,index){ return ((el['sender'] == d['name']) && (datetime_asdate > d['firstborn dob'])) }) == true) { el['father'] = 'yes'; } } if(lockdown_array) { el['pandemic'] = false; if(datetime_asdate >= lockdown_array[0]['start']) { el['pandemic'] = 'UK pandemic non-lockdown'; } if (lockdown_array.some(function(d,index){ return (datetime_asdate >= d['start']) && (datetime_asdate <= d['end']) }) == true) { el['pandemic'] = 'UK pandemic lockdown'; } } el['datetime_asdate'] = datetime_asdate el['monthyear_asdate'] = monthyear_asdate el['timestamp'] = datetime_asdate.getTime() return el; }) return pf_with_source; } 33:Ta1e,exploded_file_function = function(parsed_file, fathers_array = false) { let post_categories_top15_array = d3.rollups(parsed_file, v => v.length, d => d['Google classification category - top']).sort((a,b) => b[1] - a[1]).filter((el,ind) => (ind > 0) && (ind <= 15)).map(e => e[0]); //post_categories_top15_array[post_categories_top15_array.indexOf("News")] = "News & Politics"; let parsed_file_deep_copy = JSON.parse(JSON.stringify(parsed_file)); let pf_with_source = parsed_file_deep_copy.map(function(el,ind) { //add previous sender let date_split = el['date'].split("/"); //[0] day, [1] month, [2] year let datetime_asdate = new Date(`${date_split[2]}-${date_split[1]}-${date_split[0]}T${el['time']}:00-06:00`); let monthyear_asdate = new Date(`${date_split[2]}-${date_split[1]}-01T${el['time']}:00-06:00`); el['previous sender'] = 'first post'; if (ind > 0) { el['previous sender'] = parsed_file[ind - 1]['sender']; if (Number(el['time diff (minutes)']) > 180) { el['previous sender'] = 'max reply time exceed'; } if (parsed_file[ind - 1]['sender'] == parsed_file[ind]['sender']) { el['previous sender'] = 'not reply'; } } el['father'] = 'no'; if (fathers_array.some(function(d,index){ return ((el['sender'] == d['name']) && (datetime_asdate > d['firstborn dob'])) }) == true) { el['father'] = 'yes'; } if (lockdown_array) { el['pandemic'] = false; if(datetime_asdate >= lockdown_array[0]['start']) { el['pandemic'] = 'UK pandemic non-lockdown'; } if (lockdown_array.some(function(d,index){ return (datetime_asdate >= d['start']) && (datetime_asdate <= d['end']) }) == true) { el['pandemic'] = 'UK pandemic lockdown'; } } el['datetime_asdate'] = datetime_asdate; el['monthyear_asdate'] = monthyear_asdate; el['timestamp'] = datetime_asdate.getTime(); el['Google classification category - top (ungrouped)'] = el['Google classification category - top']; el['Google classification category - top'] = el['Google classification category - top'] == ''? 'Unclassified': post_categories_top15_array.indexOf(el['Google classification category - top']) == -1? 'Other': el['Google classification category - top'].replace("News","News & Politics"); return el; }) return pf_with_source; }34:T487,entities_per_topic_map = { const entities_to_array = function(input){ return input.replace("['","").replace("']","").split("', '").filter(el => el != "[]"); } const list = d3.groups(exploded_file.filter(e => e['Google classification category - top'] != 'Unclassified'), d => d['Google classification category - top'] ).map(function(e){ let topic = e[0]; let by_topic_data = e[1];//['Google entity names']; return by_topic_data.map(function(el) { return { 'topic': topic, 'entities': entities_to_array(el['Google entity names']) } }) }) .reduce(function(arr, elem) { return arr.concat(elem); }, []); const all_list = exploded_file.map(function(e){ return { 'topic': "All", 'entities': entities_to_array(e['Google entity names']) } }) .reduce(function(arr, elem) { return arr.concat(elem); }, []); let grouped_list = d3.rollup( all_list.concat(list), v => v.reduce(function(arr, elem) { return arr.concat(elem.entities); }, []), d => d.topic ); return grouped_list; }35:[{"id":"ef520f5caf3b4e5b3ae20f4969fdf458cfe87a2013ff90391a1246fcc22ecb9b5d2c8694ad973e717a7e0d9f18689a56bf2d32578e4775abb18728eaacf45b6b","href":"https://static.observableusercontent.com/files/ef520f5caf3b4e5b3ae20f4969fdf458cfe87a2013ff90391a1246fcc22ecb9b5d2c8694ad973e717a7e0d9f18689a56bf2d32578e4775abb18728eaacf45b6b","name":"observable.PNG","type":"image/png","lastModified":1614461277370.501,"size":2201},{"id":"062420636910a912ffa232dc4c2e68dfbeb1c0d75322fed751f1e5ceeea51889ec9187b29d215cf42bddc8702e89a98d1fe4d759fbcafbc7620e9d8c941a09ee","href":"https://static.observableusercontent.com/files/062420636910a912ffa232dc4c2e68dfbeb1c0d75322fed751f1e5ceeea51889ec9187b29d215cf42bddc8702e89a98d1fe4d759fbcafbc7620e9d8c941a09ee","name":"gcp@1.png","type":"image/png","lastModified":1614555757491.004,"size":13065},{"id":"8711e8a6fefb3d03bd84c71d497af1587620130a55245081fcec61a3e658fc1cd34926910c267ec014d9c2a8a7793e946d905eecc5f0b7a1c3d73837c12c3e20","href":"https://static.observableusercontent.com/files/8711e8a6fefb3d03bd84c71d497af1587620130a55245081fcec61a3e658fc1cd34926910c267ec014d9c2a8a7793e946d905eecc5f0b7a1c3d73837c12c3e20","name":"gnlapi_processed_vars_cleaned_exploded-08-03-2021v2.csv","type":"text/csv","lastModified":1615227093723.938,"size":7354318},{"id":"9405d1f13e329af0a279f9dd87913d30a8160013a0ce05f9553b0020c4015ba01d7074eee6e0a8dc0e41404c88fc80f2d63c2d089475514171cbe8505c56dc88","href":"https://static.observableusercontent.com/files/9405d1f13e329af0a279f9dd87913d30a8160013a0ce05f9553b0020c4015ba01d7074eee6e0a8dc0e41404c88fc80f2d63c2d089475514171cbe8505c56dc88","name":"gnlapi_processed_vars_cleaned-08-03-2021v2.csv","type":"text/csv","lastModified":1615227085328.698,"size":7028585},{"id":"81e6c0181ec73845f715778d0da3f3245209445e0a61763151524f7e250ac2d2bfe0f63a78630083d32065352c00def7720027534bf9176390fbdaac999be9f3","href":"https://static.observableusercontent.com/files/81e6c0181ec73845f715778d0da3f3245209445e0a61763151524f7e250ac2d2bfe0f63a78630083d32065352c00def7720027534bf9176390fbdaac999be9f3","name":"gnlapi_processed_vars_cleaned-08-03-2021v3.csv","type":"text/csv","lastModified":1615227859724.301,"size":7035527},{"id":"47fa631757910e8b32ef7f0c634d0b2f246a2a1d193d5d450d13403b26869890e5b429050cb252e6ca5f6763103e31a4492c12c4a36e7d27ef424962443b54a1","href":"https://static.observableusercontent.com/files/47fa631757910e8b32ef7f0c634d0b2f246a2a1d193d5d450d13403b26869890e5b429050cb252e6ca5f6763103e31a4492c12c4a36e7d27ef424962443b54a1","name":"gnlapi_processed_vars_cleaned_exploded-08-03-2021v3.csv","type":"text/csv","lastModified":1615227844315.59,"size":7361383},{"id":"258370f5e2de4c581ae6cb8f59b7cc622f43f4a2385f16bcd7bb0d1b31f7649e8746a2969eca0890c66455a301e79641d8dfdccbca942947e008bb45954a739b","href":"https://static.observableusercontent.com/files/258370f5e2de4c581ae6cb8f59b7cc622f43f4a2385f16bcd7bb0d1b31f7649e8746a2969eca0890c66455a301e79641d8dfdccbca942947e008bb45954a739b","name":"gnlapi_processed_vars_cleaned-08-03-2021v3@1.csv","type":"text/csv","lastModified":1615808521618.46,"size":7035527},{"id":"5b70c59fdab07397f655412a01abd0daffb77550f2cc391d624d15ae796e7ca36c33e4ee6069d020e1ab647649e2e3194ff8c31c9a57cdeffb83b192ec8894ef","href":"https://static.observableusercontent.com/files/5b70c59fdab07397f655412a01abd0daffb77550f2cc391d624d15ae796e7ca36c33e4ee6069d020e1ab647649e2e3194ff8c31c9a57cdeffb83b192ec8894ef","name":"gnlapi_processed_vars_cleaned_exploded-08-03-2021v3@1.csv","type":"text/csv","lastModified":1615808481909.266,"size":7361383}] 2f:{"title":"Book Club WhatsApp Group: What Our Posts Say About Us - Intro","cells":[{"id":0,"value":"md`# Book Club WhatsApp Group: What Our Posts Say About Us - Intro`","pinned":false,"hidden":false,"mode":"ojs"},{"id":82,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":327,"value":"selected_rows = {\n \n let cols = example_table_input.columns\n \n return example_table_input.map(function(e){ \n return Object.keys(e)\n .filter(key => cols.includes(key))\n .reduce((obj, key) => {\n obj[key] = e[key];\n return obj;\n }, {}); \n });\n \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":228,"value":"md `### Limitations\n

Cannot identify replies to specific messages, replies are interpreted as next message in sequence as long as it is 3 hours or less after a message.\n

Media is ommited, i.e. images are stripped out and links are recorded only as 'http' (so no other descriptive information. Emoticons are processed and do contribute to the sentiment scores.

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":6,"value":"md`### Appendix: Code`","pinned":false,"hidden":false,"mode":"ojs"},{"id":453,"value":"md `### Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":47,"value":"d3 = require(\"d3@6\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"import {swatches} from \"@d3/color-legend\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":4,"value":"import {Button, Checkbox, Toggle, Radio, Range, Select, Text, Search, Table} from \"@observablehq/inputs\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":13,"value":"import {rangeSlider,persistentSelect} from \"@bumbeishvili/utils\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":16,"value":"import {rangeSlider as multiRangeSlider} from \"@mootari/range-slider\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":18,"value":"Highcharts = {\n const Highcharts = await require(\"highcharts\");\n await require(\"highcharts/modules/heatmap\");\n await require(\"highcharts/modules/wordcloud\");\n await require(\"highcharts/modules/timeline\");\n await require(\"highcharts/highcharts-more\");\n await require(\"highcharts/modules/solid-gauge\");\n \n return Highcharts; \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"md`### File processed data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"parsed_file = d3.csvParse(await FileAttachment(\"gnlapi_processed_vars_cleaned-08-03-2021v3@1.csv\").text());","pinned":false,"hidden":false,"mode":"ojs"},{"id":50,"value":"parsed_exploded_file = d3.csvParse(await FileAttachment(\"gnlapi_processed_vars_cleaned_exploded-08-03-2021v3@1.csv\").text());","pinned":false,"hidden":false,"mode":"ojs"},{"id":34,"value":"main_file = main_file_function(parsed_file, fathers_array)","pinned":false,"hidden":false,"mode":"ojs"},{"id":53,"value":"exploded_file = exploded_file_function(parsed_exploded_file, fathers_array)","pinned":false,"hidden":false,"mode":"ojs"},{"id":59,"value":"md`### File processing functions`","pinned":false,"hidden":false,"mode":"ojs"},{"id":28,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":49,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":61,"value":"md`### Configs objects`","pinned":false,"hidden":false,"mode":"ojs"},{"id":42,"value":"fathers_array = [\n{\"name\":\"Will\", \"firstborn name\": \"Rosie\", \"firstborn dob\": new Date(2015, 9, 12)},\n{\"name\":\"Alan\", \"firstborn name\": \"Eric\", \"firstborn dob\":new Date(2020, 4, 3)},\n{\"name\":\"Misha\", \"firstborn name\": \"Noah\", \"firstborn dob\":new Date(2019, 2, 18)},\n{\"name\":\"Angus\", \"firstborn name\": \"Adessa\", \"firstborn dob\":new Date(2018, 10, 4)},\n{\"name\":\"Andy\", \"firstborn name\": \"Jake\", \"firstborn dob\":new Date(2018, 9, 5)},\n{\"name\":\"Jim\", \"firstborn name\": \"Maddie\", \"firstborn dob\":new Date(2017, 10, 14)}\n];","pinned":false,"hidden":false,"mode":"ojs"},{"id":406,"value":"lockdown_array = [\n{\"name\":\"lockdown 1\", \"start\": new Date(2020,2,23), \"end\": new Date(2020,4,10)},\n{\"name\":\"lockdown 2\", \"start\": new Date(2020, 10, 5), \"end\": new Date(2020, 11, 2)},\n{\"name\":\"lockdown 3\", \"start\": new Date(2020, 11, 20), \"end\": new Date(2021,3,12)},\n];","pinned":false,"hidden":false,"mode":"ojs"},{"id":97,"value":"post_volume_array = d3.rollups(main_file, v => v.length, d => d.sender).sort(function(a,b){\nreturn b[1] - a[1];\n}) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":188,"value":"members_by_post_volume_array = post_volume_array.map(e => e[0])","pinned":false,"hidden":false,"mode":"ojs"},{"id":74,"value":"people_by_post_volume_object = { \n let obj = {}; \n post_volume_array.forEach(function(e,i){\n obj[e[0]] = i\n }) \n return obj;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":199,"value":"topics_by_post_volume_object = { \n let desc_order = d3.rollups(exploded_file, v => v.length, d => d['Google classification category - top']).sort((a,b) => b[1] - a[1]);\n let other_threshold = 15;\n let mapped_filtered_grouped_mapped = d3.groups(\n desc_order.map((e,i) => [i d[0]).map(e => [e[0],e[1][0][1]]);\n \n return Object.fromEntries(mapped_filtered_grouped_mapped);\n\n} \n// ","pinned":false,"hidden":false,"mode":"ojs"},{"id":73,"value":"types = Object.keys(people_by_post_volume_object)","pinned":false,"hidden":false,"mode":"ojs"},{"id":70,"value":"color = d3.scaleOrdinal(types, d3.schemeCategory10)","pinned":false,"hidden":false,"mode":"ojs"},{"id":65,"value":"colors = Object.fromEntries(\n new Map( d3.rollup(main_file, v => color(d3.max(v, d => d['sender'])), d => d['sender'] )\n )\n)","pinned":false,"hidden":false,"mode":"ojs"},{"id":193,"value":"post_categories_top15_array = d3.rollups(exploded_file, v => v.length, d => d['Google classification category - top']).filter((el,ind) => (ind > 0) && (ind <= 15)).sort((a,b) => b[1] - a[1]).map(e => e[0]).filter(e => e != \"Other\").concat([\"Other\"]) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":196,"value":"post_volume_map = d3.rollup(main_file, v => v.length, d => d.sender)","pinned":false,"hidden":false,"mode":"ojs"},{"id":355,"value":"post_categories_top10_array = d3.rollups(exploded_file, v => v.length, d => d['Google classification category - top'])\n.filter(e => e[0] != \"Other\")\n.filter((el,ind) => (ind > 0) && (ind <= 10))\n.sort((a,b) => b[1] - a[1]).map(e => e[0])","pinned":false,"hidden":false,"mode":"ojs"},{"id":222,"value":"days_of_week_object = ({\n1: 'Mon',\n2: 'Tue',\n3: 'Wed',\n4: 'Thu',\n5: 'Fri',\n6: 'Sat',\n0: 'Sun'\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":224,"value":"days_of_week_order_object = ({\n'Mon':0,\n'Tue':1,\n'Wed':2,\n'Thu':3,\n'Fri':4,\n'Sat':5,\n'Sun':6\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":260,"value":"years_array = d3.rollups(main_file, v => v.length, d => d['datetime_asdate'].getFullYear()).map(e => e[0])","pinned":false,"hidden":false,"mode":"ojs"},{"id":277,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":286,"value":"//exploded file filtered by category\nexploded_file_category_filtered_indexes = exploded_file.filter(e => (engagement_category_input == 'All'? true: e['Google classification category - top'] == engagement_category_input)).map(e => e['index']) ","pinned":false,"hidden":false,"mode":"ojs"},{"id":84,"value":"md `### Example Table `","pinned":false,"hidden":false,"mode":"ojs"},{"id":270,"value":"viewof senders_copy2_input = Radio(['Any'].concat(members_by_post_volume_array), {label: \"Initial sender:\", value: \"Any\"})\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":274,"value":"viewof engagement_category_input = Select(entities_per_topic_map.keys(), {value: \"Any\", label: \"Initial category\"})","pinned":false,"hidden":false,"mode":"ojs"},{"id":295,"value":"example_table_data = {\n let parsed_file_deep_copy = JSON.parse(JSON.stringify(parsed_file));\n let shuffled_filtered = d3.shuffle(parsed_file_deep_copy.filter(e => [\n (senders_copy2_input == 'Any'? true: senders_copy2_input == e['sender']), \n (engagement_category_input == 'All'? true: exploded_file_category_filtered_indexes.indexOf(e['index']) > -1? true: false)\n ].every((input) => input == true)))\n \n let started_post_index = shuffled_filtered.slice(0, 1)[0]['index'];\n let started_post_indexOf = main_file.findIndex((element) => element['index'] == started_post_index);\n let starter_plus_replies = main_file.slice(started_post_indexOf, started_post_indexOf + 10);\n \n return starter_plus_replies;\n \n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":90,"value":"function text_truncate(str, length, ending) {\n if (length == null) {\n length = 100;\n }\n if (ending == null) {\n ending = '...';\n }\n if (str.length > length) {\n return str.substring(0, length - ending.length) + ending;\n } else {\n return str;\n }\n };\n","pinned":false,"hidden":false,"mode":"ojs"},{"id":92,"value":"viewof example_table_input = Table(example_table_data, {columns: ['sender','previous sender','datetime','words','unique words','characters','Google sentiment product','Google classification category names','Google classification category confidences','Google entity names','Google entity saliences','Google entity sentiment products','time diff (minutes)','father'], layout: \"fixed\",\n width: {\"message\": \"20%\"}\n } \n);","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W35","stdlib":"1","titling":"auto"}