Odtnhj

I have this dataset.

 "date": "2018-01-01", 
 "body": "some txt", 
 "id": 111, 
 "sentiment": null
, 

 "date": "2018-01-02", 
 "body": "some txt", 
 "id": 112, 
 "sentiment": 
 "basic": "Bearish"
 

I want to read this with pandas, and change the column sentiment foreach rows differents from null.

When I do this:

pd.read_json(path)

this is the result that I get:

body ... sentiment
0 None
1 u'basic': u'Bullish'

I don't want to have u'basic': u'Bullish' but only the value of basic.
So to find the correct rows I use

df.loc[self.df['sentiment'].isnull() != True, 'sentiment'] = (?)

and it work, but I don't know what I have to put instead of (?)

I've tried this but doens't work

df.loc[self.df['sentiment'].isnull() != True, 'sentiment'] = df['sentiment']['basic]

Any ideas? Thanks

You can try:

mask = df['sentiment'].notnull()
df.loc[mask, 'sentiment'] = df.loc[mask, 'sentiment'].apply(lambda x: x['basic'])

You can do this:

df = pd.read_json(path) # creates the dataframe with dict objects in sentiment column 
pd.concat([df.drop(['sentiment'], axis=1), df['sentiment'].apply(pd.Series)], axis=1) # create new columns for each sentiment type

So for example, if your json is :

[
 "date": "2018-01-01", 
 "body": "some txt", 
 "id": 111, 
 "sentiment": null
, 

 "date": "2018-01-02", 
 "body": "some txt", 
 "id": 112, 
 "sentiment": 
 "basic": "Bearish"
 
,

 "date": "2018-01-03", 
 "body": "some other txt", 
 "id": 113, 
 "sentiment": 
 "basic" : "Bullish",
 "non_basic" : "Bearish"
 
]

df after line 1:

 body date id sentiment
0 some txt 2018-01-01 111 None
1 some txt 2018-01-02 112 'basic': 'Bearish'
2 some other txt 2018-01-03 113 'basic': 'Bullish', 'non_basic': 'Bearish'

df after line 2:

 body date id basic non_basic
0 some txt 2018-01-01 111 NaN NaN
1 some txt 2018-01-02 112 Bearish NaN
2 some other txt 2018-01-03 113 Bullish Bearish

HTH.

fillna + pop + join

Here's an extendible solution which avoids row-wise apply and converts an arbitrary number of keys to series:

df = pd.DataFrame('body': [0, 1],
 'sentiment': [None, u'basic': u'Bullish'])

df['sentiment'] = df['sentiment'].fillna(pd.Series([]*len(df.index), index=df.index))

df = df.join(pd.DataFrame(df.pop('sentiment').values.tolist()))

print(df)

 body basic
0 0 NaN
1 1 Bullish