0

Here is a data set where I need to filter out data from column 'if' to then sort and match based on 'Desc'. But if/Status are linked and need to stay together. Column Desc/ONT is data that needs to stay tied together as well.

if/Status was a single column that I divided into two,(1627662401 up), is it possible to filter out the needed data from C before this stage?

'if' needs filtered to match 'Desc' but keep 'Status' data tied to 'if'

TestMerge.csv
Desc(A)      ONT(B)         if(C)       Status(D)
16292413441  ADTN210784db   25300071    down
16292423681  ADTN21078135   25300072    down
16292433921  ADTN210780ef   25300200    up
16292444161  ADTN2039e094   25300201    up
16292454401  ADTN201242db   1627653120  up
16292464641  ADTN2019306c   1627653185  up

This is what I used to create the data set.

import pandas as pd


df_1 = pd.read_csv('TEST_ifDescr.csv')

df_2 = pd.read_csv('WG_ifOperStatus.csv')

frames = [df_1, df_2]

df = pd.concat(frames, sort=False, axis=1)

df.to_csv('TestMerge.csv', index=False)`

Desired output would be:

16292413441  ADTN210784db   16292413441 down
16292423681  ADTN21078135   16292423681 down
16292433921  ADTN210780ef   16292433921 up
16292444161  ADTN2039e094   16292444161 up
16292454401  ADTN201242db   16292454401 up

To make this more clear, I need to remove any value in C that is not in A, but also remove/keep values in that relate to C.

For example I need all values in C like '500004' to be removed, but also remove 'testing' in D on the same row as C.

I'm open for any suggestions. Thank you to anyone who reads and responds!

Nuclear7740
  • 11
  • 4
  • How looks expected ouout from sample input data posted in question? – jezrael Aug 10 '22 at 05:37
  • Don't use screenshots of data. Instead using code fencing to format it. – Nick Aug 10 '22 at 05:41
  • I think instead `concat` need `merge`. – jezrael Aug 10 '22 at 05:48
  • @jezrael Desired output would be Column A and C match, but data in D column stays with C – Nuclear7740 Aug 10 '22 at 05:53
  • yes, I am right, need merge. `df = df_1.merge(df_2, left_on='Desc', right_on='if')` – jezrael Aug 10 '22 at 05:55
  • @Nick thanks for the pointer, I'll stay away from screen shots. – Nuclear7740 Aug 10 '22 at 05:55
  • @jezrael I replaced df = pd.concat(frames, sort=False, axis=1) with your comment. However now the csv has many empty rows from the top for columns Desc/ONT(A/B). I added "axis=1" but received an error. Also, one objective was to remove any value in column if(C) that wasn't in A. But I need to keep C and D rows aligned. Thank you for the engagement in this post! – Nuclear7740 Aug 10 '22 at 06:20
  • It is inner join, how possible `However now the csv has many empty rows from the top for columns Desc/ONT(A/B)` ? – jezrael Aug 10 '22 at 06:31

0 Answers0