new dataframe using values in existing dataframe

user_2781402 0
Viewed 81
exdf = pd.DataFrame({'Employee name': ['Alex','Mike'],
 '2014.1': [5, 2], '2014.2': [3, 4], '2014.3': [3, 6], '2014.4': [4, 3], '2015.1': [7, 5], '2015.2': [5, 4]})
exdf


  Employee name  2014.1  2014.2  2014.3  2014.4  2015.1  2015.2
0          Alex       5       3       3       4       7       5
1          Mike       2       4       6       3       5       4

Suppose the above dataframe has several such rows and columns with output from each employee for each quarter. I want to create a new dataframe with columns:

newdf=pd.Dataframe(columns=['Employee name','Year','Quarter','Output'])

So, new dataframe will have nxm rows where n and m are rows and columns in original dataframe. What I have tried is filling every row and column entry using nested for loop.

But I'm sure there is a more efficient method.

for i in range(df.shape[0]):
    for j in range(df.shape[1]):
        newdf.iloc[?]=exdf.iloc[?]
melt pandas dataframe python
2 Answers

Use DataFrame.melt with Series.str.split, last change order of columns:

df = exdf.melt('Employee name', var_name='Year', value_name='Output')
df[['Year', 'Quarter']] = df['Year'].str.split('.', expand=True)
df = df[['Employee name','Year','Quarter','Output']]
print (df)
   Employee name  Year Quarter  Output
0           Alex  2014       1       5
1           Mike  2014       1       2
2           Alex  2014       2       3
3           Mike  2014       2       4
4           Alex  2014       3       3
5           Mike  2014       3       6
6           Alex  2014       4       4
7           Mike  2014       4       3
8           Alex  2015       1       7
9           Mike  2015       1       5
10          Alex  2015       2       5
11          Mike  2015       2       4
user_29010020

Convert the columns to a multiIndex, using str.split, then you stack the columns to get ur final output

#set Employee name as index
exdf = exdf.set_index('Employee name')

#convert columns to multiIndex
exdf.columns = exdf.columns.str.split('.',expand = True)
exdf.columns = exdf.columns.set_names(['year','quarter'])

#stack data and give column a name
(exdf
 .stack(["year","quarter"])
 .reset_index(name='output')
)


Employee name   year    quarter output
0   Alex        2014       1    5.0
1   Alex        2014       2    3.0
2   Alex        2014       3    3.0
3   Alex        2014       4    4.0
4   Alex        2015       1    7.0
5   Alex        2015       2    5.0
6   Mike        2014       1    2.0
7   Mike        2014       2    4.0
8   Mike        2014       3    6.0
9   Mike        2014       4    3.0
10  Mike        2015       1    5.0
11  Mike        2015       2    4.0

With pivot_longer the reshaping can be abstracted to a simpler form:

# pip install pyjanitor
import janitor
import pandas as pd

```py
exdf.pivot_longer(
    index="Employee name", 
    names_to=("Year", "Quarter"), 
    names_sep=".", 
    values_to="Output"
    )

   Employee name  Year Quarter  Output
0           Alex  2014       1       5
1           Mike  2014       1       2
2           Alex  2014       2       3
3           Mike  2014       2       4
4           Alex  2014       3       3
5           Mike  2014       3       6
6           Alex  2014       4       4
7           Mike  2014       4       3
8           Alex  2015       1       7
9           Mike  2015       1       5
10          Alex  2015       2       5
11          Mike  2015       2       4
user_71757130
Related