How to make a rectangular matrix square on pandas dataframe

Question

I have a matrix on the following form (not necessarily square):

   A    B    C    D
A  0   0.2  0.3  0.5
E 0.2  0.6  0.9  0.2
D 0.5  0.3  0.6   0
F 0.1  0.4  0.5  0.3

And I would like to turn it into a square matrix as follows

   A    B    C    D    E    F
A  0   0.2  0.3  0.5  0.2  0.1
B 0.2   0    0   0.3  0.6  0.4
C 0.3   0    0   0.6  0.9  0.5
D 0.5  0.3  0.6   0   0.2  0.3
E 0.2  0.6  0.9  0.2   0    0
F 0.1  0.4  0.5  0.3   0    0

In other words, I would like to expand both rows and columns so that it is a symmetric square matrix (rows and columns are in the same order) and missing values are filled with 0.

I guessed there should be a way to do this easily/efficiently using built in functions of pandas but I am not familiar with the package.

for convenience:

df = pd.DataFrame([[0, 0.2, 0.3, 0.5],
                   [0.2, 0.6, 0.9, 0.2],
                   [0.5, 0.3, 0.6, 0],
                   [0.1, 0.4, 0.5, 0.3]],
                   index=['A', 'E', 'D', 'F'],
                   columns=['A', 'B', 'C', 'D'])

There are some interesting answers about symmetric matrices here: https://stackoverflow.com/questions/2572916/numpy-smart-symmetric-matrix — David Gaertner, Jul 09 '18 at 21:16
Upper part is input and AD and DA are just given as 0.5 and we are not summing them up. So final matrix should keep the values as they are and just fill missing columns and missing rows respectively to make everything symmetric — ozgeneral, Jul 09 '18 at 21:24
I think I can just divide your previous answer by 2 and it should work no? — ozgeneral, Jul 09 '18 at 21:26
Nope, it works for all elements by AD and DA :P. Gives the sum for those specific values :( — user3483203, Jul 09 '18 at 21:26
@ÖzgenEren updated my answer to symmetrize a different way to get your desired result. — user3483203, Jul 09 '18 at 21:33

n8yoder · Accepted Answer · 2018-07-10T15:01:09.127

Just as you thought you can definitely do this pretty concisely in Pandas.

One way is by using the very nice combine_first method.

result = df.combine_first(df.T).fillna(0.0)

However, in my testing using timeit that clocked in at 3.62 ms ± 29.2 µs per loop which was actually slightly slower than the time I got for your method (3.5 ms ± 28.6 µs per loop).

However, by calculating this more directly in Pandas using the update method I was able to get this down to 2.04 ms ± 17.2 µs per loop µs per loop (~1.7x as fast).

# Find the combination of both indices
full_index = df.index.union(df.columns)
# Resize the DataFrame to include all the rows and columns
all_data = df.reindex(labels=full_index, axis=0).reindex(labels=full_index, axis=1)
# Update any values we have from the transpose 
all_data.update(all_data.T)
# Fill the missing entries
result = all_data.fillna(0.0)

Honestly I wasn't able to get as much of a performance improvement as I thought I might, but both pandas based versions are a little more readable to me at least.

Nice! Thanks for suggesting and speed testing alternatives. I pick this as answer since I also think it is more readable and slightly faster. — ozgeneral, Jul 10 '18 at 08:01

ozgeneral · Answer 2 · 2018-08-14T09:56:17.333

# create three groups
common = set.intersection(set(df.columns.values), set(df.index))
missing_row = set(df.index) - common
missing_col = set(df.columns.values) - common

# put groups in order (might not be necessary)
ordered = list(common)+list(missing_col)+list(missing_row)

def symmetrize(a):
    return a + a.T

# take the common part and extend it to order
common_part = df.loc[common, common]
common_part = common_part.reindex(index=ordered, columns=ordered, fill_value=0)

# take the remaining part
to_add = df.copy().reindex(index=ordered, columns=ordered, fill_value=0) - common_part
# make sure its symmetric, we can do a+a.T because here every value is only written once and its symmetric position is 0
to_add = symmetrize(to_add.values)

# convert to final form
common_part = common_part+common_part.T
result = pd.DataFrame(common_part + to_add, columns=ordered, index=ordered)
result = result[[*ordered]]
result = result.reindex(ordered)

Yep, this will be a much more general solution, Good answer! — user3483203, Jul 09 '18 at 23:13
In the above example is adj the same as df or where does it come from? — n8yoder, Jul 10 '18 at 03:43
@n8yoder same as df, I copy paste from a code with different notation so I forgot to rename it — ozgeneral, Jul 10 '18 at 08:07

How to make a rectangular matrix square on pandas dataframe

2 Answers2

Linked