CODEWITHSUNDEEP
pythonpandasnumpy
bbd108
bbd108

Reputation: 998

Pandas Dataframe add missing timestamp row and then forward fill previous values

I have a pandas dataframe as follows:

Timestamp                    Player Rotated Lat Rotated Lon
2018-11-11 16:22:21.999993600   G   -15.89769   84.714795
2018-11-11 16:22:21.999993600   W   -15.897637  84.714784
2018-11-11 16:22:21.999993600   K   -15.897617  84.714621
2018-11-11 16:22:21.999993600   Y   -15.897638  84.714787
2018-11-11 16:22:22.099958400   K   -15.897618  84.714623
2018-11-11 16:22:22.099958400   Y   -15.897691  84.714796
2018-11-11 16:22:22.099958400   W   -15.897619  84.714626
2018-11-11 16:22:22.200009600   Y   -15.897693  84.714794
2018-11-11 16:22:22.200009600   G   -15.897639  84.714788
2018-11-11 16:22:22.200009600   K   -15.897693  84.714802
2018-11-11 16:22:22.299974400   W   -15.897692  84.714796
2018-11-11 16:22:22.299974400   G   -15.897622  84.714629
2018-11-11 16:22:22.299974400   Y   -15.897639  84.714791
2018-11-11 16:22:22.299974400   K   -15.897694  84.714799
2018-11-11 16:22:22.400025600   G   -15.89764   84.714794
2018-11-11 16:22:22.400025600   K   -15.897622  84.714632
2018-11-11 16:22:22.400025600   Y   -15.897692  84.714804
2018-11-11 16:22:22.400025600   W   -15.897623  84.714635
2018-11-11 16:22:22.499990400   Y   -15.897692  84.714806
2018-11-11 16:22:22.499990400   W   -15.897694  84.714802
2018-11-11 16:22:22.499990400   G   -15.897641  84.714795
2018-11-11 16:22:22.499990400   K   -15.897694  84.714808

If you notice, I have 4 players: G, W, K, Y. Therefore there should be 4 of each timestamp index. However, some Timestamps are missing. How can I add all the missing timestamps and then forward fill the other values to get only those players who are not in a given timestamp?

For example, for timestamp 2018-11-11 16:22:22.099958400, Player G is missing. How can I fill for just that player?

Desired output (I have spaced the frame to make it more readable):

Timestamp                    Player Rotated Lat Rotated Lon
2018-11-11 16:22:21.999993600   G   -15.89769   84.714795
2018-11-11 16:22:21.999993600   W   -15.897637  84.714784
2018-11-11 16:22:21.999993600   K   -15.897617  84.714621
2018-11-11 16:22:21.999993600   Y   -15.897638  84.714787

2018-11-11 16:22:22.099958400   K   -15.897618  84.714623
2018-11-11 16:22:22.099958400   Y   -15.897691  84.714796
2018-11-11 16:22:22.099958400   W   -15.897619  84.714626
2018-11-11 16:22:22.099958400   G   -15.89769   84.714795

2018-11-11 16:22:22.200009600   Y   -15.897693  84.714794
2018-11-11 16:22:22.200009600   G   -15.897639  84.714788
2018-11-11 16:22:22.200009600   K   -15.897693  84.714802
2018-11-11 16:22:22.200009600   W   -15.897619  84.714626

Upvotes: 1

Views: 505

Answers (1)

jezrael
jezrael

Reputation: 863741

Use set_index with unstack for reshape, forward fill missing values and last reshape back by stack:

df = df.set_index('Player', append=True).unstack().ffill().stack().reset_index(level=1)
print (df)
                              Player  Rotated Lat  Rotated Lon
Timestamp                                                     
2018-11-11 16:22:21.999993600      G   -15.897690    84.714795
2018-11-11 16:22:21.999993600      K   -15.897617    84.714621
2018-11-11 16:22:21.999993600      W   -15.897637    84.714784
2018-11-11 16:22:21.999993600      Y   -15.897638    84.714787
2018-11-11 16:22:22.099958400      G   -15.897690    84.714795
2018-11-11 16:22:22.099958400      K   -15.897618    84.714623
2018-11-11 16:22:22.099958400      W   -15.897619    84.714626
2018-11-11 16:22:22.099958400      Y   -15.897691    84.714796
2018-11-11 16:22:22.200009600      G   -15.897639    84.714788
2018-11-11 16:22:22.200009600      K   -15.897693    84.714802
2018-11-11 16:22:22.200009600      W   -15.897619    84.714626
2018-11-11 16:22:22.200009600      Y   -15.897693    84.714794
2018-11-11 16:22:22.299974400      G   -15.897622    84.714629
2018-11-11 16:22:22.299974400      K   -15.897694    84.714799
2018-11-11 16:22:22.299974400      W   -15.897692    84.714796
2018-11-11 16:22:22.299974400      Y   -15.897639    84.714791
2018-11-11 16:22:22.400025600      G   -15.897640    84.714794
2018-11-11 16:22:22.400025600      K   -15.897622    84.714632
2018-11-11 16:22:22.400025600      W   -15.897623    84.714635
2018-11-11 16:22:22.400025600      Y   -15.897692    84.714804
2018-11-11 16:22:22.499990400      G   -15.897641    84.714795
2018-11-11 16:22:22.499990400      K   -15.897694    84.714808
2018-11-11 16:22:22.499990400      W   -15.897694    84.714802
2018-11-11 16:22:22.499990400      Y   -15.897692    84.714806

Upvotes: 5

Related Questions