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.
Just try with
data2.columns=data.columns
data = pd.concat([data, data2])