Writing data into a CSV file

  • Python
  • Thread starter Arman777
  • Start date
  • #1
2,020
173
I have a code like

Python:
from random import randint
from string import ascii_lowercase

file = open("data.txt", "w")

numbers = []
for j in range(26):
    # generating a list of random number
    random_num = [randint(0, 7) for i in range(120)]
    numbers.append(random_num)

# matching these numbers with the letters
survey = dict(zip(list(ascii_lowercase), numbers))
for (key, value) in survey.items():
    test_num = " ".join(str(i) for i in value)
    file.write(key + " " + test_num + "\n")

file.close()

and the output is

Code:
a 4 4 7 6 1 5 3 2 4 1 6 0 4 2 4 7 0 7 5 3 7 7 0 1 3 5 6 1 6 7 7 4 0 5 3 7 2 7 7 4 5 0 7 7 4 5 3 3 1 3 5 5 5 1 0 6 4 5 1 3 0 0 3 6 6 7 0 1 7 5 2 4 4 7 5 7 7 4 5 0 1 2 6 1 7 2 7 1 4 1 7 2 4 0 6 1 7 0 1 5 6 2 0 1 5 4 6 1 3 6 5 0 7 6 2 0 3 3 2 5
b 3 6 1 4 6 3 5 4 2 0 6 3 7 2 0 3 3 2 6 6 5 1 0 1 1 3 3 6 4 7 1 2 2 5 1 2 6 6 5 5 7 6 0 6 6 5 3 3 4 7 1 6 7 4 1 6 2 7 5 3 0 5 7 4 5 4 6 7 4 3 2 2 3 3 4 3 6 0 4 3 7 0 5 5 2 0 6 3 0 2 5 5 2 1 1 1 2 0 6 3 6 4 5 7 2 6 3 2 0 6 2 6 3 7 6 1 7 0 2 0
c 2 1 6 6 0 6 5 6 4 3 0 1 2 5 6 2 3 7 5 2 0 7 6 1 6 2 4 5 1 2 6 4 2 7 5 0 4 4 2 2 0 2 5 6 5 6 2 0 1 6 5 4 0 3 2 3 5 4 6 5 0 4 2 6 6 4 2 0 7 5 1 5 1 0 3 7 0 7 2 1 2 3 4 6 6 7 7 1 0 4 1 7 4 0 1 1 3 6 0 3 0 3 0 4 2 3 1 1 6 1 7 5 1 4 2 3 6 6 0 0
d 1 1 4 0 0 7 5 0 2 2 5 6 7 6 5 2 4 6 7 0 3 3 6 1 2 4 0 0 5 4 1 2 2 6 7 0 6 4 1 7 6 5 4 7 0 5 1 7 2 2 2 0 4 3 5 3 2 2 6 6 3 2 1 5 4 1 3 7 6 4 5 4 6 4 0 4 4 0 4 4 5 7 3 4 2 4 0 7 1 4 4 0 2 0 4 0 6 1 4 0 3 1 4 4 0 6 0 6 7 5 3 0 2 0 2 4 2 3 0 6
e 3 3 2 3 6 7 7 5 3 5 6 5 3 1 4 4 7 6 5 4 2 0 5 6 3 5 3 2 2 6 7 3 2 6 6 0 6 6 4 5 0 7 0 2 0 3 2 5 0 0 2 5 6 2 4 3 3 6 5 2 1 5 3 1 6 5 3 5 4 3 0 5 6 3 2 7 1 7 4 6 0 2 6 2 3 4 2 1 3 6 7 6 6 0 6 1 4 6 4 5 1 0 6 0 1 0 2 5 1 3 0 2 3 1 4 3 7 7 2 1
f 1 0 7 2 7 5 0 0 7 4 5 2 0 3 0 6 2 0 0 6 3 7 3 6 0 6 7 6 2 2 6 3 5 3 3 5 5 7 5 3 5 1 4 2 5 6 1 6 3 5 2 0 2 1 6 2 4 5 4 6 3 2 7 6 0 0 0 5 3 5 5 0 6 5 4 4 3 3 6 1 5 6 2 4 3 2 5 2 4 4 3 5 4 3 3 5 0 1 5 7 1 7 6 2 6 4 5 7 0 7 0 7 3 2 0 0 4 7 2 5
g 6 6 1 6 1 4 0 6 5 2 5 3 2 7 3 5 2 1 2 6 0 7 6 6 1 0 7 3 6 5 5 2 3 7 0 0 7 6 0 5 4 6 3 4 6 5 6 1 7 6 4 5 6 2 7 5 6 3 3 6 5 7 3 5 4 5 2 0 0 7 4 7 2 7 6 7 5 6 6 1 4 5 3 1 3 6 0 7 2 4 5 1 2 0 0 3 7 1 3 7 6 6 6 3 6 2 2 6 1 1 0 6 4 1 0 5 2 6 3 2
h 6 5 5 4 7 0 1 6 7 0 6 3 5 1 4 1 2 4 5 0 1 7 7 4 5 0 7 1 4 4 0 6 4 1 6 7 7 0 6 5 3 6 7 0 2 1 1 4 0 6 2 1 3 5 6 6 1 1 0 0 4 5 7 7 2 3 6 7 1 7 2 3 1 6 7 5 2 1 2 2 6 1 2 3 3 2 7 3 7 6 3 1 3 0 2 2 6 6 6 2 5 3 3 0 1 1 4 3 2 3 6 7 5 4 6 6 5 4 7 0
i 4 1 6 5 1 0 2 7 4 4 4 6 3 7 6 4 3 1 1 5 6 2 3 2 5 3 5 1 0 0 4 6 1 5 3 4 4 6 2 0 0 3 2 3 0 5 7 2 1 7 0 2 2 6 2 1 0 1 1 2 5 0 5 4 3 4 7 0 5 7 2 6 6 2 6 7 7 2 7 1 7 5 6 2 1 0 4 3 7 1 5 6 5 7 6 5 0 1 0 0 5 1 0 0 6 3 6 5 3 3 6 6 5 4 6 6 2 2 3 5
j 1 6 1 5 4 7 1 0 3 4 5 5 5 2 4 6 1 3 1 1 5 0 5 2 2 3 7 3 4 1 1 7 2 5 7 7 0 6 6 4 4 7 3 7 6 4 3 7 3 2 6 2 1 1 2 3 3 2 4 6 0 0 0 4 7 3 6 1 7 2 2 0 4 4 4 1 7 4 2 0 2 2 3 5 0 1 6 6 7 7 6 4 3 3 7 5 0 5 3 4 4 4 5 6 0 7 5 3 0 1 6 3 3 2 2 5 4 7 1 7
k 5 2 7 0 7 6 5 7 3 4 1 5 0 6 0 0 7 2 1 5 6 6 5 2 2 7 0 2 0 4 4 3 6 3 0 7 3 4 7 2 5 2 3 1 0 5 6 6 2 6 5 7 2 3 6 5 5 3 7 2 5 2 4 1 2 6 7 1 5 1 4 2 5 2 7 3 4 2 2 7 6 5 1 3 7 5 4 4 2 2 2 7 7 7 6 5 0 6 4 5 1 1 6 0 0 3 3 1 0 5 3 3 5 2 3 6 7 2 7 3
l 7 4 5 0 2 4 5 1 1 0 6 3 6 1 6 0 7 0 4 4 1 4 0 3 3 6 4 3 1 3 0 2 5 6 4 1 6 2 5 3 7 3 6 1 2 4 5 4 3 1 7 7 6 5 5 0 6 1 2 4 7 1 4 2 7 0 6 3 1 7 6 4 4 1 2 5 2 0 5 4 4 2 0 4 4 4 0 7 1 0 4 4 7 3 7 4 0 2 3 4 0 6 4 7 7 6 5 4 1 1 3 3 2 0 0 0 3 5 1 1
m 7 3 1 4 6 4 0 4 4 3 0 4 2 5 4 4 1 0 6 7 0 7 0 2 5 6 7 5 1 0 7 5 5 2 3 7 4 5 4 4 4 4 6 1 3 4 1 2 6 4 6 0 5 2 0 3 0 5 5 6 0 1 1 7 4 1 0 2 0 6 6 0 0 6 0 0 2 4 1 0 6 1 7 7 5 3 5 0 5 3 6 6 4 7 7 0 3 7 3 7 2 2 6 2 1 6 6 2 2 5 7 2 1 4 0 6 7 5 1 7
n 3 3 7 1 4 3 5 3 0 0 5 6 2 2 6 4 6 7 5 1 2 0 6 2 5 5 4 2 5 0 2 4 0 2 0 1 7 6 2 6 5 5 5 3 4 7 1 6 2 2 3 6 1 2 4 2 2 5 7 7 6 2 1 1 2 0 5 4 4 0 0 0 0 2 4 4 3 5 7 3 4 2 0 2 3 3 1 0 4 0 5 5 5 3 3 3 5 5 1 0 2 6 2 2 0 6 6 5 4 3 2 0 6 6 0 7 1 7 7 4
o 2 7 4 1 0 1 0 6 1 3 0 0 3 2 4 5 5 1 4 0 1 0 2 7 2 2 1 3 6 2 3 2 3 5 5 6 4 2 2 4 6 5 4 1 6 5 2 6 1 2 7 7 3 7 4 7 6 1 4 1 4 1 6 2 2 6 3 6 4 6 3 1 3 2 2 1 1 6 7 7 3 2 5 0 7 2 7 4 6 3 4 0 1 3 0 2 2 3 0 7 5 1 0 2 1 6 2 4 2 2 6 1 5 1 3 7 7 0 2 0
p 4 5 4 7 2 4 7 7 3 7 2 6 6 4 7 4 7 7 3 2 4 7 0 7 3 5 5 4 3 5 5 1 0 3 3 7 0 2 6 7 6 6 4 3 4 7 5 4 6 1 0 2 5 2 2 6 7 7 6 3 2 6 7 5 6 7 2 4 2 5 4 3 1 2 5 6 5 6 6 6 7 2 6 7 7 5 3 3 1 7 5 5 5 0 3 5 1 1 7 4 1 4 3 6 4 3 6 6 5 2 6 4 7 1 5 1 4 1 1 2
q 5 6 6 3 4 4 7 0 7 2 3 7 2 1 3 6 1 3 1 6 6 0 5 4 5 0 1 5 5 7 0 7 5 4 4 0 5 6 5 5 0 0 2 0 0 1 5 2 2 0 4 3 5 1 5 5 3 3 0 0 1 3 6 6 5 4 4 0 3 1 6 1 5 0 5 2 1 7 2 3 2 1 6 1 2 1 0 6 5 0 7 6 6 3 2 5 7 4 0 0 2 5 6 5 1 2 0 7 5 2 5 3 6 0 7 1 4 3 5 1
r 0 3 6 3 2 4 1 0 1 1 5 7 5 3 5 6 3 7 1 3 5 6 1 6 0 5 7 5 7 3 0 4 3 2 7 5 5 1 0 1 6 0 2 3 7 3 1 5 5 7 6 0 5 6 5 0 3 1 1 0 1 4 1 2 7 3 0 0 0 2 1 7 2 4 2 4 7 5 3 7 6 5 5 2 5 7 0 4 3 0 4 7 7 6 7 6 6 0 4 7 0 3 0 2 0 2 5 1 3 3 4 2 2 3 7 0 1 5 4 5
s 0 4 1 3 4 6 1 3 5 0 3 4 7 3 4 7 7 0 6 4 2 7 1 0 7 2 6 1 0 4 2 3 7 4 4 4 4 3 4 3 1 0 6 1 4 1 5 6 2 5 0 3 4 7 2 6 5 3 5 6 2 1 7 4 6 4 1 4 6 7 5 1 3 4 2 1 2 5 3 3 7 1 2 7 3 2 4 1 0 5 6 7 1 6 2 7 4 0 7 0 5 1 5 6 6 5 6 2 2 5 0 5 6 4 5 5 1 0 2 7
t 1 1 6 2 6 6 4 3 5 5 0 2 0 0 2 2 0 2 3 1 7 0 1 2 2 2 0 5 3 7 5 3 0 6 7 5 1 3 3 7 2 4 2 0 7 0 7 2 6 5 4 7 0 0 3 4 1 1 2 6 3 5 7 0 4 6 4 7 3 4 4 6 3 1 3 3 1 7 5 6 0 4 4 6 2 4 4 4 3 5 7 6 2 2 0 6 2 3 5 2 7 7 5 3 0 6 3 5 6 5 4 2 1 0 6 0 4 0 6 1
u 5 0 0 4 3 7 0 5 0 6 4 1 5 0 4 3 3 1 5 4 6 0 7 3 0 6 0 5 5 0 5 1 6 3 4 0 1 7 4 3 2 1 5 2 5 3 5 6 6 2 4 5 6 3 3 4 2 1 7 0 2 4 6 5 4 4 1 3 7 4 0 0 4 6 2 2 6 3 1 2 0 1 7 7 1 5 1 3 0 0 6 1 0 1 0 5 0 2 2 2 7 7 3 2 4 1 1 6 7 4 5 7 0 6 6 5 2 2 5 7
v 5 1 7 3 7 7 5 1 7 1 5 0 0 3 6 5 7 3 2 6 6 2 3 7 6 3 6 4 2 7 7 7 2 1 3 5 5 5 4 0 7 4 3 3 7 1 5 2 0 1 0 2 3 6 6 0 1 2 7 5 7 6 1 2 7 0 0 7 7 2 2 6 4 1 4 7 3 3 5 1 7 5 0 7 6 5 1 6 3 6 1 5 3 7 0 6 3 5 6 0 1 7 4 7 2 5 3 4 7 0 2 3 7 3 5 1 7 2 4 5
w 2 5 0 1 2 1 2 7 0 2 4 3 6 0 3 3 2 1 4 6 1 1 4 7 6 5 4 4 3 5 5 2 2 1 3 7 5 1 0 2 7 6 1 5 0 7 5 4 3 0 6 4 3 1 0 7 7 2 0 3 3 5 5 0 7 1 4 7 7 2 4 1 3 6 7 0 2 7 7 5 1 5 5 5 5 1 0 5 3 5 0 5 2 3 3 7 7 2 5 7 7 0 0 5 4 7 0 6 5 0 7 4 0 6 5 6 0 6 0 2
x 4 4 0 0 6 1 6 7 2 0 1 5 0 7 0 7 0 1 2 3 3 4 6 2 2 7 5 1 3 1 2 3 2 3 4 3 3 2 6 3 1 6 4 1 2 1 0 4 3 4 7 5 5 0 5 6 3 7 1 5 7 1 1 1 0 5 3 0 7 4 7 0 6 0 5 6 2 2 5 6 5 1 3 0 7 3 0 2 3 6 1 7 0 0 1 1 3 7 6 5 0 7 2 1 2 3 2 6 2 2 3 5 1 5 1 0 5 3 4 7
y 1 1 4 3 4 7 6 3 4 5 1 6 7 4 6 1 6 0 3 0 1 4 1 6 4 0 0 1 3 0 6 5 2 1 0 6 5 1 4 4 3 3 2 5 3 0 2 1 0 3 4 7 5 3 7 2 2 2 1 1 6 6 5 5 5 6 2 0 6 3 5 3 7 7 7 2 0 4 1 1 2 4 4 6 3 4 5 6 4 2 6 2 6 0 1 7 4 6 0 1 7 6 7 1 4 3 5 3 2 6 4 4 5 1 2 4 3 4 0 4
z 0 0 1 1 5 6 3 6 1 6 2 2 4 4 3 5 2 5 4 0 5 2 5 7 7 2 2 2 7 5 2 7 0 1 1 5 0 3 0 5 5 1 4 1 6 5 6 1 7 7 0 5 4 7 2 1 5 2 0 3 0 1 4 5 5 3 7 2 7 2 3 7 2 6 1 2 6 1 4 1 4 0 1 2 3 0 2 3 4 1 6 1 1 6 7 7 6 6 3 2 6 3 7 3 7 6 6 1 3 6 7 5 3 7 5 3 0 2 1 1

