Pandas interpolation on specified column

Question

How would I interpolate monthly->daily for a single column in a dataframe?

Example DF:

df = pd.DataFrame({'date': date, 'one': np.random.randint(100, 200, date.size),
                  'two': np.random.randint(100, 200, date.size)})

    date        one two
0   1991-01-01  149 157
1   1991-02-01  173 160
2   1991-03-01  168 135
3   1991-04-01  141 174
4   1991-05-01  169 161
5   1991-06-01  186 132
6   1991-07-01  181 191
7   1991-08-01  108 102
8   1991-09-01  197 189
9   1991-10-01  105 174
10  1991-11-01  144 138

To interpolate to daily and drop the last date:

df.set_index('date').resample('D').interpolate()[:-1]

I want to output daily values for 'date' and 'one', and ignore 'two'. Do I need to break it up into steps or similar?

Answer 1

See whether this is what you want:

# Assuming your `date` column is in datetime format.  If not, convert it first
df['date'] = pd.to_datetime(df['date'])

df.set_index('date')[['one']].resample('D').interpolate()[:-1]

This selects only column one (as a Pandas Dataframe rather than Pandas series by using double square brackets), and keep index date, for the resampling and interpolation.

Result:

                   one
date                  
1991-01-01  149.000000
1991-01-02  149.774194
1991-01-03  150.548387
1991-01-04  151.322581
1991-01-05  152.096774
1991-01-06  152.870968
1991-01-07  153.645161
1991-01-08  154.419355
1991-01-09  155.193548
1991-01-10  155.967742
1991-01-11  156.741935
1991-01-12  157.516129
1991-01-13  158.290323
1991-01-14  159.064516
1991-01-15  159.838710
1991-01-16  160.612903
1991-01-17  161.387097
1991-01-18  162.161290
1991-01-19  162.935484
1991-01-20  163.709677
1991-01-21  164.483871
1991-01-22  165.258065
1991-01-23  166.032258
1991-01-24  166.806452
1991-01-25  167.580645
1991-01-26  168.354839
1991-01-27  169.129032
1991-01-28  169.903226
1991-01-29  170.677419
1991-01-30  171.451613
1991-01-31  172.225806
1991-02-01  173.000000
1991-02-02  172.821429
1991-02-03  172.642857
1991-02-04  172.464286
1991-02-05  172.285714
1991-02-06  172.107143
1991-02-07  171.928571
1991-02-08  171.750000
1991-02-09  171.571429
1991-02-10  171.392857
1991-02-11  171.214286
1991-02-12  171.035714
1991-02-13  170.857143
1991-02-14  170.678571
1991-02-15  170.500000
1991-02-16  170.321429
1991-02-17  170.142857
1991-02-18  169.964286
1991-02-19  169.785714
1991-02-20  169.607143
1991-02-21  169.428571
1991-02-22  169.250000
1991-02-23  169.071429
1991-02-24  168.892857
1991-02-25  168.714286
1991-02-26  168.535714
1991-02-27  168.357143
1991-02-28  168.178571
1991-03-01  168.000000
1991-03-02  167.129032
1991-03-03  166.258065
1991-03-04  165.387097
1991-03-05  164.516129
1991-03-06  163.645161
1991-03-07  162.774194
1991-03-08  161.903226
1991-03-09  161.032258
1991-03-10  160.161290
1991-03-11  159.290323
1991-03-12  158.419355
1991-03-13  157.548387
1991-03-14  156.677419
1991-03-15  155.806452
1991-03-16  154.935484
1991-03-17  154.064516
1991-03-18  153.193548
1991-03-19  152.322581
1991-03-20  151.451613
1991-03-21  150.580645
1991-03-22  149.709677
1991-03-23  148.838710
1991-03-24  147.967742
1991-03-25  147.096774
1991-03-26  146.225806
1991-03-27  145.354839
1991-03-28  144.483871
1991-03-29  143.612903
1991-03-30  142.741935
1991-03-31  141.870968
1991-04-01  141.000000
1991-04-02  141.933333
1991-04-03  142.866667
1991-04-04  143.800000
1991-04-05  144.733333
1991-04-06  145.666667
1991-04-07  146.600000
1991-04-08  147.533333
1991-04-09  148.466667
1991-04-10  149.400000
1991-04-11  150.333333
1991-04-12  151.266667
1991-04-13  152.200000
1991-04-14  153.133333
1991-04-15  154.066667
1991-04-16  155.000000
1991-04-17  155.933333
1991-04-18  156.866667
1991-04-19  157.800000
1991-04-20  158.733333
1991-04-21  159.666667
1991-04-22  160.600000
1991-04-23  161.533333
1991-04-24  162.466667
1991-04-25  163.400000
1991-04-26  164.333333
1991-04-27  165.266667
1991-04-28  166.200000
1991-04-29  167.133333
1991-04-30  168.066667
1991-05-01  169.000000
1991-05-02  169.548387
1991-05-03  170.096774
1991-05-04  170.645161
1991-05-05  171.193548
1991-05-06  171.741935
1991-05-07  172.290323
1991-05-08  172.838710
1991-05-09  173.387097
1991-05-10  173.935484
1991-05-11  174.483871
1991-05-12  175.032258
1991-05-13  175.580645
1991-05-14  176.129032
1991-05-15  176.677419
1991-05-16  177.225806
1991-05-17  177.774194
1991-05-18  178.322581
1991-05-19  178.870968
1991-05-20  179.419355
1991-05-21  179.967742
1991-05-22  180.516129
1991-05-23  181.064516
1991-05-24  181.612903
1991-05-25  182.161290
1991-05-26  182.709677
1991-05-27  183.258065
1991-05-28  183.806452
1991-05-29  184.354839
1991-05-30  184.903226
1991-05-31  185.451613
1991-06-01  186.000000
1991-06-02  185.833333
1991-06-03  185.666667
1991-06-04  185.500000
1991-06-05  185.333333
1991-06-06  185.166667
1991-06-07  185.000000
1991-06-08  184.833333
1991-06-09  184.666667
1991-06-10  184.500000
1991-06-11  184.333333
1991-06-12  184.166667
1991-06-13  184.000000
1991-06-14  183.833333
1991-06-15  183.666667
1991-06-16  183.500000
1991-06-17  183.333333
1991-06-18  183.166667
1991-06-19  183.000000
1991-06-20  182.833333
1991-06-21  182.666667
1991-06-22  182.500000
1991-06-23  182.333333
1991-06-24  182.166667
1991-06-25  182.000000
1991-06-26  181.833333
1991-06-27  181.666667
1991-06-28  181.500000
1991-06-29  181.333333
1991-06-30  181.166667
1991-07-01  181.000000
1991-07-02  178.645161
1991-07-03  176.290323
1991-07-04  173.935484
1991-07-05  171.580645
1991-07-06  169.225806
1991-07-07  166.870968
1991-07-08  164.516129
1991-07-09  162.161290
1991-07-10  159.806452
1991-07-11  157.451613
1991-07-12  155.096774
1991-07-13  152.741935
1991-07-14  150.387097
1991-07-15  148.032258
1991-07-16  145.677419
1991-07-17  143.322581
1991-07-18  140.967742
1991-07-19  138.612903
1991-07-20  136.258065
1991-07-21  133.903226
1991-07-22  131.548387
1991-07-23  129.193548
1991-07-24  126.838710
1991-07-25  124.483871
1991-07-26  122.129032
1991-07-27  119.774194
1991-07-28  117.419355
1991-07-29  115.064516
1991-07-30  112.709677
1991-07-31  110.354839
1991-08-01  108.000000
1991-08-02  110.870968
1991-08-03  113.741935
1991-08-04  116.612903
1991-08-05  119.483871
1991-08-06  122.354839
1991-08-07  125.225806
1991-08-08  128.096774
1991-08-09  130.967742
1991-08-10  133.838710
1991-08-11  136.709677
1991-08-12  139.580645
1991-08-13  142.451613
1991-08-14  145.322581
1991-08-15  148.193548
1991-08-16  151.064516
1991-08-17  153.935484
1991-08-18  156.806452
1991-08-19  159.677419
1991-08-20  162.548387
1991-08-21  165.419355
1991-08-22  168.290323
1991-08-23  171.161290
1991-08-24  174.032258
1991-08-25  176.903226
1991-08-26  179.774194
1991-08-27  182.645161
1991-08-28  185.516129
1991-08-29  188.387097
1991-08-30  191.258065
1991-08-31  194.129032
1991-09-01  197.000000
1991-09-02  193.933333
1991-09-03  190.866667
1991-09-04  187.800000
1991-09-05  184.733333
1991-09-06  181.666667
1991-09-07  178.600000
1991-09-08  175.533333
1991-09-09  172.466667
1991-09-10  169.400000
1991-09-11  166.333333
1991-09-12  163.266667
1991-09-13  160.200000
1991-09-14  157.133333
1991-09-15  154.066667
1991-09-16  151.000000
1991-09-17  147.933333
1991-09-18  144.866667
1991-09-19  141.800000
1991-09-20  138.733333
1991-09-21  135.666667
1991-09-22  132.600000
1991-09-23  129.533333
1991-09-24  126.466667
1991-09-25  123.400000
1991-09-26  120.333333
1991-09-27  117.266667
1991-09-28  114.200000
1991-09-29  111.133333
1991-09-30  108.066667
1991-10-01  105.000000
1991-10-02  106.258065
1991-10-03  107.516129
1991-10-04  108.774194
1991-10-05  110.032258
1991-10-06  111.290323
1991-10-07  112.548387
1991-10-08  113.806452
1991-10-09  115.064516
1991-10-10  116.322581
1991-10-11  117.580645
1991-10-12  118.838710
1991-10-13  120.096774
1991-10-14  121.354839
1991-10-15  122.612903
1991-10-16  123.870968
1991-10-17  125.129032
1991-10-18  126.387097
1991-10-19  127.645161
1991-10-20  128.903226
1991-10-21  130.161290
1991-10-22  131.419355
1991-10-23  132.677419
1991-10-24  133.935484
1991-10-25  135.193548
1991-10-26  136.451613
1991-10-27  137.709677
1991-10-28  138.967742
1991-10-29  140.225806
1991-10-30  141.483871
1991-10-31  142.741935

Edit

If you want to restore date as a data column (from index), you can do a .reset_index(), as follows:

df.set_index('date')[['one']].resample('D').interpolate()[:-1].reset_index()