Stack Overflow Asked by SOScodehelp on February 8, 2021
Hi I need help formatting a json file that I converted to a pandas dataframe.
Json looks like
{
"test":
{
"1":["test1_a", "test1_b", "test1_c"]
"2":["test2_a", "test2_b", "test2_c"]
"3":["test3_a", "test3_b", "test3_c"]
}
}
And I need this json to be converted to a pandas dataframe and for it to be printed like this:
col1 col2 col3
test1_a test1_b test1_c
test2_a test2_b test2_c
test3_a test3_b test3_c
How would I do this? I need it to be a pandas dataframe and need to define the column rows.
So far I have tried:
json_file = open(json_file_path, 'r')
data = json.load(json_file)
pandasDataframe = pd.Dataframe.from_dict(data)
print(pandasDataframe)
And it prints this, which I don’t want 🙁
1 ["test1_a", "test1_b", "test1_c"]
2 ["test2_a", "test2_b", "test2_c"]
3 ["test3_a", "test3_b", "test3_c"]
updated: when I do
pd.DataFrame(data['test'])
It looks like [not quite what I want, but it’s getting there]
1 2 3
0 test1_a test2_a test3_a
1 test1_b test2_b test3_b
2 test1_c test2_c test3_c
Update #2: when I transpose it looks like this:
0 2
1 test1_a test1_b test1_c
2 test2_a test2_b test2_c
3 test3_a test3_b test3_c
How would I get rid of the 0 and 2 at the top? And what does it mean?
Also how do I get rid of the 1,2,3 (aka the first column altogether)
desired output:
the col names (col1, col2, col3) need to be added, but don’t know how)
col1 col2 col3
test1_a test1_b test1_c
test2_a test2_b test2_c
test3_a test3_b test3_c
IIUC, you need add_prefix
import pandas as pd
pd.DataFrame(data['test']).add_prefix('col')
col1 col2 col3
0 test1_a test2_a test3_a
1 test1_b test2_b test3_b
2 test1_c test2_c test3_c
Answered by sushanth on February 8, 2021
You could try with:
pd.DataFrame(data['test']).T.rename(columns={0:'col1',1:'col2',2:'col3'})
Output:
col1 col2 col3
1 test1_a test1_b test1_c
2 test2_a test2_b test2_c
3 test3_a test3_b test3_c
Answered by MrNobody33 on February 8, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP