Getting nested cells when using dictionaries in pandas (lasio for .LAS files)

user_18348040 0
Viewed 58

I am using lasio (https://lasio.readthedocs.io/en/latest/index.html) to call out data within a .LAS file. It's an oil and gas drilling type file with data in the heading and in the body (called the curve). TL;DR on the lasio docs, but it reads the data as a pandas DataFrame. Hence me using a dictionary to assign the data.

This is an output of a lasio file in notepad: output of a lasio file in notepad

At the end, I need a file that has the UWI (unique well #), the depth and it's porosity reading. The UWI is one value but there are multiple values for the depth and porosity. So I need the UWI repeated. To complicate matters, not all of my files have the porosity data so I have had to screen for them too.

My code was going ok until I export it and see that in the csv, the cells are nested. The code reads in the values in a dictionary and I need the UWI duplicated for each depth value.

data = []  
df_global = pd.DataFrame(data)  
alias = ["DPHI", "DPHI_LS", "DPH8", "DPHZ", "DPHZ_LS", "DPOR_LS", "DPOR", "PORD", "DPHI_SCANNED", "SPHI"]  
for filename in all_files:  
        las = lasio.read(filename)  
        df = las.df().reset_index()  
        mnemonic = las.keys()  
        match = set(alias).intersection(mnemonic)  
        if len(match) != 0:  
                DEPT = df["DEPT"]  
                DPHI2 = df[match]  
                DPHI = DPHI2.iloc[:,0]    
                UWI = las.well.UWI.value  
                df_global = df_global.append({'UWI': UWI, 'DEPTH': DEPT, 'DPHI': DPHI}, ignore_index=True)  
df_global.to_csv('las_output.csv', index=False)

This is my output, note the nested rows.

output

I have tried

df.loc[:,"UWI"] = np.array(las.well.UWI.value*len(df.DEPT))

but the UWI value is just repeated and not put into rows.

pandas dictionary python
1 Answers

Problem

You are appending dictionaries to an already-existing DataFrame. Each dictionary contains a variety of types (an integer under the key UWI, and pandas Series under other keys). This is a very general operation, and pandas reacts by converting the Series contained within the dictionary to strings, which is what you are seeing in columns B and C in Excel.

This is also probably not the operation you want to do, which appears to be appending DataFrames (i.e. one per file) to an existing DataFrame (df_global). Pandas does not make this easy for existing DataFrames, for good reason.

Solution

This is much simpler if you create a Python list (data) containing DataFrames, then use pandas' concat function to create a single DataFrame as the last step. See below for an example. I have not tested the code, because you didn't include a minimal reproducible example, but hopefully it helps.

data = []  
alias = ["DPHI", "DPHI_LS", "DPH8", "DPHZ", "DPHZ_LS", "DPOR_LS", "DPOR", "PORD", "DPHI_SCANNED", "SPHI"]  
for filename in all_files:  
        las = lasio.read(filename)  
        df = las.df().reset_index()  
        mnemonic = las.keys()  
        match = set(alias).intersection(mnemonic)  
        if len(match) != 0:  
                columns_to_keep = [las.curves[0].mnemonic] + list(match)
                
                # Assign the single UWI value to a new column called "UWI"
                df['UWI'] = las.well.UWI.value
                columns_to_keep.append('UWI')
                
                data.append(df[columns_to_keep])

df_final = pd.concat(data, join='outer')  # join='outer' means that it will keep all of the different values found from `alias`

df_final.to_csv('las_output.csv', index=False)
user_5963280
Related