Skip to content
Advertisement

pandas.concat two data frames (one with and one without headers)

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])
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement