Generating Sequential Dates and Reordering Columns in Pandas for FIFO Data

1. Introduction

Hello, I'm PiPi. A user in a Python community asked for a Pandas solution to generate date data for a FIFO‑style table. The original data and the desired result are shown below.

The user also wondered whether the solution works when there are multiple products.

Here is the original code:

data = [[
    ['铅笔','入','60','3.5','京东'],
    ['钢笔','入','35','5.5','京东'],
    ['钢笔','出','21','10.9','A公司'],
    ['铅笔','出','31','5','B公司'],
    ['铅笔','入','120','3.2','苏宁'],
    ['钢笔','入','5','6','国美'],
    ['铅笔','出','88','4','B公司'],
    ['钢笔','出','10','7','C公司'],
    ['牙膏','入','150','6.5','苏宁'],
    ['牙膏','出','110','7','A公司'],
    ['铅笔','出','20','7','B公司']
]

df = pd.DataFrame(data=data, columns=['品名','入/出','数量','单价/元','供应商/购买方'])

df[['数量','单价/元']] = df[['数量','单价/元']].apply(lambda x: x.astype(float))  # data type conversion

df['日期'] = pd.date_range('2023-11-15', periods=len(df))  # add date column

df = df.take([5,0,1,2,3,4], axis=1)  # reorder columns

2. Implementation

A contributor suggested a simple approach: cumulative subtraction. The following image illustrates the idea.

The contributor later simplified the code, as shown in the next image.

The problem was solved successfully.

3. Conclusion

This article presented a Pandas data‑filtering issue, explained the problem, and provided a concrete implementation to help the community solve similar data‑analysis tasks.