0

I'm creating columns with past data from the same columns of the database. Like for the same day, I need the Y value of the day before and the same day of week in the week before. So:

x = df.copy()
x["Date"] = pd.to_datetime(df.rename(columns={"Año":"Year","Mes":"Month","Dia":"Day"})[["Year","Month","Day"]])

y = x[["Date","Y"]]
y.rename(columns={"Y":"Y_DiaAnterior"}, inplace=True)
y["Date"] = y["Date"] + dt.timedelta(days=1)
z = pd.merge(x,y,on=["Date"], how="left")
display(y.head()) # First merge result

a = x[["Date","Y"]]
a.rename(columns={"Y":"Y_DiaSemAnterior"}, inplace=True)
a["Date"] = a["Date"] + dt.timedelta(days=7)
z = pd.merge(x,a,on=["Date"], how="left")

z.head() # Second merge result

Where y df is an auxiliar df to create the column Y with last day data, and a df is an auxiliar df to create the column Y with same-day-last week data.

When I merge them separately it works perfectly, but when I want to merge all of them (first x with y and then x with a) the merge of x with y is 'deleted', as you can see that the Y_DiaAnterior columns is not in the final df (or 'second merge result') even when I already merged them.

First merge result Foto1

Second merge result Foto2

So, how can I do that the final df have Y_DiaAnterior and Y_DiaSemAnterior variables?

Chris
  • 2,019
  • 5
  • 22
  • 67

1 Answers1

1

Because you're overwriting your z with the new merge of x and a. Also you aren't showing the results of the first merge in your code because you're using y.head().

If you want the merge results of all 3 df's, you can chain the merges:

# prep x
x = df.copy()
x["Date"] = pd.to_datetime(df.rename(columns={"Año":"Year", "Mes":"Month", "Dia":"Day"})[["Year", "Month", "Day"]])

# prep y
y = x[["Date", "Y"]].copy()
y.rename(columns={"Y":"Y_DiaAnterior"}, inplace=True)
y["Date"] = y["Date"] + dt.timedelta(days=1)

# prep a
a = x[["Date", "Y"]].copy()
a.rename(columns={"Y":"Y_DiaSemAnterior"}, inplace=True)
a["Date"] = a["Date"] + dt.timedelta(days=7)

# now merge all
z = x.merge(y, on='Date', how='left') \
    .merge(a, on='Date', how='left')
Scratch'N'Purr
  • 9,959
  • 2
  • 35
  • 51
  • It works ! Thanks!, Btw, what's that division sign in merge? the "/.merge()" ? I can't find any references to that. – Chris Dec 14 '18 at 16:17
  • You mean the backslash? That's to break code into multiple lines. You can see examples [here](https://stackoverflow.com/questions/4172448/is-it-possible-to-break-a-long-line-to-multiple-lines-in-python) – Scratch'N'Purr Dec 14 '18 at 21:45