Now I need to write this into a csv (Excel file) rather than txt. The format of the excel must be something like this


a
2​
0​
.
b
2​
2​
.
c
3​
4​
.
d
1​
5​
.
e
6​
6​
.
f
4​
1​
.
...
...
...



How can I do this ?
 

Answers and Replies

  • #2
PeterDonis
Mentor
Insights Author
2020 Award
34,275
12,522
How can I do this ?

AFAIK you can tell Excel when loading a text file that column values are separated by spaces, so you might be able to load the text file you already have and have Excel treat it the way you want.

If you need to have a CSV file, well, that just means the column values are separated by commas, not spaces. Can you see where those separators (currently spaces) are defined in your source code? (Hint: there is one on line 15 and one on line 16.)
 
  • #3
2,020
173
AFAIK you can tell Excel when loading a text file that column values are separated by spaces, so you might be able to load the text file you already have and have Excel treat it the way you want.

If you need to have a CSV file, well, that just means the column values are separated by commas, not spaces. Can you see where those separators (currently spaces) are defined in your source code? (Hint: there is one on line 15 and one on line 16.)
If you mean this
Code:
from random import randint
from string import ascii_lowercase
import csv

file = open("data.csv", "w")

numbers = []
for j in range(26):
    # generating a list of random number
    random_num = [randint(0, 7) for i in range(120)]
    numbers.append(random_num)

