I have a Dataframe that has date on it and I resample it monthly
**(T1)**
date_gr p
0 2017-03 24122.818182
1 2017-04 29696.000000
2 2017-05 37135.500000
3 2017-06 42871.555556
4 2017-07 46941.600000
5 2017-08 46963.750000
6 2017-09 40710.714286
7 2017-10 31212.200000
8 2017-11 28834.750000
9 2017-12 29319.666667
10 2018-01 28833.250000
11 2018-02 29657.800000
12 2018-03 28773.071429
13 2018-04 30049.142857
14 2018-05 34283.750000
15 2018-06 43694.222222
16 2018-07 51136.500000
17 2018-08 45297.250000
18 2018-09 39780.833333
19 2018-10 32073.600000
20 2018-11 28176.000000
21 2018-12 28315.250000
22 2019-01 28213.500000
23 2019-02 28886.500000
24 2019-03 26971.428571
25 2019-04 27644.875000
26 2019-05 38581.500000
27 2019-06 46501.857143
28 2019-07 50121.333333
29 2019-08 48226.250000
30 2019-09 42919.800000
31 2019-10 34589.571429
32 2019-11 29877.000000
33 2019-12 30223.000000
34 2020-01 30932.666667
35 2020-02 31630.800000
36 2020-03 27894.000000
37 2020-04 29523.000000
38 2020-05 40462.400000
39 2020-06 50798.428571
40 2020-07 51814.200000
41 2020-08 48111.714286
42 2020-09 46026.750000
43 2020-10 35544.000000
Now I need to create a new column and assign every monthly value you see above to it , base on month.I mean if value is for 2019-10 , The new column has 2019-10 value for all October days from 1 to 31.
for example we have :
**(T2)**
date_gr p_ins
0 2019-10-01 2122.818182
1 2019-10-02 2696.000000
2 2019-10-03 3135.500000
3 2019-10-04 4871.555556
4 2019-10-05 4941.600000
5 2019-10-06 4963.750000
6 2019-10-07 4710.714286
7 2019-10-08 3212.200000
8 2019-10-09 2834.750000
9 2019-10-10 2319.666667
10 2019-10-11 2833.250000
11 2019-10-12 2657.800000
12 2019-10-13 2773.071429
13 2019-10-14 3049.142857
14 2019-10-15 3283.750000
15 2019-10-16 4694.222222
16 2019-10-17 5136.500000
17 2019-10-18 4297.250000
18 2019-10-19 3780.833333
19 2019-10-20 3073.600000
20 2019-11-01 2176.000000
21 2019-11-02 2315.250000
22 2019-11-03 2213.500000
23 2019-11-04 2886.500000
24 2019-11-05 2971.428571
25 2019-11-06 2644.875000
26 2019-11-07 3581.500000
27 2019-11-08 4501.857143
28 2019-11-09 5121.333333
29 2019-11-10 4226.250000
30 2019-11-11 4919.800000
31 2019-11-12 3589.571429
32 2019-11-13 2877.000000
33 2019-11-14 3223.000000
34 2019-11-15 3932.666667
35 2019-11-16 3630.800000
36 2019-11-17 2894.000000
37 2019-11-18 2523.000000
38 2019-11-19 4462.400000
39 2019-11-20 5798.428571
We need to find the month value in (T2) that its month matche (T1)'s month and assign its value to every day of that month.We must do this for every month and day.
output:
date_gr p_ins p
0 2019-10-01 2122.818182 34589.571429
1 2019-10-02 2696.000000 34589.571429
2 2019-10-03 3135.500000 34589.571429
3 2019-10-04 4871.555556 34589.571429
4 2019-10-05 4941.600000 34589.571429
5 2019-10-06 4963.750000 34589.571429
6 2019-10-07 4710.714286 34589.571429
7 2019-10-08 3212.200000 34589.571429
8 2019-10-09 2834.750000 34589.571429
9 2019-10-10 2319.666667 34589.571429
10 2019-10-11 2833.250000 34589.571429
11 2019-10-12 2657.800000 34589.571429
12 2019-10-13 2773.071429 34589.571429
13 2019-10-14 3049.142857 34589.571429
14 2019-10-15 3283.750000 34589.571429
15 2019-10-16 4694.222222 34589.571429
16 2019-10-17 5136.500000 34589.571429
17 2019-10-18 4297.250000 34589.571429
18 2019-10-19 3780.833333 34589.571429
19 2019-10-20 3073.600000 34589.571429
20 2019-11-01 2176.000000 29877.000000
21 2019-11-02 2315.250000 29877.000000
22 2019-11-03 2213.500000 29877.000000
23 2019-11-04 2886.500000 29877.000000
24 2019-11-05 2971.428571 29877.000000
25 2019-11-06 2644.875000 29877.000000
26 2019-11-07 3581.500000 29877.000000
27 2019-11-08 4501.857143 29877.000000
28 2019-11-09 5121.333333 29877.000000
29 2019-11-10 4226.250000 29877.000000
30 2019-11-11 4919.800000 29877.000000
31 2019-11-12 3589.571429 29877.000000
32 2019-11-13 2877.000000 29877.000000
33 2019-11-14 3223.000000 29877.000000
34 2019-11-15 3932.666667 29877.000000
35 2019-11-16 3630.800000 29877.000000
36 2019-11-17 2894.000000 29877.000000
37 2019-11-18 2523.000000 29877.000000
38 2019-11-19 4462.400000 29877.000000
39 2019-11-20 5798.428571 29877.000000
How can I do that in pandas? Thank you in advance for your help.
There are several approaches. One thing you could do is take your monthly data and convert each date to the start of the month. Then you can use merge_asof to join the two data frames.
# Add day one to each monthly value
t1.date_gr = t1.date_gr + '-01'
# Convert to datetime objects
t1.date_gr = pd.to_datetime(t1.date_gr)
t2.date_gr = pd.to_datetime(t2.date_gr)
# merge t2 to closest previous date in t1
pd.merge_asof(t2, t1, on='date_gr', direction='backward')
Output
date_gr p_ins p
0 2019-10-01 2122.818182 34589.571429
1 2019-10-02 2696.000000 34589.571429
2 2019-10-03 3135.500000 34589.571429
3 2019-10-04 4871.555556 34589.571429
4 2019-10-05 4941.600000 34589.571429
5 2019-10-06 4963.750000 34589.571429
6 2019-10-07 4710.714286 34589.571429
7 2019-10-08 3212.200000 34589.571429
8 2019-10-09 2834.750000 34589.571429
9 2019-10-10 2319.666667 34589.571429
10 2019-10-11 2833.250000 34589.571429
11 2019-10-12 2657.800000 34589.571429
12 2019-10-13 2773.071429 34589.571429
13 2019-10-14 3049.142857 34589.571429
14 2019-10-15 3283.750000 34589.571429
15 2019-10-16 4694.222222 34589.571429
16 2019-10-17 5136.500000 34589.571429
17 2019-10-18 4297.250000 34589.571429
18 2019-10-19 3780.833333 34589.571429
19 2019-10-20 3073.600000 34589.571429
20 2019-11-01 2176.000000 29877.000000
21 2019-11-02 2315.250000 29877.000000
22 2019-11-03 2213.500000 29877.000000
23 2019-11-04 2886.500000 29877.000000
24 2019-11-05 2971.428571 29877.000000
25 2019-11-06 2644.875000 29877.000000
26 2019-11-07 3581.500000 29877.000000
27 2019-11-08 4501.857143 29877.000000
28 2019-11-09 5121.333333 29877.000000
29 2019-11-10 4226.250000 29877.000000
30 2019-11-11 4919.800000 29877.000000
31 2019-11-12 3589.571429 29877.000000
32 2019-11-13 2877.000000 29877.000000
33 2019-11-14 3223.000000 29877.000000
34 2019-11-15 3932.666667 29877.000000
35 2019-11-16 3630.800000 29877.000000
36 2019-11-17 2894.000000 29877.000000
37 2019-11-18 2523.000000 29877.000000
38 2019-11-19 4462.400000 29877.000000
39 2019-11-20 5798.428571 29877.000000