Dataframe index object

Question

I am converting the following json string into dataframe. A list of keys is supposed to be unique and represent a primary key column values. For col1 and col2 we also have values set.

{"col1": {"0": col1val0, "1": col1val1}, "col2": {"0" : col2val0, "1": col2val1}, "index": {"0": ["key1","key2",""], "1": ["key1","key2","key3"]}}

using

df = pd.read_json(json_string)

Question about my index - it is a dict in its original form but when converted to df, it becomes a part of the index that I can't seem to extract.

                index         col1        col2
0        [key1,key2,]     col1val0    col2val0
1    [key1,key2,key3]     col1val1    col2val1

I tried to copy index into a new column, transposing df, tried getting shape of the index - it only returns numerical part - {0,1} and shows up as Int64Index type despite showing [key1,key2,] part of the index when printed. How can I extract it? Preferably I'd like to create primary key columns pk1, pk2, pk3 and populate them with these values.

What's your expected output?

Ynjxsjmh
– Ynjxsjmh

2021-03-15 13:49:11 +00:00
Commented Mar 15, 2021 at 13:49 — Ynjxsjmh
– Ynjxsjmh, Commented Mar 15, 2021 at 13:49

perl · Accepted Answer · 2021-03-15 13:51:23Z

0

You can split index into columns by converting the values to list (making it a list of lists) and assigning those values to the respective columns:

df[['pk1', 'pk2', 'pk3']] = df['index'].to_list()

df

Output:

       col1      col2               index   pk1   pk2   pk3
0  col1val0  col2val0      [key1, key2, ]  key1  key2      
1  col1val1  col2val1  [key1, key2, key3]  key1  key2  key3

answered Mar 15, 2021 at 13:51

perl

9,9811 gold badge14 silver badges23 bronze badges

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

4 Comments

cleanoxi Over a year ago

Thanks for the tip! It didn't convert to list as expected but stack gave me enough to be able to determine the type of object and convert it using values.tolist() ('Series' object has no attribute 'to_list')

perl Over a year ago

@cleanoxi Cool, but Series does have to_list method, does it not? pandas.pydata.org/pandas-docs/stable/reference/api/…

perl Over a year ago

Ah, maybe you're on an older version of pandas... stackoverflow.com/questions/57854228/…

cleanoxi Over a year ago

Definitely on the older version - that explains it.

Collectives™ on Stack Overflow

Dataframe index object

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related