How to append a column from one csv file to second csv (with different indices)

user_11700199 0
Viewed 9782

I am working on concatenating many csv files together and want to take one column, from a multicolumn csv, and append it as a new column in a second csv. The problem is that the columns have different numbers of rows so the new column that I am adding to the existing csv gets cut short once the row index from the existing csv is reached.

I have tried to read in the new column as a second dataframe and then add that dataframe as a new column to the existing csv.

df = pd.read_csv("Existing CSV.csv")
df2 = pd.read_csv("New CSV.csv", usecols = ['Desired Column'])
df["New CSV"] = df2

"Existing CSV" has 1200 rows of data while "New CSV" has 1500 rows. When I run the code, the 'New CSV" column is added to "Existing CSV", however, only the first 1200 rows of data are included.

Ideally, all 1500 rows from "New CSV" will be included and the 300 rows missing from "Existing CSV" will be left blank.

pandas csv python
1 Answers

By default, read_csv gives the resulting DataFrame an integer index, so I can think of a couple of options to try.

Setup

df = pd.read_csv("Existing CSV.csv")
df2 = pd.read_csv("New CSV.csv", usecols = ['Desired Column'])

Method 1: join

df = df.join(df2['Desired Column'], how='right')

Method 2: reindex_like and assign

df = df.reindex_like(df2).assign(**{'Desired Column': df2['Desired Column']})
user_80087760
Related