How to keep NaN in pivot table?

2024/9/20 19:37:33

Looking to preserve NaN values when changing the shape of the dataframe.

These two questions may be related:

  • How to preserve NaN instead of filling with zeros in pivot table?
  • How to make two NaN as NaN after the operation instead of making it zero?

but not been able to use the answers provided - can I set a min count for np.sum somehow?

import pandas as pd
import numpy as np
df = pd.DataFrame([['Y1', np.nan], ['Y2', np.nan], ['Y1', 6], ['Y2',8]], columns=['A', 'B'], index=['1988-01-01','1988-01-01', '1988-01-04', '1988-01-04'])
df.index.name = 'Date'
dfpivot_df = pd.pivot_table(df, values='B', index=['Date'], columns=['A'],aggfunc=np.sum)
pivot_df

The output is:

A   Y1  Y2
Date        
1988-01-01  0.0 0.0
1988-01-04  6.0 8.0

and the desired output is:

A   Y1  Y2
Date        
1988-01-01  NaN NaN
1988-01-04  6.0 8.0
Answer

From the helpful comments the following solution meets my requirements:


pivot_df_2 = pd.pivot_table(df, values='B', index=['Date'], columns=['A'],aggfunc=min, dropna=False)
pivot_df_2

Values are supposed to be unique per slot so replacing the sum function with a min function shouldn't make a difference (in my case)

https://en.xdnf.cn/q/72316.html

Related Q&A

Using Pandas df.where on multiple columns produces unexpected NaN values

Given the DataFrameimport pandas as pddf = pd.DataFrame({transformed: [left, right, left, right],left_f: [1, 2, 3, 4],right_f: [10, 20, 30, 40],left_t: [-1, -2, -3, -4],right_t: [-10, -20, -30, -40], }…

Django star rating system and AJAX

I am trying to implement a star rating system on a Django site.Storing the ratings in my models is sorted, as is displaying the score on the page. But I want the users to be able to rate a page (from 1…

Create inheritance graphs/trees for Django templates

Is there any tool out there that would take a directory with a Django application, scan it for templates and draw/print/list a hierarchy of inheritance between templates?Seeing which blocks are being …

Python SVG converter creates empty file

I have some code below that is supposed to convert a SVG image to a PNG. It runs without errors but creates a PNG file that is blank instead of one with the same image as the original SVG. I did find t…

Fastest way to iterate through a pandas dataframe?

How do I run through a dataframe and return only the rows which meet a certain condition? This condition has to be tested on previous rows and columns. For example:#1 #2 #3 #4 1/1/1999 4 …

Constraints do not follow DCP rules in CVXPY

I want to solve this problem using CVXPY but I dont know why I get the following error message:DCPError: Problem does not follow DCP rules. I guess my constraints are not DCP. Is there any way to model…

is this betweenness calculation correct?

I try to calculate betweenness for all nodes for the path from 2 to 6 in this simple graph.G=nx.Graph() edge=[(1,5),(2,5),(3,5),(4,5),(4,6),(5,7),(7,6)] G.add_edges_from(edge) btw=nx.betweenness_centra…

Why does PIL thumbnail not resizing correctly?

I am trying to create and save a thumbnail image when saving the original user image in the userProfile model in my project, below is my code:def save(self, *args, **kwargs):super(UserProfile, self).sa…

Put the legend of pandas bar plot with secondary y axis in front of bars

I have a pandas DataFrame with a secondary y axis and I need a bar plot with the legend in front of the bars. Currently, one set of bars is in front of the legend. If possible, I would also like to pla…

Printing floats with a specific number of zeros

I know how to control the number of decimals, but how do I control the number of zeros specifically?For example:104.06250000 -> 104.0625 119.00000 -> 119.0 72.000000 -> 72.0