Pulling a Value from One CSV Based on a Value in Another

cities = Hash[CSV.read('lookup.csv', :col_sep => ' | ').to_a[1..-1]]

cities = {}
CSV.foreach('lookup.csv', :col_sep => ' | ', :headers => true, :return_headers => false) do |line|
  cities[line['City']] = line['City ID']  
end  

CSV.open('output.csv', "w", :headers => ['First Name', 'Last Name', 'City ID'], :write_headers => true) do |output|
  CSV.foreach('master.csv', :col_sep => ' | ', :headers => true, :return_headers => false) do |line|
    output << [line['First Name'], line['Last Name'], cities[line['City']]]
  end  
end

mcsv = csv.DictReader(filename)

rows = list(mcsv)

def get_value(myname, mypass, clm):
    for row in rows:
        if row['uname']==myname and row['pass'] == mypass:
            return row['e unit price']

def set_value(myname, mypass, clm, new_value):
    for row in rows:
        if row['uname']==myname and row['pass'] == mypass:
            row[clm] = new_value

def get_sum(myname, mypass, clm):
    esaved = 0
    for row in rows:
        if row['uname']==myname and row['pass'] == mypass:
            esaved += int(row[clm])
    return esaved

print('energy saved: ', get_sum(myname, mypass, 'energy saved'))
print('e unit price before: ', get_value(myname, mypass, 'e unit price'))

set_value(myname, mypass, 'e unit price', 201)
print('e unit price after: ', get_value(myname, mypass, 'e unit price'))

uname	pass	e unit price	energy saved
abc	123	100	10
cde	456	101	11
abc	123	100	13
fgh	789	102	12

Search one CSV file in another csv file and update column value in first csv

I would suggest using the csv module to do it because it makes handling them relatively easy. In this particular case I decided to use its DictReader and DictWriter objects because they allow the fields of each row to be accessed symbolically via a dictionary key which make it easier to keep track of what you're doing in the code.

The entire input.csv file is read into a list in memory because it needs to be traversed multiple times (once for each row in the search.csv file) and any updates made need to be store somewhere until they're written back out to a file.

import csv

IP, EXISTS, PROTOCOL = 'IP', 'Exists', 'Protocol'  # Field names referenced.

# Read entire input file into a list.
with open('input.csv', 'r', newline='') as inp:
    reader = csv.DictReader(inp)
    inputs = list(reader)

# Update input rows that match data in search.csv file.
with open('search.csv', 'r', newline='') as sea:
    sea_reader = csv.DictReader(sea)
    for row in sea_reader:
        protocol, ip = row[PROTOCOL], row[IP]
        for input_ in inputs:
            if input_[PROTOCOL] == protocol and input_[IP] == ip:  # Match?
                input_[EXISTS] = 'Yes'
                break

# Write updated input.csv data out into a file.
with open('input_updated.csv', 'w', newline='') as outp:
    fieldnames = inputs[0].keys()
    writer = csv.DictWriter(outp, fieldnames)
    writer.writeheader()
    writer.writerows(inputs)

print('done')

CSV Dictionary, comparing 2 CSV files and replacing values based on matching values

Break your code into smaller chunks for each part:

1. Read rows of "book1.csv" where Kilo="1" to file1
2. Read rows of "book2.csv" to file2
3. Replace values of "Beta" in file1 based on file2 values of Oscar
4. Write back to a new csv file

with open("book1.csv", "r") as infile:
    reader = csv.DictReader(infile)
    file1 = [row for row in reader if row["Kilo"]=='1']

with open("book2.csv") as infile:
    reader = csv.DictReader(infile)
    file2 = [row for row in reader]

output = list()
oscars = [row["Oscar"] for row in file2]
for row in file1:
    if row["Alfa"] in oscars:
        row["Beta"] = [r["Lima"] for r in file2 if r["Oscar"]==row["Alfa"]][0]
    output.append({new:row[old] for old,new in fieldnames_dict.items()})

with open("output.csv", "w", newline="") as outfile:
    writer = csv.DictWriter(outfile,fieldnames=list(fieldnames_dict.values()))
    writer.writeheader()
    for row in output:
        writer.writerow(row)

output.csv:

Beta_New,Echo_New,Foxtrot_New_ALL,Hotel_New,India_New,Charlie_New
X2,E2,F2,H2,I2,C2
J2,E1,F5,H5,I5,C5
A2,E6,F6,H6,I6,C6
S3,E8,F8,H8,I8,C8

R - Get specific cell in CSV from value in another cell?

We can pull the column after the filter step

library(dplyr)
data %>% 
  filter(primary.name == "Down syndrome" | other.names == "Down syndrome") %>%
  pull(dx)

---

Or another option is summarise (which can now return more than one row)

data %>%
  summarise(dx = dx[primary.name == "Down syndrome" |
                    other.names == "Down syndrome"]) %>%
  pull(dx)

However, it is better to use filter for understanding the code

---

If we use

data$dx %>%
    ...

The rhs of %>% only gets the value of that column and there is no way it can access the 'primary.name' or 'other.names' column in the dataset

Python how to search value in one csv based on another csv - pandas?

I believe what you want can be achieved using isin. This method is used to filter data frames by selecting rows with having a particular value in a particular column.

In your case, you can create a list that contains all the unique values of Listings['Item Number'], and then check which of the elements are present in inventoryValue['Item Number'], and return back a reduced dataframe:

my_list = listings['Item Number'].unique().tolist()
new_inventoryValue = inventoryValue[inventoryValue['Item Number'].isin(my_list)]

Which will return back a smaller dataframe (row-wise), with all the columns, but your 'Iterm Number'' column will have only the elements in my_list.

---

Related Topics