# matching these numbers with the letters
survey = dict(zip(list(ascii_lowercase), numbers))
for (key, value) in survey.items():
    test_num = ",".join(str(i) for i in value)
    file.write(key + "," + test_num + "\n")

file.close()

I get this

1612646492486.png
 
  • #4
FactChecker
Science Advisor
Gold Member
6,272
2,437
It looks like you have generated a valid CSV file but have loaded it into EXCEL wrong. Did you tell EXCEL to use comma separation when the file is opened into EXCEL?
 
  • #5
PeterDonis
Mentor
Insights Author
2020 Award
34,275
12,522
I get this

Open the file in a text editor; it should be values separated by commas on each line. That's a CSV file.

Excel might not be loading it properly. The line ending character you are using is for Unix/Linux, so if you're on Windows or MAC Excel might not be properly interpreting it. Also there might be character encoding issues; as your code is written it will save the file with the default encoding of whatever Python you are running, probably UTF-8; but Excel might not default to UTF-8 when loading. Finally, Excel's definition of "CSV" might not match the standard one; you might have to explicitly tell it that the field separators on each line are commas and not something else. (I would lean towards the last of those since it looks like the lines are being separated properly and the characters are being read properly.)
 
  • #6
2,020
173
It looks like you have generated a valid CSV file but have loaded it into EXCEL wrong. Did you tell EXCEL to use comma separation when the file is opened into EXCEL?
how can ı do that ?
 
  • #7
