Excel to pandas to numpy array conversion

Question

I could import excel data into python 3.6 using pandas.

Code I used for this purpose is as follows:

import pandas as pd

import numpy as np

df=pd.read_excel("I:/Python/Excel.xlsx")

df.head()

This above code shows me the Table1 on Jupyter notebook.

I am not getting how to convert two columns from the Table1 (obtained from above code) which I can see in Jupyter notebook to numpy arrays inorder to carry out further analysis on them.

There are in all 17 columns in this table. I need 'Column 15' and 'Column 16' from pandas frame work into numpy array so that final structure is as follows:

data_set=[[x1,y1],[x2,y2],...[x1000,y1000]]

# x1 = row1 from Column 15
# y1 = row1 from Column 16

I will be finally using this data to plot 'xy scatter plot' into matplotlib. This part I know how to do. Learned a lot from this forum. Thanks to all!

I looked online for this solution, but unable to find a solution for it.

Thanks for reading.

Answer 1

I think need select columns by positions by iloc, convert to numpy array and if neccesary to list:

np.random.seed(1245)

df = pd.DataFrame(np.random.randint(10, size=(3, 17))).add_prefix('data')
print (df)
   data0  data1  data2  data3  data4  data5  data6  data7  data8  data9  \
0      6      5      3      5      0      6      9      3      4      6   
1      7      1      2      9      3      9      8      8      7      1   
2      4      1      8      2      5      6      6      5      1      5   

   data10  data11  data12  data13  data14  data15  data16  
0       9       4       9       5       2       7       9  
1       7       5       5       2       2       4       0  
2       4       6       6       0       2       7       6  

data_set = df.iloc[:, [14, 15]].values.tolist()
#alternative
#data_set = df.values[:, [14, 15]].tolist()
print(data_set)
[[2, 7], [2, 4], [2, 7]]

EDIT:

#if want select columns by names
x = df['data01']
y = df['data02']

#if want select columns by positions
#x = df.iloc[:, 1]
#y = df.iloc[:, 2]

s = plt.scatter(x, y)