convert top and bottom depth intervals to one column with fixed sample rate

user_6306373 0
Viewed 59

I have the following csv file in this structure input file

| Top  |  Bottom| Value |
|:---- |:------:| -----:|
|1000  | 1003   | 3 |
|1004  | 1006   | 2 |
|1008  | 1010   | 5 |

I would like the result to be

| Top  |  Value |
|:---- |:------:|
| 1000 | 3      |
| 1000.5 | 3      |
| 1001 | 3      |
| 1001.5 | 3      |
| 1002 | 3      |
| 1002.5 | 3      |
| 1003 | 3      |
| 1003.5 | Nan      |
| 1004 | 2      |
| 1004.5 | 2      |
| 1005 | 2      |
| 1005.5| 2      |
| 1006 | 2      |
| 1006.5 | Nan      |
| 1007 | Nan     |
| 1007.5 | Nan      |
| 1008 | 5     |
| 1008.5 | 5      |
| 1009 | 5     |
| 1009.5 | 5      |
| 1010 | 5      |

I have tried this code:

df= 'D:\\Range.csv'
df = pd.read_csv(df,header=[0])
df = df.set_index('value')
print (df)
stacked = df.stack()
stacked=stacked.reset_index()
print(stacked)
python
1 Answers

Given an example dataframe df:

import sys
import numpy as np
from io import StringIO
txt = StringIO(
"""
Top|Bottom|Value
1000|1003|3 
1004|1006|2
1008|1010|5
""")

df = pd.read_csv(txt, sep="|")

df:

    Top  Bottom  Value
0  1000    1003      3
1  1004    1006      2
2  1008    1010      5

First concatenate dataframes obtained by applying lambda function to each row of df:

df_ranges = pd.concat(
    df.apply(lambda r: pd.DataFrame({"Top": np.arange(r.Top,r.Bottom+0.5,0.5), "Value":r.Value }), axis = 1).values
    )

Then create dataframe with index containing all values from entire global range:

df_idx = pd.DataFrame({"Top": np.arange(df_ranges.Top.min(), df_ranges.Top.max()+0.5, 0.5)})

Finally left join df_ranges to df_index:

df_idx.merge(df_ranges, on = "Top", how = "left")

Result is:

       Top  Value
0   1000.0    3.0
1   1000.5    3.0
2   1001.0    3.0
3   1001.5    3.0
4   1002.0    3.0
5   1002.5    3.0
6   1003.0    3.0
7   1003.5    NaN
8   1004.0    2.0
9   1004.5    2.0
10  1005.0    2.0
11  1005.5    2.0
12  1006.0    2.0
13  1006.5    NaN
14  1007.0    NaN
15  1007.5    NaN
16  1008.0    5.0
17  1008.5    5.0
18  1009.0    5.0
19  1009.5    5.0
20  1010.0    5.0
user_28050830
Related