So now I have a .csv file which is a df.to_csv stuff and It looks like this

I want to ask if there's someway to merge that dict stuff into the df. You can find the whole csv file here

Here is my code

import requests
import pandas as pd
from requests import api

params = {
    'access_key': 'YOUR_ACCESS_KEY'
}

api_result = requests.get(
    'http://api.marketstack.com/v1/tickers/aapl/eod', params)

api_response = api_result.json()
df = pd.DataFrame([api_response['data']][0])
# df = pd.concat([df, pd.DataFrame((df['eod'][:])[0])], axis=1) # df['eod'][:] is the dict
df.to_csv('stock.csv')

CodePudding user response：

I think you want to convert dict data to Series of pandas by each key.

There is so strong function in pandas. that is .apply(). You can use this function for each row/column.

In this case, you can use .apply(pd.Series) to convert data.

this is example code.

import pandas as pd

# read your csv file
df = pd.read_csv('stock.csv')

# your csv has two "eod" column that has same data, so I drop last eod column.
# if you want to use all eod columns, then make your on solution using below code.
# drop unexpected columns
df = df.drop(columns=['Unnamed: 0', 'eod.1'])

# apply(eval): To change the data of "eod" column from "str" type to "dict" type
# apply(pd.Series): To make pd.DataFrame from one Series with dict data
df2 = df['eod'].apply(eval).apply(pd.Series)

# merge origin df and new df2
result_df = pd.merge(df, df2, left_index=True, right_index=True)

# drop eod column, because we don't need "eod" column, anymore.
result_df = result_df.drop(columns='eod')

print(result_df)

You need change some line for the data from api server. maybe,, your stock.csv data is not equal with data from api server.