How can I export my NUMPY array into a CSV or EXCEL file

Question

This is my first time trying to do coding in Python, so I learned how to make a numpy array, and how I can export it as CSV file using np.savetxt. But, when I open the CSV file in excel, the columns of my matrix seems to be merged in one and it is impossible to do analysis on it. I was wondering how I can fix this issue. I don't know whether numpy is a proper choice for doing this analysis or not. So, if you have any other suggestions, please include.

Here, I have created an empty array with a1, b1 dimensions.

# Create an empty array with dim = (a1: num of months, b1:num of stations)

aa = np.empty((a1, b1))
aa[:] = np.nan

Here, I have filled the empty array row by row with a for loop:

for i in range(1, a1):

    S_Obs = Sta_M.iloc [i-1, 2]
    R_Val = Rad_M.iloc [i, 2:]


    addadjuster = adjust.AdjustAdd(coords, coords, nnear_raws = 5)
    addadjusted = addadjuster(S_Obs, R_Val)

    aa[i,:] = addadjusted

Finally, when I display my array row by row, it looks like this:

aa[111, :]

array([   nan,    nan,    nan, 16.296, 24.888,    nan,    nan,    nan,
          nan,    nan,    nan,    nan,    nan,    nan, 23.496,  1.704,
       52.32 ,    nan, 25.368,    nan,    nan,    nan,    nan,    nan,
          nan,    nan,    nan, 21.264, 19.584, 22.272,  0.144, 10.008,
        1.68 ,  0.   ,    nan,    nan,    nan,    nan,    nan,  0.   ,
        0.   , 30.696,    nan,    nan, 24.888,    nan,    nan,  3.648,
       14.832,  7.944,    nan,    nan,    nan,    nan,    nan,    nan,
          nan])

I want to save this array in a way that I can do some simple analysis on it. It can be in EXCEL or CSV. I used this code, but it doesn't show the columns properly.

np.savetxt("AAtest.csv", aa, delimiter="/")

We don't know what you think is a proper column, or what you don't like about the ones you get. What else from the savetxt documentation have your tried? That is the standard numpy writer for this task. — hpaulj
– hpaulj, Commented Oct 19, 2018 at 6:59

Amir · Accepted Answer · 2020-08-06 22:55:11Z

12

You can use Pandas to save your NumPy array as CSV (See here)

Suppose numArr is your numpy array. And you can do like this:

import pandas as pd

df = pd.DataFrame(numArr)
df.to_csv('file.csv',index=False)

edited Aug 6, 2020 at 22:55

Amir

11.1k10 gold badges52 silver badges76 bronze badges

answered Oct 19, 2018 at 6:36

Xiuchao Wu

3691 gold badge4 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kaveh P. Yousefi Over a year ago

Thank you very much. This worked, but with the Index = True.

Xiuchao Wu Over a year ago

Yeah. If you need index, then you can set it True. So I hope you can accept my answer!.

hpaulj · Accepted Answer · 2018-10-19 06:51:24Z

In [155]: arr = np.zeros((4,5))
In [156]: arr[:] = np.nan
In [158]: arr[[0,0,1,2,2,3],[0,2,1,3,4,3]]=1.23
In [159]: arr
Out[159]: 
array([[1.23,  nan, 1.23,  nan,  nan],
       [ nan, 1.23,  nan,  nan,  nan],
       [ nan,  nan,  nan, 1.23, 1.23],
       [ nan,  nan,  nan, 1.23,  nan]])
In [160]: np.savetxt('test.csv',arr, delimiter=',')

This is a properly formatted comma separated file. But the numbers are saved with scientific notation.

In [161]: cat test.csv
1.229999999999999982e+00,nan,1.229999999999999982e+00,nan,nan
nan,1.229999999999999982e+00,nan,nan,nan
nan,nan,nan,1.229999999999999982e+00,1.229999999999999982e+00
nan,nan,nan,1.229999999999999982e+00,nan

To line up the columns we need to specify a format. For example:

In [162]: np.savetxt('test.csv',arr, delimiter=',', fmt='%10f')
In [163]: cat test.csv
  1.230000,       nan,  1.230000,       nan,       nan
       nan,  1.230000,       nan,       nan,       nan
       nan,       nan,       nan,  1.230000,  1.230000
       nan,       nan,       nan,  1.230000,       nan

gehbiszumeis · Accepted Answer · 2018-10-19 06:41:13Z

1

If you cannot use pandas for some reason, stick with numpy by

aa.tofile('my_csv.csv', sep=',', format='%s')

answered Oct 19, 2018 at 6:41

gehbiszumeis

3,7495 gold badges28 silver badges45 bronze badges

3 Comments

hpaulj Over a year ago

np.savetxt is the normal numpy tool for creating csv files.

gehbiszumeis Over a year ago

I know np.savetxt, but the questioner tried this already and wasn't happy with the resulting formatting (as its written in the question)

hpaulj Over a year ago

But you suggestion produces a file like: 1.23,nan,1.23,nan,nan,nan,1.23,nan,nan,nan,nan,nan,nan,1.23,1.23,nan,nan,nan,1.23,nan. Everything is on one line.

Collectives™ on Stack Overflow

How can I export my NUMPY array into a CSV or EXCEL file

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related