Python CSV joining columns

Question

I am trying to make a new columnn with conditional statements utilizing Pandas Version 0.17.1. I have two csv's both about 100mb's in size.
What I have:

CSV1:

    Index        TC_NUM
    1241        1105.0017
    1242        1105.0018
    1243        1105.0019
    1244        1105.002
    1245        1105.0021
    1246        1105.0022

CSV2:

KEYS           TC_NUM
UXS-689     3001.0045
FIT-3015    1135.0027
FIT-2994    1140.0156
FIT-2991    1910, 1942.0001, 3004.0004, 3004.0020, 3004.0026, 3004.0063, 3004.0065, 3004.0079, 3004.0084, 3004.0091, 2101.0015, 2101.0016, 2101.0017, 2101.0018, 2101.0050, 2101.0052, 2101.0054, 2101.0055, 2101.0071, 2101.0074, 2101.0075, 2206.0001, 2103.0001, 2103.0002, 2103.0009, 2103.0011, 3000.0004, 3000.0030, 1927.0020
FIT-2990    2034.0002, 3004.0035, 3004.0084, 2034.0001
FIT-2918    3001.0039, 3004.0042

What I want:

    Index        TC_NUM       Matched_Keys
    1241        1105.0017     FIT-3015
    1242        1105.0018     UXS-668
    1243        1105.0019     FIT-087
    1244        1105.002      FIT-715
    1245        1105.0021     FIT-910
    1246        1105.0022     FIT-219

If the TC_NUM in CSV2 matches the TC_NUM from CSV1, it prints the key in a column on CSV1

Code:

dftakecolumns = pd.read_csv('JiraKeysEnv.csv')
dfmergehere = pd.read_csv('output2.csv')
s = dftakecolumns['KEYS']
a = dftakecolumns['TC_NUM']
d = dfmergehere['TC_NUM']


for crows in a:
    for toes in d: 
        if toes == crows:
            print toes
            dfmergehere['Matched_Keys'] = dftakecolumns.apply(toes, axis=None, join_axis=None, join='outer')

