Extract specific string data in csv (data frame) using Python

Question

I used Python to read a CSV file as DataFrame, I don't know how to write a code to extract the number e.g. 21 and 35 behind the word "interval", with a condition "win".

import pandas as pd
order = pd.read_csv('C:/Users/Desktop/order.csv')
order.rate.str.extractall(interval)

here is a sample data:

id  status                     rate

1,  good,       {"id": 101, "win": {"interval": 21, "pay_rate": 0.239}}

2,  good,       {"id": 1892, "win": {"interval": 35, "pay_rate": 0.769}}

3,  bad,        {"id": 153, "lose": {"interval": 39, "pay_rate": 0.369}}

BENY · Accepted Answer · 2018-02-14 21:51:17Z

Base on my experience , when reading from csv, your dict column is string , so We need convert it back firstly by using literal_eval from ast, then we need following steps

s=df.rate.apply(pd.Series).set_index('id').stack().apply(pd.Series)
s
Out[289]: 
           interval  pay_rate
id                           
101  win       21.0     0.239
1892 win       35.0     0.769
153  lose      39.0     0.369

Then we need slice out the condition you need

s.loc[(slice(None),'win'),:].interval
Out[301]: 
id       
101   win    21.0
1892  win    35.0
Name: interval, dtype: float64

Data :

from ast import literal_eval

df=pd.DataFrame({'id':[1,2,3],'status':['good','good','bad'],'rate':['{"id": 101, "win": {"interval": 21, "pay_rate": 0.239}}','{"id": 1892, "win": {"interval": 35, "pay_rate": 0.769}}','{"id": 153, "lose": {"interval": 39, "pay_rate": 0.369}}']})
df['rate'] = df['rate'].apply(literal_eval)

Collectives™ on Stack Overflow

Extract specific string data in csv (data frame) using Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related