Home > other >  Outer merge df1 & df2 together, outer merge df3 to df1&2, columns become unsorted
Outer merge df1 & df2 together, outer merge df3 to df1&2, columns become unsorted

Time:09-21

I have 3 different dataframe's where each one contains different dates (df1, df2, & df3(df3 contains a list of every date starting from 2014-01-01)). What I am trying to accomplish is merging all 3 together and have all matching dates line up on the same row, and any rows where dates don't match to display 'NaN' or something similar. I'm having a bit of a problem where I outer merge df1 to df2 (we'll call the df1 & df2 merged dataframe df4) on the columns which contain dates. My result is below:

    VIPR (Y-M-D) Viriciti (Y-M-D)
0            NaN       2021-07-07
1            NaN       2021-07-08
2            NaN       2021-07-09
3            NaN       2021-07-10
4            NaN       2021-07-11
5            NaN       2021-07-13
6            NaN       2021-07-14
7            NaN       2021-07-15
8            NaN       2021-07-16
9            NaN       2021-07-18
10           NaN       2021-07-20
11           NaN       2021-07-21
12           NaN       2021-07-22
13           NaN       2021-07-31
14           NaN       2021-08-02
15           NaN       2021-08-03
16           NaN       2021-08-10
17           NaN       2021-08-11
18           NaN       2021-08-12
19           NaN       2021-08-16
20           NaN       2021-08-17
21           NaN       2021-08-18
22           NaN       2021-08-19
23           NaN       2021-08-21
24           NaN       2021-08-24
25           NaN       2021-08-25
26           NaN       2021-08-26
27           NaN       2021-08-27
28           NaN       2021-08-28
29           NaN       2021-08-29
30           NaN       2021-08-31
31           NaN       2021-09-01
32           NaN       2021-09-02
33           NaN       2021-09-03
34           NaN       2021-09-04
35           NaN       2021-09-05
36           NaN       2021-09-10
37           NaN       2021-09-12
38           NaN       2021-09-14
39           NaN       2021-09-15
40           NaN       2021-09-16
41           NaN       2021-09-17
42           NaN       2021-09-18
43           NaN       2021-09-21
44           NaN       2021-09-22
45           NaN       2021-09-23
46           NaN       2021-09-24
47           NaN       2021-09-25
48           NaN       2021-09-28
49           NaN       2021-09-29
50           NaN       2021-09-30
51           NaN       2021-10-01
52           NaN       2021-10-02
53           NaN       2021-10-05
54           NaN       2021-10-12
55           NaN       2021-10-13
56           NaN       2021-10-14
57           NaN       2021-10-15
58           NaN       2021-10-16
59           NaN       2021-10-17
60           NaN       2021-10-18
61           NaN       2021-10-20
62           NaN       2021-10-21
63           NaN       2021-10-23
64           NaN       2021-10-27
65           NaN       2021-10-28
66           NaN       2021-10-29
67           NaN       2021-10-30
68           NaN       2021-11-01
69           NaN       2021-11-02
70           NaN       2021-11-03
71           NaN       2021-11-04
72           NaN       2021-11-09
73           NaN       2021-11-11
74           NaN       2021-11-12
75           NaN       2021-11-16
76           NaN       2021-11-19
77           NaN       2021-11-20
78           NaN       2021-11-23
79           NaN       2021-11-24
80           NaN       2021-11-25
81           NaN       2021-12-02
82           NaN       2021-12-03
83           NaN       2021-12-10
84           NaN       2021-12-11
85           NaN       2021-12-14
86           NaN       2021-12-15
87           NaN       2021-12-16
88           NaN       2021-12-17
89           NaN       2021-12-18
90           NaN       2022-01-04
91           NaN       2022-01-05
92           NaN       2022-01-06
93           NaN       2022-01-07
94           NaN       2022-01-08
95           NaN       2022-01-10
96           NaN       2022-01-11
97           NaN       2022-01-18
98           NaN       2022-01-19
99           NaN       2022-01-20
100          NaN       2022-01-21
101          NaN       2022-01-22
102          NaN       2022-01-25
103          NaN       2022-01-26
104          NaN       2022-01-27
105          NaN       2022-01-28
106          NaN       2022-01-29
107          NaN       2022-02-01
108          NaN       2022-02-02
109          NaN       2022-02-03
110          NaN       2022-02-04
111          NaN       2022-02-05
112          NaN       2022-02-08
113          NaN       2022-02-09
114          NaN       2022-02-11
115          NaN       2022-02-12
116          NaN       2022-02-15
117          NaN       2022-02-16
118          NaN       2022-02-17
119          NaN       2022-02-23
120          NaN       2022-02-24
121          NaN       2022-02-25
122          NaN       2022-02-26
123          NaN       2022-02-27
124          NaN       2022-03-03
125          NaN       2022-03-04
126          NaN       2022-03-08
127          NaN       2022-03-10
128          NaN       2022-03-22
129          NaN       2022-03-23
130          NaN       2022-03-24
131          NaN       2022-03-30
132          NaN       2022-03-31
133          NaN       2022-04-04
134          NaN       2022-04-05
135          NaN       2022-04-06
136          NaN       2022-04-07
137          NaN       2022-04-08
138          NaN       2022-04-09
139          NaN       2022-04-13
140          NaN       2022-04-18
141          NaN       2022-04-19
142          NaN       2022-04-20
143          NaN       2022-04-21
144          NaN       2022-04-22
145          NaN       2022-04-23
146          NaN       2022-04-24
147          NaN       2022-04-25
148          NaN       2022-04-26
149          NaN       2022-04-27
150          NaN       2022-04-28
151          NaN       2022-04-29
152          NaN       2022-05-02
153          NaN       2022-05-03
154          NaN       2022-05-04
155          NaN       2022-05-05
156          NaN       2022-05-06
157          NaN       2022-05-07
158          NaN       2022-05-08
159          NaN       2022-05-10
160          NaN       2022-05-11
161          NaN       2022-05-12
162          NaN       2022-05-13
163          NaN       2022-05-14
164          NaN       2022-05-15
165          NaN       2022-05-16
166          NaN       2022-05-17
167          NaN       2022-05-19
168          NaN       2022-05-20
169          NaN       2022-05-21
170          NaN       2022-05-22
171          NaN       2022-05-23
172          NaN       2022-05-24
173          NaN       2022-05-25
174          NaN       2022-05-26
175          NaN       2022-05-27
176          NaN       2022-05-28
177          NaN       2022-05-29
178          NaN       2022-05-30
179          NaN       2022-05-31
180          NaN       2022-06-01
181          NaN       2022-06-02
182          NaN       2022-06-03
183   2022-06-04       2022-06-04
184   2022-06-05       2022-06-05
185   2022-06-06       2022-06-06
186   2022-06-07       2022-06-07
187          NaN       2022-06-08
188          NaN       2022-06-09
189   2022-06-10       2022-06-10
190   2022-06-11       2022-06-11
191   2022-06-12       2022-06-12
192   2022-06-13       2022-06-13
193   2022-06-14       2022-06-14
194          NaN       2022-06-15
195   2022-06-16              NaN
196          NaN       2022-06-17
197   2022-06-20              NaN
198   2022-06-21       2022-06-21
199          NaN       2022-06-22
200          NaN       2022-06-23
201          NaN       2022-06-24
202          NaN       2022-06-25
203          NaN       2022-06-26
204          NaN       2022-06-27
205          NaN       2022-06-28
206          NaN       2022-06-29
207          NaN       2022-06-30
208          NaN       2022-07-01
209          NaN       2022-07-02
210          NaN       2022-07-03
211          NaN       2022-07-04
212   2022-07-05       2022-07-05
213   2022-07-06       2022-07-06
214   2022-07-07       2022-07-07
215   2022-07-08       2022-07-08
216   2022-07-09       2022-07-09
217          NaN       2022-07-10
218          NaN       2022-07-16
219          NaN       2022-07-19
220          NaN       2022-07-20
221   2022-07-21              NaN
222   2022-07-22       2022-07-22
223   2022-07-22       2022-07-22
224   2022-07-23       2022-07-23
225   2022-07-24       2022-07-24
226   2022-07-25       2022-07-25
227   2022-07-26       2022-07-26
228   2022-07-27       2022-07-27
229   2022-07-28       2022-07-28
230   2022-07-29       2022-07-29
231   2022-07-30       2022-07-30
232   2022-07-31       2022-07-31
233   2022-08-01       2022-08-01
234   2022-08-02       2022-08-02
235   2022-08-03       2022-08-03
236   2022-08-04       2022-08-04
237   2022-08-05       2022-08-05
238          NaN       2022-08-06
239          NaN       2022-08-09
240          NaN       2022-08-13
241          NaN       2022-08-14
242   2022-08-15              NaN
243          NaN       2022-08-16
244          NaN       2022-08-17
245   2022-08-20              NaN
246          NaN       2022-08-21
247   2022-08-25              NaN
248   2022-08-26       2022-08-26
249          NaN       2022-08-27
250   2022-08-29              NaN
251   2022-08-30       2022-08-30
252   2022-08-31       2022-08-31
253   2022-09-01       2022-09-01
254          NaN       2022-09-02
255          NaN       2022-09-03
256   2022-09-08              NaN
257   2022-09-09       2022-09-09
258          NaN       2022-09-10
259   2022-09-13              NaN
260   2022-09-14       2022-09-14
261   2022-09-15       2022-09-15
262   2022-09-16       2022-09-16
263          NaN       2022-09-17
264          NaN       2022-09-21

