0

I have a large dataframe df1 with many data columns, two of which are dates and colNum. I have built a second dataframe df2 which spans the date range and colNum of df1. I now want to fill df2 with a third column (any of the many other data columns) of df1 which meet the criteria of dates and colNum from df1 that match dateIndex and colNum of df2.

I've tried various incarnations of MERGE with no success.

I can loop through the combinations, but df1 is very large (270k, 2k) so it takes forever to do fill one df2 from one of df1's columns, let alone all of them.

Slow looping version

dataList = ['revt']
for i in dataList:
    goodRows = df1.index[~np.isnan(df1[i])].tolist()
    for j in goodRows:
        df2.loc[df1['dates'][j], str(df1['colNum'][j])] = df1[i][j]

Input

Desired Output

dmigo
  • 2,849
  • 4
  • 41
  • 62
BBob
  • 1
  • [Stack Overflow Discourages Screenshots](https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors). It is likely, the question will be downvoted, for containing unnecessary screenshots. By using screenshots, you are discouraging anyone from assisting you. No one wants to retype your stuff, from a screenshot, and screenshots are often, not readable. – Trenton McKinney Oct 15 '19 at 18:39
  • Please [provide a reproducible copy of the DataFrame with `to_clipboard`](https://stackoverflow.com/questions/52413246/how-do-i-provide-a-reproducible-copy-of-my-existing-dataframe) – Trenton McKinney Oct 15 '19 at 18:40

1 Answers1

0

convert index to column e.g

df1.reset_index() #as per your statement date seems to be in index df2.rest_index() df2 = pd.merge(df2, df1, on = ['dateIndex', 'colNum'], how = 'left') #keep either "left" or "inner" as per your convenience

update rather you can keep date in index and in pd.merge there is a option to join via index too

Abhishek
  • 3
  • 3
  • If I'm not mistaken, this will produce a 3-column DF (once I drop the index) rather than a matrix DF based on dateIndex and colNum - sorry if that wasn't clear in my question – BBob Oct 16 '19 at 12:19