Converting verbose categorical columns to abbreviated codes

[roll]

From Holger

Is there some frictionless tooling to compress large CSV datasets by converting verbose categorical columns to abbreviated codes and have the mapping extracted to a schema description?

Sounds great! A sample of the dataset is available here http://data.mfdz.de/marktstammdatenregister/mastr_head.csv. The complete dataset is 2.6GB in size and has much redundancy in it's columns, which I'd like to reduce. Is this functionality which Frictionless Transform already provides or should I submit a feature request, describing a bit more detailed, what I'm looking for?

[hbruch]

Thanks @roll for starting the discussion here.

Regarding the dataset cited above, I'd like to do the following transformation:

Multiple Column pairs contain in one column the categoryID, in the second the verbose category label. I'd like to delete the verbose column after having used it to extract a foreign key referenced table resource.

Examples for such column tuples are:

• (BetriebsStatusId, BetriebsStatusName)
• (EnergietraegerId,EnergietraegerName)
• (Gemeindeschluessel, Gemeinde)
• (NetzbetreiberMaStRNummer,NetzbetreiberNamen)
• (HauptausrichtungSolarModule,HauptausrichtungSolarModuleBezeichnung)
• (SystemStatusId,SystemStatusName)
• (TechnologieStromerzeugungId,TechnologieStromerzeugung)
• (VollTeilEinspeisung,VollTeilEinspeisungBezeichnung)

Note, that the ID might be of type Integer (e.g. BetriebsStatusId), String (e.g. Gemeindeschluessel, though Frictionless normalizes it to integer) or Decimal (e.g. HauptausrichtungSolarModule, though it never should have decimal placesand hence probably be converted to Integer before)

Would be nice if there'd be a transform which

• takes column names for key and value column
• for all distinct keys stores their corresponding value
• creates a table resource with the foreign key/values
• returns a new target table without the value column
• adds schema information for the foreign key field as well as the new extracted table resource
• performs implicitly a validation that the same keys always map to the same values and throws an exception otherwise

[augusto-herrmann]

If the problem is in the resting size of data for storage or the size of downloads, a simple solution to remove such redundancies is simply to pack the CSVs, along with the datapackage metadata, as e.g. a ZIP file. We do that, for instance to this dataset, in which we do have a lot of redundancies as well. After packing to ZIP, it shrinks from 248 MB to just under 18 MB.

On the other hand, if you're worried about memory usage when opening the dataset, you can define a table schema with an enum type of constraint, listing the possible values there. Then, when you read it with something like Pandas, you can read the constraint and interpret the column as a categorical column, so that it will take less memory space.

I'm not familiar with the Frictionless Transform part of the Frictionless tool set yet. But what you are describing looks to me a bit like some form of data normalization, taking a single table and relaying some of the data to other tables using foreign keys.

[roll]

Hi @hbruch,

I didn't fully understand what is the target table(s) shape.

If e.g. we have:

id,someId,someName
1,35,somename
2,40,othername
3,45,othername
4,50,somename

What will be our target table(s)?