Clarification on CSV Format Needed to Build a Converter for SenseMaker Integration

[bestian]

Hello Red-Dwarf team,

I’m currently working on the development of SenseMaker, which is an opinion aggregator. It can analyze the comments portion of a Polis report (CSV format) and generate a human-readable summary from a large number of opinions.

Since we’re in discussion with the Agora-Citizen platform about integration, their CTO [Nicolas[(https://github.com/nicobao) mentioned that the core engine is Red-Dwarf, and suggested I reach out here to discuss the alignment of exported data.

Because CSV parsing requires matching specific column names, and I have made a converter that can transform Pol.is exported data into sensemaker's input format. I'm not sure if that converter can parse Red-Dwarf's exported data too. I’d like to ask:

Does the comments CSV format exported by Red-Dwarf use the same column names as Pol.is, or are there some differences?

If there are differences, I can write another converter to handle parsing, but I first need to know Red-Dwarf’s CSV format for comments before I can proceed.

Here is an example of Pol.is exported data (truncated from real data, with comment-id, agrees,disagrees,moderated,comment-body):

timestamp,datetime,comment-id,author-id,agrees,disagrees,moderated,comment-body
1681230375,Tue Apr 11 2023 16:26:15 GMT+0000 (Coordinated Universal Time),355,0,3,2,1,"Generative AI might bring about Athenian-like leisure time."
1680530603,Mon Apr 03 2023 14:03:23 GMT+0000 (Coordinated Universal Time),273,161,1,0,-1,"h"
1680124112,Wed Mar 29 2023 21:08:32 GMT+0000 (Coordinated Universal Time),0,0,5,17,1,"I'm excited about the possibilities of generative AI for targeting and mobilizing voters."
1680124115,Wed Mar 29 2023 21:08:35 GMT+0000 (Coordinated Universal Time),1,0,14,34,1,"If AI leads to economic growth and prosperity, that makes it good for democracy."

Here's converted version(fit sensemaker's input format, with comment-id, agrees, disagrees, passes, votes, comment_text):

timestamp,datetime,comment-id,author-id,agrees,disagrees,passes,votes,moderated,comment_text
1681230375,Tue Apr 11 2023 16:26:15 GMT+0000 (Coordinated Universal Time),355,0,3,2,0,5,1,Generative AI might bring about Athenian-like leisure time.
1680530603,Mon Apr 03 2023 14:03:23 GMT+0000 (Coordinated Universal Time),273,161,1,0,0,1,-1,h
1680124112,Wed Mar 29 2023 21:08:32 GMT+0000 (Coordinated Universal Time),0,0,5,17,0,22,1,I'm excited about the possibilities of generative AI for targeting and mobilizing voters.
1680124115,Wed Mar 29 2023 21:08:35 GMT+0000 (Coordinated Universal Time),1,0,14,34,0,48,1,"If AI leads to economic growth and prosperity, that makes it good for democracy."

If convenient, could you please paste a short sample of Red-Dwarf’s exported comments CSV here in this issue? That would help me figure out how to align the formats.

Thanks a lot!

Bestian

[nicobao]

Thank you Bestian, we will take a look rapidly.

[patcon]

Hey @bestian 👋 Thanks so much for getting in touch!

First off, you can thank @derjogi for the work on this export feature :)

---

tl;dr - re: exports. CSV header structure matches polis CSV exports. comment-groups.csv is missing. small bug in summary.csv generation. (export only works for pre-computed data extracted from Polis API right now, not from data re-computed locally via the red-dwarf data pipeline.)

---

The format is intended to exactly match the Polis export format in header structure, though content will differ slightly (as explained below).

Here's a demo of it working in CoLab:
https://gist.github.com/patcon/c768fb0d009fa5533beaccfe54e5c6b9

What it essentially does right now is recreate the CSV export files (which normally require a sometimes-secret report_id to fetch) from just the Polis API data fetched from a publicly-known conversation_id. So the main reason to use it right now is to get a workable CSV export from the publicly-knowm Polis conversation ID.

(To set clear expectations: We do not yet have the ability to generate a fresh CSV export from a red-dwarf-generated run of the data pipeline, but that is certainly of interest, and very welcome work. We currently just transform Polis API data into a CSV export format.)

Notes on the file contents

Basically, our CSV export has all the core files except comment-groups.csv (which is empty).

Otherwise, they differ like so:

• in summary.csv, there's a minor bug in the voters-in-conv count. In CSV export, voters-in-conv shows counts of total participants instead of grouped participants #103
• if red-dwarf's CSV export data is fetched from the Polis API, it only contains the single most recent vote for any users' vote on a statement, rather than the duplicates that appear in Polis' votes.csv export. So our generated votes.csv has only one vote per participant per statement, and no duplicates

[bestian]

Thank you @derjogi for the work on this export feature.
and thank you @patcon for reply and explain.

I will learn more about them in the next few days.

[nicobao]

@bestian for SenseMaker, do you only need comments.csv?
Or do you use the other csv files, and if yes how so?
Do you make any other change to the pol.is export format, than the one exposed above? If yes, how so?

I'm still evaluating whether we will use reddwarf's export feature to implement the export function in Agora. We may reimplement this in Typescript, since our backend is written in Typescript anyway so it may be more work to write the bridge between Python data structures and Typescript, compared to just implementing the CSV output in Typescript.

If you only need comments.csv we'll definitely reimplement this in Typescript, since the file format is trivial.

[bestian]

@bestian for SenseMaker, do you only need comments.csv? Or do you use the other csv files, and if yes how so? Do you make any other change to the pol.is export format, than the one exposed above? If yes, how so?

I'm still evaluating whether we will use reddwarf's export feature to implement the export function in Agora. We may reimplement this in Typescript, since our backend is written in Typescript anyway so it may be more work to write the bridge between Python data structures and Typescript, compared to just implementing the CSV output in Typescript.

If you only need comments.csv we'll definitely reimplement this in Typescript, since the file format is trivial.

hi @nicobao ,

yes, for SenseMaker, we only need comments.csv

My backend service's csv converter related codes are here in the two files(comments are just translated into English for sharing), both in is Typescript, I hope the code and comments itself are understandable:

1. https://github.com/bestian/sensemaker-backend/blob/master/src/utils/sensemake_openrouter_utils.ts

2. https://github.com/bestian/sensemaker-backend/blob/master/src/utils/csv_converter_new.ts

If I hope the code and comments itself are not understandable enough, please tell me, and I'll try to explain the logic further. Thank you.

[nicobao]

We'll reimplement the output of comments.csv in Agora then, since it's trivial to do so, compared to integrating Typescript with Python and then outputing csv files.

@patcon I let you close this issue if you deem it necessary, unless you see any changes that should be done to the export feature of reddwarf

@bestian, let's follow-up discussions here: zkorum/agora#346