Transform 2D numpy array to row-column-value pandas DataFrame

Question

Suppose I have a 2D numpy array like this:

arr = np.array([[1, 2], [3, 4], [5, 6]])
# array([[1, 2],
#        [3, 4],
#        [5, 6]])

How can one transform that to a "long" structure with one record per value, associated with the row and column index? In this case that would look like:

df = pd.DataFrame({'row': [0, 0, 1, 1, 2, 2],
                  'column': [0, 1, 0, 1, 0, 1],
                  'value': [1, 2, 3, 4, 5, 6]})

melt only assigns the column identifier, not the row:

pd.DataFrame(arr).melt()
#   variable    value
# 0        0        1
# 1        0        3
# 2        0        5
# 3        1        2
# 4        1        4
# 5        1        6

Is there a way to attach the row identifier?

For efficient solutions using NumPy - stackoverflow.com/questions/46135070/… — Divakar
– Divakar, Commented Dec 1, 2018 at 7:36

Max Ghenis · Accepted Answer · 2018-12-02 22:48:10Z

2

Pass index to idvar:

pd.DataFrame(arr).reset_index().melt('index')
#    index variable  value
# 0      0        0      1
# 1      1        0      3
# 2      2        0      5
# 3      0        1      2
# 4      1        1      4
# 5      2        1      6

You can rename:

df = pd.DataFrame(arr).reset_index().melt('index')
df.columns = ['row', 'column', 'value']

edited Dec 2, 2018 at 22:48

Max Ghenis

16k17 gold badges93 silver badges142 bronze badges

answered Dec 1, 2018 at 6:46

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Comments

Max Ghenis · Accepted Answer · 2018-12-01 06:46:23Z

1

melt can use the index if it's a column:

arrdf = pd.DataFrame(arr)
arrdf['row'] = arrdf.index
arrdf.melt(id_vars='row', var_name='column')

#    row    column  value
# 0    0         0      1
# 1    1         0      3
# 2    2         0      5
# 3    0         1      2
# 4    1         1      4
# 5    2         1      6

answered Dec 1, 2018 at 6:46

Max Ghenis

16k17 gold badges93 silver badges142 bronze badges

Collectives™ on Stack Overflow

Transform 2D numpy array to row-column-value pandas DataFrame

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related