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

user_6110371 0
Viewed 4472

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.

pandas xlrd python-3.x python
3 Answers

Just try with 

data2.columns=data.columns
data = pd.concat([data, data2])
user_79645270

concatanate the values and create new dataframe.

import numpy as np
pd.DataFrame(np.concatenate((df1.values,df2.values)),columns=df1.columns)
user_54395460

with concatenate one solution which i can think off is defining columns name and using your list one columns with list 2

Try with below

data = pd.concat([data, data2]), columns=data.columns)

Example

np.random.seed(100)
df1 = pd.DataFrame(np.random.randint(10, size=(2,3)), columns=list('ABF'))
print (df1)
df2 = pd.DataFrame(np.random.randint(10, size=(1,3)), columns=list('ERT'))
print (df2)

Output

A  B  F

0  8  8  3

1  7  7  0

E  R  T
0  4  2  5

Using the Columns of Df1 list

df = pd.DataFrame(np.concatenate([df1.values, df2.values]), columns=df1.columns)
print (df)

 A  B  F
0  8  8  3
1  7  7  0
2  4  2  5
user_107541380
Related