-1

I need to format the layout of a CSV or JSON-file.

So I'm working on a project, where the user is able to do messurements. The results are written into a mysql database. There is a button so you can convert the database into csv-format and download it.

The table looks like this

You can see three columns named (register, pressure and timestamp). What I want to do is to format the layout so if the user downloads the data he gets another representation of the table. The value of the timestamp shouldnt be multiple times in the table.

It's hard to explain so I created a table of how I want it to look like right here

Justin
  • 3
  • 5
  • so you want the timestamps to be rows and the measurements to be the data in the cells right ? – EliteGamerSiddhu Sep 20 '22 at 09:20
  • @EliteGamerSiddhu Exactly! I dont want the registers and timestamps to be multiple times in the table. So the "only actual" data should be the messurement. – Justin Sep 20 '22 at 09:24
  • This is "pivot table". MySQL does not implement this. You need in stored procedure which uses dynamic SQL. – Akina Sep 20 '22 at 09:31
  • ok, so just to be sure, if you are storing it in a json file the main dictionary should have the timestamp as the keys, the register number as the keys in the nested dict and the values of the register to be the data ? – EliteGamerSiddhu Sep 20 '22 at 09:33
  • @EliteGamerSiddhu Yes, you got it – Justin Sep 20 '22 at 09:36
  • @Akina Seems like it is! Never heared about privot tables but good to know. I think it should be possible to format it from a csv or json file. It has nothing to do with mysql right then. – Justin Sep 20 '22 at 09:41
  • I don't think you can directly do that, but i can help you with a python code to do that – EliteGamerSiddhu Sep 20 '22 at 10:07
  • @EliteGamerSiddhu This would be awesome! I've never worked with python but would love to learn it! – Justin Sep 20 '22 at 10:45
  • Please provide enough code so others can better understand or reproduce the problem. – Community Sep 20 '22 at 13:01

1 Answers1

1

You should add real data, not only images.

Here an example, similar to your input structure:

Register Druck Zeitstempel
1 3 a
2 5 a
3 45 a
1 9 b
2 12 b

The raw data

Register,Druck,Zeitstempel
1,3,a
2,5,a
3,45,a
1,9,b
2,12,b

Using Miller and running

mlr --csv reshape -s Zeitstempel,Druck then unsparsify input.csv

You will have in output

Register a b
1 3 9
2 5 12
3 45
aborruso
  • 4,938
  • 3
  • 23
  • 40