create pandas dataframe from URL

Question

This must be easy, but I am not able to get this dataframe in the correct form.

df = pd.read_json('https://wikimedia.org/api/rest_v1/metrics/pageviews/per-article/en.wikipedia.org/all-access/user/Python_(programming_language)/daily/20210101/20210501')

The expected columns are:

project, article,granularity,timestamp,access,agent,user,views

Yiannis · Accepted Answer · 2021-06-30 11:53:24Z

1

>>> df = pd.read_json('https://wikimedia.org/api/rest_v1/metrics/pageviews/per-article/en.wikipedia.org/all-access/user/Python_(programming_language)/daily/20210101/20210501')
>>> pd.concat([df.drop(['items'], axis=1), df['items'].apply(pd.Series)], axis=1)
          project                        article granularity   timestamp      access agent  views
0    en.wikipedia  Python_(programming_language)       daily  2021010100  all-access  user   7238
1    en.wikipedia  Python_(programming_language)       daily  2021010200  all-access  user   8449
2    en.wikipedia  Python_(programming_language)       daily  2021010300  all-access  user   8669
3    en.wikipedia  Python_(programming_language)       daily  2021010400  all-access  user  10688
4    en.wikipedia  Python_(programming_language)       daily  2021010500  all-access  user  11383
..            ...                            ...         ...         ...         ...   ...    ...
116  en.wikipedia  Python_(programming_language)       daily  2021042700  all-access  user   6125
117  en.wikipedia  Python_(programming_language)       daily  2021042800  all-access  user   6184
118  en.wikipedia  Python_(programming_language)       daily  2021042900  all-access  user   5960
119  en.wikipedia  Python_(programming_language)       daily  2021043000  all-access  user   5489
120  en.wikipedia  Python_(programming_language)       daily  2021050100  all-access  user   4297

[121 rows x 7 columns]
>>>

answered Jun 30, 2021 at 11:53

Yiannis

1632 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joao Over a year ago

Why the concat and drop ? It seems that df['items'].apply(pd.Series) produces the same result ?

Yiannis Over a year ago

It's just a common pandas pattern. It helps when you have columns, other than the nested one, that you want to keep

Vaebhav · Accepted Answer · 2021-06-30 11:58:56Z

You can utilise assign as well -


>>> df = pd.read_json('https://wikimedia.org/api/rest_v1/metrics/pageviews/per-article/en.wikipedia.org/all-access/user/Python_(programming_language)/daily/20210101/20210501')
>>> 
>>> 
>>> df.drop('items', 1).assign(**df['items'].apply(pd.Series))
          project                        article granularity   timestamp      access agent  views
0    en.wikipedia  Python_(programming_language)       daily  2021010100  all-access  user   7238
1    en.wikipedia  Python_(programming_language)       daily  2021010200  all-access  user   8449
2    en.wikipedia  Python_(programming_language)       daily  2021010300  all-access  user   8669
3    en.wikipedia  Python_(programming_language)       daily  2021010400  all-access  user  10688
4    en.wikipedia  Python_(programming_language)       daily  2021010500  all-access  user  11383
..            ...                            ...         ...         ...         ...   ...    ...
116  en.wikipedia  Python_(programming_language)       daily  2021042700  all-access  user   6125
117  en.wikipedia  Python_(programming_language)       daily  2021042800  all-access  user   6184
118  en.wikipedia  Python_(programming_language)       daily  2021042900  all-access  user   5960
119  en.wikipedia  Python_(programming_language)       daily  2021043000  all-access  user   5489
120  en.wikipedia  Python_(programming_language)       daily  2021050100  all-access  user   4297

[121 rows x 7 columns]

Collectives™ on Stack Overflow

create pandas dataframe from URL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related