0

I have a dataset that looks like this which I created using a nested for loop to go through scenarios and fill/manipulate the data, then I had it go through a pandas pivot to result in this current view Sample Dataset. The index is Store, Currency, Record, and the sort columns (to make sure its opening -> activity -> closing). The columns are Date and the values are Amount Local (to whatever currency it is) on aggfunc of sum.


`df = df.pivot_table(
 index=["Store", "Currency", "Record", "sort"]
 columns="Date",
 values="Amount Local",
 aggfunc="sum",
) sort_values(by=["Custodian Account", "Currency", "Sort"])
`

The problem is the NaN values, if its NaN then the next days Opening is the last days close and next days Close is last days close as well. There won't be a case where activity is not NaN and opening and close will be populated because my for loop satisifies for that... if there is a NaN all 3 Records will be NaN.

I tried using a mask based on index equaling activity and then filling NaN values with 0 but that resulted in a run without the proper outcome (nothing happened... it remained NaN).

I tried a forward fill (df.fillna(axis=1, method='ffill', inplace=True) which was promising but it forward fills from last column so NaN opening balance becomes prior days Opening balance and NaN activity becomes prior days activity... the only one that worked (they all worked but working for the logic I want to achieve) through this forward fill was closing because it should be the prior days closing.

Unsure of how to go about this...the DataFrame technically only has columns date on datetime so it would have to go through the index I believe. I would want the final result to look like this with the highlighted being forward filled from last image I provided. Final Visual

Mark
  • 7,785
  • 2
  • 14
  • 34
edsaniti
  • 3
  • 2
  • Refrain from showing your dataframe as an image. Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. – itprorh66 Jul 18 '23 at 23:21
  • Can you run df.to_dict() and add it to your question? Thanks! – Mark Jul 19 '23 at 15:49

0 Answers0