Ibix
Science Advisor
Insights Author
2020 Award
7,869
7,136
With the file open, if you select all of column A, go to the Data tab, select "Text to columns", then "delimited", then make sure the "comma" is ticked, then click OK.
 
  • #9
2,020
173
With the file open, if you select all of column A, go to the Data tab, select "Text to columns", then "delimited", then make sure the "comma" is ticked, then click OK.

I see the point. This works. Okay it works .. I just need to save it as xlsx file. I guess there is not direct way to output like that by coding.
 
Last edited:
  • #10
Ibix
Science Advisor
Insights Author
2020 Award
7,869
7,136
I see the point. This works. But it seems some data is missing ?
We can't read your mind. What do you mean "some data is missing"? Are the lines shorter than expected? Are some lines missing? Something else?
 
  • Like
Likes Dale and Vanadium 50
  • #12
2,020
173
We can't read your mind. What do you mean "some data is missing"? Are the lines shorter than expected? Are some lines missing? Something else?
Its not missing sorry my mistake. Excel gave an error but it is okay now.
 
  • #13
Ibix
Science Advisor
Insights Author
2020 Award
7,869
7,136
Actually, you do need to get Excel to load it properly, rather than do the post-hoc fix I proposed. I think Excel won't store more than a certain number of characters in a cell, so you will get data loss if your lines are too long.

