I have two data frames, that I am trying to combine.
A json file with headers:
| category 1 | category 2 | category 3 | category 4 | |:-----------|------------:|:------------:|:------------:| | name1 | attribute1 | amount1 | other1 | | name2 | attribute2 | amount2 | other2 |
And an Excel file with data in the same format, but without headers:
|:-----------|------------:|:------------:|:------------:| | name3 | attribute3 | amount3 | other3 | | name4 | attribute4 | amount4 | other4 |
I am trying to achieve the data frame below:
| category 1 | category 2 | category 3 | category 4 | |:-----------|------------:|:------------:|:------------:| | name1 | attribute1 | amount1 | other1 | | name2 | attribute2 | amount2 | other2 | | name3 | attribute3 | amount3 | other3 | | name4 | attribute4 | amount4 | other4 |
My code:
import pandas as pd import json import xlrd data = pd.read_json('pandas_test.json', orient='split') data2 = pd.read_excel("guys2.xlsx", header=None) data = pd.concat([data, data2])
Problem: When I run my code, the combined data frame looks like this:
| category 1 | category 2 | category 3 | category 4 | 1 | 2 | 3 | 4 | |:-----------|------------:|:------------:|:------------:|:---------:|:----------:|:---------:|:---------:| | name1 | attribute1 | amount1 | other1 | NaN | NaN | NaN | NaN | | name2 | attribute2 | amount2 | other2 | NaN | NaN | NaN | NaN | | NaN | NaN | NaN | NaN | name3 | attribute3 | amount3 | other3 | | NaN | NaN | NaN | NaN | name4 | attribute4 | amount4 | other4 |
I have tried the concat function with a few attributes already like ignore_index=True
, but nothing worked so far.
Advertisement
Answer
Just try with
data2.columns=data.columns data = pd.concat([data, data2])