How to split a specific selected row to multiple rows using Python

Question

I have a sample dataframe

import pandas as pd
import numpy as np

data = {"Key" : ["First Row", "Sample sample first row: a Row to be splitted $ 369", "Sample second row : a Depreciation $ 458", "Last Row"],
        "Value1" : [365, 265.0, np.nan, 256],
        "value2" : [789, np.nan, np.nan, np.nan]
}


df = pd.DataFrame(data)

print(df)

 
                                Key                           Value1      value2
0                            First Row                          365.0      789.0
1   Sample sample first row: a Row to be splitted $ 369         265.0       NaN
2   Sample second row : a Depreciation $ 458                     NaN        NaN
3    Last Row                                                   256.0       NaN

I do know splitting any categories to multiple rows using Split cell into multiple rows in pandas dataframe

I'm unable to find splitting a row of strings at the selected part.

Desired output

              Key                  Value1   value2
0        First Row                  365.0    789.0
1   Sample sample first row:        265.0     NaN
2   a Row to be splitted $            369     NaN
3   Sample second row :               NaN     NaN
4   Depreciation $                    458     NaN
5    Last Row                       256.0     NaN

Answer 1

in 3 steps using .explode .str.extract() and str.replace()

df1 = df.assign(Key=df['Key'].str.split(':')).explode('Key')

df1['Value1'] = df1['Value1'].fillna(
                      df1['Key'].str.extract('\$\s(\d+)').astype(float)[0]
                                  )
df1['Key'] = df1['Key'].str.replace('(\$\s)(\d+)',r'\1',regex=True)

---

                        Key  Value1  value2
0                 First Row   365.0   789.0
1   Sample sample first row   265.0     NaN
1   a Row to be splitted $    265.0     NaN
2        Sample second row      NaN     NaN
2         a Depreciation $    458.0     NaN
3                  Last Row   256.0     NaN

Answer 2

split, explode, extract and update

We can split the column Key on one or more space characters which are preceded by the :, then explode the dataframe on Key, next extract the numbers from the Key column which are preceded by $ symbol and update the corresponding values in Value1

df1 = df.assign(Key=df['Key'].str.split(r'(?<=:)\s+')).explode('Key')
df1['Value1'].update(df1['Key'].str.extract(r'\$\s*(\d+)', expand=False).astype(float))

---

>>> df1
                          Key  Value1  value2
0                   First Row   365.0   789.0
1    Sample sample first row:   265.0     NaN
1  a Row to be splitted $ 369   369.0     NaN
2         Sample second row :     NaN     NaN
2        a Depreciation $ 458   458.0     NaN
3                    Last Row   256.0     NaN