Merging two CSV files using Python

by

When I’m working with csv files, I often use the pandas library. It makes things like this very easy. For example:

import pandas as pd

a = pd.read_csv("filea.csv")
b = pd.read_csv("fileb.csv")
b = b.dropna(axis=1)
merged = a.merge(b, on='title')
merged.to_csv("output.csv", index=False)

Some explanation follows. First, we read in the csv files:

>>> a = pd.read_csv("filea.csv")
>>> b = pd.read_csv("fileb.csv")
>>> a
   title  stage    jan    feb
0   darn  3.001  0.421  0.532
1     ok  2.829  1.036  0.751
2  three  1.115  1.146  2.921
>>> b
   title    mar    apr    may       jun  Unnamed: 5
0   darn  0.631  1.321  0.951    1.7510         NaN
1     ok  1.001  0.247  2.456    0.3216         NaN
2  three  0.285  1.283  0.924  956.0000         NaN

and we see there’s an extra column of data (note that the first line of fileb.csv — title,mar,apr,may,jun, — has an extra comma at the end). We can get rid of that easily enough:

>>> b = b.dropna(axis=1)
>>> b
   title    mar    apr    may       jun
0   darn  0.631  1.321  0.951    1.7510
1     ok  1.001  0.247  2.456    0.3216
2  three  0.285  1.283  0.924  956.0000

Now we can merge a and b on the title column:

>>> merged = a.merge(b, on='title')
>>> merged
   title  stage    jan    feb    mar    apr    may       jun
0   darn  3.001  0.421  0.532  0.631  1.321  0.951    1.7510
1     ok  2.829  1.036  0.751  1.001  0.247  2.456    0.3216
2  three  1.115  1.146  2.921  0.285  1.283  0.924  956.0000

and finally write this out:

>>> merged.to_csv("output.csv", index=False)

producing:

title,stage,jan,feb,mar,apr,may,jun
darn,3.001,0.421,0.532,0.631,1.321,0.951,1.751
ok,2.829,1.036,0.751,1.001,0.247,2.456,0.3216
three,1.115,1.146,2.921,0.285,1.283,0.924,956.0

More Related Contents:

  1. Connecting data in python to spreadsheets
  2. How do I merge two dictionaries in a single expression (take union of dictionaries)?
  3. How to merge multiple dicts with same key or different key?
  4. Return a default value if a dictionary key is not available
  5. Dictionary: Get list of values for list of keys
  6. How to get a random value from dictionary?
  7. merging several python dictionaries
  8. How do I convert this list of dictionaries to a csv file?
  9. Writing a dictionary to a csv file with one line for every ‘key: value’
  10. How to add multiple values to a dictionary key in python?
  11. Creating a dictionary from a CSV file
  12. merging Python dictionaries
  13. How to add multiple values to a dictionary key?
  14. How to print a dictionary’s key?
  15. Python Dictionary to CSV
  16. switching keys and values in a dictionary in python [duplicate]
  17. Accessing dict_keys element by index in Python3
  18. Float values as dictionary key
  19. Python read csv – BOM embedded into the first key
  20. How to merge dicts, collecting values from matching keys?
  21. Merge several Python dictionaries
  22. How can I get a random key-value pair from a dictionary?
  23. How can I merge 200 CSV files in Python?
  24. convert csv file to list of dictionaries
  25. How can I get dictionary key as variable directly in Python (not by searching from value)?
  26. Why does CSV file contain a blank line in between each data line when outputting with Dictwriter in Python [duplicate]
  27. merging dictionaries in ansible
  28. Converting a csv file into a list of tuples with python
  29. Why must dictionary keys be immutable?
  30. Python: Writing Nested Dictionary to CSV

Leave a Comment