'Merge multiple date columns and drop the not needed date columns
I have the following dataframe:
pd.Dataframe({'Col1': {0: '04/01/2021',
1: '04/01/2021',
2: '04/01/2021',
3: '04/01/2021',
4: '04/01/2021',
5: '05/01/2021',
6: '05/01/2021',
7: '05/01/2021',
8: '06/01/2021',
9: '06/01/2021',
10: '06/01/2021',
11: '06/01/2021',
12: '06/01/2021',
13: '06/01/2021',
14: '06/01/2021',
15: '06/01/2021',
16: '07/01/2021',
17: '07/01/2021',
18: '07/01/2021',
19: '07/01/2021',
20: '07/01/2021',
21: '07/01/2021',
22: '07/01/2021',
23: '08/01/2021',
24: '08/01/2021',
25: '08/01/2021',
26: '08/01/2021',
27: '08/01/2021',
28: '08/01/2021',
29: '08/01/2021',
30: '08/01/2021',
31: '08/01/2021',
32: '08/01/2021',
33: '08/01/2021',
34: '08/01/2021',
35: '08/01/2021',
36: '08/01/2021',
37: '08/01/2021',
38: '11/01/2021',
39: '11/01/2021',
40: '11/01/2021',
41: '11/01/2021',
42: '11/01/2021',
43: '11/01/2021',
44: '11/01/2021',
45: '11/01/2021',
46: '11/01/2021',
47: '11/01/2021',
48: '11/01/2021',
49: '11/01/2021',
50: '11/01/2021',
51: '11/01/2021',
52: '11/01/2021',
53: '11/01/2021',
54: '12/01/2021',
55: '12/01/2021',
56: '12/01/2021',
57: '12/01/2021',
58: '12/01/2021',
59: '12/01/2021',
60: '12/01/2021',
61: '12/01/2021',
62: '12/01/2021',
63: '12/01/2021',
64: '12/01/2021',
65: '12/01/2021',
66: '12/01/2021',
67: '12/01/2021',
68: '12/01/2021',
69: '12/01/2021',
70: '12/01/2021',
71: '13/01/2021',
72: '13/01/2021',
73: '13/01/2021',
74: '13/01/2021',
75: '13/01/2021',
76: '13/01/2021',
77: '13/01/2021',
78: '13/01/2021',
79: '13/01/2021',
80: '13/01/2021',
81: '13/01/2021',
82: '14/01/2021',
83: '14/01/2021',
84: '14/01/2021',
85: '14/01/2021',
86: '14/01/2021',
87: '14/01/2021',
88: '14/01/2021',
89: '14/01/2021',
90: '14/01/2021',
91: '14/01/2021',
92: '14/01/2021',
93: '14/01/2021',
94: '14/01/2021',
95: '14/01/2021',
96: '14/01/2021',
97: '14/01/2021',
98: '14/01/2021',
99: '14/01/2021',
100: '15/01/2021',
101: '15/01/2021',
102: '15/01/2021',
103: '15/01/2021',
104: '15/01/2021',
105: '15/01/2021',
106: nan,
107: nan},
'Col2': {0: 17.0,
1: 93.0,
2: 76.0,
3: 93.0,
4: 72.0,
5: 52.0,
6: 97.0,
7: 78.0,
8: 38.0,
9: 42.0,
10: 40.0,
11: 25.0,
12: 18.0,
13: 23.0,
14: 69.0,
15: 12.0,
16: 5.0,
17: 82.0,
18: 92.0,
19: 98.0,
20: 85.0,
21: 39.0,
22: 37.0,
23: 84.0,
24: 79.0,
25: 40.0,
26: 46.0,
27: 92.0,
28: 53.0,
29: 92.0,
30: 60.0,
31: 36.0,
32: 19.0,
33: 100.0,
34: 36.0,
35: 39.0,
36: 48.0,
37: 59.0,
38: 78.0,
39: 30.0,
40: 22.0,
41: 41.0,
42: 71.0,
43: 73.0,
44: 34.0,
45: 91.0,
46: 47.0,
47: 15.0,
48: 75.0,
49: 2.0,
50: 82.0,
51: 68.0,
52: 46.0,
53: 85.0,
54: 100.0,
55: 93.0,
56: 13.0,
57: 12.0,
58: 14.0,
59: 35.0,
60: 27.0,
61: 75.0,
62: 49.0,
63: 100.0,
64: 98.0,
65: 19.0,
66: 50.0,
67: 88.0,
68: 85.0,
69: 47.0,
70: 91.0,
71: 24.0,
72: 45.0,
73: 86.0,
74: 80.0,
75: 70.0,
76: 73.0,
77: 47.0,
78: 65.0,
79: 19.0,
80: 30.0,
81: 43.0,
82: 73.0,
83: 10.0,
84: 57.0,
85: 3.0,
86: 56.0,
87: 76.0,
88: 51.0,
89: 65.0,
90: 94.0,
91: 23.0,
92: 78.0,
93: 24.0,
94: 94.0,
95: 74.0,
96: 95.0,
97: 11.0,
98: 1.0,
99: 90.0,
100: 78.0,
101: 20.0,
102: 11.0,
103: 69.0,
104: 75.0,
105: 33.0,
106: nan,
107: nan},
'Col3': {0: '06/01/2021',
1: '06/01/2021',
2: '06/01/2021',
3: '06/01/2021',
4: '06/01/2021',
5: '06/01/2021',
6: '06/01/2021',
7: '07/01/2021',
8: '07/01/2021',
9: '07/01/2021',
10: '07/01/2021',
11: '07/01/2021',
12: '07/01/2021',
13: '07/01/2021',
14: '08/01/2021',
15: '11/01/2021',
16: '11/01/2021',
17: '11/01/2021',
18: '11/01/2021',
19: '11/01/2021',
20: '11/01/2021',
21: '11/01/2021',
22: '11/01/2021',
23: '11/01/2021',
24: '12/01/2021',
25: '12/01/2021',
26: '12/01/2021',
27: '12/01/2021',
28: '12/01/2021',
29: '12/01/2021',
30: '12/01/2021',
31: '12/01/2021',
32: '12/01/2021',
33: '12/01/2021',
34: '12/01/2021',
35: '12/01/2021',
36: '12/01/2021',
37: '12/01/2021',
38: '12/01/2021',
39: '12/01/2021',
40: '12/01/2021',
41: '13/01/2021',
42: '13/01/2021',
43: '13/01/2021',
44: '13/01/2021',
45: '06/01/2021',
46: '07/01/2021',
47: '07/01/2021',
48: '07/01/2021',
49: '07/01/2021',
50: '07/01/2021',
51: '07/01/2021',
52: '07/01/2021',
53: '08/01/2021',
54: '08/01/2021',
55: '08/01/2021',
56: '08/01/2021',
57: '08/01/2021',
58: '08/01/2021',
59: '08/01/2021',
60: '08/01/2021',
61: '08/01/2021',
62: '08/01/2021',
63: '08/01/2021',
64: '08/01/2021',
65: '08/01/2021',
66: '08/01/2021',
67: '08/01/2021',
68: '06/01/2021',
69: '06/01/2021',
70: '07/01/2021',
71: '07/01/2021',
72: '07/01/2021',
73: '07/01/2021',
74: '07/01/2021',
75: '07/01/2021',
76: '07/01/2021',
77: '08/01/2021',
78: '08/01/2021',
79: '08/01/2021',
80: '08/01/2021',
81: '08/01/2021',
82: '08/01/2021',
83: '08/01/2021',
84: '08/01/2021',
85: '08/01/2021',
86: '08/01/2021',
87: '08/01/2021',
88: '08/01/2021',
89: nan,
90: nan,
91: nan,
92: nan,
93: nan,
94: nan,
95: nan,
96: nan,
97: nan,
98: nan,
99: nan,
100: nan,
101: nan,
102: nan,
103: nan,
104: nan,
105: nan,
106: nan,
107: nan},
'Col4': {0: 40.0,
1: 45.0,
2: 63.0,
3: 10.0,
4: 52.0,
5: 71.0,
6: 24.0,
7: 44.0,
8: 10.0,
9: 64.0,
10: 43.0,
11: 5.0,
12: 34.0,
13: 63.0,
14: 86.0,
15: 81.0,
16: 81.0,
17: 4.0,
18: 25.0,
19: 68.0,
20: 48.0,
21: 68.0,
22: 33.0,
23: 32.0,
24: 70.0,
25: 10.0,
26: 11.0,
27: 70.0,
28: 44.0,
29: 75.0,
30: 80.0,
31: 52.0,
32: 67.0,
33: 27.0,
34: 38.0,
35: 49.0,
36: 26.0,
37: 23.0,
38: 95.0,
39: 9.0,
40: 29.0,
41: 47.0,
42: 19.0,
43: 21.0,
44: 88.0,
45: 81.0,
46: 95.0,
47: 62.0,
48: 32.0,
49: 75.0,
50: 82.0,
51: 39.0,
52: 43.0,
53: 55.0,
54: 70.0,
55: 50.0,
56: 82.0,
57: 5.0,
58: 2.0,
59: 89.0,
60: 100.0,
61: 43.0,
62: 61.0,
63: 43.0,
64: 68.0,
65: 41.0,
66: 76.0,
67: 49.0,
68: 12.0,
69: 36.0,
70: 13.0,
71: 54.0,
72: 64.0,
73: 66.0,
74: 19.0,
75: 45.0,
76: 66.0,
77: 51.0,
78: 47.0,
79: 92.0,
80: 90.0,
81: 79.0,
82: 69.0,
83: 67.0,
84: 72.0,
85: 13.0,
86: 50.0,
87: 12.0,
88: 69.0,
89: nan,
90: nan,
91: nan,
92: nan,
93: nan,
94: nan,
95: nan,
96: nan,
97: nan,
98: nan,
99: nan,
100: nan,
101: nan,
102: nan,
103: nan,
104: nan,
105: nan,
106: nan,
107: nan},
'Col5': {0: '12/01/2021',
1: '12/01/2021',
2: '12/01/2021',
3: '12/01/2021',
4: '12/01/2021',
5: '12/01/2021',
6: '12/01/2021',
7: '12/01/2021',
8: '12/01/2021',
9: '13/01/2021',
10: '13/01/2021',
11: '13/01/2021',
12: '13/01/2021',
13: '13/01/2021',
14: '13/01/2021',
15: '13/01/2021',
16: '13/01/2021',
17: '13/01/2021',
18: '13/01/2021',
19: '13/01/2021',
20: '14/01/2021',
21: '14/01/2021',
22: '14/01/2021',
23: '14/01/2021',
24: '14/01/2021',
25: '14/01/2021',
26: '14/01/2021',
27: '14/01/2021',
28: '14/01/2021',
29: '14/01/2021',
30: '14/01/2021',
31: '14/01/2021',
32: '14/01/2021',
33: '14/01/2021',
34: '14/01/2021',
35: '14/01/2021',
36: '14/01/2021',
37: '14/01/2021',
38: '15/01/2021',
39: '15/01/2021',
40: '15/01/2021',
41: '15/01/2021',
42: '15/01/2021',
43: '15/01/2021',
44: nan,
45: nan,
46: nan,
47: nan,
48: nan,
49: nan,
50: nan,
51: nan,
52: nan,
53: nan,
54: nan,
55: nan,
56: nan,
57: nan,
58: nan,
59: nan,
60: nan,
61: nan,
62: nan,
63: nan,
64: nan,
65: nan,
66: nan,
67: nan,
68: nan,
69: nan,
70: nan,
71: nan,
72: nan,
73: nan,
74: nan,
75: nan,
76: nan,
77: nan,
78: nan,
79: nan,
80: nan,
81: nan,
82: nan,
83: nan,
84: nan,
85: nan,
86: nan,
87: nan,
88: nan,
89: nan,
90: nan,
91: nan,
92: nan,
93: nan,
94: nan,
95: nan,
96: nan,
97: nan,
98: nan,
99: nan,
100: nan,
101: nan,
102: nan,
103: nan,
104: nan,
105: nan,
106: nan,
107: nan},
'Col6': {0: 466.0,
1: 421.0,
2: 497.0,
3: 487.0,
4: 426.0,
5: 421.0,
6: 408.0,
7: 461.0,
8: 470.0,
9: 419.0,
10: 492.0,
11: 406.0,
12: 424.0,
13: 496.0,
14: 475.0,
15: 431.0,
16: 457.0,
17: 449.0,
18: 466.0,
19: 458.0,
20: 491.0,
21: 484.0,
22: 404.0,
23: 474.0,
24: 459.0,
25: 421.0,
26: 461.0,
27: 484.0,
28: 493.0,
29: 496.0,
30: 439.0,
31: 465.0,
32: 414.0,
33: 449.0,
34: 490.0,
35: 416.0,
36: 432.0,
37: 472.0,
38: 465.0,
39: 428.0,
40: 422.0,
41: 414.0,
42: 471.0,
43: 450.0,
44: nan,
45: nan,
46: nan,
47: nan,
48: nan,
49: nan,
50: nan,
51: nan,
52: nan,
53: nan,
54: nan,
55: nan,
56: nan,
57: nan,
58: nan,
59: nan,
60: nan,
61: nan,
62: nan,
63: nan,
64: nan,
65: nan,
66: nan,
67: nan,
68: nan,
69: nan,
70: nan,
71: nan,
72: nan,
73: nan,
74: nan,
75: nan,
76: nan,
77: nan,
78: nan,
79: nan,
80: nan,
81: nan,
82: nan,
83: nan,
84: nan,
85: nan,
86: nan,
87: nan,
88: nan,
89: nan,
90: nan,
91: nan,
92: nan,
93: nan,
94: nan,
95: nan,
96: nan,
97: nan,
98: nan,
99: nan,
100: nan,
101: nan,
102: nan,
103: nan,
104: nan,
105: nan,
106: nan,
107: nan}})
Col1 is the master data column. I need to merge Col3 and Col5 dates, to the master column. The final dataframe should be the merging of Col3 and Col5, to Col1 (master date field), and the values of all those dates are maintained.
It will be ideal to automatically drop the date fields Col3 and Col5, and only the value fields maintained at the end.
Col1 (date) maps with values in Col2, Col3 (date) maps with values in Col4 and Col5 (date) maps with values in Col6.
Based on this, is there a simple Pandas code that can be applied to perform the above intended operation ?
Solution 1:[1]
I'm not sure what you mean with "merge" in this case. Your dates are not unique so I imagine you want to concatenate Col3/4 and Col5/6 to Col1/2 (renaming Col3 and Col5 in the process):
df = pd.DataFrame({'Col1': {0: '04/01/2021',
...
107: nan}})
df_12 = df[["Col1", "Col2"]].dropna()
df_34 = df[["Col3", "Col4"]].dropna()
df_34.columns = ["Col1", "Col4"]
df_56 = df[["Col5", "Col6"]].dropna()
df_56.columns = ["Col1", "Col6"]
df_new = pd.concat([df_12, df_34, df_56], ignore_index=True)
print(df_new)
Output:
Col1 Col2 Col4 Col6
0 04/01/2021 17.0 NaN NaN
1 04/01/2021 93.0 NaN NaN
2 04/01/2021 76.0 NaN NaN
3 04/01/2021 93.0 NaN NaN
4 04/01/2021 72.0 NaN NaN
.. ... ... ... ...
234 15/01/2021 NaN NaN 428.0
235 15/01/2021 NaN NaN 422.0
236 15/01/2021 NaN NaN 414.0
237 15/01/2021 NaN NaN 471.0
238 15/01/2021 NaN NaN 450.0
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Tranbi |