what is type of column TC_NUM of CSV2? print type(dftakecolumns.at[0,'TC_NUM']) — jezrael
– jezrael, Commented Feb 24, 2016 at 21:11
ValueError: At based indexing on an non-integer index can only have non-integer indexers — Anekdotin
– Anekdotin, Commented Feb 24, 2016 at 21:17
sorry, try print type(dftakecolumns.iat[0,0]) and print type(dftakecolumns.iat[0,1]) — jezrael
– jezrael, Commented Feb 24, 2016 at 21:24
File "/media/logic/B201-8C7E/CsvTasker/csvMake.py", line 48, in processcolumns print type(dftakecolumns.at['FIT-3015','TC_NUM']) File "/usr/local/lib/python2.7/dist-packages/pandas/core/indexing.py", line 1554, in getitem key = self._convert_key(key) File "/usr/local/lib/python2.7/dist-packages/pandas/core/indexing.py", line 1589, in _convert_key raise ValueError("At based indexing on an integer index can only have integer " ValueError: At based indexing on an integer index can only have integer indexers — Anekdotin
– Anekdotin, Commented Feb 24, 2016 at 21:26

jezrael · Accepted Answer · 2016-02-24 21:55:42Z

You can try this solution:

Notice - I change value in first (1105.0017) and fourth (1105.0022) row of df2 for test of merge.

print df1
   Index     TC_NUM
0   1241  1105.0017
1   1242  1105.0018
2   1243  1105.0019
3   1244  1105.0020
4   1245  1105.0021
5   1246  1105.0022

print df2
       KEYS                                             TC_NUM
0   UXS-689                                          1105.0017
1  FIT-3015                                          1135.0027
2  FIT-2994                                          1140.0156
3  FIT-2991  1105.0022, 1942.0001, 3004.0004, 3004.0020, 30...
4  FIT-2990         2034.0002, 3004.0035, 3004.0084, 2034.0001
5  FIT-2918                               3001.0039, 3004.0042

#convert string column TC_NUM to dataframe df3
df3 = pd.DataFrame([ x.split(',') for x in df2['TC_NUM'].tolist() ])
#convert string df3 to float df3
df3 = df3.astype(float)

print df3
          0          1          2          3          4          5   \
0  1105.0017        NaN        NaN        NaN        NaN        NaN   
1  1135.0027        NaN        NaN        NaN        NaN        NaN   
2  1140.0156        NaN        NaN        NaN        NaN        NaN   
3  1105.0022  1942.0001  3004.0004  3004.0020  3004.0026  3004.0063   
4  2034.0002  3004.0035  3004.0084  2034.0001        NaN        NaN   
5  3001.0039  3004.0042        NaN        NaN        NaN        NaN   

          6          7          8          9     ...            19         20  \
0        NaN        NaN        NaN        NaN    ...           NaN        NaN   
1        NaN        NaN        NaN        NaN    ...           NaN        NaN   
2        NaN        NaN        NaN        NaN    ...           NaN        NaN   
3  3004.0065  3004.0079  3004.0084  3004.0091    ...     2101.0074  2101.0075   
4        NaN        NaN        NaN        NaN    ...           NaN        NaN   
5        NaN        NaN        NaN        NaN    ...           NaN        NaN   

          21         22         23         24         25         26        27  \
0        NaN        NaN        NaN        NaN        NaN        NaN       NaN   
1        NaN        NaN        NaN        NaN        NaN        NaN       NaN   
2        NaN        NaN        NaN        NaN        NaN        NaN       NaN   
3  2206.0001  2103.0001  2103.0002  2103.0009  2103.0011  3000.0004  3000.003   
4        NaN        NaN        NaN        NaN        NaN        NaN       NaN   
5        NaN        NaN        NaN        NaN        NaN        NaN       NaN   

         28  
0       NaN  
1       NaN  
2       NaN  
3  1927.002  
4       NaN  
5       NaN  

[6 rows x 29 columns]

#concat column KEYS to df3
df2 = pd.concat([df2['KEYS'], df3], axis=1)

#stack - rows to one column for merging
df2 = df2.set_index('KEYS').stack().reset_index(level=1,drop=True).reset_index(name='TC_NUM')

print df2
        KEYS     TC_NUM
0    UXS-689  1105.0017
1   FIT-3015  1135.0027
2   FIT-2994  1140.0156
3   FIT-2991  1105.0022
4   FIT-2991  1942.0001
5   FIT-2991  3004.0004
6   FIT-2991  3004.0020
7   FIT-2991  3004.0026
8   FIT-2991  3004.0063
9   FIT-2991  3004.0065
10  FIT-2991  3004.0079
11  FIT-2991  3004.0084
12  FIT-2991  3004.0091
13  FIT-2991  2101.0015
14  FIT-2991  2101.0016
15  FIT-2991  2101.0017
16  FIT-2991  2101.0018
17  FIT-2991  2101.0050
18  FIT-2991  2101.0052
19  FIT-2991  2101.0054
20  FIT-2991  2101.0055
21  FIT-2991  2101.0071
22  FIT-2991  2101.0074
23  FIT-2991  2101.0075
24  FIT-2991  2206.0001
25  FIT-2991  2103.0001
26  FIT-2991  2103.0002
27  FIT-2991  2103.0009
28  FIT-2991  2103.0011
29  FIT-2991  3000.0004
30  FIT-2991  3000.0030
31  FIT-2991  1927.0020
32  FIT-2990  2034.0002
33  FIT-2990  3004.0035
34  FIT-2990  3004.0084
35  FIT-2990  2034.0001
36  FIT-2918  3001.0039
37  FIT-2918  3004.0042

#merge on column TC_NUM
print pd.merge(df1, df2, on=['TC_NUM'])
   Index     TC_NUM      KEYS
0   1241  1105.0017   UXS-689
1   1246  1105.0022  FIT-2991

Collectives™ on Stack Overflow

Python CSV joining columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related