Merge 2 csv files rows

Question

So i'm trying to predict the winner of a sport game, and i have 2 CSV files. One with the current year statistics and the other with last years statistics.

I would like to merge them but only with the colums from the first file:

So that if the first table has columns ['Away','Home','Result']

and the second one has ['Away','Home','Match-Rating']

the result would contain ['Away','Home','Result'] and the 'Result' column would contain 0 or other default value if not found in second CSV.

I tried :

data = panda.read_csv('PremierLeagueDataSet/19-20.csv')
display(data.head())
data2= panda.read_csv('PremierLeagueDataSet/18-19.csv')
data.append(data2)

but gives me a warning and doesn't do the wanted concatenation

FutureWarning: Sorting because non-concatenation axis is not aligned. A future version of pandas will change to not sort by default.

['Div', 'Date', 'Time', 'HomeTeam', 'AwayTeam'] ==> 1st df ['Div', 'Date', 'HomeTeam', 'AwayTeam'] ==> 2nd df And i would like my result to be just like 1st df but for 'Time' to have a generic value — Seles Calin
– Seles Calin, Commented Jan 3, 2020 at 16:52

Valdi_Bo · Accepted Answer · 2020-01-03 21:15:30Z

2

To block data2.Match-Rating from appending, invoke append passing data2 with column names to be included:

data.append(data2[['Away', 'Home']], ignore_index=True, sort=False)\
    .replace(np.nan, '')

As you can see, I added ignore_index=True to avoid repeating indices. I added also sort=False to avoid a warning concerning planned changes in future versions.

I added also replace to change NaN values into empty strings.

edited Jan 3, 2020 at 21:15

answered Jan 3, 2020 at 21:08

Valdi_Bo

31.1k4 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge 2 csv files rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related