Pandas adding additional values between two row-values in a dataframe with number constraint

user_12478546 0
Viewed 103

I have data frame. Under the same index, I have "early_date" & "latest_date", which are in "int" dtype. I want to create additional values in between the "early_date" & "latest_date" row-values. Incidentally, I want to stack the generated values into new rows between them.

Here is how I did it,

df = pd.DataFrame({'index': [1,1,2,2,3,3],
                   'variable': ['early_date', 'late_date']*3,
                   'value': [201952,202001,202002,202004,202006,202012]})

# This is what your data looks like unmelted
df_p = df.pivot('index', 'variable', 'value').reset_index()
df_p.columns.name = ''

df_p['new'] = [list(range(x,y+1)) for x, y in zip(df_p.pop('early_date'), df_p.pop('late_date'))]

This is the result enter image description here

In the column "new", the filling between "201952" & "202001" in index 1 has became 201952, 201953, 201954...201999, 202001.

However, since the "new" column is actually representing the year and weeks. In index 1 case, It shall not be filling anything between 201952 & 202001, and the result should be [201952, 202001]. Since week 52 is the end of the year.

What can I do to handling these cases?

pandas dataframe python
1 Answers

IIUC, you can add a condition in your for loop:

df_p['new'] = [list(range(x,y+1)) if str(x)[-2:]!='52' else [x,y] 
              for x, y in zip(df_p.pop('early_date'), df_p.pop('late_date'))]

print(df_p)

   index                                                new
0      1                                   [201952, 202001]
1      2                           [202002, 202003, 202004]
2      3  [202006, 202007, 202008, 202009, 202010, 20201...
user_98406370
Related