0

I have two csv files, first one (master file) contain Keys and its values and the second one (daily file) contain key and some other columns.

Example file (master file)

  Key  value
   A     1
   B     2
   c     3

Example file (daily file)

 Name  Key  date
 Red   A    dd/mm/yy
 Blue  B    dd/mm/yy
 Pink  C    dd/mm/yy

The outcome file I need looks like this:

 Name   Key   value   date
 Pink    C      3     dd/mm/yy
 Blue    B      2     dd/mm/yy
 Red     A      1     dd/mm/yy

I've tried using dataframe and creating dicts from external file or dataframe but not idea how can I do the lookup base on key and obtain its value.

Improve this question
3
  • What is the logic that would take inputs of C, 3 and Pink, C, dd/mm/yy and merge them to become Red, C, 3, dd/mm/yy? Commented Nov 21, 2019 at 11:05
  • Sorry my mistake C is always Pink as A is always Red, I just wanted to illustrate that it doesn't need to follow the same order. Commented Nov 21, 2019 at 11:41
  • I have modified the question base on the valid answer since I found merge approach easier to implement for my code Commented Nov 22, 2019 at 2:30

3 Answers 3

Reset to default
2

Use this code, may be it will help you and you will get your desire output

# import Library
import pandas as pd 

# Create Dataframe as like as imported CSV file for your (master file) and (daily file)
masterfile = {'key':['A','B','C'], 'value':[1,2,3]}
dailyfile = {'Name':['Red','Blue','Pink'],'key':['A','B','C'], 'date':['dd/mm/yy','dd/mm/yy','dd/mm/yy']}

masterfil = pd.DataFrame(data=masterfile, index=None)
dailyfile = pd.DataFrame(data=dailyfile, index=None)

# Change the order of dataframe(descending order)
df_masterfil = masterfil.sort_values(by='key', ascending=False)
df_dailyfile = dailyfile.sort_values(by='key', ascending=False)

# merge the both dataframe or csv file
df=df_dailyfile.merge(df_masterfil)
df=df[['key', 'value', 'date']]

dailyfile['Name']

# As like your dataset
result= pd.concat([dailyfile['Name'], df1], axis=1)
result

enter image description here

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

1 Comment

It works as well, the problem is that my files are not exactly as it described in the question (my mistake, just I wanted to put more clear examples). concat options has given many problem with different format files. Thanks anyway!
1

If your csv files as you've described, you can do this very easily with Pandas to merge records from both files with matching Key values:

import pandas as pd
df1 = pd.read_csv('master.csv')
df2 = pd.read_csv('daily.csv')
df3 = df2.merge(df1, left_on='Key', right_on='Key')

This gives you a merged dataframe for all data with matching Keys:

   Name Key      date  value
0   Red   A  dd/mm/yy      1
1  Blue   B  dd/mm/yy      2
2  Pink   C  dd/mm/yy      3

If you want the columns in the order in your question you can just add

df3 = df3[['Name', 'Key', 'value', 'date']]
Improve this answer

1 Comment

Great!! merge option was a good approach, Ive had to do some changes because int64 in my data files but works fine, thanks.
1

i think the most basic answer to your question is given by w3schools.

How you first put the content of your files into dicts is a different story that starts by reading the file. I think i would choose the readline() option and split()ting the gotten strings into key and value pairs for the dict()

Improve this answer

1 Comment

I think your answer match better even to my question, but probably I was wrong formulating the question. Merge option save me time and code but I found well your advise as well. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.