Try changing your program to create data.csv instead of data.txt. Excel ought to recognise the extension.
 
  • #14
2,020
173
Actually, you do need to get Excel to load it properly, rather than do the post-hoc fix I proposed. I think Excel won't store more than a certain number of characters in a cell, so you will get data loss if your lines are too long.

Try changing your program to create data.csv instead of data.txt. Excel ought to recognise the extension.
I am already creating data.csv see post 3
 
  • #15
Ibix
Science Advisor
Insights Author
2020 Award
7,869
7,136
I am already creating data.csv see post 3
Sorry - I was looking at the version in post #1.

How are you opening it? I don't have a Windows box to hand to play with, but my recollection is that Excel does different things if you open the file with drag-and-drop, double click, and going through the File->Open menu. At least one of them ought to pop up the same dialog as the Text-to-columns menu option.
 
  • #17
2,020
173
hmm thats the problem I guess. So I am using vs code and it looks like this

1612649497304.png


as you can see it says data.cvs

And when I look it from the folder view, I see

1612649562070.png


I am just clicking data.csv to open the file. Thats how I get

1612649600569.png
 
  • #18
Ibix
Science Advisor
Insights Author
2020 Award
7,869
7,136
Try the other ways, then.

Incidentally, your full name is in the title bar of your screenshots - I don't know if that bothers you.
 
  • #19
