1

I have a CSV file organized like this:

id, time, val
1, 0.1, a1, 0.2, a2, 0.3, a3, ...
2, 0.1, b1, 0.2, b2, 0.3, b3, ...

i.e. each row has one id followed by many time, val pairs (I'm not sure if this is even considered a valid CSV). Is there an easy way to split these rows into rows indexed by id and time? I.e.

id, time, val
1, 0.1, a1
1, 0.2, a2
1, 0.3, a3
...
2, 0.1, b1
2, 0.2, b2
2, 0.3, b3

Is there any standard terminology for the way this data is organized? I've struggled to find anything like it through simple google searches. I can certainly make a script from scratch which iterates through the file line-by-line and outputs what I need, but I'm worried that I will need to do this on a file that's large enough to complicate things. I'll be using Python to work with the data but I'm open to other technologies.

Any tips would be greatly appreciated!

Archie Gertsman
  • 1,601
  • 2
  • 17
  • 45

1 Answers1

0

Try melt:

import pandas as pd

df = pd.DataFrame({'id': {0: '1', 1: '2'},
                  'time_01': {0: '0.1', 1: '0.1'},
                  'val_01': {0: 'a1', 1: 'b1'},
                  'time_02': {0: '0.2', 1: '0.2'},
                  'val_02': {0: 'a2', 1: 'b2'}})
pd.melt(df, id='id', value_vars=['time_01', 'val_01', 'time_02', 'val_02'])

I'm sure there's a more efficient way to select columns for value_vars, but this should get you started after you checked the documentation.

David Collins
  • 848
  • 3
  • 10
  • 28