Python pandas convert csv file into wide long txt file and put the values that have the same name in the "MA" column in the same row

Question

I want to get a file from the csv file formatted as follows: CSV file:

Desired output txt file (Header italicized):

MA Am1 Am2 Am3 Am4

MX1 X Y - -

MX2 9 10 11 12

Any suggestions on how to do this? Thank you!

Need help with writing the python code for achieving this. I've tried to loop through every row, but still struggling to find a way to write this.

score 0 · Answer 1 · answered Mar 28 '22 at 03:57

0

You can try this.

Based on unique MA value groups, get the values [names column here]
Create a new dataframe with it.
Expand the values list to columns and add it to new dataframe.
Copy name column from first data frame.
Reorder 'name' column.

Code:

import pandas as pd
df = pd.DataFrame([['MX1', 1, 222],['MX1', 2, 222],['MX2', 4, 44],['MX2', 3, 222],['MX2', 5, 222]], columns=['name','values','etc'])

df_new = pd.DataFrame(columns = ['name', 'values'])
for group in df.groupby('name'):    
    df_new.loc[-1] = [group[0], group[1]['values'].to_list()]
    df_new.index = df_new.index + 1 
    df_new = df_new.sort_index() 

df_expanded = pd.DataFrame(df_new['values'].values.tolist()).add_prefix('Am')
df_expanded['name'] = df_new['name']

cols = df_expanded.columns.tolist()
cols = cols[-1:] + cols[:-1]
df_expanded = df_expanded[cols]

print(df_expanded.fillna('-'))

Output:

  name  Am0  Am1  Am2
0  MX2    4    3  5.0
1  MX1    1    2    -

answered Mar 28 '22 at 03:57

Manjunath K Mayya

1,078
1
11
20

Thank you! If there are many rows in the csv file, is there an easier way to do step 1? – grapefruiticecoffee Mar 28 '22 at 10:48
@Abby: You can refer this link and try. It talks about transform. It should help. https://stackoverflow.com/questions/27298178/concatenate-strings-from-several-rows-using-pandas-groupby – Manjunath K Mayya Mar 28 '22 at 12:49
That worked, thank you! I was wondering, if there is another column before the name column from the input .csv file, is there a way to transfer that column to the output file? – grapefruiticecoffee Mar 29 '22 at 16:08
@Abby: If this answered your question, I would appreciate if you mark this as answer. – Manjunath K Mayya Mar 29 '22 at 17:06

Python pandas convert csv file into wide long txt file and put the values that have the same name in the "MA" column in the same row

1 Answers1