numpy array to pandas dataframe conversion - ValueError

Question

I have the following numpy array called 'data' -

array([['ksr-usconeng101', 'C', '632.3', '1'],
       ['ksr-usconeng101', 'D', '242.9', '2'],
       ['ksr-usconeng158', 'C', '1044.5', '3'],
       ['ksr-usconeng158', 'D', '2771.2', '4'],
       ['ksr-usconeng158', 'G', '7.3', '5'],
       ['ksr-usconeng163', 'C', '1597.0', '6'],
       ['ksr-usconeng163', 'D', '1676.3', '7'],
       ['server', 'drive', 'size', '']],
      dtype='<U15')

I'm trying to convert it to a dataframe -

pd.DataFrame(data=data[0:-1,0:3],
                   index = data[0:-1,-1],
                   columns = data[-1:, 0:-1])

Data -

data[0:-1,0:3]
Out[145]: 
array([['ksr-usconeng101', 'C', '632.3'],
       ['ksr-usconeng101', 'D', '242.9'],
       ['ksr-usconeng158', 'C', '1044.5'],
       ['ksr-usconeng158', 'D', '2771.2'],
       ['ksr-usconeng158', 'G', '7.3'],
       ['ksr-usconeng163', 'C', '1597.0'],
       ['ksr-usconeng163', 'D', '1676.3']],
      dtype='<U15')

Index -

data[0:-1,-1]
Out[146]: 
array(['1', '2', '3', '4', '5', '6', '7'],
      dtype='<U15')

Columns -

data[-1:, 0:-1]
Out[147]: 
array([['server', 'drive', 'size']],
      dtype='<U15')

However, python doesn't agree and responds with -

ValueError: Shape of passed values is (3, 7), indices imply (1, 7)

Please suggest what am I missing ..

Mike Müller · Accepted Answer · 2018-02-22 18:40:57Z

1

The columns need to be 1D:

df = pd.DataFrame(data=data[:-1,:3],
                  index=data[:-1,-1],
                  columns=data[-1, :-1])
print(df)

Output:

         server drive    size
1  ksr-usconeng101     C   632.3
2  ksr-usconeng101     D   242.9
3  ksr-usconeng158     C  1044.5
4  ksr-usconeng158     D  2771.2
5  ksr-usconeng158     G     7.3
6  ksr-usconeng163     C  1597.0
7  ksr-usconeng163     D  1676.3

You have:

>>> data[-1:, 0:-1].shape
(1, 3)

But need:

>>> data[-1, :-1].shape
(3,)

answered Feb 22, 2018 at 18:40

Mike Müller

86k21 gold badges174 silver badges165 bronze badges

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

Comments

MichaelRSF · Accepted Answer · 2018-02-22 18:43:44Z

0

import  numpy as np, pandas as pd

df = pd.DataFrame(data[0:7, 0:3].flatten().reshape(7,3),
       columns = ["a", "b", "c"])

            a           b     c
0   ksr-usconeng101     C   632.3
1   ksr-usconeng101     D   242.9
2   ksr-usconeng158     C   1044.5
3   ksr-usconeng158     D   2771.2
4   ksr-usconeng158     G   7.3
5   ksr-usconeng163     C   1597.0
6   ksr-usconeng163     D   1676.3

answered Feb 22, 2018 at 18:43

MichaelRSF

9066 gold badges22 silver badges42 bronze badges

Comments

Rayadurai · Accepted Answer · 2018-02-22 18:47:58Z

0

Try this

pd.DataFrame(data=data[0:-1,0:3],
                   index = data[0:-1,-1],
                   columns = data[-1:, 0:-1].tolist())

edited Feb 22, 2018 at 18:47

answered Feb 22, 2018 at 18:39

Rayadurai

666 bronze badges

Collectives™ on Stack Overflow

numpy array to pandas dataframe conversion - ValueError

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related