Pivot table not showing all the df values-CodePudding

I have this df:

         CODE      STATION       PLACE               SECTOR   ALTITUDE MONTH  YEARS_OF_STATION
0        103036        NaN       TUMBES            COSTA NORTE      70  ENE       27
1        103036        NaN       TUMBES            COSTA NORTE      70  ENE       27
2        103036        NaN       TUMBES            COSTA NORTE      70  ENE       27
3        103036        NaN       TUMBES            COSTA NORTE      70  ENE       27
4        103036        NaN       TUMBES            COSTA NORTE      70  ENE       27
        ...        ...          ...                    ...     ...  ...      ...
4022090  117043  PAUCARANI        TACNA  SIERRA SUR OCCIDENTAL    4556  DIC       27
4022091  117043  PAUCARANI        TACNA  SIERRA SUR OCCIDENTAL    4556  DIC       27
4022092  117043  PAUCARANI        TACNA  SIERRA SUR OCCIDENTAL    4556  DIC       27
4022093  117043  PAUCARANI        TACNA  SIERRA SUR OCCIDENTAL    4556  DIC       27
4022094  117043  PAUCARANI        TACNA  SIERRA SUR OCCIDENTAL    4556  DIC       27

I'm creating a pivot table with this code:

table_years=pd.pivot_table(df,index=['CODE','STATION','PLACE',
                                                'SECTOR','ALTITUDE'],columns=['MONTH'],values=['YEARS_OF_STATION'])

And i have 371 unique values in CODE column but table_years is only showing 351 values. Do you know why this happens?

I'll appreciate any help or suggestion.

Thanks in advance.

CodePudding user response：

It's because there are NaNs in your dataset, you should specify dropna=False:

table_years=pd.pivot_table(df, index=['CODE','STATION','PLACE', 'SECTOR','ALTITUDE'], 
                               columns=['MONTH'], 
                               values=['YEARS_OF_STATION'], 
                               dropna=False)

CodePudding user response：

Noted you have NaN value in index STATION, if you would like keep the original CODE then you need to fill the value with fillna

df
   index1  index2  columns  values
0       1     1.0        1       1
1       1     1.0        2       1
2       1     1.0        1       1
3       2     2.0        2       1
4       3     NaN        1       1
pd.pivot_table(df,index=['index1','index2'],columns=['columns'],values=['values'])
              values     
columns            1    2
index1 index2            
1      1.0       1.0  1.0
2      2.0       NaN  1.0

After fillna

pd.pivot_table(df.fillna(0),index=['index1','index2'],columns=['columns'],values=['values'])
              values     
columns            1    2
index1 index2            
1      1.0       1.0  1.0
2      2.0       NaN  1.0
3      0.0       1.0  NaN