Interpolate pandas series with no nulls

122 views Asked by At

The Prototype Dataframe is missing consecutive numbers in the A Prime column. Once they have been filled in, the missing values in the B column need to be interpolated from A Prime.

After this has been completed, I can merge the Prototype Dataframe with the Primary Dataframe.

The interpolations are grouped by Prototype name.

What is the pythonic way to recreate this dataframe with missing values, interpolate the B column, and join the Prototype Dataframe to the Primary Dataframe?

        Prototype Dataframe
| Proto Name| A Prime |    B    |
|-----------|---------|---------|
| Alpha     | 3.26    | 0.16608 |
| Alpha     | 3.24    | 0.16575 |
| Alpha     | 3.22    | 0.16540 |
| Alpha     | 3.20    | 0.16506 |
| Alpha     | 3.18    | 0.16470 |
| Alpha     | 3.16    | 0.16434 |
| Alpha     | 3.14    | 0.16398 |
| Bravo     | 1.52    | 0.10759 |
| Bravo     | 1.50    | 0.10687 |
| Bravo     | 1.48    | 0.10614 |
| Bravo     | 1.46    | 0.10541 |
| Bravo     | 1.44    | 0.10469 |
| Bravo     | 1.42    | 0.10396 |

Below is the primary dataframe which I have merged on using a left join.

                        Primary Dataframe
|  Name  |   Date    |  A   | A Prime |    B    | Proto Name |
|--------|-----------|------|---------|---------|------------|
| Z3Pats | 6/1/2018  | 3.24 | 3.24    | 0.16575 | Alpha      |
| Z3Pats | 5/1/2018  | 3.23 | NaN     | NaN     | Alpha      |
| Z3Pats | 4/1/2018  | 3.21 | NaN     | NaN     | Alpha      |
| Z3Pats | 3/1/2018  | 3.20 | 3.20    | 0.16506 | Alpha      |
| Z3Pats | 2/1/2018  | 3.16 | 3.16    | 0.16434 | Alpha      |
| CO-119 | 5/1/2018  | 1.53 | NaN     | NaN     | Bravo      |
| CO-119 | 4/1/2018  | 1.51 | NaN     | NaN     | Bravo      |
| CO-119 | 3/1/2018  | 1.48 | 1.48    | 0.10614 | Bravo      |
| CO-119 | 2/1/2018  | 1.48 | 1.48    | 0.10614 | Bravo      |
| CO-119 | 1/1/2018  | 1.45 | NaN     | NaN     | Bravo      |
| CO-119 | 12/1/2017 | 1.44 | 1.44    | 0.10469 | Bravo      |
| CO-119 | 11/1/2017 | 1.41 | 1.41    | 0.10396 | Bravo      |
1

There are 1 answers

3
Dickster On

I use multiIndex, reindex, and interpolate:

data = {'A Prime': {0: 3.26,  1: 3.24,  2: 3.22,  3: 3.2,  4: 3.18,  5: 3.16,
  6: 3.14,  7: 1.52,  8: 1.5,  9: 1.48,  10: 1.46,  11: 1.44,  12: 1.42},
 'B': {0: 0.16608,  1: 0.16575,  2: 0.1654,  3: 0.16505999999999998,  4: 0.1647,  5: 0.16434,  6: 0.16398,  7: 0.10759,  8: 0.10687,  9: 0.10614000000000001,
  10: 0.10540999999999999,  11: 0.10469,  12: 0.10396}, 'Proto Name': {0: 'Alpha',
  1: 'Alpha',  2: 'Alpha', 3: 'Alpha',  4: 'Alpha',  5: 'Alpha',  6: 'Alpha',  7: 'Bravo',  8: 'Bravo',  9: 'Bravo',  10: 'Bravo',  11: 'Bravo',  12: 'Bravo'}}

df = pd.DataFrame(data)
df_ranges =  df[['Proto Name', 'A Prime']].groupby(['Proto Name']).agg(['min', 'max'])
df =df.set_index(['Proto Name','A Prime'])
print df_ranges

           A Prime       
               min    max
Proto Name               
Alpha       3.1400 3.2600
Bravo       1.4200 1.5200


l_idx = []
for i in range(len(df_ranges)):
    PN = df_ranges.index[i]
    min = df_ranges.iloc[i][0]
    max = df_ranges.iloc[i][1]
    l_idx.extend([(PN,i) for i in np.array(range(int(min*100),int(max*100)+1,1))/100.]) # make tuples to reindex with

idx = pd.MultiIndex.from_tuples(l_idx)
df = df.reindex(idx)

print df
print df.interpolate()

which gives

                  B
Alpha 3.14  0.16398
      3.15      NaN
      3.16  0.16434
      3.17      NaN
      3.18  0.16470
      3.19      NaN
      3.20  0.16506
      3.21      NaN
      3.22  0.16540
      3.23      NaN
      3.24  0.16575
      3.25      NaN
      3.26  0.16608
Bravo 1.42  0.10396
      1.43      NaN
      1.44  0.10469
      1.45      NaN
      1.46  0.10541
      1.47      NaN
      1.48  0.10614
      1.49      NaN
      1.50  0.10687
      1.51      NaN
      1.52  0.10759

                   B
Alpha 3.14  0.163980
      3.15  0.164160
      3.16  0.164340
      3.17  0.164520
      3.18  0.164700
      3.19  0.164880
      3.20  0.165060
      3.21  0.165230
      3.22  0.165400
      3.23  0.165575
      3.24  0.165750
      3.25  0.165915
      3.26  0.166080
Bravo 1.42  0.103960
      1.43  0.104325
      1.44  0.104690
      1.45  0.105050
      1.46  0.105410
      1.47  0.105775
      1.48  0.106140
      1.49  0.106505
      1.50  0.106870
      1.51  0.107230
      1.52  0.107590