pasmith
Homework Helper
2,016
649
Why reinvent the wheel? There is a python library specifically for writing CSV files. Alternatively, if you turn your data a pandas DataFrame then those can also be written as CSV with a single function call,


There is also a package which will allow you to create xlsx files directly from python, if that is actually what you want. Pandas can also create an a xlsx file directly with one function call.
 
  • Like
Likes Locrian, Nugatory, lomidrevo and 1 other person
  • #20
8
4
Why reinvent the wheel? There is a python library specifically for writing CSV files. Alternatively, if you turn your data a pandas DataFrame then those can also be written as CSV with a single function call,


There is also a package which will allow you to create xlsx files directly from python, if that is actually what you want. Pandas can also create an a xlsx file directly with one function call.
I love pandas for that reason. You can write the code that arrange the data the way you like it once which saves lots of time on larger projects

Edit: there is also a numpy random function which creates an array of random numbers for you
 
  • #21
2,020
173
Python:
from random import randint
from string import ascii_lowercase

file = open("data.csv", "w")

numbers = []
for j in range(26):
    # generating a list of random number
    random_num = [randint(0, 7) for i in range(120)]
    numbers.append(random_num)

# matching these numbers with the letters
survey = dict(zip(list(ascii_lowercase), numbers))
for (key, value) in survey.items():
    test_num = ";".join(str(i) for i in value)
    file.write(key + ";" + test_num + "\n")

file.close()

Using ";" instead of "," works.
 
  • #22
8
4
yes, your code looks like it does the job and it seems like you know that the delimiter separates things left to right, and a new line starts a new row. It's a good practice to keep the delimiter as "," though

It might be worthwhile looking into pandas, because it does everything excel does and more.
 
  • #23
FactChecker
Science Advisor
Gold Member
6,272
2,437
There are many ways to skin a cat. VS and EXCEL can do this job. There is no reason to be changing programming languages and spreadsheet software, especially if one is working with others who may be much more familiar with EXCEL.
 
  • #24
8
4
There are many ways to skin a cat. VS and EXCEL can do this job. There is no reason to be changing programming languages and spreadsheet software, especially if one is working with others who may be much more familiar with EXCEL.
That's a good point, for this job VS and Excel is probably faster.
It looks like OP knows some python already, and later down the road, pandas would give more options.

Edit: Personally, I use both pandas and excel. Usually I first clean up a file in excel, then I move it to pandas to do more of the filtering and computation, then I put it back into a csv format
 
Last edited:
  • Like
Likes FactChecker
  • #25
434
247
Using ";" instead of "," works.
It's a good practice to keep the delimiter as "," though

The behavior of Excel depends on the regional settings in Windows. In some countries, comma "," is used as decimal separator instead of point "." character. In such case, comma "," cannot be used to separate values and Excel uses semicolon ";" to separate the values instead.

@Arman777, that would explain why the standard CSV is not automatically loaded into table format when you open it. Instead of overwriting the regional settings you can explicitly tell Excel what character should be used as separator as already mentioned in previous posts. Or you can keep using semicolon if it works well with your settings, but you should keep in mind that it might cause troubles to other users with different settings, in case you plan to distribute the file.
 
  • Like
Likes cgrie, FactChecker, Arman777 and 1 other person

Related Threads on Writing data into a CSV file

Replies
1
Views
191
  • Last Post
Replies
9
Views
4K
  • Last Post
Replies
2
Views
2K
Replies
3
Views
2K
Replies
9
Views
16K
Replies
6
Views
9K
Replies
2
Views
1K
  • Last Post
Replies
2
Views
3K
Replies
2
Views
20K
Replies
1
Views
5K
Top