0

I have a following problem. I am creating a heat map. To make my dataframe more "heatmap friendly" I need to fill missing values on X and Y axis. This is how my dataframe looks like now: enter image description here

I need to go through both axis (range 20 - 393 for axis = 0 and 584 - 648 for axis = 1) and if the axis is missing in my dataframe thwn insert a column full of zeros.

I followed the advice here (https://stackabuse.com/ultimate-guide-to-heatmaps-in-seaborn-with-python/) and I tried:

df = df.reindex(range(20,393), axis=0, fill_value=0)
df = df.reindex(range(584,648), axis=1, fill_value=0).astype(int)

but it rewrites all existing values to zero (including the nonzero ones). Can you help me, please? This answer seems not relevant for my case: Insert missing rows in a dataframe (variable index range)

EDIT: Reproducible example:

import pandas as pd

my_df = pd.DataFrame(data={'Centroid1': [20, 20, 22, 26, 27], 
                                'Centroid2': [584, 596, 597, 608, 602], 
                                'counts': [2, 0, 0, 1, 1]})

my_df = my_df.reset_index().pivot(index='Centroid2', 
                                            columns='Centroid1', values='counts')


my_df.fillna(0, inplace=True)

#HOW TO FILL MISSING COLUMNS 20-30 and 582-605??? 
#examples bellow does not work - everything is zero

#Ex 1:
my_df = my_df.rename(columns=int, index=int)
my_df = my_df.reindex(index=range(20,30), columns=range(582,605), fill_value=0)

#Ex 2:
my_df = my_df.reindex(range(20,30), axis=0, fill_value=0)
my_df = my_df.reindex(range(582,605), axis=1, fill_value=0).astype(int)
vojtam
  • 1,157
  • 9
  • 34

2 Answers2

1

It seems you swap ranges for index and for columns each other:

my_df = my_df.reindex(index=range(582,605), columns=range(20,30), fill_value=0).astype(int)

print (my_df) 
Centroid1  20  21  22  23  24  25  26  27  28  29
Centroid2                                        
582         0   0   0   0   0   0   0   0   0   0
583         0   0   0   0   0   0   0   0   0   0
584         2   0   0   0   0   0   0   0   0   0
585         0   0   0   0   0   0   0   0   0   0
586         0   0   0   0   0   0   0   0   0   0
587         0   0   0   0   0   0   0   0   0   0
588         0   0   0   0   0   0   0   0   0   0
589         0   0   0   0   0   0   0   0   0   0
590         0   0   0   0   0   0   0   0   0   0
591         0   0   0   0   0   0   0   0   0   0
592         0   0   0   0   0   0   0   0   0   0
593         0   0   0   0   0   0   0   0   0   0
594         0   0   0   0   0   0   0   0   0   0
595         0   0   0   0   0   0   0   0   0   0
596         0   0   0   0   0   0   0   0   0   0
597         0   0   0   0   0   0   0   0   0   0
598         0   0   0   0   0   0   0   0   0   0
599         0   0   0   0   0   0   0   0   0   0
600         0   0   0   0   0   0   0   0   0   0
601         0   0   0   0   0   0   0   0   0   0
602         0   0   0   0   0   0   0   1   0   0
603         0   0   0   0   0   0   0   0   0   0
604         0   0   0   0   0   0   0   0   0   0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

Let's say df_calc is your original calculated data frame and your require data frame is df_final then

df_final = pd.DataFrame()
df_final = df_final.reindex(range(20,393), axis=0, fill_value=0)
df_final = df_final.reindex(range(584,648), axis=1, fill_value=0).astype(int) 

df_final.update(df_calc)
Deven Ramani
  • 751
  • 4
  • 10