0

I am new to Python and Stack Overflow. I am trying to copy data from one Excel file to another Excel file using pandas and numpy.

Let's say, the first.csv contains:
ID Title Country Status Date Region
1 Project1 US Active 09/29/20 America
2 Project2 Brazil Active America
3 Project3 China Active Asia

and the second.csv contains:
ID Title Country Region Date Status Description
1 Project1 US America N/A Active zzz
4 Project4 Canada America N/A Active zzz
5 Project5 Africa Africa N/A Active zzz

In the second file, the Column Status is after Date where as in first file it is after Country. I want to copy the first.csv data to the second.csv file based on the column structure of the second.csv.

After copying I want my second.csv to look like this.
ID Title Country Region Date Status Description
1 Project1 US America N/A Active zzz
2 Project2 Brazil America N/A Active zzzzzzz
3 Project3 China Asia N/A Active zzzzzzzzzzz
4 Project4 Canada America N/A Active zzz
5 Project5 Africa Africa N/A Active zzz

Is there any way to merge/copy the file in this way in Python using numpy and pandas library?

karel
  • 5,489
  • 46
  • 45
  • 50
Nirakar Nepal
  • 9
  • 6
  • Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) – G. Anderson Sep 29 '21 at 21:23

1 Answers1

0

The pandas library makes this easy. Once you have both in memory as data frames, you can just append one to the other. The append will rearrange the columns to match the first df, and just keep empty any columns in csv1_df that aren't in csv2_df.

csv1_df = pd.read_csv('first.csv')
csv2_df = pd.read_csv('second.csv')
combined_df = csv2_df.append(csv1_df, ignore_index=True)
combined_df.to_csv('third.csv', header=True, mode='w')
MaMaG
  • 359
  • 1
  • 9
  • It kind a works.. But it created duplicate for ID 1/Project1. I was thinking of overriding the data with second.csv file – Nirakar Nepal Sep 29 '21 at 18:35
  • Oh in that case you want to do an outer join... https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html `csv1_df.join(csv2_df, how='outer')`. – MaMaG Sep 29 '21 at 19:00