[FIXED] Query using just the CSV files maintaining foreign key relationship

Issue

I have a bunch of tables that have been exported as CSV files. They maintain some primary key/foreign key relationships and I would like to make a new CSV file view using these.

The databases/csv:

Replys.csv
-PostId
-ReplyId 
-ReplyText

Users.csv
-ReplyId
-UserId
-UserText

ReplyId is the primary key in Replys.csv and a foreign key in Users.csv.

For each User find the ReplyId Users.csv -> Replys.csv ReplyId.

To print each user, their ReplyId and ReplyText in SQL:

SELECT ReplyId, ReplyText
FROM Users, Replys
WHERE Users.ReplyId = Replys.ReplyID

How can I do this using just the CSV files?

Solution

You could perform an inner merge:

assuming replys is from replys.csv and users is from users.csv:

replys.merge(users, on='reply_id')

for a many to one relationship then you can perform a left merge:

replys.merge(users, on='reply_id', how='left')

There is a useful section in the docs if you're from SQL

You'd read the csvs like so:

replys = pd.read_csv('Replys.csv')
users = pd.read_csv('Users.csv')

The above assumes that the first line in the csvs contain a header line and are comma separated

Answered By - EdChum