CODEWITHSUNDEEP
pythonpandas
Emma
Emma

Reputation: 119

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

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

Upvotes: 0

Views: 34

Answers (1)

Michael Delgado
Michael Delgado

Reputation: 15442

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)
)

Upvotes: 1

Related Questions