Reformatting a dataframe by matching its row and column index to the column of another dataframe

user_14459677 0
Viewed 74

My goal is to transform the following contents of File1 and File2 columns of my dataframe named 'concatenated':

concatenated

File1                        File2                  Frequency
    
    Cambo_1.csv                 Cambo_2.csv                3
    Cambo_1.csv                 Cambo_3.csv                2
    Cambo_2.csv                 Cambo_4.csv                1
    Cambo_2.csv                 Cambo_5.csv                5

into the following format:

dataframe

           Cambo_1          Cambo_2             Cambo_3         Cambo_4        Cambo_5      
Cambo_1          NA                3                    2              NA             NA
Cambo_2          NA                NA                   NA             1              5
Cambo_3          NA                NA                   NA             NA             NA

The format looks like a correlation table. The only difference is that File1 should appear in the row part of the new dataframe, and File2 on the column part of the dataframe. If they are interchanged, "NA" value will appear. Also, take note that ".csv" is already disregarded on the newly formatted dataframe.

I am new to programming and in python, anyway my code looks like this:

for i in dataframe.iterrows():
if  re.match(dataframe.loc[i,].astype(str))==re.match(concatenated_ans2['0'].astype(str)) and re.match(dataframe.loc[:,i].astype(str))==re.match(concatenated_ans2['1'].astype(str)):
    dataframe.at[rows,columns] = concatenated_ans2['2']
else dataframe.at[rows,columns] = 'NA'

But I got this error:

ValueError: Location based indexing can only have [integer, integer slice (START point is INCLUDED, END point is EXCLUDED), listlike of integers, boolean array] types

Anyone willing to help?

pandas dataframe python
1 Answers

One way to do it would be by crosstabulating the two columns and applying the sum function:

cross = pd.crosstab(df.File1.str.replace(".csv", ''), 
                    df.File2.str.replace(".csv", ''), 
                    values=df.Frequency, 
                    aggfunc='sum')

This gives a result like:

File2   Cambo_2 Cambo_3 Cambo_4 Cambo_5
File1               
Cambo_1 3.0     2.0     NaN     NaN
Cambo_2 NaN     NaN     1.0     5.0

Notice that the index doesn't include Cambo_3, because that value doesn't appear in the column File1 of the example dataframe you provided. Additionally, if you want to remove the col/index names, just also add:

cross.index.name = None
cross.columns.name = None

Finally, you're probably getting that error because df.iterrows() generates a tuple of (row_nr, row_vals), such as:

>> for i in dataframe.iterrows():
>>     print(i)
(0, File1        Cambo_1.csv
File2        Cambo_2.csv
Frequency              3
Name: 0, dtype: object)

The more typical way to iterate like this is for i, r in dataframe, iterrows():... or to just use df.apply(lambda row: {some code}]).

user_103547890
Related