CODEWITHSUNDEEP
pythoncsv
Torb
Torb

Reputation: 279

How to access a certain entry in a csv file in python?

I have CSV file hello.csv with a cik-numbers in the third column. Now I have second file cik.csv where are the related companies (in column 4) to the cik-numbers (in column 1) and I want to have a list with the related companies to the cik-numbers in hello.csv.

I tried it with a loop:

with open('hello.csv', 'r', encoding="latin-1") as csvfile:
    readCSV=csv.reader(csvfile, delimiter=',')
    list1=list(readCSV)
    b=-1
    for j in list1:
        b=b+1
        if b>0:
            cik=j[2] 
            with open('cik.csv', 'r', encoding="latin-1") as csvfile:
                readCSV=csv.reader(csvfile, delimiter=',')
                list2=list(readCSV)

I don't now how find my cik in the csv-file cik.csv and get the related company. Can I use pandas there ?

Upvotes: 0

Views: 62

Answers (1)

Asmus
Asmus

Reputation: 5247

Use pandas to read in the two .csv files and map the respective values:

import pandas as pd

## create some dummy data
hello_csv="""
a,b,cik_numbers,d
'test',1,12, 5
'var', 6, 2, 0.1
"""

cik_csv="""
cik_numbers,b,c,related_companies
12,1,12, 'Apple'
13,6,20, 'Microsoft'
 2,1,712,'Google'
"""

## note: you would rather give this a path to your csv files
#  like: df_hello=pd.read_csv('/the/path/to/hello.csv')

df_hello=pd.read_csv(pd.compat.StringIO(hello_csv))
df_cik=pd.read_csv(pd.compat.StringIO(cik_csv))

## and add a new column to df_hello based on a mapping of cik_numbers
df_hello['related_companies'] = df_hello['cik_numbers'].map(df_cik.set_index('cik_numbers')['related_companies'])

print(df_hello)

yields:

        a  b  cik_numbers    d related_companies
0  'test'  1           12  5.0           'Apple'
1   'var'  6            2  0.1          'Google'

Upvotes: 1

Related Questions