Convert partially numeric data values and average to fully numeric Python

WHOGONCHECKME 0
Viewed 29

Data looks like:

x = ['Range: $1,430,000 - $1,670,000', 'Range: $20,000 - $30,000', '$160,000']

though many more values. I am trying to remove all text and characters aside from numerical and average the numbers when there is a range. I have tried

x = x.astype(str).str.replace("$", "")
x = x.str.replace(",", "")
x = x.str.replace(" -", "").astype(int)

This removes all the text and characters but I don't know how to replace the 2 numbers in a range with the average of the 2 numbers.

I would like the data to end up as:

x = ['1550000', '25000', '160000']

Thanks for your help.

data-manipulation python
2 Answers
x = ['Range: $1,430,000 - $1,670,000', 'Range: $20,000 - $30,000', '$160,000']

avg_x = [item.replace("$", "").replace(",", "").replace(" -", "") for item in x]
avg_x = [(int(item.split()[1]) + int(item.split()[2]))/2 if item.split()[0] == 'Range:' else int(item) for item in avg_x]


>>> avg_x
[1550000.0, 25000.0, 160000]

It uses the same replacements you did. Then, for each item in the list, if the first element of the split is "Range:", it takes the average of the other two elements. If not, it keeps that first element.

Ignatius Reilly0

I managed to do it, though probably could have used much less lines with more experience and knowledge.

x = x.astype(str)
df[['A', 'B']] = x.str.split(' - ', 1, expand=True)
q = ['A', 'B']
for i in q:
  df[i] = df[i].str.replace("$", "")
  df[i] = df[i].str.replace("Range: ", "")
  df[i] = df[i].str.replace(",", "")
  df[i] = df[i].str.replace(" ", "")
#This is to replace all the 'None' values in columns A & B with 'NaN'
df[['A', 'B']] = df[['A', 'B']].fillna(value=pd.np.nan)
df['A'] = pd.to_numeric(df['A'], errors='coerce')
df['B'] = pd.to_numeric(df['B'], errors='coerce')
df['A'] = df['A'].dropna()
df['B'] = df['B'].dropna()
df['A'].astype('Int64')
df['B'].astype('Int64')
df['C'] = df[['A', 'B']].mean(axis=1)
df['C'] = df['C'].astype(int)
WHOGONCHECKME0
Related