Create dataframe with values from other dataframe's indices and columns

Question

I have a large dataframe df1 that looks like:

    0     1     2

0   NaN   1     5
1   0.5   NaN   1
2   1.25  3     NaN

And I want to create another dataframe df2 with three columns where the values for the first two columns correspond to the df1 columns and indices, and the third column is the cell value.

So df2 would look like:

   src   dst    cost 

0   0     1     0.5
1   0     2     1.25
2   1     0     5
3   1     2     3

How can I do this?

Thanks

Michael Delgado · Accepted Answer · 2021-08-11 22:23:30Z

1

I'm sure there's probably a clever way to do this with pd.pivot or pd.melt but this works:

df2 = (
    # reorganize the data to be row-wise with a multi-index
    df1.stack()
    # drop missing values
    .dropna()
    # name the axes
    .rename_axis(['src', 'dst'])
    # name the values
    .to_frame('cost')
    # return src and dst to columns
    .reset_index(drop=False)
)

answered Aug 11, 2021 at 22:23

Michael Delgado

15.7k4 gold badges39 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Create dataframe with values from other dataframe's indices and columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related