Pandas sort columns and find difference

Question

I have a dataframe and I wanted to sort it so column a == columnb. If there is no match then it puts it into column C

My data looks like

filenamesLocal          FilenamesServer
  filea.csv                  fileab.csv
  filec.csv                  filea.csv
  fileab.csv                 filec.csv
  filexyz.csv
  fileyh.csv

I want them sorted where filenamesLocal = FilenamesServer with remainder in another column..

filenamesLocal          FilenamesServer        Difference
  filea.csv                  filea.csv         filexyz.csv    
  filec.csv                  filec.csv          fileyh.csv
  fileab.csv                 fileab.csv

My code so far ..

    ldsdata = pd.read_csv('filelist.csv', sep=" ", header = None)
    #data.to_csv("filelist.csv", index=False)
    dataproj = pd.read_csv('edslist.txt', sep=" ", header = None)
    dataproj.columns = ["fileNameEdsComputer"]
    result = pd.concat([ldsdata, dataproj], axis=1, ignore_index=True)
    result.columns = ['fileNameLDS', path]
    result.sort(['fileNameLDS',path], ascending=[True, False], inplace=True)
    result.to_csv('list.csv', index=False)
    checkDifferences()

score 1 · Accepted Answer · answered Apr 28 '16 at 19:36

Setup

import pandas as pd
from StringIO import StringIO

text="""filenamesLocal          FilenamesServer
  filea.csv                  fileab.csv
  filec.csv                  filea.csv
  fileab.csv                 filec.csv
  filexyz.csv
  fileyh.csv"""

df = pd.read_csv(StringIO(text), delim_whitespace=True)

fnl = df.iloc[:, [0]].set_index(['filenamesLocal'], drop=False).dropna()
fns = df.iloc[:, [1]].set_index(['FilenamesServer'], drop=False).dropna()

print fnl

              filenamesLocal
filenamesLocal               
filea.csv           filea.csv
filec.csv           filec.csv
fileab.csv         fileab.csv
filexyz.csv       filexyz.csv
fileyh.csv         fileyh.csv

print fns

                FilenamesServer
FilenamesServer                
fileab.csv           fileab.csv
filea.csv             filea.csv
filec.csv             filec.csv

Align fnl and fns

aligned = pd.concat([fnl, fns], axis=1)

print aligned

            filenamesLocal FilenamesServer
filea.csv        filea.csv       filea.csv
fileab.csv      fileab.csv      fileab.csv
filec.csv        filec.csv       filec.csv
filexyz.csv    filexyz.csv             NaN
fileyh.csv      fileyh.csv             NaN

master = aligned.filenamesLocal.combine_first(aligned.FilenamesServer)

print master

filea.csv        filea.csv
fileab.csv      fileab.csv
filec.csv        filec.csv
filexyz.csv    filexyz.csv
fileyh.csv      fileyh.csv
Name: filenamesLocal, dtype: object

assign difference

aligned['Difference'] = master[aligned.isnull().any(axis=1)]

print aligned

            filenamesLocal FilenamesServer   Difference
filea.csv        filea.csv       filea.csv    filea.csv
fileab.csv      fileab.csv      fileab.csv   fileab.csv
filec.csv        filec.csv       filec.csv    filec.csv
filexyz.csv    filexyz.csv             NaN  filexyz.csv
fileyh.csv      fileyh.csv             NaN   fileyh.csv

I don't have your csv. I have to create a dataframe somehow in order to test. You should try what I've done then figure out how to apply to your situation. — piRSquared, Apr 28 '16 at 19:53

Pandas sort columns and find difference

1 Answers1

Setup

Linked