Appending DataFrame columns to another DataFrame at an index/location that meets conditions

Question

I have a one_sec_flt DataFrame that has 300,000+ points and a flask DataFrame that has 230 points. Both DataFrames have columns Hour, Minute, Second. I want to append the flask DataFrame to the same time it was taken in the one_sec_flt data.

Flasks DataFrame

      year  month  day  hour  minute  second...  gas1  gas2  gas3
0     2018      4    8    16      27      48...    10    25   191
1     2018      4    8    16      40      20...    45    34   257
...
229   2018      5   12    14      10      05...     3    72   108

one_sec_flt DataFrame

        Year  Month  Day  Hour  Min  Second...  temp  wind
0       2018      4    8    14   30      20...   300    10
1       2018      4    8    14   45      15...   310     8
...
305,212 2018      5   12    14      10   05...   308    24

I have this code I started with but I don't know how to append one DataFrame to another at that exact timestamp.

for i in range(len(flasks)):
    for j in range(len(one_sec_flt)):

        if (flasks.hour.iloc[i] == one_sec_flt.Hour.iloc[j]):
            if (flasks.minute.iloc[i] == one_sec_flt.Min.iloc[j]):
                if (flasks.second.iloc[i] == one_sec_flt.Sec.iloc[j]):
                    print('match')

My output goal would look like:

        Year  Month  Day  Hour  Min  Second...  temp  wind  gas1  gas2  gas3
0       2018      4    8    14   30      20...   300    10   nan   nan   nan
1       2018      4    8    14   45      15...   310     8   nan   nan   nan
2       2018      4    8    15   15      47...   ...   ...   nan   nan   nan
3       2018      4    8    16   27      48...   ...   ...    10    25   191
4       2018      4    8    16   30      11...   ...   ...   nan   nan   nan 
5       2018      4    8    16   40      20...   ...   ...    45    34   257
...      ...     ...  ...  ...  ...      ...     ...   ...   ...   ...   ...
305,212 2018      5   12    14   10      05...   308    24     3    72   108

Please show your expected output given these input dfs. The dataframes seem incomplete and unclear. — ggorlen, Jun 10 '20 at 19:26
have you tried merge? sometime like `new_df = pd.merge(one_sec_flt, flask, how='left', left_on=['year', 'month', 'day', 'hour', 'minute'], right_on = ['year', 'month', 'day', 'hour', 'minute'])` — XXavier, Jun 10 '20 at 19:28
@ggorlen I have edited my question to add an output goal if that helps clarify. — Natasha, Jun 11 '20 at 14:52
Thanks, but I'm still not really sure what logic governs the merge. There are a lot of "fill in the blanks" in these data frames that leave too much to the imagination. The two constituent frames have 2 rows each and the output frame has 6 rows. Where is the 3rd row coming from, for example (hour/min both 15)? — ggorlen, Jun 11 '20 at 14:55
@ggorlen I tried adding more information to my question in hopes that it adds clarification. Essentially I'm trying to merge the two dataframes and I want the flask data to merge with the time variables to give me an output as updated in my question. The pd.merge() seems to do the trick! — Natasha, Jun 11 '20 at 16:09

score 0 · Answer 1 · answered Jun 11 '20 at 15:14

If you can concatenate both the dataframes Flask & one_sec_flt, then sort by the times, it might achieve what you are looking for(at least, if I understood the problem statement correctly).

Flasks
Out[13]: 
   year  month  day  hour  minute  second
0  2018      4    8    16      27      48
1  2018      4    8    16      40      20
one_sec
Out[14]: 
   year  month  day  hour  minute  second
0  2018      4    8    14      30      20
1  2018      4    8    14      45      15
df_res = pd.concat([Flasks,one_sec])
df_res
Out[16]: 
   year  month  day  hour  minute  second
0  2018      4    8    16      27      48
1  2018      4    8    16      40      20
0  2018      4    8    14      30      20
1  2018      4    8    14      45      15
df_res.sort_values(by=['year','month','day','hour','minute','second'])
Out[17]: 
   year  month  day  hour  minute  second
0  2018      4    8    14      30      20
1  2018      4    8    14      45      15
0  2018      4    8    16      27      48
1  2018      4    8    16      40      20

Appending DataFrame columns to another DataFrame at an index/location that meets conditions

1 Answers1