adding rows based on missing data

Question

I have a currency data based on minutes like below. What I want to do is if one minute is missing for example if it jumps to 00:05:00 from 00:02:00 I want to create a new row with same 'Date' and 'AUDJPY' value. I want below dataframe to be: I have no idea what to do, I could not even understand how to add rows based on conditionality.

AUDJPY       Date      Time
    0  76.150 2020-01-02  00:00:00
    1  76.151 2020-01-02  00:01:00
    2  76.152 2020-01-02  00:02:00
    3  76.162 2020-01-02  00:05:00
    4  76.164 2020-01-02  00:06:00
    5  76.175 2020-01-02  00:07:00
    6  76.162 2020-01-02  00:08:00
    7  76.153 2020-01-02  00:09:00

 

      AUDJPY       Date      Time
    0  76.150 2020-01-02  00:00:00
    1  76.151 2020-01-02  00:01:00
    2  76.152 2020-01-02  00:02:00
    3  76.152 2020-01-02  00:03:00
    4  76.152 2020-01-02  00:04:00
    5  76.162 2020-01-02  00:05:00
    6  76.164 2020-01-02  00:06:00
    7  76.175 2020-01-02  00:07:00
    8  76.162 2020-01-02  00:08:00
    9  76.153 2020-01-02  00:09:00

Nk03 · Accepted Answer · 2021-06-07 17:00:57Z

1

You can try:

df['Date-Time'] = pd.to_datetime(df['Date'] + ' ' + df['Time'])
k = df.set_index('Date-Time').asfreq('T').fillna(method = 'ffill').drop(['Date', 'Time'], 1).reset_index()

If you want to separate date/time from Date-time:

k[['Date', 'Time']] = k['Date-Time'].astype(str).str.split(' ', expand = True)

edited Jun 7, 2021 at 17:00

answered Jun 7, 2021 at 13:34

Nk03

15k2 gold badges11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

adding rows based on missing data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related