Add column from another csv file

user_9780369 0
Viewed 691

I have some csv files, lets say now I have 3 files in a single folder with three columns each file.

1.csv                2.csv                3.csv

A    B    C        A    B    C        A    B    C

5   23    56       5    43   23       5    65   08
10  31    77       10   76   66       10   34   72
20  33    98       20   39   28       20   23   64
30  18    26       30   27   39       30   73   92

I want to make a new csv file with A column and add only B columns from another csv files by looping, like below:

desired result:

new.csv

A    B     B    B
5    23    43   65
10   31    76   34
20   33    39   23
30   18    27   73

but I have failed.

This is my current code:

import pandas as pd
import numpy as np
import csv
import glob
import os 

path = "C:/Users/SYIFAAZRA/Documents/belajar_wradlib/learning/" 
os.chdir(path) 
file = glob.glob("*.csv") 
one = { 'A' : ['5','10','20','30'] } 
i = 1 
for f in file: 
  i = i+1 
  col_names = ['B', 'C'] 
  df = pd.read_csv(f, delimiter=',',usecols=[1, 2], names=col_names) 
  df = pd.DataFrame(one) 
  df['B'] = pd.Series(df) 
  print(df)
pandas csv python
2 Answers

You're going to want to merge your dataframes on the key 'A', since it exists in all of your files. I recommend moving the creation of your df before the loop.

df = pd.DataFrame(one) 
for f in file: 
  i = i+1 
  col_names = ['B', 'C'] 
  df_dummy = pd.read_csv(f, delimiter=',',usecols=[1, 2], names=col_names) 
  df.merge(df_dummy['B'],left_on='A',right_on='A',suffixes=('_left','_right'))

Note that you may need to clean up the names of your columns, depending on what you ultimately intend to do.

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html#pandas.DataFrame.merge

user_97436950

Leaving out the reading of csv files as it is not related to the question and it's easier to have a complete minimal example :

csv1=pd.DataFrame(np.array([[5,23,56],[10,31,77],[20,33,98]]), columns=['a','b','c'])
csv2=pd.DataFrame(np.array([[5,43,23],[10,76,66],[20,39,28]]), columns=['a','b','c'])
csv3=pd.DataFrame(np.array([[5,65,8],[10,34,72],[20,23,64]]), columns=['a','b','c'])
 
df1= csv1.iloc[:,:2]
df1['b1']=csv2.iloc[:,1]
df1['b2']=csv3.iloc[:,1]
df1

enter image description here

Your second question below is about many files. If the number of files is not massive I would split the operation into two loops. One where you read the files into a list of dataframes, another where you aggregate them into one dataframe.

 path = "C:/Users/SYIFAAZRA/Documents/belajar_wradlib/learning/" 
 os.chdir(path) 
 files = glob.glob("*.csv") 
 aa=[]
 for f  in files:
     aa.append(  pd.read_csv(f)) 

 df = aa[0].iloc[:,:2]  # makes first two columns AB

 for i,a in enumerate(aa[1:]):        # go through the remaining dataframes
     df[str(i)] = a.iloc[:,1]    # name the remaining columns b1,b2,b3...

All of this is not very elegant, but I have trouble remembering the elegant solutions in pandas. I prefer simple to read and understand.

user_9066930
Related