Getting a data frame from other data frame

user_16206182 0
Viewed 48

This is my dataframe

     Date       Name  Count
0   Sep-2015    Adam    3
1   Sep-2015    David   3
2   Sep-2015    Adam    4
3   Sep-2015    Adam    3
4   Sep-2015    David   6
5   Sep-2015    Hardik  1
6   Sep-2015    David   3

Now I want to unique Name with respect to Date and enter in other dataframe. I tried this

 df = pd.DataFrame((['Sep-2015',  'Adam',3 ],         
['Sep-2015',  'David',3],
['Sep-2015',  'Adam',4],                   
['Sep-2015',  'Adam' ,3],         
['Sep-2015',  'David',6],         
['Sep-2015' , 'Hardik',1],        
['Sep-2015' , 'David' ,3]),columns=['Date','Name','Count'])
df = df.drop_duplicates(['Date', 'Name'], keep='last')
df2 = pd.DataFrame ({i : [j for j in df['Count']] for i in df['Name']},index = [i for i in df['Date']])

I am getting this

         Adam   Hardik  David
Sep-2015    3      3    3
Sep-2015    1      1    1
Sep-2015    3      3    3

Here data entry is somewhere wrong. The following is the expected output.

           Adam   Hardik    David
Sep-2015    3        1       3
dataframe python
3 Answers

After your run your drop_duplicates(), and you are here:

       Date    Name  Count
3  Sep-2015    Adam      3
5  Sep-2015  Hardik      1
6  Sep-2015   David      3

Try using pd.pivot_table() as below:

pd.pivot_table(df1,index='Date',columns='Name',values='Count').reset_index()

Which will give you:

Name      Date  Adam  David  Hardik
0     Sep-2015     3      3       1
user_91673820

What you want to do is a Pivot table. pandas already has a method for that called pd.pivot_table

try:

# import pandas
import pandas as pd


# the example dataframe
df = pd.DataFrame((['Sep-2015',  'Adam',3 ],         
['Sep-2015',  'David',3],
['Sep-2015',  'Adam',4],                   
['Sep-2015',  'Adam' ,3],         
['Sep-2015',  'David',6],         
['Sep-2015' , 'Hardik',1],        
['Sep-2015' , 'David' ,3]),columns=['Date','Name','Count'])

# drop duplicates
df = df.drop_duplicates(['Date', 'Name'], keep='last')

#pivot table
pd.pivot_table(data = df, values = 'Count', columns = 'Name', index = 'Date').reset_index()

Output:

Date      Adam  David  Hardik                         
Sep-2015     3      3       1
user_121850280

The first issue is coming from index=[i for i in df['Date']]. As soon as you are doing this, it will put a line for each component in df['Date'] : you have three times in a row September-2015 so it will create three lines : ['Sep-2015', 'Sep-2015', 'Sep-2015']

The second issue is coming from {i : [j for j in df['Count']] for i in df['Name']} which corresponds to {'Adam': [3, 1, 3], 'Hardik': [3, 1, 3], 'David': [3, 1, 3]}. I guess that is not what you expect.

Assuming your desired output, I guess that what you desire is : {'Adam': [3], 'Hardik': [1], 'David': [3]} and ['Sep-2015']

An idea to achieve it would be to construct it beforehand, here is what I suggest :

a={}
for i in range(df['Name'].size):
    a[df['Name'].iloc[i]]=df['Count'].iloc[i]

dftest=pd.DataFrame (a,index = [i for i in df['Date'].drop_duplicates()])

Of course, the loop may be modified to manage your expectations, specifically if you have different dates in your program.

user_169257650
Related