merging tables and combining columns together in python

Question

I have 2 csv files that I merged together based on their code number. Now there are 2 columns for dates because one column is for dates in 2013 and another is for dates in 2014. I'm not sure if it is a thing, but is there a way in pandas or python where I can "append" them into on entire column for just dates?

Csv1

countyCode       Date        AQI
1              2013-01-14    122
6              2013-06-10    60
8              2013-10-20    82

Csv 2

countyCode       Date        AQI
1              2014-02-29    22
6              2014-08-11    41
8              2014-11-06    87

Here is my attempt in merging:

air2013=pd.read_csv("aqi_2013.csv", index_col=0)
air2014=pd.read_csv("aqi_2014.csv", index_col=0)
air2013.merge(air2014,on=['countyCode'])

Output (so far)

countyCode       Date_x      AQI         Date_y        AQI
1              2013-01-14    122         2014-02-29    22
6              2013-06-10    60          2014-08-11    41
8              2013-10-20    82          2014-11-06    87

Overall, is there a way where I can add the values from Date_y to Date_x so there is just one Date column?

Your *output so far* is not really your output isn't it? There should be `_x` and `_y` after `AQI` as well. Plus, values in `Date_x` should be `2013-...`? — Quang Hoang, Oct 30 '20 at 21:00

score 0 · Answer 1 · answered Oct 30 '20 at 21:16

When you read_csv and specify the index_col=0, it treats the countyCode column values as unique identifiers. But they do not appear to be unique, as they are repeated in both dataframes, so I don't think you want these to be your index in a merged dataframe. Try this instead:

import pandas as pd

air2013=pd.read_csv("aqi_2013.csv")
air2014=pd.read_csv("aqi_2014.csv")
air = pd.concat([air2013, air2014])

This gives:

   countyCode       Date        AQI
0   1              2013-01-14    122
1   6              2013-06-10    60
2   8              2013-10-20    82
0   1              2014-02-29    22
1   6              2014-08-11    41
2   8              2014-11-06    87

score 0 · Answer 2 · answered Oct 30 '20 at 21:50

Csv1.append(Csv2, ignore_index=True)



countyCode        Date  AQI
0           1  2013-01-14  122
1           6  2013-06-10   60
2           8  2013-10-20   82
3           1  2014-02-29   22
4           6  2014-08-11   41
5           8  2014-11-06   87

merging tables and combining columns together in python

2 Answers2