1

I have problems with pd.merge. I have the following data

from pandas import DataFrame
clients = {'DATE': [20150430,20150531,20150630,20150331,20150430],
'CLIENT_ID': [1,1,1,2,2],
'VALUE' : [145,202,150,175,180]}
dates = {'DATE' : [20150331,20150430,20150531,20150630,20150731]}
df1 = DataFrame(clients,columns= ['DATE', 'CLIENT_ID','VALUE'])
df2 = DataFrame(dates,columns=['DATE'])

DF1 DF2

I want all dates repeated for each client, as follows:

results = {'DATE': [20150331,20150430,20150531,20150630,20150731,20150331,20150430,20150531,20150630,20150731],
'CLIENT_ID': [1,1,1,1,1,2,2,2,2,2],
'VALUE': [None,145,202,150,None,175,180,None,None,None]}
df_results = DataFrame(results,columns= ['DATE', 'CLIENT_ID','VALUE'])

DF_RESULT

I tried this, but the result is not the desired

pd.merge(df1, df2, on='DATE', how='outer')

MERGE

Thank you for your help.

Joao Martín Vidarte Chinchay
  • 13
  • 3

3 Answers3

2

No sure why you need that for df1, you can create it from df2 , I think, and here is the method reindex

df1.groupby('CLIENT_ID').apply(lambda  x : x.set_index('DATE').reindex(df2.DATE).ffill().bfill()).reset_index(level=1)
               DATE  CLIENT_ID
CLIENT_ID                     
1          20150331        1.0
1          20150430        1.0
1          20150531        1.0
1          20150630        1.0
1          20150731        1.0
2          20150331        2.0
2          20150430        2.0
2          20150531        2.0
2          20150630        2.0
2          20150731        2.0

If we create from df2

pd.DataFrame({'ID':df1.CLIENT_ID.unique()}).assign(key=1).merge(df2.assign(key=1))
   ID  key      DATE
0   1    1  20150331
1   1    1  20150430
2   1    1  20150531
3   1    1  20150630
4   1    1  20150731
5   2    1  20150331
6   2    1  20150430
7   2    1  20150531
8   2    1  20150630
9   2    1  20150731
BENY
  • 317,841
  • 20
  • 164
  • 234
  • Thank you very much for your answer, I get the desired result of the original question, however when I add the column "values" I do not get the desired result. Could you tell me how could I modify the code? – Joao Martín Vidarte Chinchay May 12 '19 at 19:07
1

Can form the basis from a product, then left merge the other information on. 

from itertools import product
import pandas as pd

(pd.DataFrame(product(df1.CLIENT_ID.unique(), df2.DATE),
              columns=['CLIENT_ID', 'DATE'])
   .merge(df1, how='left'))

   CLIENT_ID      DATE  VALUE
0          1  20150331    NaN
1          1  20150430  145.0
2          1  20150531  202.0
3          1  20150630  150.0
4          1  20150731    NaN
5          2  20150331  175.0
6          2  20150430  180.0
7          2  20150531    NaN
8          2  20150630    NaN
9          2  20150731    NaN

If performance of the product step is a concern this answer is very helpful

Alternatively with set_index + reindex:

idx = pd.MultiIndex.from_product([df1.CLIENT_ID.unique(), df2.DATE],
                                 names=['CLIENT_ID', 'DATE'])
df1.set_index(['CLIENT_ID', 'DATE']).reindex(idx).reset_index()
ALollz
  • 57,915
  • 7
  • 66
  • 89
0

this seem to be what you are looking for:

 import pandas as pd
 import numpy as np
 clients = {'DATE': [20150430,20150531,20150630,20150331,20150430],
 'CLIENT_ID': [1,1,1,2,2],
 'VALUE' : [145,202,150,175,180]}
 dates = {'DATE' : [20150331,20150430,20150531,20150630,20150731]}

 df1 = pd.DataFrame(clients,columns= ['DATE', 'CLIENT_ID','VALUE'])
 df2 = df1.copy()
 df2['CLIENT_ID'].map({1:2,2:1})
 df2['VALUE']=np.NaN
 df_result=df1.append(df2).reset_index()

    DATE    CLIENT_ID   VALUE
   0    20150430    1   145.0
   1    20150531    1   202.0
   2    20150630    1   150.0
   3    20150331    2   175.0
   4    20150430    2   180.0
   5    20150430    1   NaN
   6    20150531    1   NaN
   7    20150630    1   NaN
   8    20150331    2   NaN
   9    20150430    2   NaN

Unique rows for each DATE and CLIENT_ID

Dimitri
  • 103
  • 3