Split rows and write into columns

Question

I have output from a dataframe like

0     0.010168   7191516  branch-instructions
1     0.010168  33047525         instructions
2     0.010168    290566     cache-references
3     0.010168    104974         cache-misses
4     0.010168  25109349           cpu-cycles
...        ...       ...                  ...
1913  3.376448   2524814         instructions
1914  3.376448     32524     cache-references
1915  3.376448     18957         cache-misses
1916  3.376448   2144874           cpu-cycles
1917  3.376448      3349        branch-misses

I need to write it into csv with the third column as header and the values corresponding to it like

time       branch-instructions  instructions   cache-references   cache-misses  cpu-cycles  branch-misses
0.010168   7191516              33047525       290566             104974        25109349    2458

How can I do this? Any help is appreciated

yes. This is the result of `data = pd.read_table('one1.txt', sep='\s+', header=None, thousands=',', comment='#')` — saywhat119, Mar 11 '22 at 20:30
You probably want to look at the [`pivot`](https://pandas.pydata.org/docs/reference/api/pandas.pivot.html#pandas-pivot) method then. — RoadieRich, Mar 11 '22 at 20:32
I tried this and it works!! but the time is not shown in the csv file. I can see it if I print the dataframe — saywhat119, Mar 11 '22 at 20:54
Either use the `index=True` option of `pd.to_csv` or do `df = df.reset_index(drop=False)` before writing to csv. — Timus, Mar 11 '22 at 20:59

score 0 · Answer 1 · answered Mar 11 '22 at 20:24

You need to loop on the second column, write the time,and then write only value until you reach a different time. You might want to control at the same time that the text in fourth column is correct.

So steps are:

Loop around the lines
In each line check time with the first time
Write the value in the new csv
If time is different, change your first time and create a new line in the new csv
Iterate again

Something along the lines of:

#Prepare reepting data
toReceive = ["time", "branch-instructions", "instructions"]#....
#Split by line your file
lines = data_imported.split("\n")
sameTime = True
i=0
refTime=lines[i].split(" ")[1]
#Loop with time as criteria
while(i<len(lines)):
  sameTIme=True
  localReceive=refTime
  while(sameTime):
     #Split by elements
     elements = lines[i].split(" ")
     sameTime=elements[1]==refTime
     localReceive.append(elements[2])
  refTime=elements[1]
  toReceive.append(localReceive)

Split rows and write into columns

1 Answers1