Adding Columns to Dataframe having different Row Counts

Question

I am trying to read a file and have the distinct values of each column in a dataframe having similar column names.

File has say 3 columns

EMP ID  DEPT    Salary
=============================
100 Sales   10000
200 MFG 10000
300 IT  10000
400 Sales   10000
500 MFG 10000
600 IT  10000

Expected Output

EMP ID  DEPT    Salary
========================
100 Sales   10000
200 MFG 
300 IT  
400     
500     
600

I have read the file, got the list of unique values as below

df=pd.read_csv('C:/Users/jaiveeru/Downloads/run_test1.csv')
cols=df.columns.tolist()
df1=pd.DataFrame()
df2=pd.DataFrame()
for i in cols:
    lst=df[i].unique().tolist()
    str1 = ','.join(lst)
    lst2=[str1]
    df1[i]=lst2
    df2=pd.concat([df2,df1])

However as each column can have different number of unique values I am getting the below error

ValueError: Length of values does not match length of index

perl · Accepted Answer · 2019-03-27 19:01:06Z

2

There may be two different versions, depending on how exactly you want to treat unique values. I've set Salary of EMPID=400 to 20,000 to illustrate this.

Using the duplicated method on columns:

df[df.apply(lambda x: x.duplicated())] = np.nan
df = df.fillna('')

print(df)

Output:

   EMPID   DEPT Salary
0    100  Sales  10000
1    200    MFG       
2    300     IT       
3    400         20000
4    500              
5    600

Alternatively, if you don't want to preserve the indices of these unique values, but instead want to collect them at the top of each column:

df = pd.DataFrame(
    [df[x].unique() for x in df.columns],
    index=df.columns).T.fillna('')

print(df)

Output:

   EMPID   DEPT Salary
0  100.0  Sales  10000
1  200.0    MFG  20000
2  300.0     IT       
3  400.0              
4  500.0              
5  600.0

edited Mar 27, 2019 at 19:01

answered Mar 27, 2019 at 18:40

perl

9,9811 gold badge14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding Columns to Dataframe having different Row Counts

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