Updating a Two Dimensional Dataframe Python with a Multiindex Dataframe

Question

I'm trying to create a heatmap with some data. For this I've created an empty bidimensional dataframe

dft = pd.DataFrame(0, index=matrix_values_index , columns= matrix_values_columns)

Which gives me a 58x58 matrix.

On the other hand, there are some data within an excel spreadsheet which I've transformed into a multiindex dataframe

df2 = heatmap_values.groupby(['ID Requester','ID Supplier']).agg({'ID Supplier':'count'})

df2.rename(columns={'ID Supplier': 'No. Interdependencies'})

The first index is the index of the bidimensional dataframe

The second index is the column of the bidimensional dataframe

Is there a way to update the bidimensional dataframe with the values of the multiindex dataframe without looping over it?

I've been looking for a solution for it all over the internet, but without success so far.

Do you have any suggestions for this problem?

Thanks in advance.

jezrael · Accepted Answer · 2021-05-12 05:31:47Z

1

Use crosstab with DataFrame.reindex if some index or columns names missing instead your solution:

dft = (pd.crosstab(heatmap_values['ID Requester'],heatmap_values['ID Supplier'])
         .reindex(index=matrix_values_index, columns= matrix_values_columns, fill_value=0))

Your solution should be changed for same ouput with Series.unstack and also reindex:

df2 = heatmap_values.groupby(['ID Requester','ID Supplier']).agg({'ID Supplier':'count'})

df2 = df2.rename(columns={'ID Supplier': 'No. Interdependencies'})

dft = (df2['No. Interdependencies'].unstack(fill_value=0)
                                   .reindex(index=matrix_values_index,
                                            columns= matrix_values_columns, 
                                            fill_value=0))

edited May 12, 2021 at 5:31

answered May 11, 2021 at 12:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

4 Comments

Nikolas Over a year ago

I'm trying your approach but not it's not working. I've replaced the (df['ID Requester'],df['ID Supplier']) by (df2.index.names[0],df2.index.names[1]) since it is a Multiindex dataframe, but without success so far

jezrael Over a year ago

@Nikolas - Added your solution to my answer.

Nikolas Over a year ago

Hello @jezrael I've used some of your tips and finally succeded: dfn = df2.unstack() dfn_reduced = dfn.xs('ID IPM Supplier', axis=1, drop_level=True).fillna(0) dfn_full = dfn_reduced.reindex(index=matrix_values_index , columns= matrix_values_columns, fill_value=0) –

jezrael Over a year ago

@Nikolas - So data are different like

df2 = heatmap_values.groupby(['ID Requester','ID Supplier']).agg({'ID Supplier':'count'})  df2 = df2.rename(columns={'ID Supplier': 'No. Interdependencies'})

, so not possible use df2['No. Interdependencies'].unstack(fill_value=0) instead dfn = df2.unstack() dfn_reduced = dfn.xs('ID IPM Supplier', axis=1, drop_level=True).fillna(0) ?

Collectives™ on Stack Overflow

Updating a Two Dimensional Dataframe Python with a Multiindex Dataframe

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related