This is the result I am looking for! Now when I try to outer merge df3 to df4 the 'VIPR (Y-M-D)' column becomes unsorted. It seems that the dates in 'VIPR (Y-M-D)' that had 'NaN' next to them in df4 are becoming unsorted and going to the bottom are doing so because on the last merge of df3 to df4 my code is:

df5 = df3.merge(df4, how='outer', left_on='Date', right_on='Viriciti (Y-M-D)', sort=True)

The result of this merge is:

            Date VIPR (Y-M-D) Viriciti (Y-M-D)
0     2014-01-01          NaN              NaN
1     2014-01-02          NaN              NaN
2     2014-01-03          NaN              NaN
3     2014-01-04          NaN              NaN
4     2014-01-05          NaN              NaN
5     2014-01-06          NaN              NaN
...
2744  2021-07-07          NaN       2021-07-07
...
3076  2022-06-04   2022-06-04       2022-06-04
3077  2022-06-05   2022-06-05       2022-06-05
3078  2022-06-06   2022-06-06       2022-06-06
3079  2022-06-07   2022-06-07       2022-06-07
3080  2022-06-08          NaN       2022-06-08
3081  2022-06-09          NaN       2022-06-09
3082  2022-06-10   2022-06-10       2022-06-10
3083  2022-06-11   2022-06-11       2022-06-11
3084  2022-06-12   2022-06-12       2022-06-12
3085  2022-06-13   2022-06-13       2022-06-13
3086  2022-06-14   2022-06-14       2022-06-14
3087  2022-06-15          NaN       2022-06-15
3088  2022-06-16          NaN              NaN
3089  2022-06-17          NaN       2022-06-17
3090  2022-06-18          NaN              NaN
3091  2022-06-19          NaN              NaN
3092  2022-06-20          NaN              NaN
3093  2022-06-21   2022-06-21       2022-06-21
3094  2022-06-22          NaN       2022-06-22
3095  2022-06-23          NaN       2022-06-23
3096  2022-06-24          NaN       2022-06-24
3097  2022-06-25          NaN       2022-06-25
3098  2022-06-26          NaN       2022-06-26
3099  2022-06-27          NaN       2022-06-27
3100  2022-06-28          NaN       2022-06-28
3101  2022-06-29          NaN       2022-06-29
3102  2022-06-30          NaN       2022-06-30
3103  2022-07-01          NaN       2022-07-01
3104  2022-07-02          NaN       2022-07-02
3105  2022-07-03          NaN       2022-07-03
3106  2022-07-04          NaN       2022-07-04
3107  2022-07-05   2022-07-05       2022-07-05
3108  2022-07-06   2022-07-06       2022-07-06
3109  2022-07-07   2022-07-07       2022-07-07
3110  2022-07-08   2022-07-08       2022-07-08
3111  2022-07-09   2022-07-09       2022-07-09
3112  2022-07-10          NaN       2022-07-10
3113  2022-07-11          NaN              NaN
3114  2022-07-12          NaN              NaN
3115  2022-07-13          NaN              NaN
3116  2022-07-14          NaN              NaN
3117  2022-07-15          NaN              NaN
3118  2022-07-16          NaN       2022-07-16
3119  2022-07-17          NaN              NaN
3120  2022-07-18          NaN              NaN
3121  2022-07-19          NaN       2022-07-19
3122  2022-07-20          NaN       2022-07-20
3123  2022-07-21          NaN              NaN
3124  2022-07-22   2022-07-22       2022-07-22
3125  2022-07-22   2022-07-22       2022-07-22
3126  2022-07-23   2022-07-23       2022-07-23
3127  2022-07-24   2022-07-24       2022-07-24
3128  2022-07-25   2022-07-25       2022-07-25
3129  2022-07-26   2022-07-26       2022-07-26
3130  2022-07-27   2022-07-27       2022-07-27
3131  2022-07-28   2022-07-28       2022-07-28
3132  2022-07-29   2022-07-29       2022-07-29
3133  2022-07-30   2022-07-30       2022-07-30
3134  2022-07-31   2022-07-31       2022-07-31
3135  2022-08-01   2022-08-01       2022-08-01
3136  2022-08-02   2022-08-02       2022-08-02
3137  2022-08-03   2022-08-03       2022-08-03
3138  2022-08-04   2022-08-04       2022-08-04
3139  2022-08-05   2022-08-05       2022-08-05
3140  2022-08-06          NaN       2022-08-06
3141  2022-08-07          NaN              NaN
3142  2022-08-08          NaN              NaN
3143  2022-08-09          NaN       2022-08-09
3144  2022-08-10          NaN              NaN
3145  2022-08-11          NaN              NaN
3146  2022-08-12          NaN              NaN
3147  2022-08-13          NaN       2022-08-13
3148  2022-08-14          NaN       2022-08-14
3149  2022-08-15          NaN              NaN
3150  2022-08-16          NaN       2022-08-16
3151  2022-08-17          NaN       2022-08-17
3152  2022-08-18          NaN              NaN
3153  2022-08-19          NaN              NaN
3154  2022-08-20          NaN              NaN
3155  2022-08-21          NaN       2022-08-21
3156  2022-08-22          NaN              NaN
3157  2022-08-23          NaN              NaN
3158  2022-08-24          NaN              NaN
3159  2022-08-25          NaN              NaN
3160  2022-08-26   2022-08-26       2022-08-26
3161  2022-08-27          NaN       2022-08-27
3162  2022-08-28          NaN              NaN
3163  2022-08-29          NaN              NaN
3164  2022-08-30   2022-08-30       2022-08-30
3165  2022-08-31   2022-08-31       2022-08-31
3166  2022-09-01   2022-09-01       2022-09-01
3167  2022-09-02          NaN       2022-09-02
3168  2022-09-03          NaN       2022-09-03
3169  2022-09-04          NaN              NaN
3170  2022-09-05          NaN              NaN
3171  2022-09-06          NaN              NaN
3172  2022-09-07          NaN              NaN
3173  2022-09-08          NaN              NaN
3174  2022-09-09   2022-09-09       2022-09-09
3175  2022-09-10          NaN       2022-09-10
3176  2022-09-11          NaN              NaN
3177  2022-09-12          NaN              NaN
3178  2022-09-13          NaN              NaN
3179  2022-09-14   2022-09-14       2022-09-14
3180  2022-09-15   2022-09-15       2022-09-15
3181  2022-09-16   2022-09-16       2022-09-16
3182  2022-09-17          NaN       2022-09-17
3183  2022-09-18          NaN              NaN
3184  2022-09-19          NaN              NaN
3185  2022-09-20          NaN              NaN
3186         NaN          NaN       2022-09-21
3187         NaN   2022-06-16              NaN
3188         NaN   2022-06-20              NaN
3189         NaN   2022-07-21              NaN
3190         NaN   2022-08-15              NaN
3191         NaN   2022-08-20              NaN
3192         NaN   2022-08-25              NaN
3193         NaN   2022-08-29              NaN
3194         NaN   2022-09-08              NaN
3195         NaN   2022-09-13              NaN

As you can see the 'VIPR (Y-M-D)' column has some dates that are not matched at the very bottom. How can I fix this so all dates are matched in the corresponding rows?

CodePudding user response:

since your third dataframe includes all dates, use how="left". Additionally, in your second join, you're joining on a column which does have NaNs. Instead, you should join both df1 and df2 directly to df3:

merged = (
    df3
    .merge(df1, how="left", left_on="Date", right_on="VIPR (Y-M-D)")
    .merge(df2, how="left", left_on="Date", right_on="Viriciti (Y-M-D)")
)
  • Related