Matching data from two csv files based on two columns and creating a new csv file with selected columns

Question

I have two csv files.

one.csv:

1, 12.1455675, -13.1287564, 23, 9, 4.5, 4
2, 12.5934593, -13.0856385, 14, 5, 9.7, 6
3, 12.0496204, -13.8938582, 14, 6, 3.4, 9
4, 12.1456084, -12.1939589, 45, 2, 3.4, 8

two.csv:

9, 12.0496, -13.8939, .3, 55
3, 12.1456, -13.1288, 3.4, 9

What I want to do is match the two csv files based on columns one and two. I want another csv file that has the matched columns 1 and 2, but also includes the corresponding 3rd column values from two.csv and 6th column values from one.csv. Like this:

12.0496, -13.8939, 55, 3.4
12.1456, -12.1288, 9, 4.5

I am unsure how to go about this especially when some of the values in two.csv are rounded.

Any help is greatly appreciated!

Does anyone know of a way to do this without using SQL?

user2483176
– user2483176

2013-06-24 17:57:53 +00:00
Commented Jun 24, 2013 at 17:57 — user2483176
– user2483176, Commented Jun 24, 2013 at 17:57

jfs · Accepted Answer · 2013-06-24 20:10:38Z

3

You could use pandas' io to read/write csv files and its database-style joining/merging capabilities to merge the files:

import pandas as pd

normalize = lambda x: "%.4f" % float(x) # round
df = pd.read_csv("one.csv", index_col=(0,1), usecols=(1, 2, 5),
                 header=None, converters=dict.fromkeys([1,2], normalize))
df2 = pd.read_csv("two.csv", index_col=(0,1), usecols=(1, 2, 4),
                  header=None, converters=dict.fromkeys([1,2], normalize))
result = df.join(df2, how='inner')
result.to_csv("output.csv", header=None) # write as csv

Result

12.0496,-13.8939,3.4,55
12.1456,-13.1288,4.5,9

edited Jun 24, 2013 at 20:10

answered Jun 24, 2013 at 19:38

jfs

417k210 gold badges1k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:15:42Z

1

This is a quite common question on SO.

As of myself, same answer: for a medium-term solution, import in a DB, then perform a query using a JOIN ...

Try a search: https://stackoverflow.com/search?q=combining+csv+python

edited May 23, 2017 at 12:15

CommunityBot

11 silver badge

answered Jun 24, 2013 at 15:33

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

6 Comments

user2483176 Over a year ago

I haven't been able to find any that match based on two columns and deal with the rounding issue though.

Sylvain Leroux Over a year ago

My answer is still valid thought. This is not (only) a joke: Perform a join on two columns. In addition, you could even do some calculations at SQL level...

user2483176 Over a year ago

Could you possibly point me to an example? I am not familiar with join in python.

Sylvain Leroux Over a year ago

I wasn't taking about a "join in Python" but instead of using Python to perform a SQL join: Here is an example: zetcode.com/db/sqlite/joins You will find many ressources on the internet. As a side note, standard Python distributions embed Sqlite3 so you have no extra lib to install.

user2483176 Over a year ago

Thanks. Sorry I am not familiar with SQL at all.. do I need to convert my csv files to tables?

|

Collectives™ on Stack Overflow

Matching data from two csv files based on two columns and creating a new csv file with selected columns

2 Answers 2

Result

Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Result

Comments

6 Comments

Linked

Related