Creating more than one row in a CSV file/Chess database creation

Question

I am trying to create a database to train a basic machine learning algorithm off of. However, when I run the code, it only creates two rows, but I am trying to create multiple rows, of each individual position in the game, accompanied by a stockfish analysis at the end for the position. The code either seems to be making three moves, then writing the file or overwriting the file for each move. I cannot tell which one it is. To elaborate further, the example below is the output of the code I wrote, where number = 3:

However, I am looking for something like this:

0	1	2	3	4	5	6	7	8
P	P						p	p
P			P				p	p
P			P		p			p

Here is my code:

import chess
import chess.engine
import random
import numpy
import csv
from stockfish import Stockfish

header = [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63]
data = []



def create_file(number):
  board = chess.Board()
  moves = 0
  turns = 0
  black_v = 0
  white_v = 0
  while moves < number:
    if chess.Board.is_game_over(board) == True:
      chess.Board.reset()
    if turns > 200:
      chess.Board.reset()
    for position in header:
      data.append(board.piece_at(position))

    with open('/content/Positions Data/position.csv', 'w', encoding='UTF8', newline='') as f:
        writer = csv.writer(f)
        header.append('sf')
        data.append((stockfish(board, 10))/100)
        writer.writerow(header)
        writer.writerow(data)
        data.clear()
        header.pop()
        turns = turns + 1
        moves = moves + 1
        random_move = random.choice(list(board.legal_moves))
        board.push(random_move)

Answer 1

As pointed out by Camaendir in the comment, you're opening your output file on every iteration of the move. This will open the file "for creating" which overwrites any previous data.

Also, from looking at your code and what you're trying to do, I see there are two ways to accomplish what you want, and you're trying both at the same time and that's creating other issues.

Either open the CSV file for writing, and loop over your moves writing as you process the board/moves with writer.writerow(stockfish(board, 10))/100):

1. open file for writing
2. create csv.Writer from file
3. write your header
4. loop over your moves
   1. process a move/board
   2. write move/board
5. close file

Or, process all the moves/board appending to data, then open your CSV file and write data writer.writerows(data):

1. loop over your moves
   1. process a move/board
   2. append that to data
2. open file for writing
3. create csv.Writer from file
4. write your header
5. write all rows with data
6. close the file