Convert notepad data into excel

Question

I have some data in my notepad which looks like this :

node1

7

p 2

r 5

node2

6

p 2

r 4

I want to convert these data to excel file using python so that a excel is created with rows as node1 and node2 and columns will be : total , primary and replicas ie p means primary , r means replicas and digit goes to column called "total . So basically in excel it should like below :

Answer 1

The easiest way to transfer data to Excel is using CSV file.

The main idea is to read four lines sequentially then make one row from them and write the row into the CSV file "data.csv".

Your data will be look like this

node1;7;2;5
node2;6;2;4

Next you should open "data.csv" in Excel.

Some explanations:

1. I asume there are no blank lines in your text file.
2. In first you should clean a text from new line symbol. Its make by rstrip() method.
3. The data with 'p' and 'r' are needed to clean from 'p' and 'r'. It make by .rsplit()[-1] as splitting by space and getting the last element of list.

import csv

with open("data.txt", "rt") as data, open("data.csv", "w", newline='') as csvfile:
    for line in data:
        row = [line.rstrip()]  # node name
        row.append(data.readline().rstrip())  # total
        row.append(data.readline().rstrip().rsplit()[-1])  # primary
        row.append(data.readline().rstrip().rsplit()[-1])  # replicas
        csv_data = csv.writer(csvfile, delimiter=";", quoting=csv.QUOTE_MINIMAL)
        csv_data.writerow(row)
    else:
        print("Done!")

It's possible to write the data into XLSX file but it's a few harder.